Illegal mix of collations error in MySQL query

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

For example, the encoding and 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

id :1
name :'john'
description:'description'

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%“;

Output

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

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

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Next, restart the MySQL instance to take effect.

Conclusion

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.