CODEWITHSUNDEEP
mysqlmysql-8.0
Bibin Gangadharan
Bibin Gangadharan

Reputation: 1649

After migration to MySql 8 from 5.7 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

After migration of MySql from 5.7 to 8, I am getting the Illegal mix of collations exceptions. The same stored procedure works very fine in 5.7 version.

I have added the correct collation to the database, tables and to the table columns

See the images for more information.

Database: Database

Tables Tables

I have added CharSet=utf8mb4; to my connection string. That didn't made any change.

Exception:

MySql.Data.MySqlClient.MySqlException (0x80004005): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)

Upvotes: 1

Views: 4401

Answers (1)

riggaP
riggaP

Reputation: 21

I also had the exact same issue after restoring databases from v5.6 to v8.0.

Altering the tables/columns was going to bring a world of hurt, so for me the solution was to simply set the default charset/collation when creating the schemas:

CREATE SCHEMA `new_schema` 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci ;

Modifying connection strings, and altering defaults at the schema level after the schema was created had no effect.

Upvotes: 1

Related Questions