Corrupt database when try to duplicate database with import/export

Hi,

When I export and import with HeidiSQL to duplicate a database, the new db tables have corrupted contents for non-ASCII data.

Here's what I do...

Current database: all tables have default-collation=utf8_general_ci
Create new db, set character set to utf8, default-collation=utf8_general_ci
Export: RMC on the current db > "export db as SQL" > op=one big file
Now click on the new empty db.
Menu > tools > load SQL file > find the file and load it in.
And press Execute.

When I look at the new database table contents, non-ascii characters are corrupted.

The text in the exported sql file has the UK pound sign represented as hex: C2A3. I think this means that the file is in utf8.

I have also tried with phpmyadmin. This seems to work.
E.g. if I take the sql file exported by HeidiSQL and import with phpmyadmin, I get no corruption. There is an option in phpmyadmin import dialogue to specify the character set which I set to utf8.

Have I missed something in the HeidiSQL setup that might be causing this problem?

check the server variables character_set_client, character_set_connection, character_set_results; if any of these is not 'utf8', the server will auto-translate to/from what it must assume is not utf8, which will create garbled data if the data values used are indeed utf8 but the status vars indicate otherwise.
use SET NAMES 'utf8' to change them.

Thanks jfalch,

I guess this should be a feature request..

Can I suggest HeidiSql can control these settings itself so that it is able to import what it has exported. (So HeidiSql can be even better.)

You can automate this yourself:
-write the statement
SET NAMES 'utf8';
into a text file, name it e.g. SNU.sql
-add this file as "startup script" in the session manager entry for your server (bottom field in "settings" page of session manager´s right panel for server)
-save entry
from now on, it will be executed automatically as first thing when connecting to this server, which will cause the desired behaviour.

HeidiSQL uses utf8 as the default encoding for client-to-server communication, so there should never be a need to run SET NAMES utf8, unless you ran some SET NAMES latin1 or whatever manually.

In your database which you call "current db", you say "all tables have default-collation=utf8_general_ci". Do the column definition also have that collation? The tables default collation is not necessairily the same as for columns. However, not even that should make any problems, as HeidiSQL still runs in utf8 mode.

The fact that the file is perfectly imported via phpMyAdmin could mean that HeidiSQL misdetects the file encoding, e.g. interpretes it as latin1, and you get broken characters in the SQL editor (or in the "Run file" dialog). By the way, what encoding did you select in the "Open SQL file" dialog? Please try "Auto detect", and watch out for the broken pound character in the editor contents afterwards.

Hi,
I searched my my.ini file and I have no "SET NAMES"
My tables dont have any collation set on the columns

For the "Load SQL" command, I changed auto detect to explicitly stating 'utf-8' and the pound sign came in OK.

Anyway, looks likes thats the problem fixed - thanks.

SET NAMES 'xxx' is SQL, and you will never find it in a my.ini file.
What you might find are lines like
character_set_xxx=yyy
(eg character_set_client=latin1), usually in the [client] section.