0

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?

1 Answer 1

3

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

1
  • The failed insert was probably caused by the BOM at the beginning of the file. There's an option in Notepad++ to omit the BOM, but in the end I managed to import the original, unchanged SQL file using HeidiSQL. More info here, for those interested: heidisql.com/forum.php?t=8302
    – unpossible
    Commented Apr 20, 2011 at 13:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .