{

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 ``markup 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
[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.

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.