distal-attribute
distal-attribute
distal-attribute
distal-attribute

Blob column displayed wrong and exported wrong

User, date Message
Written by zufuliu
11 months ago
Category: General
11 posts since Fri, 31 May 13
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?
Written by ansgar
11 months ago
4801 posts since Fri, 07 Apr 06
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.
Written by zufuliu
11 months ago
11 posts since Fri, 31 May 13
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.



Written by ansgar
11 months ago
4801 posts since Fri, 07 Apr 06
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.
Written by ansgar
11 months ago
4801 posts since Fri, 07 Apr 06
Ah, worth to mention: The blob value here starts with "0x89504E470D0A1A0A0000000D4948445200...", which is different to your both values from HS and PMA.
Written by jruv
8 months ago
4 posts since Fri, 09 Aug 13
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?
Written by jruv
8 months ago
4 posts since Fri, 09 Aug 13
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.
Written by ansgar
8 months ago
4801 posts since Fri, 07 Apr 06
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.
Written by ansgar
8 months ago
4801 posts since Fri, 07 Apr 06
Ah, you meant the default collation for the table. Ok, but why not post the CREATE TABLE code here.
Written by jruv
8 months ago
4 posts since Fri, 09 Aug 13
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;
Written by kalvaro
8 months ago
564 posts since Thu, 29 Nov 07
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)
Written by jruv
8 months ago
4 posts since Fri, 09 Aug 13
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.


Written by zufuliu
8 months ago
11 posts since Fri, 31 May 13
no, see http://dev.mysql.com/doc/refman/4.1/en/timestamp.html
Written by zufuliu
8 months ago
11 posts since Fri, 31 May 13
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
Written by zufuliu
8 months ago
11 posts since Fri, 31 May 13
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.
surprised
Written by zufuliu
8 months ago
11 posts since Fri, 31 May 13
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)).
Written by ansgar
8 months ago
4801 posts since Fri, 07 Apr 06
jruv meant, CURRENT_TIMESTAMP for *datetime* columns were introduced later, in 5.6.
Written by zufuliu
8 months ago
11 posts since Fri, 31 May 13
thx, not read the doc carefully.
 

Please login to leave a reply, or register at first.