I have a MySQL database, which was originally set up with the default latin1 character set and latin1_swedish_ci collation. I was using the database like this for sometime, until I noticed strange characters on my production web site, which is powered by a database exported from my development machine.
At this point, I changed the default character set of the database and tables to utf8 and the collation to utf8_unicode_ci, converted the latin1 data inside each table to utf8 (using the 'convert data' option) and exported the database as a single SQL file using HeidiSQL.
When the resulting SQL file is opened in Notepad++, several characters are rendered incorrectly. For example, en dashes (-) are displayed as – and e with accent (é) are displayed as é.
I changed the encoding of the file from ANSI to UTF-8 (using the encoding menu option in Notepad++) and the offending characters are rendered correctly. I saved the new utf8-encoded SQL file and attempted to import the contents into the MySQL database on my production server. The import process fails with following error:
/* SQL Error (1064): 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 '?# -------------------------------------------------------- # Host: ' at line 1 */
/* Error with snippets directory: The specified path was not found */
The head of the SQL file:
# --------------------------------------------------------
# Host: 127.0.0.1
# Server version: 5.1.33-community
# Server OS: Win32
# HeidiSQL version: 6.0.0.3773
# Date/time: 2011-04-20 09:48:36
# --------------------------------------------------------
It chokes on the first line of the file, which is commented out. Why is this happening? I didn't have a problem loading data from SQL files until I changed the character set and collation of the database.
I came up with an ugly workaround to this problem by performing following steps:
Export database as single SQL file using HeidiSQL
Open resulting file in Notepad++ and convert from ANSI to UTF-8 encoding
Create new empty file in Notepad++, paste in UTF-8 and save file normally
What am I missing here?
