MySQL Replication – Resolving Master 5.5 to Slave 5.1 Error on Create Database

MySQLmysql-5.1mysql-5.5replication

I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we cannot upgrade yet.

Everything is going fine until a new database is created in the master, then the slave replication fails and shows the error:

Query caused different errors on master and slave.     
Error on master: message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d. Created with MySQL %d, now running %d. 
Please use mysql_upgrade to fix this error.' error code=1558 ; Error on slave: actual message='no error', error code=0. Default database: 'newdb'. Query: 'DROP DATABASE newdb'

My workaround is:

set global sql_slave_skip_counter=2;
create database newdb;
start slave

After doing a checksum of all database tables everything seems ok, but we have an awful application that creates databases very often, so I would like to know if there is a way that doesn't imply coding to solve the problem without manually creating the database.

Best Answer

Every replication event generated by the master includes the error code that resulted from execution of the query that generated the event. This is usually "success" (0, no error).

When the slave executes a query, it expects the error it encounters to be the same error as the master encountered (again, usually "no error"). When this doesn't happen, replication stops. Note that the error message from the master isn't preserved, just the code. That's why you see placeholders in the error message, because the slave displays the sprintf() template for the error message.

But note, carefully, where the error is.

Check the master server's error log.

It looks as if the master was not correctly upgraded to MySQL 5.5; specifically, it looks like the mysql_upgrade utility was not used to upgrade the system tables to be fully compatible with MySQL 5.5.

http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html

You are indeed running an unsupported configuration, with a newer master and an older slave... but in this case, it appears that your master has a problem that is actually unrelated to replication. 

Error on master: 
message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d.  Created with MySQL %d, now running %d. 
Please use mysql_upgrade to fix this error.' 
error code=1558 ;

The master encountered an error; you may find similar errors in the master's log, although it's possible that they only occur at startup, if at all. In any event, your application should have seen this error also... perhaps it's ignoring it. :(

The slave didn't encounter any error executing the actual query.

Error on slave: actual message='no error', error code=0.

It occurred to me that it's very important that the actions performed by mysql_upgrade can't be safely replicated, since the slave server is older. It turns out, this was anticipated in the design, so the mysql_upgrade utility appears to be safe to run on a master, without disconnecting the slave, even if the slave is older (or was already upgraded). According to this comment in the source code of mysql_upgrade.c:

Master and slave should be upgraded separately. 
All statements executed by mysql_upgrade will not be binlogged.
Related Question