SQL Error (1064) when importing data from SQL file

[expired user #5672]'s profile image [expired user #5672] posted 13 years ago in General Permalink
Hello. 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. The live site 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 */

# --------------------------------------------------------
# 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?
ansgar's profile image ansgar posted 13 years ago Permalink
Could you please try loading the Heidi export directly into Heidis query window, without touching it with any other editor? Just to be sure Notepad+ does not add a wrong BOM at the very start of the file.
kalvaro's profile image kalvaro posted 13 years ago Permalink
HeidiSQL has problems with the byte order marker (BOM) in UTF-8 files: it doesn't detect it as such, but as regular character (which is not, thus the error). I've suffered it but never took the time to report it.

The safest approach is to remove the BOM with Notepad++: HeidiSQL does not need it. If you want to see the data, you can probably instruct Notepad++ to open the file as UTF-8 without adding a BOM.
ansgar's profile image ansgar posted 13 years ago Permalink
Btw, the "load sql file" dialog has an "Encoding" dropdown, where "Auto detect (may fail)" is the default. Please try selecting UTF-8 for testing purposes.
[expired user #5672]'s profile image [expired user #5672] posted 13 years ago Permalink
I didn't notice that you can select encoding when importing SQL file. Selecting UTF-8 solves the problem. Thanks for your help.
kalvaro's profile image kalvaro posted 13 years ago Permalink
Hmmm... Then the issue is slightly different from what I thought.

I've just tested again using a UTF-8 file with BOM. Both "Auto detect" and "UTF-8" successfully recognise the file as UTF-8. However, "Auto detect" does not remove the BOM when displaying the file: I see a little square in the query tab.

Please login to leave a reply, or register at first.