Category Archives: Version Upgrades

Upgrading MySQL 4.0 to 4.1 and Unicode conversion

Posted on by
Reply

You’d think upgrading from MySQL 4.0 to 4.1 would be a trivial task. Unfortunately it’s not due to the fact that MySQL 4.1 has knowledge of character sets where 4.0 did not. If you have plans for internationalization or are working with other non-English language, now is the time to take the plunge and make sure your data is in utf-8 (unicode) in the database.

The following describes the process of upgrading MySQL from 4.0.16 to 4.1.22.

Step 1 – Put System in Maintenance Mode
Make sure there is no data changing in the MySQL database.

Step 2 – Data Backup
• Make a copy of mysql data directory.

Step 3 – MySQL Upgrade + Data Verification
• STOP SLAVE on slaves if running replication
• Shutdown mysql on all servers
• Modify my.cnf on all servers as follows
• [mysqld]
character-set-server = latin1
default-character-set = latin1

• Upgrade MySQL to 4.1.22 on all servers
• Start MySQL on master database
• Upgrade password handling (mysql_fix_privilege_tables script).
# mysql_fix_privilege_tables rootpassword
• Run following statement for every table to convert from latin-1 to utf-8 character set.
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 ;
• Modify my.cnf
[mysqld]
# Remove the following two lines
character-set-server = latin1
default-character-set = latin1
# Add
[mysqld]
character-set-server = utf8
default-character-set = utf8
[client]
default-character-set=utf8
• Restart MySQL
• Restart replication

Step 4 – Apache Modifications
• Modify Apache to set.
AddDefaultCharset utf-8
• Turn off maintenance mode

Step 5 – Testing
Test entire application
Issues to expect:
• TIMESTAMP fomat output has changed significantly so scripts that parse a date are very likely broken.
• Check all instances of Load Data Infile
• Syntax for multiple-table DELETE statements has changed

References:
http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html
http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html
http://peter-zaitsev.livejournal.com/12083.html
http://www.w3.org/International/O-HTTP-charset
http://dev.mysql.com/doc/refman/4.1/en/charset-connection.html

Michael Sleman
Tech Lead