Converting your MySQL database to UTF8
This document describes how to convert your MySQL database from the latin1 charset to UTF8. Moodle requires that your Database is now UTF8 and will not upgrade if your database is not.
For more information about UTF8 have a look at the doc on unicode.
Why?
You may see the following error when upgrading your Moodle.
It is required that you store all your data in Unicode format (UTF-8). New installations must be performed into databases that have their default character set as Unicode. If you are upgrading, you should perform the UTF-8 migration process (see the Admin page).
Default Mysql character set
Moodle requires UTF8 in order to provide better multilingual support and has done since Moodle 1.8. However the UTF8 check during install and upgrade was only been implemented in Moodle 2.0 and you may find you are unable to upgrade because your database was not set up originally as utf8 when you first installed Mysql or because you have been running Moodle since before 1.8 and haven't previously converted your database. It is perhaps worth noting that Mysql is nothing to do specifically with Moodle. It is a database engine that is very widely used in open source projects and it contains details of all the stuff in your Moodle such as usernames etc and pointers to all the files that have been uploaded to it.
You need to do two things. 1) Change your mysql to have utf8 as its character set and 2) Change your database to utf8.
The descriptions elsewhere in this section cover making the utf8 database versions using mysqldump.
To make mysql default to utf8 you can edit /etc/my.cnf as follows.
(This was good for ubuntu server lucid 10.04 2.6.32-24-server Jan 2011)
In the client section of my.cnf
[client] ... .... ...
default-character-set=utf8 ....
and further down in my.cnf
[mysqld] ... ...
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_unicode_ci
... ...
Having made your default character set utf, a mysqldump restore of your database with the --skip-character-set parameter, will restore the database with your new default character set of utf8.
Converting an empty database
If you have created your database schema and are receiving the error during your initial installation your Moodle database will still be empty. You can simply run the below query in your database to resolve the issue.
alter database mydatabasename charset=utf8;
Converting a database containing tables
If you have previously installed Moodle and are now getting the error the following process will allow you to convert your database.
Linux & Mac
mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql
cp dump.sql dump-fixed.sql
vim dump-fixed.sql
- %s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
- %s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
- wq
mysql -uusername -ppassword < dump-fixed.sql
or alternatively using sed:
- $1-dbusername $2-password $3-dbname
mysqldump -u$1 -p$2 -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B $3 > dump.sql
sed 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/' <dump.sql | sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/' >dump-fixed.sql
mysql -u$1 -p$2 < dump-fixed.sql
Explained
The following steps will guide you in creating a database dumb, editing the database dump so that the correct charset and collation are used and then restoring the new database.
To start please open a new terminal and move to a temp directory.
mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql
The first step is of course to dump out the database and of course we will use mysqldump for this. We do however need to set several arguments in order to clean up the charsets and provide a dump that is not going to cause you any problems if you are moving this database to a different database server or find yourself having to restore on a reverted system.
- username
- The username to access your database.
- password
- The password for the above user.
- -c
- Complete inserts for better compatibility.
- -e
- Extended inserts for better performance.
- --default-character-set=utf8
- To set the default character set.
- --single-transaction
- To reduce our workload if anything goes wrong.
- --skip-set-charset
- Obviously not wanted or needed as we are changing it anyway.
- --add-drop-database
- Required so we can restore over the top of our existing database.
- -B
- We use this option so that our dump will contain drop table and create table syntax (which we will change the syntax for).
- dbname
- The name of the database to convert.
When you run this command a database dump will be generated into dump.sql
Next step is to copy dump.sql to dump-fixed.sql achieved by
cp dump.sql dump-fixed.sql
We will make the desired changes within dump-fixed.sql and we will keep dump.sql as it is as a backup just in case.
vim dump-fixed.sql
- %s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
- %s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
- wq
Now we need to edit the dump and correct the incorrect charsets that have been used. I have chosen to do this with VIM however you can use any search+replace editor or program. ( I choose VIM for this only because every linux user is/should be familiar with it).
First we open the file using VIM, and then run the three commands.
The first command replaces all instances of DEFAULT CHARACTER SET latin1 with DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci. This is used to fix up the database's default charset and collation.
The second command replaces all instances of DEFAULT CHARSET=latin1 with DEFAULT CHARSET=utf8. This converts all tables from using latin1 to using UTF8.
The third command simply saves it and exits.
mysql -uusername -ppassword < dump-fixed.sql
Now that we've made the required changes we simply need to restore the database over top of the existing database. We can do this by running the above command.
Windows
Could someone who is familiar with Windows please convert the above into something that will work on Windows? There are likely several additional arguments for mysqldump you will need to specify including setting the file for output using -r to avoid newline issues.
