MySQL, ERROR 1366 and Windows cmd prompt
Tuesday, August 3rd, 2010I have been trying to restore a database backup onto my laptop.
The source database used Character Set UTF8, so I created a local database with the same character set. The database contained several unusual characters such as â and é, so I was careful to choose the same character set.
I opened a command prompt and tried to load the backup at the command line:
D:\backup> mysql -u user -ppassword mydb < backup.sql
I got the error:
ERROR 1366 (HY000) at line 21364: Incorrect string value: '\xC1guas ...' for column 'address2' at row 1
I checked my.ini, and as far as I could see, everything was set to UTF8.
A quick internet search located this MySQL bug report: http://bugs.mysql.com/bug.php?id=30131
The work around to set the code page did not seem to work for me.
So I tried loading the backup using a GUI interface to MySQL instead (I use the free DBManager from DbTools). And the backup loaded fine! So definitely some odd behaviour from the Windows Command prompt.