---
title: "Database Character Set and Collation Best Practices"
date: "2026-07-29T18:53:59+00:00"
summary: "Fix Drupal collation issues on Acquia Cloud. Learn best practices for utf8mb4 setup, auditing mismatched tables, and safe ALTER TABLE execution."
image:
type: "article"
url: "/acquia-cloud-platform/help/98716-database-character-set-and-collation-best-practices"
id: "f92b1c19-6b51-4b25-b988-d7eb0e1e1b01"
---

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
----------------------------------------

Warning

Running `ALTER TABLE ... CONVERT TO` statement on large databases can lock tables and causes HTTP 500 or HTTP 504 downtime. 

1.  Generate a database backup in Cloud Platform before you run schema conversion commands:
    
        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.
*   Index Length Limits (Index column size too large): In MySQL, `utf8mb4` reserves up to 4 bytes per character, compared to 3 bytes for `utf8` or 1 byte for `latin1`. If legacy tables exist from older MySQL configurations, converting an indexed `VARCHAR` column, such as `VARCHAR(255`), can exceed the maximum allowed index key length and cause the conversion command to fail.