Archive for the ‘mysql’ Category

h1

MySQL 4.0 -> 4.1 collation / character issues

September 8, 2006

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 🙂