How To: Changing the MySQL collation for a Drupal application under Cloud Platform

Issue

Site builders may encounter MySQL collation mismatch errors during queries or may wish to leverage more performant collations. To resolve these issues, normalize the database collation by following the steps below. This process involves converting table collations and configuring Drupal connection settings to enforce consistency for future module installations and updates.

Steps

1. Determine the MySQL version(s) of all of your application environments (including local development). 

The MySQL version dictates what the available collations are.

• Ideally, all your environments should be running the same MySQL version. 

• If running different versions, then a requirement is to use a common collation compatible with all environments. Note that mismatched versions can still cause some behavioral differences.

• Example: the utf8mb4_general_ci collation works with both MySQL 5.7 and 8.0, whereas utf8mb4_0900_ai_ci requires MySQL 8.0+.
   

2. Select the target collation 

• Note that different collations offer tradeoffs between functionality, performance, and types of data supported (like emojis). This step may require some experimentation and testing to ensure you make the right choice.
• See https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations/ for related information.
• If using MySQL 8, we recommend using utf8mb4_0900_ai_ci 
   

3. Identify the databases and tables to convert 

• You can do this by examining the log entries for queries that triggered any mismatched collation errors.
• Alternatively you can run a query like this in each database: 

## Example. Edit the final line with the collation you picked in the previous step(s).
SELECT table_name, table_collation 
FROM information_schema.TABLES 
WHERE table_schema = DATABASE() ## Replace with an actual DB name if needed.
AND table_collation != 'utf8mb4_0900_ai_ci';

4. Convert the tables

• You can use the following example MySQL command to change the collation of a single table. Remember you would need to pick the database before running this command.

## Example for converting a table to utf8mb4_general_ci. 
##   Edit to match the desired charset and collation.
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

• Alternatively, use this query to generate ALTER TABLE statements for all tables that do not match your target collation:

## Example to generate SQL statements (which should be verified and then run later) 
##   to modify all tables that do not match the desired collation utf8mb4_0900_ai_ci
## EDIT the table_schema value of 'EDIT_ME' (or you can use table_schema = DATABASE() to use the current selected DB).
SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS _sql
FROM information_schema.TABLES
WHERE table_schema = 'EDIT_ME' AND table_collation != 'utf8mb4_0900_ai_ci';

5. Configure Drupal’s database connection

Update settings.php to prevent new tables from using incorrect defaults. This requires the addition of the collation key to the $databases array.

• For Acquia Cloud: Follow the  https://docs.acquia.com/acquia-cloud-platform/overriding-drupal-databases-settings  documentationAcquia database override documentation Acquia database override documentation Acquia database override documentationand insert the following line after the require statement:

# Edit to specify the target collation
$databases['default']['default']['collation'] = 'utf8mb4_general_ci';

6. Ensure your team and environments are aligned!

Ensure all developers use the updated settings.php and compatible local MySQL versions to prevent "collation reversion."

• Also consider creating a script that runs within a scheduled (cron) job that can alert the team if a table is created with an unsupported collation.