MySQL full unicode support for Moodle

Steps to upgrade

Most important: Please backup your database before making any changes or running the CLI script.

• Change configuration settings for MySQL (exactly the same for MariaDB). This step is optional. You can run the script and it will try and make these changes itself. If errors occur then try manually changing these settings as listed below.
  • On Linux based systems you will want to alter my.cnf. This may be located in '/etc/mysql/'.
  • Make the following alterations to my.cnf:

[client]
default-character-set = utf8mb4

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

[mysql]
default-character-set = utf8mb4

• Restart your MySQL server.
• Run the CLI script to convert to the new Character set and Collation (requires Moodle 3.1.5, 3.2.2 or newer):

$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data.

• Adjust the $CFG->dboptions Array in your config.php to make sure that Moodle uses the right Collation when connecting to the MySQL Server:

$CFG->dboptions = array(
  …
  'dbcollation' => 'utf8mb4_unicode_ci',
  …
);

If you only have access to the database command line (or something like phpmyadmin) you can try the following sql commands:

SET GLOBAL innodb_file_format = barracuda

SET GLOBAL innodb_file_per_table = 1

SET GLOBAL innodb_large_prefix = 'on'