MySQL replication – Query caused different errors on master and slave

MySQL replication works quite reliable most of the time. But from time to time, especially if you change network, servers or the master has gone away the slave will get in a “weird” state. Today, I’ve encountered the following error message on one of the slaves;

Query caused different errors on master and slave

All tries to work around the error did not resolve it. Most of the “solutions” on the net were pointing out that one should do a “reset slave” and then grab the new log files from the master. While this “could” work, I was not in for that, since it would have meant to put the master into “read” and dumping the databases. Something that was out of the question on a production system (at least outside of a maintenance window).

But I’ve remembered that there is some setting in order to skip errors and continue with the sync. In my case, the errors on the salve were trivial as they have been caused by temporary tables and columns. So, in order to get the replication going again I issued the following:

[code]stop slave;[/code]
(This will obviously stop the slave)

[code]SET GLOBAL sql_slave_skip_counter = 1;[/code]
(resetting the skip/error by 1)

[code]start slave;[/code]
(This will obviously start the slave)

[code]show slave status;[/code]
(checking if the replication could continue)

In my case, I’ve had three more errors. In order to get it working 100% again, I just repeated the steps above until there were no errors anymore in the status query.

I hope this helps anyone out there.