This is an short tutorial an 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
collation table and columns as UTF8 and data inserted with greek encoding values.
The solution is to maintain same encoding and collation across database all tables and all columns in mysql system.
Let’s see an example case where this error reproduced
In my application, One of the table is throwing an error while inserting an data
My Database and table and columns are defined with
charset : utf8 collation: utf8_general_ci
I have employee table with following fields and data inserted into it
id :1 name :'john' description:'description'
I used below query with data
select * from employee where name =‘Test 🐟 user’
In the above WHERE condition, checking name with some unicode mix characters
I got an error in 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 are the sequence of steps and approaches to solve an error
- use alter to modify column encoding and collate
First check what are exising options for encoding
show variables WHERE variable_name like “col%";
If columns is giving an error change column encoding and collate using
Here is an query to change collation and encoding for 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 as per your character unicode values.
Changing encoding and collation at table using below command
ALTER TABLE employee CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
To change the database encoding and collaion
ALTER DATABASE empdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
- Always use latest database driver
if this error comesn in java or any applicaitons, Always use latest drivers.
Here is an database connection to change collation
SET collation_connection = ‘utf8_general_ci’
- Global change at mysql instance
This applies to all databases in mysql instance
updated in mysql.conf file
[mysql] default-character-set = utf8mb4 [mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci
Next, restart mysql instance to take effect
Illegal mix of collations is an error occur at application level or mysql clients when you are dealing with different charset of data.
Learned ways to fix this error.