Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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 –  mejpark Apr 20 '11 at 13:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.