Web Development

Lately my attention has been focused on distributed MySQL database replication to other web servers. All was going well, but then I noticed the replication would stop and there were there "Duplicate Primary Key" errors in SHOW SLAVE STATUS;

I started to trace these down and noticed that this is a very common issue in MySQL replication with many of these distributed web applications. It seems that the slave has dB tables of it's own like log files, authorization files, statistics. A mind of it's own! What kind of a slave is that!? Ha Ha...

Anyway, after doing the google-research-thing (GRT) for nearly an hour, I finally made the decision to add this line to my.cnf:

Code:

slave-skip-errors = 1062       #skip duplicate key errors in slave

.... and I feel "bad" about it.

I always thought dB replication was dB replication, pure-and-simple, but now there is a more complex side.

It seems to actually be a "good replicator" we need to know which tables in the database to replicate, heaven forbid!

Most folks on the net, based on my GRT, use the slave-skip-errors = 1062 solution (where 1062 is the error number of the duplicate key insert error), but I feel like I've just struck a deal with the devil.

Now, I have no idea what is going on... because the longer I run in this slave-skip-errors = 1062 mode, the further and further the dBs will lose consistency.

GRT yielded no good solution.

I'm lazy to test, test, test... find 1062 errors .... and then see if the table in question should be excluded from the replicate list.

Surely there must be a better way!?

I'm an Oracle DBA and haven't used MySql. However, queries can be run against the tables based on the key columns to determine which rows are duplicates. I'm guessing that cleaning up the duplicates in the master, will in turn, fix the slaves.

Code:

select <KEY COLUMN>, count(*)
  from <TABLE>
 having count(*) > 1
 group by <KEY COLUMN>;

In this situation, there are no duplicates in the MySQL master. The duplication errors happen in the slave during master-slave replication.