Less Less - 2 years ago 123
SQL Question

MySQL UTF8 Windows export - Linux import

I have MySQL database version 5.1.36, that came with WAMP installation. I used it for development purposes on Windows XP SP3, and it has some data in it, which is cyrillic, and the collation for all of those tables/columns is set to


Now the time has come to move this database to pseudo-production environment, which is on Debian Lenny. Version of MySQL here is 5.0.51a.

I tried the following:

  1. I exported the databse with data from phpmyadmin on Windows and saved the
    file to be in UTF8.

  2. Then, I transferred it through WinSCP (both with default and binary transfer settings) to Linux machine.

  3. I created the database through command line:
    mysqladmin -u root -p create nbs

  4. Finally, I tried to create tables and fill the data:

    mysql -u root -p --default-character-set=utf8 nbs < NBS_utf8_1.sql

However, this is where I'm getting the error, like:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
`history_members` (
int(11) NOT NULL AUTO_' at line 1

Something is messed up with encoding, I suppose... but don't know how and where. I think I read in the similar question on SO that binary mode for text transfer will only change the line breaks CRLF to LF (don't know if this is correct...). What am I missing here?


Answer Source

For this particular case, the problem was solved with the following modifications:

1) I set initial collation while creating the target database to utf8_general_ci,
2) I transferred the file with text mode through WinSCP,
3) I added SET NAMES 'utf8' COLLATE 'utf8_general_ci'; to the top of sql dump.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download