📋

Description

This error signifies that MySQL has encountered an operation (such as a comparison, concatenation, or join) involving strings or columns that possess different character sets or collations, which it deems incompatible. MySQL cannot implicitly resolve these conflicting text encodings, preventing the operation from completing successfully. It commonly occurs when working with diverse data sources or when collation settings are not consistently managed across your database schema.

💬

Error Message

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

🔍

Known Causes

4 known causes

⚠️

Mismatched Column Collations

Occurs when attempting to compare, join, or perform other operations on columns that are configured with different character sets or collations.

⚠️

Literal String Collation Conflict

Arises when a string literal provided directly in a query has a different character set or collation than the column it's being evaluated against.

⚠️

Inconsistent Schema Collations

The database, table, or specific column-level collation settings are not unified, leading to conflicts during operations that span these different configurations.

⚠️

Function Argument Collation Mismatch

A MySQL function is called with multiple arguments (e.g., CONCAT(), COALESCE()) that possess incompatible character sets or collations.

🛠️

Solutions

4 solutions available

1. Explicitly Set Collation for the Operation easy

Temporarily force a common collation for a specific query.

1

Identify the collations involved in the error message. The error message will show something like `Incompatible collations (utf8mb4_unicode_ci,utf8mb4_general_ci) and (utf8mb4_unicode_ci,utf8mb4_general_ci) for operation 'concat'`. In this example, `utf8mb4_unicode_ci` and `utf8mb4_general_ci` are the collations.

2

When performing operations (like `JOIN`, `WHERE`, `ORDER BY`, `GROUP BY`, or functions like `CONCAT`, `GROUP_CONCAT`) on columns with different collations, use the `COLLATE` clause to unify them. Choose a collation that is compatible with both, or the one you intend to use for the comparison.

SELECT * FROM table1 JOIN table2 ON table1.column1 COLLATE utf8mb4_unicode_ci = table2.column2 COLLATE utf8mb4_unicode_ci;

3

Alternatively, for functions, apply `COLLATE` to the result or individual arguments.

SELECT CONCAT(column1 COLLATE utf8mb4_unicode_ci, column2) FROM your_table;

2. Alter Table to Standardize Column Collations medium

Change the collation of one or more columns to match another, resolving future conflicts.

1

Determine which table and column(s) are causing the conflict. You can often infer this from the query that produced the error, or by examining the `SHOW CREATE TABLE` output for tables involved in that query.

SHOW CREATE TABLE your_table_name;

2

Choose a target collation. This should ideally be the most common or desired collation for your database, often `utf8mb4_unicode_ci` or `utf8mb4_general_ci`.

3

Use the `ALTER TABLE` statement to change the collation of the offending column(s). This will reorder the data if necessary.

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

4

Repeat for all columns involved in the conflict across different tables that are being joined or compared.

3. Set Default Collation for Tables or Database medium

Establish a consistent default collation for all new tables and columns.

1

Identify the desired default collation for your database. This is usually set at the database level and inherited by new tables.

2

To change the default collation for a specific database, use the `ALTER DATABASE` statement. This affects all *new* tables created in this database.

ALTER DATABASE your_database_name COLLATE utf8mb4_unicode_ci;

3

To ensure existing tables also adopt this collation, you'll need to alter each table individually as described in the 'Alter Table to Standardize Column Collations' solution.

4

When creating new tables, you can also explicitly define the collation for character set columns.

CREATE TABLE new_table (
    id INT PRIMARY KEY,
    text_data VARCHAR(255) COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4. Adjust Server-Wide Default Collations advanced

Modify MySQL server configuration to set global defaults for character set and collation.

1

Locate your MySQL configuration file (e.g., `my.cnf` or `my.ini`). The location varies by operating system and installation method.

2

Add or modify the following settings within the `[mysqld]` section of your configuration file to set the server's default character set and collation.

[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.

# For systemd-based systems (e.g., Ubuntu 15.04+, CentOS 7+)
sudo systemctl restart mysql

# For older init.d systems (e.g., Ubuntu 14.04, CentOS 6)
sudo service mysql restart

5

Note: This change primarily affects new databases and tables created *after* the change. Existing databases and tables will retain their original collations and will need to be altered if standardization is required.

🔗

Related Errors

5 related errors

MySQL Error 1267: Incompatible Collation Mix