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