Today, I involved in migration from MySQL 4.1 to 3.23. Yes, I mean to downgrade the software. Actually, I don’t want to downgrade but just to move from a hosting service to another one. Unfortunately, the new host is running MySQL 3.23 and the old one is running MySQL 4.1. Before to go further, the software running on top of MySQL is Joomla and its eCommerce package.

To migrate Joomla from one host to another host, it is just to dump and restore the database and then copy the whole PHP code to the new place. Unfortunately, I’m in Thailand and my language is Thai. MySQL does support character set and unicode since 4.1. That’s the problem. Joomla stores all contents in MySQL in UTF-8. Good work! Well, the database was created to store data in TIS-620. Thanks god to make it accidentally work for so long time. It’s lucky that TIS-620 are compatible with latin1. In other words, TIS-620 is 8-bit character encoding. You may call it binary. So technically, TIS-620 stores all strings as is even though the data are in UTF-8 encoding.

So I have to dump this database into SQL script format to rerun on the new host. Note that the real problem is I don’t have shell access to run mysqldump on command line. As a result, I have to use phpMyAdmin instead. I don’t suppose to get trouble by phpMyAdmin to dump database before. By the way, the problem was phpMyAdmin forced me to connect to MySQL using UTF-8 character set and MySQL tried to convert from TIS-620 to UTF-8. Beware! The actual data in the database are stored in UTF-8 so I don’t need the conversion at all. As a result, I got all crabs.

To fix this problem, I have to modify phpMyAdmin to force using TIS-620 character set instead of UTF-8 when they found the current database is 4.1 or later. The result of this dump is in UTF-8 so it is ready to be imported to MySQL 3.23.

Technorati Tags: English, IT, Programming, Software, Tips and Tricks, Joomla, PhpMyAdmin