Offlate I’ve been working on a pet project, part of which involved restoring an old MySQL database. The backup was of MySQL data files and not a SQL dump. However, restoring database from those data files was not much of an issue; I just hopped over to PuPHPet and downloaded config for a VM which was quickly setup, MySQL data files copied over to
/var/lib/mysql/<db-name>, repair command run and a SQL dump created.
Now to my dismay (I was kind of hoping not to run into this), the data appeared mucked up, gibberish. The reason? Most of the text is in Hindi written in Devnagari script but in the database it was encoded in
latin1 charset (past snafu when apps were not considerate enough to use appropriate charset & collations – this database was backed up more than 10 years ago).
Now there are several articles out there which would tell you to set the collation of your database, tables & columns to
utf8 to fix this issue but this only sets the charset so that new data entered into those tables/columns would be encoded correctly – it does not fix the gibberish which is existing data.
A friend suggested the following command to fix the data encoding using
- mysqldump --add-drop-table database_to_correct | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql database_to_correct
But this did not work in my case but I tested a hunch & it worked for me:
- <col-1> = CONVERT( CAST( CONVERT( <col-1> USING latin1 ) AS BINARY ) USING utf8 ),
- <col-2> = CONVERT( CAST( CONVERT( <col-2> USING latin1 ) AS BINARY ) USING utf8 ),
- <col-3> = CONVERT( CAST( CONVERT( <col-3> USING latin1 ) AS BINARY ) USING utf8 )
What this SQL does is, it tells MySQL to cast
latin1 encoded column into binary and then re-encode it into
Having found a working solution to my encoding woes, my last hurdle was to run this on the whole database. With 100+ tables and several columns per table, running this SQL manually for each table/column would be time consuming and not very smart.
And so, I ended up writing a quick PHP script to do the job for me. This script would traverse the whole database, look into all tables, determine which columns to update and then update all of them. Writing the script took about 15 minutes, running it on CLI took about 1 minute (time will vary depending on the size of database, which in my case was less than 100 MB).
Sharing the script in case it may be of use to someone else in a similar predicament.
Before you run this script, here are some caveats:
- This script is best run on CLI – not a good idea to run it via web server as it could time out, not to mention, someone might get the URL & run this thus messing up your database even further.
- The script assumes you have already set your table columns to
utf8_unicode_cicollation since it relies on this to identify which columns to re-encode.
- If the charset you want to encode data into is not
utf8then you should update
- You will, ofcourse, need to update
$_credswith appropriate credentials – server domain/IP, database name, username and password.
- Backup your database before you run this script on it & never run such things on production.