The Illegal mix of collations

Here’s the exact error we’re talking about:

Note that the wp_xxxx_ part of those table names is a table prefix, and yours will look different. It might be wp_ if you took the WordPress default and never changed it, or it might be something custom like wp_a1b2c3_ if your installer randomized it or your host set it that way. The prefix doesn’t matter for diagnosing this error. What matters is the part after the prefix and the collation names in the error message.

While we’re on the subject: if your prefix is the default wp_, you should fix that. It’s the single easiest piece of information for an attacker to assume about your site, and changing it closes off a whole category of automated SQL injection attempts that hardcode wp_ into their payloads. That’s a different post for a different day, but file it away.

This particular error is thrown by Rank Math when it tries to pull analytics data, but the underlying problem isn’t a Rank Math problem. Rank Math is just the plugin that happens to be unlucky enough to expose what’s already broken in your database and bubble it up to your admin screen.

Quick aside for Glimmernet customers

If you’re hosted with us, stop reading and don’t run any SQL. Submit a ticket and we’ll fix it for you, usually within a few hours. You can email [email protected] or open a ticket at support.glimmernet.com. This is exactly the kind of thing your managed WordPress hosting plan covers, and it’s faster for us to do it than for you to read the rest of this post.

If you’re not a Glimmernet customer, keep going.

What’s Actually Going Wrong

A collation is the set of rules MySQL uses to compare and sort text. Two columns that store the same characters can still refuse to talk to each other if they were created with different collation rules. When MySQL tries to join one table to another on a text column, it checks that both sides use the same collation. If they don’t, it throws this error and refuses to run the query.

In the query above, Rank Math is joining its _analytics_ga table to its _analytics_objects table on the page column. One of those tables was created with utf8mb4_unicode_520_ci, the other with utf8mb4_unicode_ci, and MySQL won’t compare them.

This happens for a few common reasons:

• The site was migrated from an older MySQL version to a newer one, and the export carried old collations forward while new tables got created with the new default.
• A plugin was installed at a different point in the database’s history and created its own tables using whatever the server default was at that moment.
• The database was restored from a backup that came from a server with different defaults.
• WordPress core’s default collation changed over time. Older sites were built on utf8_general_ci, then utf8mb4_unicode_ci, and modern installs use utf8mb4_unicode_520_ci when MySQL supports it. When core upgrades happen, the migration routine only touches a small list of known core tables. Any custom tables, plugin tables, and columns added by older plugins stay on their original collation, which is how you end up with a mix.
• A plugin developer didn’t bother checking the current database collation when installing, and just hardcoded a collation into their CREATE TABLE statements. This one is more common than it should be, and it’s how a single careless plugin can leave a permanent mismatch in an otherwise consistent database.

The end result is the same. You have a Frankenstein database where different tables speak slightly different dialects, and the moment a plugin tries to join across the dialects, everything falls apart.

Why You Should Care

If you ignore this error, four things happen, and none of them are good.

Your Rank Math Analytics widgets and reports stop working properly. The query in the error message above is the one that joins traffic data from Google Analytics to the post and page objects on your site. When that join fails, the widget that depends on it returns nothing or returns partial data. The underlying data is still there. Rank Math collected it through its scheduled tasks, it’s sitting in the _analytics_ga and _analytics_objects tables, but the report query can’t read across them. You’re paying for the plugin and looking at an empty dashboard.

You may be making marketing decisions on bad data. The whole point of the Rank Math Analytics dashboard is to tell you which pages are pulling traffic, which ones are climbing, and which ones are falling off. If you’re feeding that data into your digital marketing strategy, you’re making decisions on a partial picture. You might double down on a page your dashboard says is dead when it’s actually your top performer. You might kill content that’s quietly ranking. If you’re running SEO work based on these numbers, the priority list is wrong. If you’re using them to inform PPC campaigns, bidding on landing pages that look underperforming in your dashboard but actually convert, you are actively burning ad spend on a bad signal. Broken analytics aren’t a cosmetic problem. They’re a budget problem.

Your error log fills up with thousands of these messages. We’ve taken over sites with debug logs in the hundreds of megabytes, almost entirely composed of collation errors. That eats disk space, slows down log rotation, and makes it impossible to find the errors that actually matter when something else breaks.

The problem spreads. Once you have mixed collations, every new plugin that does joins across those tables will hit the same wall. We’ve seen this break WooCommerce searches, membership plugins, custom reporting tools, and form submissions. It’s not a Rank Math problem. It’s a foundation problem that Rank Math just happened to find first.

The Fix

You have three options. We’ll go through all of them.

Option 1: Let Rank Math Try First

