---
title: "How To: Changing the MySQL collation for a Drupal application under Cloud Platform"
date: "2026-04-29T19:26:11+00:00"
summary:
image:
type: "article"
url: "/acquia-cloud-platform/help/97401-how-changing-mysql-collation-drupal-application-under-cloud-platform"
id: "b3545ddc-27b4-4adc-a736-daeecab057a1"
---

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.

Caution

*   These steps may not apply to every case.
*   ****Back up your data:**** Always perform a full database backup before proceeding.
    
*   ****Test in a non-production environment:**** Perform these steps in a non-production environment first to ensure the changes do not adversely affect application functionality and performance.
    
*   ****Expect downtime:**** Changing a column’s collation forces an index regeneration. This operation locks tables and can be time-consuming for large datasets. Place the site in ****Maintenance Mode**** or perform the operation during off-peak hours.
    
*   ****Verify limits:**** Converting to `utf8mb4` increases character byte requirements. Ensure index lengths do not exceed MySQL limits (e.g., 767 bytes for older configurations).
    

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/](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';

Caution

*   **Reminder:** Before any changes are done, ensure you have fresh backups of your database, and test the conversion process in a staging environment to avoid any data loss or downtime.

###   
**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](https://docs.acquia.com/acquia-cloud-platform/overriding-drupal-databases-settings)  documentation[Acquia database override documentation](https://docs.acquia.com/acquia-cloud-platform/overriding-drupal-databases-settings) [Acquia database override documentation](https://docs.acquia.com/acquia-cloud-platform/overriding-drupal-databases-settings) [Acquia database override documentation](https://docs.acquia.com/acquia-cloud-platform/overriding-drupal-databases-settings)and 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.