Note: You are currently viewing documentation for Moodle 3.9. Up-to-date documentation for the latest stable version of Moodle may be available here: MySQL full unicode support.

MySQL full unicode support

From MoodleDocs
Revision as of 15:25, 10 March 2017 by Séverin Terrier (talk | contribs) (French link)
Main page ► Managing a Moodle site ► Server settings ► Environment ► MySQL full unicode support
Server settings

UTF-8

UTF-8 is a character encoding that most websites use. It encodes each of the 1,112,064 valid code points. To store all of this information, four bytes is required. The most popular values are in the three byte region. MySQL by default only uses a three byte encoding and so values in the four byte range can not be stored. Any record that contains a four byte character will not be saved.

MySQL does support full UTF-8 support. It requires certain settings to be configured. From Moodle 3.3 the default will be to use full UTF-8 for MySQL and MariaDB. Existing databases will still run with partial support, but it is recommended to move over to full support.

Moodle comes with a Command Line Interface (CLI) script for converting to full UTF-8 for MySQL (and MariaDB). Before Moodle 3.3 this conversion tool would only change the collation to some variant of 'utf8_bin'. 'utf8_unicode_ci' was the recommended collation. We now recommend 'utf8mb4_unicode_ci'. 'utf8mb4_unicode_ci' supports 4 byte characters (utf8_unicode_ci only supports 3 byte characters) so four byte characters such as Asian characters and emoji should now be fully supported.

This script will attempt to change the database collation, character set, and default table settings.

To summarise:

  • Fresh installs of Moodle 3.1.5 and 3.2.2 onwards will use utf8mb4 by default.
  • Sites upgrading to Moodle 3.1.5 or 3.2.2 can use the script to update to utf8mb4. In Moodle 3.3 a warning will show that the database isn't using full UTF-8 support and suggest moving to utf8mb4, but you may choose to keep using utf8.

File format

To allow for large indexes on columns that are a varchar, a combination of settings needs to be set. The file format for the system needs to be using "Barracuda". This allows for the row format to be set to "Compressed" or "Dynamic". To enable this setting see the upgrade steps listed below.

File per table

To enable this setting see the upgrade steps listed below.

Large prefix

This in conjunction with the row format being either "Compressed" or "Dynamic" allows for large varchar indexes above 191 characters. To enable this setting see the upgrade steps listed below.

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-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci

[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

The upgrade is now complete.

Retrieved from "https://docs.moodle.org/39/en/index.php?title=MySQL_full_unicode_support&oldid=127104"
Powered by MediaWiki