📋

Description

Error 1270, 'Illegal mix of collations', indicates that MySQL encountered columns or string literals with conflicting character set collations during a single operation. This typically happens when comparing, joining, or aggregating data from sources that have different rules for character sorting and comparison, preventing the operation from completing successfully.

💬

Error Message

Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'

🔍

Known Causes

4 known causes

⚠️

Conflicting Table/Column Collations

Performing operations (e.g., JOINs, WHERE clauses) on columns from different tables or even within the same table that have been defined with incompatible collations.

⚠️

String Literal Collation Mismatch

Comparing a column with a specific collation against a string literal that MySQL interprets with a different default or explicit collation, leading to a conflict.

⚠️

Collation Conflict in Aggregations

Using aggregate functions (like GROUP BY, ORDER BY, UNION) on expressions or columns that result in a mix of incompatible collations.

⚠️

Inconsistent Database Defaults

When the default collation for the database or server differs from the explicit collations of tables or columns involved in an operation, causing implicit collation conflicts.

🛠️

Solutions

3 solutions available

1. Align Column Collations for Consistent Operations medium

Modify tables to use a common collation for affected columns.

1

Identify the tables and columns involved in the error. The error message will list the collations that are incompatible. You'll need to determine which tables contain these columns.

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLLATION_NAME IN ('collation1', 'collation2', ...);

2

Choose a target collation that is compatible with all involved data. Often, this will be the default server collation or a widely used collation like 'utf8mb4_unicode_ci'.

SELECT @@collation_server, @@character_set_server;

3

Alter the table schema to change the collation of the identified columns to the chosen target collation. Be aware that this operation can take time and lock tables, so perform it during maintenance windows.

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

4

Repeat the `ALTER TABLE` command for all columns in all affected tables that have incompatible collations.

5

Re-run the operation that caused the error to confirm it's resolved.

2. Force Collation for Specific Operations easy

Use the `COLLATE` clause to explicitly set the collation for the problematic query.

1

Examine the SQL query that is producing the error. Identify the parts of the query that involve comparisons or operations between columns with different collations.

2

Modify the query by adding the `COLLATE` clause to one or both of the columns involved in the operation. Choose a common collation that is compatible with both.

SELECT * FROM table1 JOIN table2 ON table1.column_a COLLATE utf8mb4_unicode_ci = table2.column_b COLLATE utf8mb4_unicode_ci;

3

Alternatively, you can cast one of the columns to the desired collation if you are performing an operation like string concatenation or comparison where one side needs to be explicitly defined.

SELECT CONCAT(column_a COLLATE utf8mb4_unicode_ci, column_b) FROM your_table;

4

Re-run the modified query.

3. Set Default Character Set and Collation for New Tables medium

Configure MySQL to use consistent character sets and collations for all newly created tables.

1

Access your MySQL server configuration file (e.g., `my.cnf` on Linux/macOS or `my.ini` on Windows). The location varies by operating system and installation method.

2

Within the `[mysqld]` section of the configuration file, add or modify the following lines to set a desired default character set and collation. `utf8mb4` and `utf8mb4_unicode_ci` are generally recommended for broad compatibility.

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

3

Save the configuration file.

4

Restart the MySQL server for the changes to take effect.

sudo systemctl restart mysql  # For systemd-based systems
# or
sudo service mysql restart     # For older init systems

5

When creating new tables, ensure you explicitly define the character set and collation if they differ from the server defaults or if you need specific settings.

CREATE TABLE new_table (
    id INT PRIMARY KEY,
    data VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

🔗

Related Errors

5 related errors

MySQL Error 1270: Incompatible Collation Mix