2

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

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 .sql 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 ' CREATE TABLE IF NOT EXISTS `history_members` ( `id` 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?

Thanks.

Less
  • 3,047
  • 3
  • 35
  • 46
  • It does appear that your dump file contains some extraneous characters. It is a text file so binary mode should not have been used for the upload. – daemonofchaos Sep 14 '12 at 13:36
  • Yes, when I showed it with `more` in PuTTY console, the first thing to see there was this ' nonsense. I have no idea how it got there, but when I transferred it with text mode, it disappeared. – Less Sep 14 '12 at 13:46
  • However, this doesn't fix the problem, because the encoding is messed up again... – Less Sep 14 '12 at 13:52
  • What is indicating to you that the encoding is messed up? – daemonofchaos Sep 14 '12 at 14:15
  • Well, I'm not sure, but when I show the content of text-mode-transferred file with `more` cyrillic chars look like Марко. It's the same situation when it is default-mode-transferred, except the very first character(s) is'. When I open the file form SCPs editor in the case of text transfer the cyrillic looks like Марко - in the case of default transfer it looks fine, eg Ахмедовски – Less Sep 14 '12 at 14:26
  • The problem is solved with the following modifications: 1) I forgot to set initial collation while creating the database to `utf8_general_ci` 2) I transferred the file with text mode 3) I added `SET NAMES 'utf8' COLLATE 'utf8_general_ci';` to the top of sql dump. – Less Sep 14 '12 at 15:05
  • 1
    The first few characters were probably a byte order mark: https://secure.wikimedia.org/wikipedia/en/wiki/Byte_order_mark. If so, I guess the mysql command line program didn't like them because they usually don't appear in an input stream. – Simon Sep 14 '12 at 16:36
  • yes, I saved the file to utf8 using notepad++, and it came to mind that it inserts byte ordering info, but I didn't have time to examine it in depth – Less Sep 14 '12 at 16:42

2 Answers2

6

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.

Less
  • 3,047
  • 3
  • 35
  • 46
0

Here is what I found using PhpMyAdmin for a rough SQL import using remote shell :

 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 /*!40101 SET NAMES utf8mb4 */;
Mantisse
  • 309
  • 4
  • 15