Instructions: 0. What's happening? 1. Preparations 1.1. Backup your database 1.2. Copy update.php to updatetemp.php 1.3. Modify updatetemp.php 2. Execution 2.1. Log in as admin user 2.2. Run updatetemp.php once 3. Clean up 3.1. Check site to see if everything is OK now 3.2. Remove updatetemp.php 0. What's happening? ==================== You upgraded your MySQL from a version prior to 4.1 to version 4.1 or higher, including version 5.x. You notice that special characters like acute accent or umlaut have been defaced. The MySQL manual [1] explains what happens, and how to properly prepare for a migration. "The MySQL 3 and 4.0 character set contains both character set and collation information in one single entity. Beginning in MySQL 4.1, character sets and collations are separate entities. Though each collation corresponds to a particular character set, the two are not bundled together. What can we do to fix this? We will convert all data to binary, and then convert it again. MySQL 4.0 and earlier do not support UTF-8. So, we tell MySQL to use an old character set (like Latin1), but we really pass in UTF-8 data. But, because Latin1 is just a simple mapping of bytes to characters, the data is accepted and returned exactly as before. Only things like case-insensitive string comparison will not work for Unicode characters. When converting to MySQL 4.1, you need to tell MySQL to keep all your data as is (it is already UTF-8) and simply re-interpret it as UTF-8. This is why we convert to binary in the process: to tell MySQL to forget about Latin1. 1. Preparations =============== Before fixing this issue, we're going to prepare some things to make this run as smooth as possible. 1.1. Backup your database ------------------------- If you don't have a back-up already, create one now even if you're backing-up your screwed site. If something goes wrong in this process at least you'll have something to fall back upon. You can create one in phpMyAdmin or with command-line. Use whatever method you are most comfortable with. What follows now are guidelines which you might need to adapt according to your version of phpMyAdmin or MySQL. a. phpMyAdmin To backup your database using phpMyAdmin simply select the database from the menu on your left, click export and check the "Save as file" checkbox. Click the "Go" button and save the file. b. Command-line This is the prefered method since we can include some options that will make restoring the database later a lot easier. Use the following command to save your database in the file "drupal-backup.sql". Replace all variables with your specific values: mysqldump --compact -u $username -p -d $database > drupal-backup.sql After entering this command, you will be asked to enter the password. Doing so, you will save the database dump in the file drupal-backup.sql. Did you notice the option "--compact"? This means that the dump will only contain the actual data and that no comments will be added. If you have a large database, this can noticably decrease the size of the dumpfile. 1.2. Copy update.php to updatetemp.php -------------------------------------- We will now create the script that will do the actual fixing of the data. We will base this off the update.php script that comes with Drupal. It is found in the Drupal root, in the same directory where index.php resides. Simple copy it to a new file. In this guide we'll call it updatetemp.php. 1.3. Modify updatetemp.php -------------------------- This is the most crucial part. We're now going to modify the newly create updatetemp.php. Look for the following code: update_fix_sessions(); For Drupal 5 this is located at line 790. Below this line, so at line 791, paste the code from the following file: http://mostrey.be/files/mostrey/drupalmysql4.1.txt Please note that this code will only fix the tables provided by Drupal core. Some contributed modules install their own tables. If you're aware of this, you can include these tablenames in comma-seperated list. 2. Execution ============ Now that we've prepared everything, it's time to execute the script and fix things. 2.1. Log in as admin user ------------------------- Go to your site and log in as user admin (uid 1). This is important since the admin user is the only user that is allowed to run the update script. If you are somehow unable to change the followin line in updatetemp.php from $access_check = TRUE; to $access_check = FALSE; 2.2. Run updatetemp.php once ---------------------------- Now run updatetemp.php from your browser, just once and ignoring any output or warnings you'll receive. An example url is: http://www.example.com/updatetemp.php 3. Clean up =========== With the script having run once, we'll check if things are OK and clean everything up. 3.1. Check site to see if everything is OK now ---------------------------------------------- Surf your site and check if any special characters are displayed correctly now. This method has been tested by a lot of people and worked flawlsly, so you should be able to move to the next step. If things do not look OK, restore your database and try again. Make sure you do not skip any steps: enter the code of 1.3. in the correct place and make sure you are either logged in as user admin or you set the $access_check to FALSE and try again. 3.2. Remove updatetemp.php -------------------------- If you verified that everything is OK, you should now remove updatetemp.php. Do not let this file linger around as it might pose security issues. Good job! [1] http://dev.mysql.com/doc/refman/4.1/en/charset-upgrading.html