Mysql Charset and Collation for Moodle

Posted May 31st, 2011 by bryanr

We have migrated and updated our Moodle databases several times over the years, which has resulted in inconsistent database collations.  I am by no means a database expert, but I know how to find the answers to the problems at hand.  Here are some of the steps that I used to upgrade our moodle site from 1.9 to 2.0.

Create a new empty database for the upgraded site:

mysql> CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Use mysqldump to create a backup file and then import the backup into your empty database.

# mysqldump -u user -p  moodledb > data.sql

# mysql -u user -p -h localhost –default-character-set=utf8 moodledbnew < data.sql

Then to make sure you don’t get collation errors during the moodle upgrade process perform one of the following (the second method worked well for me):

1. Backup the database; Dump the database to a .sql or text file; Perform a global search and replace all instances of utf8_general with utf8_unicode; Run a query on the database replacing the existing code

2. In the following script, replace “database_name” with the name of your database and edit the statement template in the parameters of the CONCAT function to have the target character set (and/or collation) of your choice. After that you can just take the statements and batch execute them in the MySQL client of your choice (eg. phpmyadmin, Navicat, the official MySQL client, etc.). Of course, you’re strongly advised to make a backup of your database before you start messing around with character set (and/or collation) conversions.

SELECT  CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '`  CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;') as stmt
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'database_name'
ORDER BY 1

Of course, you will need to download the new moodle version, copy over your themes, modules, and blocks, and adjust your web server settings appropriately.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>