Re: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

[Chuck Johnson]

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:

default-character-set=utf8

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.