HeidiSQL transforms BLOB hex values breaking them for display & SQL export (Japanese system locale!)

Hello there

HeidiSQL seems to transform binary data inside BLOB columns before displaying them or processing them for SQL exporting. This breaks all kinds of things here :-(

The windows code page used on the system here is 932 which is Japanese Shift-Jis using double byte encoding for certain character ranges.
Here is some test SQL and the result I am getting here which should make things pretty clear.

DROP TABLE IF EXISTS blobtesttable;
CREATE TABLE blobtesttable (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, data BLOB NOT NULL, PRIMARY KEY (id)) COLLATE='ascii_bin' ENGINE=InnoDB;
INSERT INTO blobtesttable (data) VALUES (0x80),(0x81),(0x82),(0x83),(0x84),(0x8400),(0x8401),(0x84F0),(0x84FF);
SELECT id, data, hex(data) FROM blobtesttable

id;HEX(data);hex(data)
1;0x80;80
2;0x8145;81
3;0x8145;82
4;0x8145;83
5;0x8145;84
6;0x814500;8400
7;0x8145;8401
8;0x8145;84F0
9;0x8145;84FF

Neither way, thanks for this amazing tool,
Bernhard in Tokyo

That doesn't look like the "Export database as SQL" feature. I suppose it's the "Export grid rows" one with the "Excel CSV" or "Delimited text" output format.

Whatever, I cannot reproduce with latest stable release, only in recent nightlies, and I can't speak for the developer but I have the impression that it's intentional. I mean, it's binary data after all. What sense does it make to convert it into text having no clue of what the data represents?

I don't know what's breaking on your side but if you're using CSV to export binary data, getting an hexadecimal dump is the only reliable way to do it.

If I totally misunderstood your, please put me on the right track.

Hi there,

As I mentioned, my Windows 7 system is running under codepage 932 (Japanese) which is a double byte encoding for certain character ranges. If you check http://en.wikipedia.org/wiki/Shift_JIS you'll see that characters starting at 0x81 are the first byte of a double-byte character.

Now my problem is that HeidiSQL somehow converts pure binary data stored inside a BLOB field while processing them for any output (either on screen, per SELECT query, or with the "Export database as SQL" feature.

You can see the output of Export database as SQL below. Showing the same problematic result:

-- --------------------------------------------------------

-- Host:                         127.0.0.1

-- Server version:               5.6.15 - MySQL Community Server (GPL)

-- Server OS:                    Linux

-- HeidiSQL Version:             9.1.0.4867

-- --------------------------------------------------------


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- Dumping structure for table acguy_local_common.blobtesttable

CREATE TABLE IF NOT EXISTS `blobtesttable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=ascii COLLATE=ascii_bin;
-- Dumping data for table acguy_local_common.blobtesttable: ~9 rows (approximately)

/*!40000 ALTER TABLE `blobtesttable` DISABLE KEYS */;
INSERT INTO `blobtesttable` (`id`, `data`) VALUES
(1, _binary 0x80),
(2, _binary 0x8145),
(3, _binary 0x8145),
(4, _binary 0x8145),
(5, _binary 0x8145),
(6, _binary 0x814500),
(7, _binary 0x8145),
(8, _binary 0x8145),
(9, _binary 0x8145);
/*!40000 ALTER TABLE `blobtesttable` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

For instance, inserting a single byte of binary data "0x81" into the table, HeidiSQL thinks that it has two bytes with "0x8145" inside that field.
Even though when using "SELECT hex(data) ..." (pasted into the previous post) clearly shows the original true 0x81 that was inserted and is stored in the table. Obviously using hex(data) in the query makes the server do the binary-to-string conversion and not HeidiSQL, so there is no chance of the HeidiSQL client doing any further transformation on it.

What breaks here is that exported BLOB data (be it copying from one database to another, or as a backup into a file) does not copy the binary data as it is stored in the database, it stores a corrupted form that has more bytes added to the original binary data. Using "mysqldump --hex-blob" on the server machine directly does no transformation/corruption.

I did some more checks and it seems that everything marked as "first byte of a double-byte JIS X 0208 character" on the Shift JIS byte map on Wikipedia gets converted to 0x8145 in HeidiSQL running here.

I am quite sure that its not something actively done by HeidiSQL, I assume its some kind of component that relies on the system codepage to do data processing that is wrongfully used for pure binary data.

So it would be a bug that does not show on European or American Windows installations. Windows in 2014 is still a huge mess when it comes to Unicode support. It's hilarious and sad...

Bernhard

There is a bug with HeidiSQL. The problem occurs on windows only, you can fix it by using the "--hex-blob" option. But in heidisql export menu you can't specify it.
e.g.,
mysqldump -u xxx -p xxx --hex-blob >
d:\temp\log\test6.sql
Enter password: ******

then import works fine (both in heidisql and using mysql command line).