Search code examples
mysqlencodingutf-8utf8mb4

UTF-8 encoding problem while importing a sql file

I have a server hosting MySQL, PHPMyAdmin reports:

Server version: 5.1.56-community
MySQL charset: UTF-8 Unicode (utf8)

I export a sql from using either mysqldump -uroot -p database > file.dump or mysqldump -uroot -p database -r file.dump (both generated files are identical anyway).

Locally, I installed MySQL 5.5 and HeidiSQL 9.5.

As the server's SQL file my.ini has:

default-character-set=utf8

I changed the local my.ini file to have

default-character-set=utf8

But also:

character-set-server=utf8

They were both set to latin1. Dunno why I have character-set-server set here while the server does not. Anyway.

Now I start HeidiSQL, it shows utf8mb4 references instead of utf8 for the sessions parameters. I don't know why:

enter image description here

Now, I import my dumped file, and I see that even if everything is apparently configured in utf8, it looks like I have some encoding problems.

On the server, I see: enter image description here

Locally, in HeidiSQL, I see: enter image description here

Special characters like à are not displayed correctly on the local database.

Am I doing something wrong?

Note that if I install HeidiSQL on the server, the variable tab shows the same values for the Session and Global parameters, and the à is shown correctly.

So this may be the root cause of the problem, but I don't know how to fix it. If I change the Session values before importing the sql file it does not fix the issue, and also values are back to utf8mb4 when I start HeidiSQL again.

Solution

  • Thanks to deceze comment, I could fix the issue.

    In HeidiSQL, when I choose the sql file to execute, there's actually an "ncoding" option I did not notice originally ;-)

    If I keep "auto-detect", the import generates bad content (with mojibake characters)

    If I force "UTF-8", the import is perfect

    Dunno why HeidiSQL fails to auto-detect the encoding...