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

Exporting UTF8 table

User, date Message
Written by rcharisse
10 months ago
Category: Import/Export
3 posts since Fri, 08 Nov 13
I'm having a problem with dumping a UTF-8 table to an sql file. The table has many non-ascii characters in it, but when I export it, the file has them encoded as an odd sequence of hex numbers.

I'm not sure if it's something to do with how the table is set up but I'm hoping someone can help me with this as my backup is now useless.

An example, the letter ö appears as it should in the table, but in the sql file, it appears as <C3><83><C2><B6> and when I reimport it back through Heidi, it appears as garbage.

Can anyone please help?
Written by jfalchMoney, Euro
10 months ago
387 posts since Sat, 17 Oct 09
looks like a multiple encoding to me - ie result of "encode something from other charset to utf8, then encode result to utf8 again".
check that your server variables charset_* all have the value "utf8" (except character_set_filesystem which should be "binary"); if any of these is different, mysql will automatically translate the utf8 values to that charset on output (may happen several times).
Written by ansgar
10 months ago
4987 posts since Fri, 07 Apr 06
Quite a few other users experienced similar problems. Mostly it turned out that HeidiSQL misdetects the encoding of its self produced UTF8 file. Solution was mostly to select "UTF8" in the open-file dialog's "Encoding" drop down.
Written by jfalchMoney, Euro
10 months ago
387 posts since Sat, 17 Oct 09
good idea - except that <C3><83><C2><B6> is not the utf8 code for ö (<00><F6> is). Also the presence of two "prefixed" characters ((C|D) xx - 16bit utf8) in place of one source character seems to indicate to me a double encoding.
Written by rcharisse
10 months ago
3 posts since Fri, 08 Nov 13
Thanks for the responses. All the character_set variables are set to utf8 (except the filesystem one) so it can't be that. Any other ideas?
Written by rcharisse
10 months ago
3 posts since Fri, 08 Nov 13
One thing I've noticed is when I do an 'Export as SQL' from one session to another, the encoding is fine. It's only when I export to a file and back in that it goes wrong
Written by ansgar
10 months ago
4987 posts since Fri, 07 Apr 06
Which encoding did you select in the file-open dialog? As said, leaving it to "Auto-detect" probably misdetects the file's encoding. Select "UTF-8" testwise.
 

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