Unsupported Screen Size: The viewport size is too small for the theme to render properly.

(Different) Chinese Characters Encoding Problem

forums forums SQLyog Using SQLyog (Different) Chinese Characters Encoding Problem

  • This topic is empty.
Viewing 10 reply threads
  • Author
    Posts
    • #11803
      rgf101
      Member

      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).

    • #30225
      peterlaursen
      Participant

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

    • #30226
      peterlaursen
      Participant

      .. 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?

    • #30227
      rgf101
      Member

      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.

    • #30228
      peterlaursen
      Participant

      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

    • #30229
      rgf101
      Member

      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 . . .

    • #30230
      peterlaursen
      Participant

      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.

    • #30231
      rgf101
      Member
      '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.

    • #30232
      nithin
      Member

      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

    • #30233
      rgf101
      Member

      Excellent, the new build works. Many thanks.

    • #30234
      nithin
      Member

      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/

Viewing 10 reply threads
  • You must be logged in to reply to this topic.