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
- Change configuration settings by adding lines to my.cnf (optional)
- Restart MySQL server
- Run the CLI script to convert to the new Character set and Collation in Plesk
- Check and repair database
- Update config.php
- Check Server Environment on Moodle
- 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/
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
service mysqld restart
systemctl restart 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.
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',
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.