Fix ERROR 1267: Illegal Mix of Collations in MySQL

The Error

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

MySQL is telling you it found two string values with different collation rules and has no idea which one wins. Rather than guess, it bails.

The classic trigger: a JOIN between two tables where one column is utf8mb4_unicode_ci and the other is utf8mb4_general_ci. Maybe one table was created six months ago and the other last week — somebody changed the server default in between. That's all it takes.

Why It Happens

Every string value in MySQL carries a collation. Comparing two strings is fine as long as one collation takes precedence. Both marked IMPLICIT means neither wins — MySQL refuses to proceed, and you get 1267.

Common causes:

• Tables created at different times with different DEFAULT COLLATE settings
• A column-level collation overriding the table default
• Stored procedures or functions returning values with a different collation
• Session variable collation_connection out of sync with the table collation
• Database restored from another server that had different defaults

Step 1: Find Where the Mismatch Is

Before fixing anything, pinpoint exactly which columns disagree. Run this against the tables in your failing query:

-- Check collation on a specific table
SHOW FULL COLUMNS FROM your_table_name;

-- Scan all string columns across the whole database
SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CHARACTER_SET_NAME,
  COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND DATA_TYPE IN ('varchar', 'char', 'text', 'mediumtext', 'longtext')
ORDER BY TABLE_NAME, COLUMN_NAME;

Also check the database-level default:

SELECT
  SCHEMA_NAME,
  DEFAULT_CHARACTER_SET_NAME,
  DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'your_database_name';

You'll often see a mix — some columns showing utf8mb4_unicode_ci, others utf8mb4_general_ci. That's your culprit list.

Step 2: Fix the Collation Mismatch

Option A — COLLATE inline (no schema change needed)

Fastest path to unblock a specific query. No ALTER, no downtime:

SELECT *
FROM table_a a
JOIN table_b b
  ON a.name COLLATE utf8mb4_unicode_ci = b.name COLLATE utf8mb4_unicode_ci
WHERE a.status = 'active';

Or use CONVERT if the character sets also differ:

SELECT *
FROM table_a a
JOIN table_b b
  ON CONVERT(a.name USING utf8mb4) COLLATE utf8mb4_unicode_ci
   = CONVERT(b.name USING utf8mb4) COLLATE utf8mb4_unicode_ci;

Treat this as a band-aid. It stops the bleeding, but the column mismatch still exists underneath.

Option B — ALTER the offending column (recommended)

Fix the root cause directly. Pick the collation you want to standardize on, then alter each mismatched column:

-- Single column
ALTER TABLE table_b
  MODIFY COLUMN name VARCHAR(255)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Multiple columns in one shot:

ALTER TABLE table_b
  MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  MODIFY COLUMN email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Option C — Convert the entire table

When half the table is wrong, altering column by column gets tedious. Convert everything at once:

ALTER TABLE table_b
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

One statement, all string columns updated. The catch: MySQL locks the table during the rewrite. On a 500K-row table that can take 30–60 seconds — schedule it during low-traffic hours.

Option D — Fix the database default

New tables inherit the database default collation. If that default is wrong, every table you create will eventually cause 1267 again:

ALTER DATABASE your_database_name
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Important: this only affects new tables created after this point. Existing tables keep their current collation until you ALTER them explicitly.

Step 3: Verify the Fix

Re-run the failing query — it should complete cleanly. Then confirm the column collations look right:

-- Confirm column collations updated
SHOW FULL COLUMNS FROM table_b;

-- Or via information_schema
SELECT COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'table_b';

You want utf8mb4_unicode_ci showing consistently across every column you touched. Any stragglers will cause 1267 again the moment someone queries them.

Tips

unicode_ci vs general_ci — which one to pick?

On MySQL 5.7 or MariaDB, go with utf8mb4_unicode_ci. It follows the Unicode standard and handles multilingual text correctly — accented characters, CJK scripts, etc.

Running MySQL 8.0+? utf8mb4_0900_ai_ci is the new default and noticeably faster for sort-heavy queries. Only switch to it if you can update all your tables at once — partial adoption just recreates the mismatch problem.

The real rule: pick one collation and use it everywhere. Mixing is exactly what causes 1267.

Lock in server defaults via my.cnf

Set collation at the server level so every new database and table starts with the right settings automatically:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

Restart MySQL after editing. Databases created afterward — without an explicit collation — will inherit these values.

Stored procedures as a source of collation conflicts

Procedure parameters can have their own collation declarations that silently conflict with your table columns. Check a suspicious procedure with:

SHOW CREATE PROCEDURE your_procedure_name;

Look for CHARACTER SET and COLLATE in the parameter list. Mismatches there are easy to miss because the error only surfaces at runtime when the procedure actually compares values.

Bulk-fix many tables at once

Dozens of tables to fix? Generate the ALTER statements from information_schema rather than typing them out:

SELECT CONCAT(
  'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS fix_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_TYPE = 'BASE TABLE';

Review the output before running it anywhere near production. Each statement locks its table during execution — sequence them during a maintenance window if table sizes are significant.