📋

Description

This error indicates that MariaDB encountered a conflict when trying to perform a string operation (such as comparison, concatenation, or sorting) involving expressions with different character sets or collations. It commonly occurs when the database engine cannot implicitly resolve the collation differences between the data being operated on.

💬

Error Message

Illegal mix of collations for operation '%s'

🔍

Known Causes

4 known causes

⚠️

Mismatched Column Collations in Joins

When joining tables, the columns used in the JOIN condition have different character sets or collations, preventing MariaDB from comparing them directly.

⚠️

Comparing Literals with Different Collations

A string literal or user-defined variable, which has a default session collation, is compared against a table column with an explicit, different collation.

⚠️

Functions Operating on Mixed Collations

String manipulation functions like CONCAT(), ORDER BY, or GROUP BY are applied to expressions or columns that possess conflicting character sets or collations.

⚠️

Inconsistent Database Schema Collations

The default character set and collation settings at the database, table, or column level are not uniform, leading to conflicts during cross-object operations.

🛠️

Solutions

4 solutions available

1. Explicitly Cast to a Common Collation easy

Convert one of the operands to match the collation of the other.

1

Identify the operation causing the error (e.g., a JOIN, WHERE clause comparison, or UNION) and the columns involved.

2

Modify your SQL query to explicitly cast one of the columns to a collation that is compatible with the other. A common and safe choice is utf8mb4_general_ci or utf8mb4_unicode_ci.

SELECT * FROM table1 JOIN table2 ON CAST(table1.column_name AS CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci) = table2.column_name;

3

Alternatively, if you are performing a UNION, ensure all selected columns have compatible collations. You might need to cast multiple columns.

SELECT column_name FROM table1 UNION SELECT CAST(column_name AS CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci) FROM table2;

2. Alter Table to Standardize Collation medium

Change the collation of one or more columns to a common standard.

1

Determine the collations of the columns involved in the operation. You can use the `INFORMATION_SCHEMA.COLUMNS` table for this.

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME IN ('table1', 'table2');

2

Choose a target collation that is suitable for all your data. `utf8mb4_general_ci` or `utf8mb4_unicode_ci` are generally recommended for modern applications.

3

Use the `ALTER TABLE` statement to change the collation of the problematic column(s) to the chosen target collation. This will require rewriting the table.

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

4

Repeat this for all tables and columns that are involved in operations and have incompatible collations.

3. Set Session Collation for Temporary Operations easy

Temporarily set the collation for the current session to resolve issues within a specific query block.

1

Identify the operation that is causing the error.

2

Before executing the problematic query, set the `collation_connection` and `collation_server` session variables to a common, compatible collation (e.g., `utf8mb4_general_ci`).

SET NAMES 'utf8mb4';
SET collation_connection = 'utf8mb4_general_ci';
SET collation_server = 'utf8mb4_general_ci';

3

Execute your query. The session-level settings will ensure that comparisons and operations within this session use the specified collation.

SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

4

Note: This is a temporary solution for the current session. The settings will revert when the connection is closed.

4. Adjust Database/Table Default Collations advanced

Change the default collation for new tables and columns in your database.

1

This is a more permanent solution and should be considered if most of your data requires a specific collation.

2

Alter the database's default character set and collation.

ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

3

Alternatively, alter the default collation for specific tables. This will affect newly added columns and existing columns that don't have an explicit collation.

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

4

After changing defaults, you may still need to explicitly alter existing columns if they were created with different collations. Refer to Solution 2 for altering individual columns.

🔗

Related Errors

5 related errors

MariaDB Error 1271: Incompatible Collations in Operations