Menu
▾
▴
#2399 (ok 2.11) Wrong export of binary character fields
For example, a latin1_bin VARCHAR gets exported with hex representation, but when this is imported back, the data is corrupted.
I have the start of a solution.
In my test, the generated file is
insert into t1 values (0xc3a9636f6c65);
but this format works:
insert into t1 values (convert(0xc3a9636f6c65 using utf8));
Logged In: YES
user_id=1383652
Originator: NO
ok,
if you export a table with a varchar field (charset=latin1_bin) and import the dump into a different db now (after your "fix") you get an idetical table. But why do you need bin2hex($row[$j]) here at all? If you inspect how mysql stores the data you will see it's pretty much the same like any other latin1 column (i *think* the difference only comes into place when compare/sort is used). If you use the mysqldump CLI you get something like this:
INSERT INTO `MediaWiki` VALUES (4,'Danish_proverbs','MÃ¥let helliger midlet','2007-03-26 00:13:51');
but not
INSERT INTO `MediaWiki` VALUES
(4, CONVERT(0x44616e6973685f70726f7665726273 USING UTF8), CONVERT(0x4dc383c2a56c65742068656c6c69676572206d69646c6574 USING UTF8), '2007-03-26 22:55:06');
In my opinion pma wrongly interprets those varchars as binary data in the first place (using wrong "$field_flags").
YMMV
Logged In: YES
user_id=210714
Originator: YES
Juergen,
I tend to agree with you about not using hex for these fields (but they are really reported as being binary by the flags).
mysqldump has a --hex-blob option (don't know why this is not the default!) but generates hex only for blobs. I asked Michal for his comments, I think he's the one who added our option "use hexadecimal for binary fields".
Logged In: YES
user_id=210714
Originator: YES
Merged: No more bin2hex() for non-BLOBs.