Field Says Binary/image, But It's Not..?

[nicmar]

Some rows in my database are called “Binary/Image” and i can't see the actual data.

When I export to Excel it looks like this:

2006-03-20 1505-1535 hos John Doe (Påminn=0)

What's binary about this and why can't I view it in SQLYog?

I'm using 5.1 Beta 6 currently..

[peterlaursen]

this term “Binary/Image” is not very precise any more. It means that it is a datatype that cannot be displayed in the grid for one of the reasons:

1) It is binary data, that cannot be represented in a meaningful way using characters

2) It is too long to 'fit in'

1) is BLOBs (used for images, media files etc)

2) is TEXTs (used for character data too long to be char or varchar)

Plan for the future include:

“Enhancements to the BLOB-viewer: BLOB-viewer should be resizable and there should be an option to display binary data in binary/hex view (like common hex-editors and the Windows Regedit program). Also support for the MySQL version 5 BIT datatype should be implemented. Finally it also should be considered to use (could be optional) the BLOB-viewer for very big VARCHARs. This is actualized by MySQL version 5, that allows for VARCHAR size of 65000+ characters.”

I think you may have a TEXT column and not a CHAR/VARCHAR column?

[Ritesh]

Quote:

2006-03-20 1505-1535 hos John Doe (Påminn=0)

If you see there is an extra in the data which means an extra NULL character in the data. You will need to strip that out. SQLyog identifies whether a data is BINARY or not by checking for a NULL character till the length sent by MySQL and there is indeed a NULL. I dont know how it came but once you remove it, it will solve the issue.

[peterlaursen]

aha .. Ritesh caught the point here .. 🙂

Now even I understand the question!

I'll add an example:

You can export .sql file, edit it and import. Have a look at attached file.

The SQL-export for this looks like:

Code:

/*
SQLyog Enterprise – MySQL GUI v5.12 BETA
Host – 5.1.9-beta-log : Database – test
*********************************************************************
Server version : 5.1.9-beta-log
*/

SET NAMES utf8;

SET SQL_MODE='';

create database if not exists `test`;

USE `test`;

/*Table structure for table `tablename1` */

DROP TABLE IF EXISTS `tablename1`;

CREATE TABLE `tablename1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`t` varchar(50) DEFAULT NULL,
`vb` varbinary(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `tablename1` */

insert into `tablename1` (`id`,`t`,`vb`) values (1,'hos John Doe (PÃ¥minn=0)\0','hos John Doe (Påminn=0)');

Note the difference between ' ' and '' in the SQL-file for the two columns

First column has an escaped followed by ''. This is valid for ''. '' alone implies that data are binary (not character data)

[Al Jey]

'Ritesh' wrote on '26:

If you see there is an extra in the data which means an extra NULL character in the data. You will need to strip that out. SQLyog identifies whether a data is BINARY or not by checking for a NULL character till the length sent by MySQL and there is indeed a NULL. I dont know how it came but once you remove it, it will solve the issue.

Ritesh,

You rock dude, you just saved my life (and I mean it), TRIM(TRAILING '' FROM columnName) totally did the trick for me; I've been fiddling with the damn thing forever… 😀 😀 😀

[Author]

Posts