How i diagnosed and fixed critical charset and collation issues in moodle

Anyone managing Moodle (Modular Object-Oriented Dynamic Learning Environment) instances knows that database consistency directly impacts platform stability.

If you have ever migrated a Moodle installation between servers or upgraded older versions, you have likely seen broken characters: accented letters turned into é or ?, Ajax requests silently failing in quizzes, and forum posts getting truncated.

The root cause is usually legacy MySQL character sets: the older utf8 charset only supported up to 3 bytes per character. Modern Moodle requires true 4-byte utf8mb4 with utf8mb4_unicode_ci collation to store multilingual text, mathematical notation, and emojis without data corruption.

Here is how to audit database variables, run the official CLI migration script, and update config.php.

1. Diagnosis: symptoms and verification#

Character encoding mismatches typically manifest in three areas:

• Corrupted characters in course titles, forum posts, and quiz questions.
• Asynchronous Ajax requests failing due to malformed JSON responses.
• Encoding warnings logged in the web server's error_log.

To check the active character set and collation variables in MySQL/MariaDB:

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

To list all Moodle tables that do not use utf8mb4_unicode_ci:

SELECT table_name, table_collation 
FROM information_schema.tables 
WHERE table_schema = 'moodle_db' 
  AND table_collation NOT LIKE 'utf8mb4_unicode_ci';

If tables return latin1_swedish_ci, utf8_general_ci, or utf8mb4_general_ci, a migration is necessary.

2. Preparation: database backup and staging#

Before modifying production data, generate a compressed backup:

# Compressed snapshot
mysqldump -u username -p --opt --default-character-set=utf8mb4 moodle_db | gzip > moodle_pre_charset_$(date +%Y%m%d).sql.gz

For high-traffic platforms, consider cloning the database to a test base (moodle_db_fix), pointing config.php temporarily to validate the migration before updating production:

$CFG->dbname = 'moodle_db_fix';

3. Step-by-step CLI migration#

Step 1: Enable large index prefixes in innodb#

Because utf8mb4 consumes up to 4 bytes per character, enable large index prefixes to prevent 767-byte index limit exceptions during table conversion:

SET GLOBAL innodb_file_per_table = 1;
SET GLOBAL innodb_large_prefix = 1;

Step 2: Run moodle's built-in collation utility#

Instead of manually executing ALTER TABLE queries, run Moodle's official PHP CLI script to safely convert tables and columns without breaking serialized data:

# Run from Moodle root directory
php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

On large databases with extensive audit logs (mdl_logstore_standard_log), rebuilding indices may take a few minutes.

Step 3: Update config.php#

Open config.php in your Moodle root and define the collation in the dboptions array:

$CFG->dbtype    = 'mariadb'; // or 'mysqli'
$CFG->dblibrary = 'native';
$CFG->dbhost    = 'localhost';
$CFG->dbname    = 'moodle_db';
$CFG->dbuser    = 'moodle_user';
$CFG->dbpass    = 'YourStrongPassword123';
$CFG->prefix    = 'mdl_';
$CFG->dboptions = array(
    'dbpersist'   => 0,
    'dbport'      => '',
    'dbsocket'    => '',
    'dbcollation' => 'utf8mb4_unicode_ci',
);

Step 4: Fix compressed rows (if applicable)#

php admin/cli/mysql_compressed_rows.php --list
php admin/cli/mysql_compressed_rows.php --fix

Step 5: Purge application caches#

After completing the migration, purge all caches to rebuild templates and cached strings:

php admin/cli/purge_caches.php

4. Global server configuration in my.cnf (dedicated/vps)#

If you manage the database server and want to standardize character sets globally, add the following to /etc/mysql/my.cnf (or /etc/my.cnf):

[client]
default-character-set = utf8mb4

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
skip-character-set-client-handshake

After restarting MySQL/MariaDB, perform an automated repair and optimization check:

mysqlcheck -u root -p --auto-repair --optimize --all-databases

Best practices for moodledata and directory permissions#

During maintenance, verify the moodledata storage directory:

1. Keep moodledata outside public_html: Storing it outside the web root prevents direct access to student submissions and course backups. If your hosting environment requires it inside the web root, enforce strict denial rules via .htaccess or web server config blocks.
2. Directory Permissions: Ensure the web server user (www-data or the account owner) has write permissions (750 or 770) to prevent file upload failures.

See also

Databases Surviving MariaDB connection exhaustion on CloudLinux with zero downtime

Infrastructure MySQL handshake desynchronization in multi-tenant environments: cPanel, CloudLinux, and CageFS

Databases How to Fix Database Mapping and Resolve phpMyAdmin Access Denied via CLI in cPanel

This post is licensed under CC BY-NC.