Comments on How To Skip Certain Errors In MySQL Replication
MySQL replication is nice, however it can happen that it stops because of an error, and restoring a working replication can be hard - you need to set locks on the master to get a consistent MySQL dump, and during that time websites are not accessible. However there's a way to make the MySQL slave ignore certain errors using the slave-skip-errors directive.
5 Comment(s)
Comments
Don 't do that!
Skipping errors blindly is the worst thing to do.
A replication error has usually a reason, and finding that reason makes your system safer. If you skip errors, you will never get to know that something went wrong
I agree, skipping errors like this is terrbily dangerous!
Rather than that, if you managed to solve your issue and you want to skip one or more sql statement restart this way:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Thanks for the advice. This helped solved our problem in replication. We had a corrupt table in production that generated errors on INSERT DELAYED statements. On the replication server, the statements executed fine but replication stopped because the error code on replication did not match the one in production. There were several hundred -- at least -- of these so incrementing the skip counter when each stoppage happened would have been time consuming. We used SLAVE_SKIP_ERRORS=0 to keep replication from stopping on these INSERTs and could then use the replicated table (which had all to rows) to replace the production version.
Thanks saved me a ton of work
Great Work, I removed the entry:
Last_SQL_Error: Error 'Duplicate entry '18' for key 'PRIMARY'' on query. Default database: 'vtiger'. Query: 'INSERT INTO vtiger_loginhistory (user_name, user_ip, logout_time, login_time, status) VALUES ('admin','107.77.68.98','0000-00-00 00:00:00','2020-06-21 17:02:10','Signed in')'
use vtiger;
delete from vtiger_loginhistory where user_name = 'admin' and login_time = '2020-06-21 17:02:10';
stop slave; start slave;
But the error continued to show up even after I stopped the slave and database (Empty Entry). I even added the single global statement stop slave; set global sql_slave_skip_counter=1; start slave;
So after adding the erroneous error to my my.cnf file in my docker mariadb DB, everything started working fine.
Thank you for all of your insight.
Thanks to you I have replicated across multiple Mariadb across multiple cloud providers (AWS, Oracle, Azure, Digital Ocean and on-premise).
Todd