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 collation defines how the database compares and sorts text. utf8mb4_unicode_ci sorts "café" and "cafe" as equal. utf8mb4_unicode_520_ci uses a newer Unicode standard (5.2.0) with better handling of emoji and supplementary characters. When a query joins two tables or compares columns with different collations, MariaDB can't decide which comparison rules to apply — so it throws an error rather than guessing.

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. Column-level drift also happens outside migrations: I've seen a WooCommerce update ALTER individual columns into wp_wc_orders without specifying a collation, leaving three columns different to their parent table. If you only audit table defaults, you miss these.

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 — this matters, because a table-level ALTER alone doesn't fix column-level mismatches:

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. Note that DB_COLLATE only controls WordPress code that goes through its own charset helpers — a plugin running raw CREATE TABLE SQL still inherits the database default. That's why the ALTER DATABASE in step two matters. Belt and braces — set both.

The MariaDB 11.x Wrinkle: utf8mb4_uca1400_ai_ci

If either server in a migration runs the MariaDB 11.x series, there's a newer variant of this problem to watch for: utf8mb4_uca1400_ai_ci, a Unicode Collation Algorithm 14.0 implementation. It has better Unicode support, but it doesn't exist in older MariaDB versions, and WordPress core doesn't use it. MariaDB 11.8 changed the shipped global default collation from latin1_swedish_ci to utf8mb4_uca1400_ai_ci, but servers across the 11.x line turn up configured with it as the default.

I hit exactly this a few weeks later on another store, migrated from MariaDB 10.6 to 11.4. The symptom was subtler than a visible database error: product search had stopped returning results — but only some searches. Searching for "hoodie" returned nothing, "t-shirt" worked fine. Product attribute filters on the shop page were broken too — clicking "Large" returned zero products even though the store had hundreds of large items in stock. Nobody noticed for two weeks, because WooCommerce swallows the database error and returns an empty result set instead of an error page. From the customer's perspective, the search just "found nothing." Only after enabling WP_DEBUG_LOG and reproducing a failing search did the familiar error show up:

WordPress database error Illegal mix of collations
(utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_uca1400_ai_ci,IMPLICIT)
for operation '=' for query SELECT ...

The imported tables had kept their original utf8mb4_unicode_520_ci collation — that part was fine. The problem started when WooCommerce ran its post-migration housekeeping and regenerated its lookup tables. On some stores it drops and recreates wp_wc_product_meta_lookup during a re-index, and because WooCommerce's schema code doesn't always specify a collation explicitly, the recreated tables inherited the server default — utf8mb4_uca1400_ai_ci. Eight tables, all WooCommerce lookup and analytics tables created after the migration, sat on the new collation while the other 42 stayed on utf8mb4_unicode_520_ci. Every product search query that joined wp_posts to wp_wc_product_meta_lookup failed. The fix was the same conversion script as above; on that store (around 15,000 orders), each ALTER completed in under 10 seconds, and search and attribute filters worked immediately afterwards.

There's a second trap in the other direction. If a table gets created with utf8mb4_uca1400_ai_ci and you later export that database and import it on an older server, the import fails completely:

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

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.x server (where the collation exists), run the ALTER TABLE ... CONVERT TO script from above to standardise, and re-export.

WordPress core is tracking uca1400 support in Trac ticket #58871, but it's been awaiting review since 2023. Until it lands, WordPress will never choose uca1400 on its own — but plugins that don't specify a collation will inherit the server default, and you get the same split I found on both these stores: WordPress tables on one collation, plugin tables on another, and silent query failures wherever they meet.

The Migration Collation Checklist

After these incidents, I added a collation check to my migration process — on both sides. Before exporting the database, a quick sanity check tells me whether the source is already mixed:

wp db query "SELECT table_collation, COUNT(*) as table_count FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE' GROUP BY table_collation;"

If that returns more than one row, I fix it before the export. After importing on the new server, and before I declare a migration complete, I run the full audit:

#!/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 — or worse, weeks of invisible breakage when WooCommerce quietly returns empty results instead of an error.

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 or newer MariaDB version, a plugin update changes a query to compare columns across tables, or WooCommerce recreates a lookup table on the new server's default, 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 →