(Different) Chinese Characters Encoding Problem

[rgf101]

Ok, have been using MySQL with Chinese content for some years, and more than once have come up against encoding problems. I thought these were all behind me, but have hit problems with SQLyog Enterprise

I have a robust, properly encoded database on a webserver, with Chinese characters on. I can move data between that server and SQLyog fine by using the sync tool or moving tables. BUT

I hit problems when trying to work with a MySQL .sql dump file. SQLyog seems to fail to recognize that it is UTF-8 data, and corrupts the Chinese characters when I import it.

I CAN get the data from smaller test dump file into my database via:

a) Via PHPmyAdmin (where I can specify that it's utf-8)

B ) Copying and pasting into the SQLyog editor

c) Adding a 'SET NAMES=UTF8' to the top of the dump file. (as suggested here)

However, none of these are adequate when dealing with multiple 100sMB dump files. I can open the test files in notepad and see the Chinese characters sitting there fine – I just need to be able to import them directly, without any intermediary steps.

Would be very grateful for any suggestions. I've described the problem as best I can, let me know if you need any more info (or if you think I've got confused somewhere).

[peterlaursen]

You will need to post an example dump that fails to import properly.

[peterlaursen]

.. but if “Adding a 'SET NAMES=UTF8' to the top of the dump file.” helps, the answer is there already.  Data in the file are utf8-encoded, there is no SET NAMES=UTF8' to the top of the dump file and server default is not utf8.

With what program do you create the dump? There is no option to let it 'SET NAMES=UTF8' to the top of the dump file?

SQLyog imports run in a seperate connection so 'SET NAMES=UTF8' in the editor will not help. The FAQ documents:

http://webyog.com/faq/content/34/148/en/do-i-need-to-set-names-to-display-characters-of-my-language.html

“However if you execute a SQL-script from a file you may include a SET NAMES statement in that file. You will need to do if character data in the file are not encoded with the server default charset. When SQLyog executes an external file, a separate connection is created for that and SET NAMES in that file will not affect the connection that the GUI uses. SQL scripts generated by SQLyog are UTF8-encoded and the file contains a “SET NAMES utf8″ statement. But if the script is generated with another program it is not necessarily the case.”

However it would be no technical difficulty to add an option for user to specify query(ies) that could be executed in the import thread before executing the file. Is that what you request?

[rgf101]

I'm creating the dumps with phpMyadmin, so basically I can only take what it gives me. I can upload a sample file if you want, but I don't think it's going to help – seems we know what the problem is.

Quote:

server default is not utf8.

Which specific variable is this? I can add that to my.ini, and presumably that should fix the issue?

Quote:

However it would be no technical difficulty to add an option for user to specify query(ies) that could be executed in the import thread before executing the file. Is that what you request?

That I suppose would be one solution, or allow us to SET NAMES for the connection that reads sql files? I'm not sure, that's probably a decision for you. But adding it into the top of every dump file is pretty inefficient and with very large dump files pretty much impossible.

I don't mind changing config files if necessary, so if you can let me know what changes I need to make I'll give it a shot. Thanks for you help.

[peterlaursen]

If server default charset is UTF8 the problem should not occur. You may add to your my.cnf/my.ini (in the [mysqld] section) this line 

Quote:

default-character-set=utf8

.. and next restart MySQL

[rgf101]

Have actually already tried that after some more investigating, but it hasn't helped. I'm really pretty mystified, I can see no more references to anything other than utf8 (

Code:

character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_results utf8
character_set_server utf8
character_set_system utf8
collation_connection utf8_general_ci
collation_database utf8_general_ci
collation_server utf8_general_ci

), the settings for the database, table and columns are all as they should be, and yet it's still failing to import a utf8 dump correctly. I'm either missing something incredibly obvious, or there's something odd happening somewhere (quite possibly not within SQLyog, it could equally be Wampserver / MySQL itself at this point.)

Any other ideas gratefully listened to . . .

[peterlaursen]

Could you please attach a small dump? It can be a single table with a few rows with silly data only as long as it is reproducible.

[rgf101]

'peterlaursen' wrote on '23:

Could you please attach a small dump? It can be a single table with a few rows with silly data only as long as it is reproducible.

Sure.

I've now tested with both Wampserver and Easyphp, as I suspected Wampserver might have been doing something odd. However, still no joy. Everything works perfectly except that stage of importing the mysql dump. Everything I can see is set to utf8 – if there's something specific I should check, do let me know though, as I'm no expert.

The attached dump has table structure data and three rows. The second of these rows should contain a short Chinese phrase, '活着就好'. I can open it fine in notepad, so the info is in there.

Many thanks for your help, especially considering the time of year – encoding issues aren't very Christmassy.

[nithin]

Hello,

Thanks for the information.

The issue is fixed and we are providing a special binary. Please download, try your case and let's know the status?

http://www.webyog.com/downloads/betas/not_released/SQLyog821.4.exe

Note:

This is v 8.21 special build. Since v 8.2 we have changed the registration key, you can get it from webyog portal.

Thank you

Nithin

[rgf101]

Excellent, the new build works. Many thanks.

[nithin]

We have released SQLyog 8.21 GA with this fix, which is available publicly!

Please refer to:

http://www.webyog.com/blog/2010/01/11/sqlyog-mysql-gui-8-21-has-been-released/

[Author]

Posts