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

Exporting UTF8 table

rcharisse posted 1 year ago in Import/Export
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?
jfalch posted 1 year ago
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).
ansgar posted 1 year ago
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.
jfalch posted 1 year ago
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.
rcharisse posted 1 year ago
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?

rcharisse posted 1 year ago
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
ansgar posted 1 year ago
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.