Automatic query restart when certain error happens in replication

[niksav77]

Hi all,

It happens from time to time randomly that replication stops with this error 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA). When replication is manually started, without skipping query, everything works just fine.

Is there way to configure server to automatically restart transaction after errors like this, in similar way it restarts 'em when deadlock happens?

Thanks,
Nikola

[scott.nemes]

There are two ways you could force replication to skip specific errors:

You may use the slave-skip-errors option to skip specific error codes:
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_slave-skip-errors

You could also use the pt-slave-restart tool from the Percona Toolkit to do the same:
http://www.percona.com/doc/percona-toolkit/2.2/pt-slave-restart.html

Just be careful with it, as you could potentially be skipping real issues. So if you do go this route, make sure you regularly check your slaves for consistency using pt-table-checksum (and even if you do not go this route, still check your slaves anyway). =)