Illegal mix of collations error in MySQL query
This is a short tutorial on how to fix an error Illegal mix of collations in MySQL.
This error is thrown when you are using select, insert, update and store procedure queries with
collation defined for database/table/columns and data sent with different
For example, the
collation table and columns as UTF8, and data is inserted with greek encoding values.
The solution is to maintain the same encoding and collation across the database all tables and all columns in the MySQL system.
Let’s see an example case where this error was reproduced.
In my application, One of the tables is throwing an error while inserting data.
My Database and table and columns are defined with
charset : utf8 collation: utf8_general_ci
I have an employee table with the following fields and data inserted into it
I used the below query with data
select * from employee where name =‘Test 🐟 user’
In the above WHERE condition, check the name with some Unicode mix characters.
I got an error in the application logs ERROR http-nio-80-exec-36 [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_gene ral_ci,COERCIBLE) for operation ’=’
There are multiple ways we can fix this error
Solution and Fix for Illegal mix of collations
Here is the sequence of steps and approaches to solve an error
- use alter to modify column encoding and collate
First, check are existing options for encoding.
show variables WHERE variable_name like “col%“;
If columns are giving an error change column encoding and collate using the
Here is a query to change the collation and encoding for the column
ALTER TABLE .employee MODIFY COLUMN name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
You can change character
utf8mb4 and collation to
utf8mb4_unicode_ci. This is not fixed, you can change it as per your character Unicode values.
Changing encoding and collation at the table using the below command
ALTER TABLE employee CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
To change the database encoding and collation
ALTER DATABASE empdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
- Always use the latest database driver
if this error comes in java or any application, Always use the latest drivers.
Here is a database connection to change the collation.
SET collation_connection = ‘utf8_general_ci’
- Global change a MySQL instance
This applies to all databases in the MySQL instance.
updated in mysql.conf file
default-character-set = utf8mb4
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Next, restart the MySQL instance to take effect.
An illegal mix of collations is an error that occurs at the application level or MySQL clients when you are dealing with different charset of data.
Learned ways to fix this error.