Configuring MySQL or MariaDB Server and Converting to full UTF-8 to Support Emoji in Moodle

When starting with a fresh install or upgrading Moodle, you may come across a requirement to change the database Collation from utf8_unicode_ci to utf8mb4_unicode_ci to support four byte characters such as emojis. Use the following steps to change the database Collation if you use a VPS/dedicated server with Centos OS and MariaDB. Here is how to change uft8 to utf8mb4.

On Moodle, Go to Site Administration > Server > Environment

You will see the following message under Other checks at the very bottom if your moodle database isn’t upgraded.

If this is a production server, put your Moodle in Maintenance mode.

Steps to Upgrade

IMPORTANT: Backup your database before making any changes or running the CLI script

Overview:

1. Change configuration settings by adding lines to my.cnf (optional)
2. Restart MySQL server
3. Run the CLI script to convert to the new Character set and Collation in Plesk
4. Check and repair database
5. Update config.php
6. Check Server Environment on Moodle
7. Upgrade Moodle to add Memoji Picker

Use the local terminal to SSH to your server as root or use an SFTP client to connect to the server.

1. Update my.cnf file to change configuration settings – Optional

The following steps are the same for MySQL and MariaDB. This is optional as you can skip to running the script and it will make these changes itself. If errors occur then come back to manually change the settings here.

Look for my.cnf file, on Centos this is found in /etc/

/etc/my.cnf

Add the following lines into the file (source: https://docs.moodle.org/310/en/MySQL_full_unicode_support)

[client]
default-character-set = utf8mb4

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = true

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

[mysql]
default-character-set = utf8mb4

2. Restart MySQL server

From terminal, use the following commands to restart MySQL and MariaDB

3. Run the CLI script in Plesk

Run the CLI script below to convert to new Character set and Collation (Moodle 3.1.5, 3.2.2 or newer)

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

I couldn’t do this via Terminal as I couldn’t find the PHP folder on my Centos + Plesk server to execute the command. If anyone knows a way to do this, please share with me.

On Plesk, go to Website& Domains > Scheduled Tasks >

We are not scheduling a task but using the task scheduler to Run a PHP script.

Click +Add task

Add your CLI script into Schedule a Task window, first by clicking on Run a PHP script and uncheck Active checkbox.

Browse to moodle’s script folder in /admin/cli/ and select mysql_collation.php. That’s first half of the command done.

Copy and paste the parameter below into the parameter field.

--collation=utf8mb4_unicode_ci

Leave everything else as default and click Apply. You can then click Run Now in the Scheduled Task window.

Here is the Script Result, you can see that it was completed successfully and some tables were converted. Running the script again a second time shown no work to be done.

4. Check and Repair Database

Go to Plesk > Databases

Click Check and Repair the moodle database (remember to pick the right site)

5. Update Config File

Use SFTP client or (File Manager in Plesk), connect to the server and update the config.php file to use the right Collation when connecting to the MySQL server

There should be a line under dboptions that says

 'dbcollation' => 'utf8_general_ci',

change this to the following

 'dbcollation' => 'utf8mb4_unicode_ci',

Save file

6. Check Server Environment

On moodle, go to Site Administration > Server > Environment

The mysql_full_unicode_support check should now be gone

7. Upgrade Moodle to Add Memoji Picker

On moodle, go to Site Administration > Notification

Confirm the upgrade by clicking Save changes

If you server is in maintenanc mode, remember to disable this.

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'

• Try adding some Emojis (e.g. 😂💩) to your Moodle site to verify that the upgrade was successful.

Once again, if you know how to run CLI command on Centos, please do share with me below.