Blob column displayed wrong and exported wrong

I have a blob column with value
0xb8ffffffff1a7f7fa421a621a522...
(using phpMyAdmin) but in HeidiSQL i got 0x3FFFFFFF1A7F7F3F3F3F3FA6683F3FA...

actually all blob column has the same problem.

Is this a bug in HeidiSQL or there have some program options to fix this problem?

I have no clue, could also be a bug in pma, but I recall I also handled such wrong strings in HeidiSQL, so I'm unsure which of both clients it is.

just create a table
CREATE TABLE IF NOT EXISTS `tb_test_blob` (
`id` int(11) NOT NULL,
`img` blob,
PRIMARY KEY (`id`)
);

then insert bytes of heidisql_logo.png (10,885 bytes) into img column.

usng HeidiSQL to export the table, the img column is only 9.945 bytes and with wrong bytes after 61 byte.

using phpMyAdmin to export the table, the img column is exactly same as heidisql_logo.png.

Which revision of HeidiSQL is it on your side?

I just tested with the latest HeidiSQL build, with both export dialogs:
* SQL export/table tools: exporting and reimporting of heidisql_logo.png works right. Checked with HeidiSQL's image preview - I expect that preview to show some broken stuff when the contents are broken or cut. But I see the whole logo image, just without the transparency, as the preview component does not handle PNG transparency, however, but the file is not broken.
* Export grid rows: Also, no broken image here when exporting and reimporting.

In all cases I have a length of 10885 bytes in the img column.

Ah, worth to mention: The blob value here starts with "0x89504E470D0A1A0A0000000D4948445200...", which is different to your both values from HS and PMA.

I have the same issue on long blob field. When export the long blob value to other table. the value is different from the original value.
Is there any update on this issue?

My HeidiSQL version is 8.0.0.4396
The table which contains longblob column set up with engine InnoDB and default collation utf8_general_ci. Maybe this information can help for reproduce this issue.

The update on this issue was my above reply - it's not reproducible for me. Also, I did not get a reply to my question about zufuliu's HeidiSQL revision. Well...

You have a LONGBLOB column with a utf8_general_ci collation? That can't be true, as that's an invalid collation for the binary charset. Please look into your "SHOW FULL COLUMNS FROM yourtable" results.

Ah, you meant the default collation for the table. Ok, but why not post the CREATE TABLE code here.

Thanks for reply.

Here is the create table code:
CREATE TABLE IF NOT EXISTS `attachments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`description` varchar(4096) DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modify` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`figure` tinyint(1) DEFAULT NULL,
`content` longblob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

I cannot reproduce either.

I get this when I run this code:

SQL Error (1067): Invalid default value for 'create'

Other than that, if I remove the date columns I've been able to:

- Load a picture using the BLOB editor
- Export and import as SQL

The SQL dump starts with:

_binary 0x47494638

... and that's what the grid shows and what the file contains according to my hex editor.

(Using HeidiSQL 8.0.0.4474)

Hi kalvaro,

It seems the CURRENT_TIMESTAMP directive is introduced from 5.6.
My database version is 5.6.12. There is no problem on my side to create the table with above SQL statement.

no, see http://dev.mysql.com/doc/refman/4.1/en/timestamp.html

I just download the lastest HeidiSQL, the blob display bug not fixed yet.
from mysql command line client:
mysql> select hex(infraredValue) from tb_infrared where id='00006c6fb0d14d749225
c94df0531ce8';
+-------------------------------------------------------------------------------
---------------------------------------------------------------------+
| hex(infraredValue)
|
+-------------------------------------------------------------------------------
---------------------------------------------------------------------+
| FFFFFFFFB87F7F1BA468A422A422A422A422A422A422A422A468A422A469A468A469A468A468A4
69A422A468A468A468A422A422A468A468A469A422A422A422A468A469A422A422A4 |
+-------------------------------------------------------------------------------
---------------------------------------------------------------------+
1 row in set (0.03 sec)

however, in HeidiSQL, I displays
0xFFFFFFFF3F7F1BA4683F3F3F3F3F3F3FA4683FA469A468A469A468A468A4693FA468A468A4683F3
FA468A468A4693F3F3FA468A4693F3F00

It's very strange, if I type above select cmd in HeidiSQL's Query box, the result is
FFFFFFFFB87F7F1BA468A422A422A422A422A422A422A422A468A422A469A468A469A468A468A469A422...(shortened)
it's same as mysql.exe's output.

when click on a table name to see it's data, I see HeidiSQL's log, it was select LEFT(`infraredValue`, 256), but not HEX(LEFT(`infraredValue`, 256)).

jruv meant, CURRENT_TIMESTAMP for *datetime* columns were introduced later, in 5.6.

thx, not read the doc carefully.