Database Character Set and Collation Best Practices

Summary

When you run Drupal on Cloud Platform, ensure that all database tables and columns use a uniform character set and collation. Mismatches in database states—especially during MySQL 8 upgrades, platform upgrades such as Classic to Acquia Cloud Next or Acquia Cloud Site Factory to Multi-Experience Operations (MEO), or data migrations—can lead to missing emojis, search failures, broken Views, and database performance degradation.

Acquia recommends that you standardize your database on the recommended Collation: utf8mb4_general_ci

Note that a common, newer collation for MySQL 8 that your Drupal team might opt for is utf8mb4_0900_ai_ci. The utf8mb4_general_ci collation is recommended because most tables in reviewed applications already use utf8mb4_general_ci, which lowers the effort required to make changes, and because it is widely used in Drupal.

Understanding the Issues

• Missing Emoji Support (? Marks or Truncated Text): Legacy databases using utf8 or utf8mb3 support a maximum of 3 bytes per character. Modern Unicode characters—such as emojis, mathematical symbols, and modern Asian language scripts—require 4 bytes. When you insert 4-byte characters into a 3-byte column, MySQL either replaces them with question marks or displays Error 1366: Incorrect string value.
• Illegal Mix of Collations Errors (Error 1267): In MySQL 8, the default collation changed to utf8mb4_0900_ai_ci. If existing tables use utf8mb4_general_ci and new tables or temporary query tables use the default setting for MySQL 8, OIN operations between them fail with the following error:  Error 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='.
• CPU Spikes and Performance Degradation: When a query joins two columns with different collations, MySQL cannot utilize existing database indexes. Instead, it performs an in-memory conversion on every row before comparing them. A fast indexed query that normally takes 2 milliseconds can turn into a CPU-intensive full table scan taking several seconds.

• Other possible impacts: These are the major impacts reported so far, though additional issues can occur.

Solutions and Implementation Guide

Step 1: Enforce Collation in Drupal Configuration

To ensure Drupal uses the recommended collation for all newly created tables, update the settings.php file to set a default collation. Ensure to follow this exact order:

• Add code to prevent autoconnecting to the database.

• Set the default collation for new tables.

• Include any additional database settings that you need, such as isolation level, which the example displays as an illustration

• Connect to the database

• Add the require line after connecting to the database.

For example:

// https://docs.acquia.com/acquia-cloud-platform/overriding-drupal-databases-settings?v=2
if (file_exists('/var/www/site-php')) {
  global $conf;
// Do not autoconnect to database
  $conf['acquia_hosting_settings_autoconnect'] = FALSE;
// Set the database array
    $databases['default']['default'] = [
      'isolation_level' => 'READ COMMITTED',
      'collation' => 'utf8mb4_general_ci',
    ];
  }
  // Connect to the database
  if (function_exists('acquia_hosting_db_choose_active')) {
    acquia_hosting_db_choose_active(
      $conf['acquia_hosting_site_info']['db'],
      'default',
      $databases,
      $conf
    );
  }
// Require line
require '/var/www/site-php/' . $_ENV['AH_SITE_GROUP'] . '/' . $_ENV['AH_SITE_GROUP'] . '-settings.inc';
// Local settings for DDEV, IDE, etc.
if (file_exists(DRUPAL_ROOT . '/' . 'sites/default/settings.local.php')) {
  include DRUPAL_ROOT . '/' . 'sites/default/settings.local.php';
}

In your settings.local.php file for DDEV, IDE, or another local environment, you probably do not need the code to prevent autoconnecting to the database. Instead, you can create the database array in the standard manner and add the collation setting. For example:

  $databases['default']['default'] = [
    'database' => 'drupal',
    'username' => 'drupal',
    'password' => 'drupal',
    'prefix' => '',
    'host' => '127.0.0.1',
    'port' => '3306',
    'isolation_level' => 'READ COMMITTED',
    'collation' => 'utf8mb4_general_ci',
    'namespace' => 'Drupal\\mysql\\Driver\\Database\\mysql',
    'driver' => 'mysql',
    'autoload' => 'core/modules/mysql/src/Driver/Database/mysql/',
  ];

Changing the settings.php file ensures that newly created tables use utf8mb4_general_ci, but it does not update existing tables.

Step 2: Audit Your Database for Mismatched Collations

To identify database tables that do not use utf8mb4_general_ci, run the following SQL command:

SELECT 
    TABLE_NAME, 
    TABLE_COLLATION 
FROM 
    information_schema.TABLES 
WHERE 
    TABLE_SCHEMA = 'your_database_name' 
    AND TABLE_COLLATION != 'utf8mb4_general_ci';

Review the returned tables and plan tests based on the types of site features related to the tables that must be updated.

To check column collation for a specific table:

SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_SET_NAME, 
    COLLATION_NAME 
FROM 
    information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'my_table_name';

The CONVERT TO CHARACTER SET command updates the table collation and the character set for the columns in the table. After you run the command on a table, verify the table to ensure that the columns are updated to utf8mb4.

Step 3: Convert Existing Tables and Columns

To convert a specific table and all of its string columns, run the following SQL command:

SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE table_name 
  CONVERT TO CHARACTER SET utf8mb4 
  COLLATE utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS = 1;

Pre-conversion Checklist and Preparation

1. Generate a database backup in Cloud Platform before you run schema conversion commands:

   copy

   acli api:environments:database-backup-create <application>.<environment> <db_name>

2. Truncate non-essential cache tables, such as cache_*, watchdog, and sessions, before you convert tables. Clearing transient data increases the speed of table conversion operations.
3. Test all database conversion scripts in a non-production environment to verify execution time and identify duplicate key conflicts or other issues before Production deployment.
4. After full testing in non-production environments, consider placing the site in maintenance mode when you run these commands in production. Alternatively, run the database changes through an update hook in a deployment.

Risks of Executing ALTER DATABASE Queries

• Unique Constraint Conflicts (Duplicate Entry Errors): Changing collations poses unique constraint conflicts, such as duplicate entry errors. Collations have different rules for character equality.
  • utf8mb4_0900_ai_ci uses Unicode 9.0 rules and treats accents differently than legacy general_ci or bin collations.
  • If a column contains a UNIQUE index, such as usernames, machine names, or external keys, that permitted two slightly different character strings under the old collation, MySQL 8 treats those strings as identical.
  • This action causes the ALTER TABLE command to fail with Error 1062: Duplicate entry '...' for key ..., leaving the migration incomplete.
• Table Locking and Extended Downtime: The ALTER TABLE ... CONVERT TO CHARACTER SET operation requires MySQL to create a copy of the table, re-encode string values row by row, rebuild table indexes, and replace the file. 
  • Small databases under 1 GB require a few minutes.
  • Large Drupal databases with massive watchdog, node_revision, or field tables require several hours.
  • During this period, MySQL locks affected tables, causing Acquia Cloud platform to display HTTP 500 or HTTP 504 gateway timeouts.
• Foreign Key Constraint Failures: Drupal core and contrib modules can use foreign keys between tables.
  • MySQL requires that parent and child columns in a foreign key relationship use identical character sets and collations.
  • If you convert Table A, but Table A references an unconverted Table B, MySQL returns a foreign key mismatch error and stops the conversion process.