MySQL Error 1267: Fixing Illegal Collation Mix

So you're running a query, maybe a JOIN or a WHERE clause, and MySQL throws Error 1267: Illegal mix of collations. I know this error is infuriating. It usually hits when you're pulling data from two tables and one column uses utf8mb4_general_ci while the other uses latin1_swedish_ci. Or maybe one is utf8mb4_unicode_ci and the other is utf8mb4_general_ci. Common trigger: you migrated data from an old system (WordPress 4.x, for example) to a new one, but the tables kept their original collations.

Why does this happen?

MySQL compares strings based on the column's collation. Collation is the rule set for how characters are sorted and compared. If you mix rules, MySQL gets confused. It's like comparing apples in English and apples in French — the letters are different even though they look the same. The error stops the query because MySQL doesn't know which rule to use.

The fix

I'll walk you through the fix in three steps. You can do this with SQL commands — no GUI needed. Skip the idea of changing the whole database collation blindly; that breaks other things. Let's target only the problematic columns.

Step 1: Find the columns causing the issue

Run this query to see all columns in your database with their collations. Adjust the database name.

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

Look for columns with different collations. Most of the time, one table uses latin1_swedish_ci and another uses utf8mb4_general_ci. That's your culprit.

Step 2: Alter the column collation to match

Pick one collation for the join. I recommend utf8mb4_unicode_ci because it supports more characters and is the modern standard. Change the column's collation using ALTER TABLE. Here's an example:

ALTER TABLE your_table_name
MODIFY your_column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Replace your_table_name, your_column_name, and the data type (VARCHAR(255) is common). If the column is INT or another non-string type, collation doesn't apply — so ignore it.

Step 3: Test the query

After changing, run your original query again. If it works, you're done. If not, check for more columns with mismatched collations in the same JOIN or WHERE clause. Repeat steps 1 and 2 for each.

What if it still fails?

Sometimes the error comes from a UNION or a subquery. Check if you have a UNION between tables with different collations — that's a common trap. Also, look at temporary tables or derived tables (subqueries in FROM clause). They inherit collation from the columns they reference. In that case, you can force the collation in the query using COLLATE:

SELECT a.column1 COLLATE utf8mb4_unicode_ci, b.column2
FROM table_a a
JOIN table_b b ON a.column1 = b.column2

But I don't like this approach for long-term fixes. It's a band-aid. Better to fix the column collation permanently.

Pro tip

If you're setting up a new database, use utf8mb4_unicode_ci from the start. It handles emoji and special characters. Avoid latin1 unless you absolutely need it for legacy systems. This error shows up less if your whole database is consistent.

When to call it a day

If you've changed all string columns to the same collation and the query still fails, the error might be from a parameter in your application code. For example, if you're using PHP PDO and the connection charset is different from the table collation. Check your connection string: it should match the database collation. For example, if your database is utf8mb4, set the connection to SET NAMES utf8mb4.

That's it. Error 1267 is annoying but it's just a mismatch. Once you find the bad columns, the fix is fast. I've seen this a hundred times in WordPress and Drupal sites — always the same cause. Good luck.