📋

Description

This error indicates that MariaDB encountered an operation (such as a comparison, join, or aggregation) involving expressions or columns with incompatible character set collations. It occurs when the server cannot implicitly resolve the collation conflict, preventing the operation from executing correctly.

💬

Error Message

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

🔍

Known Causes

4 known causes

⚠️

Inconsistent Table Collations

Performing operations (e.g., JOINs, UNIONs) between tables where the corresponding columns or the tables themselves are defined with different default character sets and collations.

⚠️

Mixed Column Collations in Expressions

Attempting to combine or compare data from multiple columns within a single query expression, where those columns have distinct collation settings.

⚠️

Database-Level Collation Mismatch

When a database is configured with a specific default collation, but tables or columns within it use different collations, leading to conflicts during cross-object operations.

⚠️

Literal String Collation Conflicts

Comparing or operating on a literal string constant with a column that has a different character set or collation, and the server cannot reconcile the difference.

🛠️

Solutions

3 solutions available

1. Explicitly Specify Collation in Queries easy

Force compatible collations for the operation by using COLLATE in your SQL statements.

1

Identify the columns involved in the operation that are causing the collation mismatch. The error message will typically show the collations involved.

2

In your SQL query, use the `COLLATE` clause to explicitly set the collation for one or more of the involved columns to match the other, or to a common, compatible collation.

SELECT column1 COLLATE utf8mb4_general_ci, column2 FROM your_table WHERE column1 = 'some_value';

-- Or for joins:
SELECT t1.column1, t2.column2
FROM table1 t1
JOIN table2 t2 ON t1.column1 COLLATE utf8mb4_unicode_ci = t2.column2 COLLATE utf8mb4_unicode_ci;

3

Test your query to ensure the error is resolved. If you encounter further issues, try a different common collation like `utf8mb4_unicode_ci` or `utf8mb4_general_ci`.

2. Alter Table to Standardize Collation medium

Modify table schemas to use a consistent and compatible collation for affected columns.

1

Determine the current collation of the columns involved in the error. You can use `SHOW CREATE TABLE` or query `information_schema.COLUMNS`.

SHOW CREATE TABLE your_table;

-- Or:
SELECT COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table' AND COLUMN_NAME IN ('column1', 'column2');

2

Choose a target collation that is compatible with all the data and intended operations. `utf8mb4_unicode_ci` or `utf8mb4_general_ci` are common choices for UTF-8 data.

3

Alter the table(s) to change the collation of the problematic columns. This will rewrite the data, so ensure you have backups and perform this during a maintenance window.

ALTER TABLE your_table MODIFY COLUMN column1 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE another_table MODIFY COLUMN column2 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4

Verify the change by re-running the query that previously caused the error.

3. Set Default Database/Table Collation medium

Ensure new tables and columns inherit a consistent collation by setting defaults.

1

Check the current default character set and collation for your database.

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

2

If the defaults are not suitable, alter the database to set a new default character set and collation. This will affect future table creations.

ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3

Alternatively, you can set default collations at the table level when creating or altering tables.

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

-- Or for existing tables:
ALTER TABLE your_table
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4

For existing tables that already have columns with mismatched collations, you will still need to use Solution 2 ('Alter Table to Standardize Collation') to modify those specific columns.

🔗

Related Errors

5 related errors

MariaDB Error 1270: Collation Mismatch in Operations