MySQL export/import and charset problems

Today I discovered I messed up a database upgrade in MySQL. I had a rather large database which I exported from my main server and imported onto my testing server. Both machines had the same version of MySQL. I ran some update scripts on the testing server DB, verified the data, then exported the updated database, and reimported it into the main servers MySQL database.

Then I notice only after a few days that the data I re-imported had all the accented characters broken. Somewhere in my conversion the Latin1 charset did not export in UTF8 from Mysql as it was supposed to in MySQL 4.1.12 or greater!

The big issue here is that I could not simply run the upgrade again, as I would lose all changes to the database in the past week.

After some research I found the following bit of information using iconv ( iconv - Convert encoding of given files from one encoding to another)

Step 1
Re-export the data from the database normalling using
mysqldump—add-drop-table databasename > mydump.sql

Step 2
Use iconv to conver tthe format from UTF8 to ISO-8859-1
iconv -f UTF-8 -t ISO-8859-1 mydump.sql -o newdump.sql

Step 3
Re-import newdump.sql into MySQL with
mysql databasename

< newdump.sql

Now, if everything is normal you should have a great working database again. Or, if like with me, Murphy didn’t allow it to work normally, you might have gotten this error:
iconv: illegal input sequence at position 49823

It took me awhile to find the solution, but after much head banging, I finally found the following solution, whiched worked the charm, I might add!

Step 2
iconv -f UTF-8 -t ISO-8859-1//TRANSLIT mydump.sql -o newdump.sql

This will get the conversion done using transliteration, which means that when a character cannot be represented in the target character set, it can be approximated through one or several similarly looking characters (source: www.gnu.org).