MySQL Forums
Forum List  »  Replication

Re: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
Posted by: Chuck Johnson
Date: February 16, 2005 02:56PM

I got a similar problem when setting up replication on a pair of 4.x databases. One server was an upgrade from a 4.1.earlier to 4.1.7, the other server was a brand new install of 4.1.7. The fix for me was in the my.ini/my.cnf files.

On the new install machine, built using the new MySQL Windows installer program, the my.ini file had a [mysqld] entry automatically added:


This entry was not present in the my.ini on the upgraded machine.

When I tried to start replication up, it reported in the Windows Event Viewer the following message:
"The slave I/O thread stops because master and slave have different values for the COLLATION_SERVER global variable. The values must be equal for replication to work."

By checking the variable settings with "show variables like coll%;" I determined that there were actually three variables set differently: collation_connection, collation_database, collation_server. On the upgraded server, all three were set to "latin1_swedish_ci", while the new install server was set to "utf8_general_ci".

So, my fix was simply to make sure to add the above "default-character-set" entry to all of my my.ini files, and then restart everything. Presto, replication now started and ran without error.

Note that the "collation_connection" variable for me still shows itself as set to "latin1_swedish_ci" on both machines, but "collation_database" and "collation_server" are now both set to "utf8_general_ci" on both machines.

If it matters, my application is a Java-based program connecting to MySQL. I hope this long story helps in some way.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.