Description:
We have one master and one slave MySQL instance on production env, There is an OOM happens on master today, and it auto restart after killed by OOM-Killer. Then we found the slave can not replay binlog from master and the error is "Duplicated key". We are sure that the slave receive writes only from master. so we dig into the master binlog to see what happens.
Unfortunately, we see two binlog rows with the same auto increment pk(INSERT statement), one is before the master killed, one is after the master restarted.
logs:
----------binlog items before master killed -----------
#160302 19:21:19 server id 2887714367 end_log_pos 881241 CRC32 0xb25fdb57 Write_rows: table id 491 flags: STMT_END_F
### INSERT INTO `xxx`.`xxx`
### SET
### @1=205212331 /* INT meta=0 nullable=0 is_null=0 */
-----------binlog items after master restart----------
#160302 19:22:00 server id 2887714367 end_log_pos 746 CRC32 0x1fabd0c6 Write_rows: table id 72 flags: STMT_END_F
### INSERT INTO `xxx`.`xxx`
### SET
### @1=205212331 /* INT meta=0 nullable=0 is_null=0 */
the master killed&restarted at 19:21
as you can see, there are two SAME "INSERT @1=205212331" shows in master binlog.
How to repeat:
currently we haven't repeat it yet.
Suggested fix:
no duplicated auto increment pk in master binlog.