replication fails and then fails again after rebuild

[Graham Colgate]

Hi

- MySQL version 5.6.22 for both Master and Slave on Solaris SPARC.
- All tables are MyISAM - to enable databases (schemas) to be copied between instance on difference clusters easily
- This is a Corporate Production environment but we have seen these same issues and can test / play with options within a UAT environment.
- There are LOTS of databases (schemas) per MySQL instance (~200)
- Each instance size ~150GB

The issue started when MySQL was stopped during an optimization job. When the server cam back online the table was in need of repair and shortly after the replication failed with a duplicate key error.

I realize stopping during a optimization is far from ideal but it happens from time to time and is pretty much out of my control!

Error example:
2015-06-23 11:16:14 25512 [ERROR] Slave SQL: Worker 3 failed executing transaction '' at master log bin.006171, end_log_pos 104066220; Error 'Duplicate entry '20048803' for key 'PRIMARY'' on query

After seeing a very similar message yesterday I rebuilt the slave by doing the following:
1) Stopped Master and Slave
2) Deleted all the database application databases (schemas) from the slave
3) copied the databases from Master to slave (again only application no default MySQL instance databases)
4) Started Master with networking disabled (all processes that write into the database are on a different server to the MySQL instance
5) Got the master log location
6) Restart Master as normal (with networking)
7) Started replication with replication turned off
8) Updated the master log position in replica instance
9) Started slave in replica instance


Shortly after I get the same error message again.


Any ideas?

I am about to delete the entire instance (Keeping only the user table from the MySQL schema.
install a new one, load the users, copy the tables from Master, reset all the master info in replication and praying that it won’t happen again - which is really not good enough.

Hope someone can help me diagnose.