MySQL 4.0 to 4.1 Encoding Problem And Solution

Here is a solution for a problem I read about many times in the Drupal forums, but couldn't find any definite answer to: When upgrading or moving a database dump from MySQL 4.0 to 4.1, Unicode data stored by Drupal becomes gibberish. The attached module iterates over all tables in Drupal's database, and converts text columns to UTF-8. The bulk of work is already in Drupal's code (_system_update_utf8), the module simply makes use of it.

To use the module, install and enable it, then navigate to update.php, and select update 1 for the module, then initiate the update process. You may disable the module when done.

If you can't log in to your site after the upgrade and as a result can't enable the module (happened to me), then run the following MySQL query through MySQL's command-line client or an interface such as phpMyAdmin:

INSERT INTO system 
  VALUES('modules/utf8upgrade/utf8upgrade.module', 
  'utf8upgrade', 'module', '', 1, 0, 0, 0, 0);

A couple of warnings: if your database contains tables that belong to a web application other than Drupal, the module may have side effects on this application as it iterates over all tables. You may need to modify the iteration logic and limit it to Drupal tables.

if you want to use the module, please backup your database first. Although it worked for me, I can't guarantee that it will work for everyone. I do appreciate feedback, however.