Fixing 'Illegal Mix of Collations' After Migrating a WordPress Site to a New Server

A client's agency migrated a WooCommerce store from a cPanel server running MariaDB 10.5 to a new CloudPanel VPS running MariaDB 10.11. The migration went smoothly — site loaded, orders were intact, products displayed correctly. Then a customer tried to search for a product and the search results page returned a database error instead of results.

The error in the debug log was unmistakable:

WordPress database error Illegal mix of collations
(utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT)
for operation 'like'

The site had been running for four years without a single collation error. One migration later, and any query that joined or compared columns across tables was at risk of failing.

Why Migrations Create Collation Mismatches

A MySQL or MariaDB database has collation settings at three levels: the database default, the table default, and the individual column. When WordPress creates a table, it picks a collation based on what the database server supports. On MySQL 5.6+ and MariaDB 10.0+, WordPress uses utf8mb4_unicode_520_ci. But if a plugin creates its own tables without explicitly specifying a collation, those tables inherit the database default — which might be utf8mb4_unicode_ci, utf8mb4_general_ci, or something else entirely depending on the server configuration.

On the old cPanel server, this mismatch was invisible. Both utf8mb4_unicode_ci and utf8mb4_unicode_520_ci existed in the database, but MariaDB 10.5 handled implicit collation comparisons between them without error in most queries. After migrating to MariaDB 10.11, the stricter collation handling surfaced the conflict.

The mysqldump export preserves the exact collation from the source. When you import that dump into a server with a different default collation, you're carrying the old server's collation decisions into a new environment. WordPress core tables might be utf8mb4_unicode_520_ci, plugin tables might be utf8mb4_unicode_ci, and the new server's database default might be utf8mb4_general_ci. Three different collations in one database, all silently coexisting until a query tries to compare columns across them.

Diagnosing the Mess

Before changing anything, I needed to see the full picture. This query lists every table and its collation:

SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'wp_store'
ORDER BY TABLE_COLLATION, TABLE_NAME;

On the client's database, the output looked like this (abbreviated):

+-------------------------------+-----------------------------+
| TABLE_NAME                    | TABLE_COLLATION             |
+-------------------------------+-----------------------------+
| wp_options                    | utf8mb4_unicode_520_ci      |
| wp_posts                      | utf8mb4_unicode_520_ci      |
| wp_postmeta                   | utf8mb4_unicode_520_ci      |
| wp_users                      | utf8mb4_unicode_520_ci      |
| wp_terms                      | utf8mb4_unicode_520_ci      |
| wp_wc_orders                  | utf8mb4_unicode_520_ci      |
| wp_actionscheduler_actions    | utf8mb4_unicode_ci          |
| wp_actionscheduler_claims     | utf8mb4_unicode_ci          |
| wp_actionscheduler_groups     | utf8mb4_unicode_ci          |
| wp_actionscheduler_logs       | utf8mb4_unicode_ci          |
| wp_yoast_seo_links           | utf8mb4_unicode_ci          |
| wp_wc_product_meta_lookup     | utf8mb4_unicode_ci          |
| wp_mailpoet_subscribers       | utf8mb4_unicode_ci          |
+-------------------------------+-----------------------------+

WordPress core tables had utf8mb4_unicode_520_ci. Plugin tables — Action Scheduler, Yoast, WooCommerce product meta lookup, MailPoet — had utf8mb4_unicode_ci. Two different collations, and any query that joined across the boundary would throw the error.

But table-level collation is only half the story. Individual columns can have their own collation that differs from the table default. This is the query that shows the real scope of the problem:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'wp_store'
  AND COLLATION_NAME IS NOT NULL
  AND COLLATION_NAME != 'utf8mb4_unicode_520_ci'
ORDER BY TABLE_NAME, COLUMN_NAME;

This returned 47 columns across 13 tables with the wrong collation. Some of them were text columns in WooCommerce's product meta lookup table — exactly the columns involved in product search queries.

