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??


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 
    is_active = 'N',
    modification_date =  1615022896
    ,passkey =  CONVERT(_latin1'9419' USING latin1_swedish_ci)
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.