How Mixed Database Collations Silently Broke Product Search on a WooCommerce Store
· 9 min read
A client reported that product search on their WooCommerce store had stopped returning results. Not all searches — just some. Searching for "hoodie" returned nothing. "T-shirt" worked fine. Product attribute filters on the shop page were also broken — clicking "Large" returned zero products even though the store had hundreds of large items in stock.
The store had been migrated to a new server two weeks earlier. Everything had seemed fine after the migration. Checkout worked, orders were processing, the admin dashboard was responsive. Nobody noticed the search issue until a customer complained.
The Error Nobody Saw
I enabled WP_DEBUG_LOG and reproduced the failing search. The debug log told me exactly what was wrong:
WordPress database error Illegal mix of collations
(utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_uca1400_ai_ci,IMPLICIT)
for operation '=' for query SELECT ...
This error was being thrown on every product search query that involved a JOIN between wp_posts and wp_wc_product_meta_lookup — the WooCommerce lookup table that speeds up product queries. The query was comparing string columns from two tables that had different collation rules, and MariaDB refused to execute it.
The reason nobody had caught this sooner: WooCommerce swallows the database error and returns an empty result set instead of showing an error page. From the customer's perspective, the search just "found nothing."
What Collation Actually Is
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. utf8mb4_uca1400_ai_ci uses Unicode 14.0.0 — MariaDB's latest.
When you JOIN two tables or compare columns with different collations, MariaDB can't decide which comparison rules to apply. It throws an error rather than guessing.
How the Mismatch Happened
The old server ran MariaDB 10.6. The new server ran MariaDB 11.4.
When I imported the database dump onto the new server, the existing tables kept their original collation — utf8mb4_unicode_520_ci, which is what WordPress sets when it detects MySQL 5.6+ or MariaDB 10.x. That part was fine.
The problem started when WooCommerce ran its post-migration housekeeping. It regenerated the product lookup tables. On some stores, it drops and recreates wp_wc_product_meta_lookup during a re-index. Because the CREATE TABLE statement in WooCommerce's schema code doesn't always specify a collation explicitly, MariaDB used the server default — which on 11.4 is utf8mb4_uca1400_ai_ci.
Now the core WordPress tables used one collation. The WooCommerce lookup tables used another. Every query that joined them broke.
Diagnosing the Mismatch
I ran a single query to see the state of every table in the database:
SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = 'wp_production'
ORDER BY table_collation, table_name;The output confirmed the split. Forty-two tables used utf8mb4_unicode_520_ci. Eight tables — all WooCommerce lookup and analytics tables created after the migration — used utf8mb4_uca1400_ai_ci.
I also checked for column-level mismatches, which are sneakier:
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'wp_production'
AND collation_name IS NOT NULL
AND collation_name != 'utf8mb4_unicode_520_ci'
ORDER BY table_name;Three columns in wp_wc_orders had a different collation to their parent table. These had been ALTERed individually by a WooCommerce update that added new columns without specifying the collation.
The Fix
The fix is straightforward but needs to be done methodically. I generated ALTER statements for every mismatched table, checking both the table-level default and individual columns:
SELECT DISTINCT CONCAT(
'ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;'
)
FROM (
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'wp_production'
AND table_collation != 'utf8mb4_unicode_520_ci'
AND table_type = 'BASE TABLE'
UNION
SELECT table_name FROM information_schema.columns
WHERE table_schema = 'wp_production'
AND collation_name IS NOT NULL
AND collation_name != 'utf8mb4_unicode_520_ci'
) AS mismatched;This catches tables where the default is wrong and tables where individual columns have drifted — the column-only case is easy to miss if you only audit table defaults. It produced eight ALTER statements. I reviewed them, then ran them during a low-traffic window:
ALTER TABLE `wp_wc_product_meta_lookup` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_wc_orders` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_wc_orders_meta` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_wc_order_addresses` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_wc_order_operational_data` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_wc_order_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_wc_order_product_lookup` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_wc_customer_lookup` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;CONVERT TO CHARACTER SET changes the collation on the table and every column in it. This matters — a table-level ALTER alone doesn't fix column-level mismatches.
For a store with a large orders table, these ALTERs can take minutes. On this store (around 15,000 orders), each one completed in under 10 seconds.
After the ALTERs, I locked down the collation at two levels. First, the database default — so any plugin that runs raw CREATE TABLE without specifying a collation inherits the right one:
ALTER DATABASE wp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;Then in wp-config.php, to ensure WordPress itself uses the same collation when creating tables through dbDelta():
define('DB_COLLATE', 'utf8mb4_unicode_520_ci');DB_COLLATE only controls WordPress code that uses its own charset helpers. Without the ALTER DATABASE, a plugin running raw SQL would still inherit the server default. Belt and braces — set both.
Verification
After fixing the tables, I verified that every table and column in the database now used the same collation:
SELECT COUNT(*) AS mismatched_tables
FROM information_schema.tables
WHERE table_schema = 'wp_production'
AND table_collation != 'utf8mb4_unicode_520_ci'
AND table_type = 'BASE TABLE';
SELECT COUNT(*) AS mismatched_columns
FROM information_schema.columns
WHERE table_schema = 'wp_production'
AND collation_name IS NOT NULL
AND collation_name != 'utf8mb4_unicode_520_ci';Both queries returned zero. Product search worked immediately. Attribute filters returned results again.
Why This Will Catch More People Soon
MariaDB started shipping utf8mb4_uca1400_ai_ci as the default collation in recent releases — the server in this incident ran 11.4, but the change applies across the 11.x line. Anyone migrating a WordPress database to a modern MariaDB server — or upgrading their existing installation past the 10.x series — is at risk of exactly this problem.
WordPress core hasn't added uca1400 support yet. There's an open Trac ticket (#58871) proposing it, but it's been in "awaiting review" since 2023. Until that lands, WordPress will never choose uca1400 on its own — but plugins that don't specify a collation in their table creation code will inherit the server default.
The result is the same split I found on this store: WordPress tables on one collation, plugin tables on another, and silent query failures wherever they meet.
What I Do Now Before Every Migration
I added a collation audit to my pre-migration and post-migration checklists. Before exporting the database, I run:
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, I run it again. Two minutes of checking prevents two weeks of invisible breakage.
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.
