SQL Error (1064) when importing data from SQL file

Question

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?

Answer 1

Your editor probably did insert a BOM "Byte Order Mark" in the beginning of the file.

This is bad behaviour from your editor as BOMs are of no use in UTF-8 encoding, it is only usefull in UTF-16 or UTF-32. However, this behaviour seems to be common in several editors.

Either, there is a way to specify that you do not want the BOM in your file (e.g. in TextWrangler on a Mac, using the "UTF-8 no BOM" encoding), or you should remove the 2 first bytes using whatever app you can...