MySQL Bugs: #67504: Duplicate error in replication with slave triggers and auto increment

Bug #67504	Duplicate error in replication with slave triggers and auto increment
Submitted:	7 Nov 2012 16:01	Modified:	6 May 2013 9:00
Reporter:	Raghavendra Prabhu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.5.28, 5.1.67, 5.7.0, 5.5.29	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	1062, Auto-increment, duplicate, replication, trigger

Description:
I can reproduce an issue similar to http://bugs.mysql.com/bug.php?id=54201

This happens when there are more than one auto-increment table on master, slave has one or more them ignored but atleast one replicated, the replicated one has triggers into another table which is only on slave, and there are updates to these auto-increment tables on master.

How to repeat:
To reproduce do the following:

A)

master:

use test;
create table t1(id int auto_increment primary key);

create table t2(id int auto_increment primary key);

slave:

Make sure replicate-ignore-table=test.t2 in my.cnf

use test;
create table slave_only(id int auto_increment primary key);

insert into slave_only values(NULL);
insert into slave_only values(NULL);

create trigger t1_update after update on t1 for each row insert into slave_only values(NULL);
create trigger t1_insert after insert on t1 for each row insert into slave_only values(NULL);

B)

master:

insert into t2 values(NULL);
insert into t2 values(NULL);

insert into t1 values(NULL);

slave:

 show slave status\G

"Last_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t1 values (null)'"

Suggested fix:
The replication should not fail. The auto-increment should be correctly handled, probably a fix similar to what was done for bug: 54201  needs to be done. The problem is (from I saw during debugging) is that same auto-increment value is used for both the table and the destination table of the trigger (which is only on slave).

Thank you for the report.

Which exact version of MySQL server do you use?

This was tested on MySQL 5.5.28

Looks similar to bug #61548.

Thank you for the feedback.

Verified as described. Yes, this looks very similar to bug #61548, but since it is still different I verify it separately.

Has this bug been fixed since the related bug - 61548 has been fixed in 5.5.30?

Not, this bug is still not fixed.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.7. Documented in the 5.7.2 changelog as follows:

        When the master had more than one table with an auto-increment
        column, *and* the slave ignored at least one of these tables due
        to --replicate-ignore-table rules, *but* at least one them was
        replicated, even so--the replicated table or tables having at
        least one trigger updating one or more tables existing only on
        the slave--updates to any of the auto-increment tables on the
        master caused replication to fail.

Closed.

$ bzr log -r 5345
------------------------------------------------------------
revno: 5345
committer: Venkatesh Duggirala<venkatesh.duggirala@oracle.com>
branch nick: mysql-trunk
timestamp: Wed 2013-03-20 11:20:47 +0530
message:
  BUG#15850951-DUPLICATE ERROR IN REPLICATION WITH SLAVE                          
  TRIGGERS AND AUTO INCREMENT                                                     
                                                                                  
  Problem:On filtered slaves, IRU(IntVar, RandVar and UserVar)                    
  events are getting executed twice in the span of one query                      
  execution time if the query involves in firing a 'AFTER' trigger.                                                                 
                                                                                  
  Analysis: IRU events are getting cleaned up at the end of                       
  the query execution. If a query, with IRU events, involves                      
  in firing a 'AFTER' trigger and if the 'AFTER' trigger contains
  DML queries, the same IRU events are getting executed once again.                            
  This result in unexpected results while inserting tuples in                     
  tables which has auto_increment columns.                                        
  
  Fix: Delete these IRU events immediately after it gets                          
  exectued for the first time.