Rank Math has a built-in tool for this exact scenario because they get the support tickets constantly. Go to WordPress Dashboard → Rank Math → Status & Tools → Database Tools and click the Fix Collations button. This will attempt to align the collations on Rank Math’s own tables. Rank Math documents this tool in their Status and Tools knowledge base article if you want to read their version of the instructions.

If your problem is isolated to Rank Math’s tables, this works and you’re done. If the underlying mismatch is elsewhere in your database, this won’t fix it permanently, because the next time Rank Math creates or modifies a table, the wider mismatch will reassert itself.

Try this first. If the errors come back within a few days, move on to Option 2 or 3.

Option 2: Fix the Whole Database with SQL

This is the real fix. It aligns every table and every text column in your database to a single collation so this problem doesn’t come back and doesn’t spread to other plugins.

Before you do anything, take a full database backup. Not a “the host probably has one” backup. An actual backup you’ve downloaded to your local machine and verified opens correctly. We recommend doing this through your hosting control panel and also through a tool like WP-CLI or phpMyAdmin so you have two copies. Collation changes are generally safe, but anything that runs ALTER TABLE on every table in your database is something you want to be able to roll back from.

First, decide which collation you’re standardizing on. The two candidates are:

• utf8mb4_unicode_ci — older, broadly compatible, what WordPress used as default for years
• utf8mb4_unicode_520_ci — newer, better Unicode sorting, what WordPress uses on modern installs

For most sites, utf8mb4_unicode_520_ci is the right choice because it matches what WordPress core uses today on new installs. If you have legacy plugins that explicitly require utf8mb4_unicode_ci, use that instead. The important thing is picking one and applying it everywhere.

Set the database default first. Replace your_database_name with your actual database name, which you can find in wp-config.php as the value of DB_NAME:

That sets the default for any new tables, but it doesn’t touch existing tables. To convert every existing table, run this query first, which generates a list of ALTER TABLE statements for every table in your database:

Copy the output of that query and run it as a new SQL batch. That will convert every table in your database to the same collation in one pass, including the text columns inside them. The CONVERT TO CHARACTER SET form is important. The shorter ALTER TABLE ... COLLATE syntax only changes the table’s default and leaves individual columns alone, which is how you end up back where you started.

If you want to verify the fix worked, run this to see which collations are still in use across your tables:

You should see one collation listed for every table. If you still see multiple, run the convert statements again.

Then finish by checking wp-config.php. You’ll see a line that looks like this:

The honest recommendation here is to leave it empty. WordPress’s database connection layer has a function called determine_charset() that automatically picks utf8mb4_unicode_520_ci when the server supports it and falls back to utf8mb4_unicode_ci when it doesn’t. Hardcoding a specific collation in DB_COLLATE is only useful if you have a particular reason to pin it, and it can actually cause problems if you ever migrate to a server with a different MySQL version. If your existing line is empty, leave it empty. If someone previously set it to a specific value, make sure that value matches what you just converted everything to. Don’t add it just for the sake of adding it.

Option 3: Use the Database Collation Fix Plugin

If you don’t have SQL access, can’t get into phpMyAdmin, and your host won’t help you, there’s a plugin called Database Collation Fix that does this automatically. The catch is that it only standardizes on utf8mb4_unicode_ci, not the newer utf8mb4_unicode_520_ci. For most sites that’s fine. If you specifically need the newer collation, you’ll need to use Option 2.

This is the worst of the three options because it requires you to install another plugin and run it on a schedule to keep fixing what shouldn’t keep breaking. But if you’re stuck, it works.

Why This Keeps Happening on Cheap Hosts

The reason we see this constantly on migrations is that budget hosts run a mix of MySQL versions across their fleet, restore backups across servers with different defaults, and don’t think about collation consistency when they roll out database upgrades. Your site gets moved to a new server, half the tables get recreated with the new default, half stay on the old one, and now you have a problem nobody told you about that’s quietly corrupting your error log and breaking your plugins.

When we migrate a site to our managed WordPress hosting, the collation audit is part of the intake. We run the diagnostic queries above, we standardize the database before we hand it back, and we make sure wp-config.php is configured correctly for the server it’s running on. It’s not glamorous work, but it’s the kind of thing that prevents the support tickets that everyone else’s customers are filing.

The Short Version

Run the fix in Rank Math first. If the errors come back, take a backup, then run the two SQL statements above to standardize your whole database on one collation. Check your wp-config.php DB_COLLATE line and leave it empty unless you have a specific reason to pin it. That’s the permanent fix. Anything less is a band-aid.

If you’d rather not touch SQL on your live database and want someone who’s done this a few hundred times to do it for you, that’s what we do. Get in touch and we’ll handle it.