Just moved from MySQL 4.0 to 4.1 and seeing wierd characters everywhere? Well I had this ‘privelage’ enforced by a host without any notice, so it was bloody important to get this up and running.

Here’s how to fix it

Firstly dump your existing MySQL 4.0 database:

mysqldump –-user=root -p –-default-character-set=latin1
–-skip-set-charset database_name > database_name.sql

Run this regular expression against the sql dump and output to new file:

sed -r ’s/latin1/utf8/g’ database_name.sql > database_name.utf.sql

Drop the database on MySQL 4.1 if it exists, create the database with default character sets

mysql –-user=root -p –-execute=”DROP DATABASE database_name; CREATE
DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;”

Finally reimport the SQL dump that has been through SED

mysql –-user=root -p –-default-character-set=utf8 database_name < database_name.utf.sql

Go back to your site, F5 and pray