I also checked the database-level default:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'wp_store';

+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_general_ci     |
+----------------------------+------------------------+

Three collations in one database. The database default was utf8mb4_general_ci (inherited from the new server's global default), WordPress core tables used utf8mb4_unicode_520_ci, and plugin tables used utf8mb4_unicode_ci.

The Fix: Standardise Everything to One Collation

The target collation was utf8mb4_unicode_520_ci — it's what WordPress uses by default on any reasonably modern MySQL or MariaDB, and it provides proper Unicode 5.2.0 sorting rules. Using utf8mb4_unicode_ci would also work, but aligning with WordPress core's own choice avoids future mismatches when WordPress creates new tables.

Step one: back up the database. Do not skip this.

wp db export /root/pre-collation-fix-$(date +%F).sql --path=/var/www/html

Step two: change the database default so any future tables created by plugins inherit the correct collation.

ALTER DATABASE wp_store
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_520_ci;

Step three: convert every table. The CONVERT TO syntax changes the table default and every column in the table:

ALTER TABLE wp_actionscheduler_actions
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_520_ci;

Doing this for 13 tables by hand is tedious and error-prone. I used this script to generate and execute the conversion for every mismatched table:

#!/bin/bash
DB_NAME="wp_store"
TARGET_COLLATION="utf8mb4_unicode_520_ci"

# Find tables with a wrong table-level default OR any column-level mismatch
mysql -u root -N -e "
  SELECT DISTINCT TABLE_NAME FROM (
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '${DB_NAME}'
      AND TABLE_COLLATION != '${TARGET_COLLATION}'
      AND TABLE_TYPE = 'BASE TABLE'
    UNION
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '${DB_NAME}'
      AND COLLATION_NAME IS NOT NULL
      AND COLLATION_NAME != '${TARGET_COLLATION}'
  ) AS mismatched;
" | while read table; do
  echo "Converting ${table}..."
  mysql -u root -e "
    ALTER TABLE \`${DB_NAME}\`.\`${table}\`
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE ${TARGET_COLLATION};
  "
done

The UNION ensures you catch tables where the table default is already correct but one or more columns still carry an old collation — a common leftover from plugins that set explicit column collations or from a previous ALTER TABLE ... DEFAULT that only changed the table default without converting existing columns.

On this database, the script completed in about 12 seconds. Larger WooCommerce stores with millions of rows in wp_postmeta or wp_wc_orders will take longer — I've seen conversions take 5-10 minutes on tables with 2M+ rows. During that time, the table is locked. For production stores, I run this during a maintenance window.

Step four: verify the fix. I re-ran the diagnostic query to confirm everything was aligned:

mysql -u root -e "
  SELECT COUNT(*) AS mismatched_columns
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'wp_store'
    AND COLLATION_NAME IS NOT NULL
    AND COLLATION_NAME != 'utf8mb4_unicode_520_ci';
" wp_store

+--------------------+
| mismatched_columns |
+--------------------+
|                  0 |
+--------------------+

Zero mismatched columns. Product search worked again. The debug log stopped filling with collation errors.

Setting wp-config.php Correctly

The client's wp-config.php had the default WordPress configuration:

define( 'DB_CHARSET', 'utf8mb4' );
define( 'DB_COLLATE', '' );

When DB_COLLATE is empty, WordPress decides the collation itself — typically utf8mb4_unicode_520_ci for core tables. But this is only applied when WordPress creates a table. Plugins that create their own tables often don't check DB_COLLATE and just inherit the database server's default.

After fixing the collation, I explicitly set it in wp-config.php:

define( 'DB_CHARSET', 'utf8mb4' );
define( 'DB_COLLATE', 'utf8mb4_unicode_520_ci' );

This won't retroactively fix existing tables, but it ensures WordPress passes the correct collation to any CREATE TABLE statement going forward.

The MariaDB 11.8 Wrinkle

If you're migrating between servers and one of them runs MariaDB 11.8 or later, there's a newer problem to watch for. MariaDB 11.8 changed the global default collation from latin1_swedish_ci to utf8mb4_uca1400_ai_ci. This is a Unicode Collation Algorithm 14.0 implementation — better Unicode support, but it doesn't exist in older MariaDB versions.

If a plugin creates a table on a MariaDB 11.8 server without specifying a collation, that table gets utf8mb4_uca1400_ai_ci. Export that database with mysqldump and try to import it on a MariaDB 10.6 server, and you'll get:

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_uca1400_ai_ci'

The import fails completely. You need to replace the collation in the dump file before importing. A naive global sed is risky — if any row data contains the collation string inside a PHP-serialised value (schema caches, migration plugin logs, backup metadata), the replacement changes the byte length and corrupts the serialised data. Limit the replacement to DDL statements only:

sed -i '/^CREATE TABLE\|^) ENGINE=\|COLLATE=/s/utf8mb4_uca1400_ai_ci/utf8mb4_unicode_520_ci/g' dump.sql

This targets only CREATE TABLE blocks and ENGINE= lines where collation declarations actually live, leaving row data untouched. If you're not confident the dump is clean, the safest approach is to import into a MariaDB 11.8+ server (where the collation exists), then run the ALTER TABLE ... CONVERT TO script from above to standardise, and re-export.

WordPress core is tracking this in Trac ticket #58871 — proper uca1400 support is coming, but it isn't in core yet. For now, standardising on utf8mb4_unicode_520_ci across all servers avoids the problem.

The Migration Collation Checklist

After this incident, I added a collation check to my migration process. Before I declare a migration complete, I run:

#!/bin/bash
DB_NAME="$1"
TARGET="utf8mb4_unicode_520_ci"

echo "=== Database default ==="
mysql -u root -N -e "
  SELECT DEFAULT_COLLATION_NAME
  FROM INFORMATION_SCHEMA.SCHEMATA
  WHERE SCHEMA_NAME = '${DB_NAME}';
"

echo ""
echo "=== Tables with wrong collation ==="
mysql -u root -N -e "
  SELECT TABLE_NAME, TABLE_COLLATION
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = '${DB_NAME}'
    AND TABLE_COLLATION != '${TARGET}'
    AND TABLE_TYPE = 'BASE TABLE';
"

echo ""
echo "=== Columns with wrong collation ==="
mysql -u root -N -e "
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = '${DB_NAME}'
    AND COLLATION_NAME IS NOT NULL
    AND COLLATION_NAME != '${TARGET}';
"

If any of those return non-empty or non-zero results, I fix them before the site goes live. Five minutes of checking saves hours of debugging when a customer reports that search is broken.

The Broader Lesson

Collation mismatches are invisible until they're not. A WordPress site can run for years with mixed collations across its tables and never throw an error — because the queries it runs don't happen to join those specific columns. Then you migrate to a server running a stricter MariaDB version, or a plugin update changes a query to include a WHERE clause that compares columns across tables, and suddenly the error surfaces.

The fix is always the same: pick one collation, standardise everything to it, and set DB_COLLATE explicitly so it stays that way. The diagnosis is the hard part — you need to check all three levels (database, table, column) and you need to know which collation you're standardising to.

For most WordPress sites in 2026, utf8mb4_unicode_520_ci is the right target. It's what WordPress core uses, it's supported by every MySQL version from 5.6 onward and every MariaDB from 10.0 onward, and it handles Unicode correctly enough for any language. Save yourself the debugging session — check your collations after every migration, not after the first customer complaint. If your database problems go deeper than collation — bloated tables, slow queries, autoload issues — I've written about those too in WordPress & WooCommerce Database Performance Optimisation.

---

Stop Firefighting. Start Maintaining.

I manage 70+ WordPress sites for agencies and businesses. Whether you need ongoing maintenance, emergency support, or a one-off performance fix — I can help.

View Maintenance Plans → Get in Touch →