Mysql – ‘Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation ‘=” on query

MySQLmysql-5.1mysql-5.7replication

I have migrated the mysql 5.1 to 5.7 by using the mysqldump file. But i am facing the issue during master slave replication as below.

Error 'Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '='' on query. Default database: 'iiflcms'. Query: 'UPDATE site_user_master SET is_active = 'N', modification_date = NAME_CONST('modificationDate',1615022896), passkey = NAME_CONST('passKey',_latin1'9419' COLLATE 'latin1_swedish_ci') WHERE email_id = NAME_CONST('userEmail',_latin1'Raj.fm.kangra@gmail.com' COLLATE 'latin1_swedish_ci')'

I have tried to check the default collection character set in database level as well as table colume level. it is showing the same as utf8. if it is the same then ther should not be any issue during replication.

but not sure why i am getting the above error on slave machine and beacuse of these error my replication is not stop.

i got workaround and set the collection and character set as latin1 and latin1_swedish_ci and my replication is started working. but problem is now on master character set and collection is utf8 and utf8_general_ci and in slave is latin1 and latin1_swedish_ci.

if i set these is there any problm in futues for few tables??

Best Answer

NAME_CONST is for internal use and has no possibility to convert text or use a collation

use the conventional way with CAST or CONVERT

UPDATE site_user_master 
SET 
    is_active = 'N',
    modification_date =  1615022896
    ,passkey =  CONVERT(_latin1'9419' USING latin1_swedish_ci)
WHERE
    email_id = CONVERT(_latin1'Raj.fm.kangra@gmail.com' USING latin1_swedish_ci)

Also it is not a good practrice to save passwords in plain text, there are a lot of function to hash the password https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html

Related Question