Illegal mix of collations error in mysql query


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 encoding and collation defined for database/table/columns and data sent with different encoding/collation.

For example, encoding and 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%";

Output

If columns is giving an error change column encoding and collate using alter query

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

Conclusion

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.

THE BEST NEWSLETTER ANYWHERE
Join 6,000 subscribers and get a daily digest of full stack tutorials delivered to your inbox directly.No spam ever. Unsubscribe any time.

Similar Posts
Subscribe
You'll get a notification every time a post gets published here.