How to Fix MySQL Error 1267: Illegal Mix of Collations

ERROR 1267 (HY000): Illegal mix of collations (collation1,coercibility1) and (collation2,coercibility2) for operation 'op' is raised when MySQL cannot determine a single collation to use for a string comparison, concatenation, or aggregation because the operands carry incompatible collation rules. The error symbol is ER_CANT_AGGREGATE_2COLLATIONS.

Impact

The query is aborted immediately — no rows are read or written. Any outer transaction remains open and must be committed or rolled back by the application. Because collation mismatches often only appear with specific data (for example, when a user-supplied literal is compared against a column that has a different collation from the connection charset), this error can go undetected in development and first appear in production when the connection charset differs from the database default.

ORMs typically surface this as a generic database exception wrapping the 1267 message. Hibernate and SQLAlchemy propagate it unchanged. ActiveRecord raises ActiveRecord::StatementInvalid. In all cases the raw MySQL message is available in the exception cause, which is the fastest way to confirm this error is responsible.

Common Causes

Column-to-column comparison across tables with different collations. A JOIN or WHERE clause comparing a utf8mb4_unicode_ci column to a latin1_swedish_ci column triggers the error because MySQL cannot coerce between the two character sets.
String literal vs. column collation mismatch. A user-supplied string introduced with _charset introducer syntax (or through a connection whose character_set_connection differs from the table column's charset) creates a collation that MySQL cannot reconcile with the column's collation.
Stored procedure or function parameter collation. A routine parameter declared without an explicit CHARSET/COLLATE clause inherits the database default. Passing a value whose effective collation differs from what the parameter resolves to causes the error inside the routine.
Mixed-collation UNION or GROUP BY. Selecting a utf8mb4_bin column in one UNION branch alongside a utf8mb4_unicode_ci column in another, or grouping on an expression that combines columns with different collations, can trigger 1267.
Application-level SET NAMES mismatch. Calling SET NAMES latin1 on a connection while the table columns are declared utf8mb4 causes every string literal sent over that connection to carry latin1_swedish_ci, which is incompatible with the column's collation.

Troubleshooting and Resolution Steps

Identify which columns or expressions are involved.

SHOW CREATE TABLE table_name\G

Look at the CHARSET and COLLATE clauses on each column. Also check the table-level default.

SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db';

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'table_name';

Check the current connection collation.
```
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
```
If character_set_connection or collation_connection differs from the column's charset/collation, literals sent from the application will carry the connection collation, causing a mismatch.

Fix the immediate query using the COLLATE operator.

Force a common collation at query time:

SELECT *
FROM orders o
JOIN customers c ON o.customer_code COLLATE utf8mb4_unicode_ci = c.code COLLATE utf8mb4_unicode_ci;

Or cast the literal:

SELECT * FROM products
WHERE name = _utf8mb4'search term' COLLATE utf8mb4_unicode_ci;

Align column declarations for a permanent fix.

If two tables need to join on a column, they should share the same charset and collation. Alter the outlier column:
```
ALTER TABLE legacy_table
MODIFY COLUMN customer_code VARCHAR(64)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
```
Changing the collation on a large table will rebuild the index; run this during a maintenance window or use pt-online-schema-change / gh-ost.
Standardise the database and connection charset.

Set the database default so new tables inherit a consistent collation:
```
ALTER DATABASE your_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
```
In the application connection string, add charset=utf8mb4 (or the driver-equivalent) so SET NAMES is issued automatically. For example, in MySQL Connector/Python:
```
cnx = mysql.connector.connect(
    host="localhost", user="app", password="...",
    database="your_db", charset="utf8mb4"
)
```

Fix stored procedure parameters.

Declare parameters with an explicit charset so they match the columns they interact with:

CREATE PROCEDURE search_users(
  IN p_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
)
BEGIN
  SELECT * FROM users WHERE name = p_name;
END;

Additional Information

The related error ERROR 1270 (HY000): Illegal mix of collations (ER_CANT_AGGREGATE_3COLLATIONS) fires when three collations conflict in the same expression — the same fixes apply.
ERROR 1253 (42000): COLLATION '...' is not valid for CHARACTER SET '...' is often seen when a COLLATE clause references a collation from a different charset. Collation names encode the charset (e.g., utf8mb4_unicode_ci belongs to utf8mb4, not utf8).
MySQL 8.0 changed the default charset from latin1 to utf8mb4 and the default collation from utf8mb4_general_ci to utf8mb4_0900_ai_ci. Databases migrated from 5.7 to 8.0 without ALTER DATABASE can end up with tables using utf8mb4_general_ci while new tables get utf8mb4_0900_ai_ci, causing collation conflicts on joins.
The COERCIBILITY value in the error message indicates how strongly each operand's collation is "forced". Lower values override higher ones. A coercibility of 0 means the collation was explicitly specified; 4 means it was inherited from the connection. Understanding coercibility helps predict which side MySQL will try to coerce.

Frequently Asked Questions

Why does this query work in my local environment but fail in production?

The most common reason is a different character_set_connection or collation_connection setting between environments. Check SHOW VARIABLES LIKE 'collation%' on both servers. Production may have a different [mysqld] collation-server setting, or the application may issue SET NAMES differently.

Can I fix this without altering tables?

Yes, for a single query you can use the COLLATE operator to coerce both sides to the same collation (as shown in step 3 above). This avoids schema changes but adds verbosity to every query that crosses the collation boundary. For long-term stability, aligning the column declarations is the cleaner solution.

Does utf8mb4_unicode_ci and utf8mb4_general_ci count as an incompatible mix?

Yes. Even though both collations belong to the utf8mb4 charset, MySQL treats them as a collation conflict because it cannot determine which ordering rule to apply. The fix is the same: use COLLATE in the query or align the column declarations to use one consistent collation.

How do I find all tables and columns that are not on my target collation?

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
       CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
  AND COLLATION_NAME != 'utf8mb4_unicode_ci'
ORDER BY TABLE_NAME, COLUMN_NAME;

Replace utf8mb4_unicode_ci with your target collation. Running this query gives you a full list of columns to migrate.