MySQL: Query caused different errors on master and slave

Posted on in System Administration

We ran across the following error on a MySQL slave server recent:

mysql> SHOW SLAVE STATUS \G
<snip>
Last_Error: Query caused different errors on master and slave. Error on master: 'Deadlock found when trying to get lock; try restarting transaction' (1213), Error on slave: 'no error' (0). Default database: '<database_name>'. Query: '<query>'
<snip>

In this case, an insert failed on the master. When this happens you have three options:

  1. If possible, make the slave table match the master table and the restart the slave process.
  2. If that isn't possible, check to see if the SQL can be run manually. If so, run it and then tell the slave to skip that command and continue. (More on how to do this below.)
  3. If the command is unimportant, then just skip it.

In the case mentioned above, the error happened on an insert into a temporary table that was later removed. Since the table was no longer needed, it was safe to skip the insert and proceed. All we had to do was:

mysql> SET GLOBAL sql_slave_skip_counter = 1 ;
mysql> START SLAVE ;

You can find more information in the MySQL docs (4.1, 5.0, 5.1, 5.5).

Slaptijack's Koding Kraken