Duplicate error in replication with slave triggers and auto increment

Bug #1068210 reported by Raghavendra D Prabhu
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
High
George Ormond Lorch III
5.5
Fix Released
High
George Ormond Lorch III

Bug Description

I can reproduce an issue similar to http://bugs.mysql.com/bug.php?id=54201 (or may be related)

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.

To reproduce do the following:

A)

master:

use test;
create table t1(s 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)'"

Related branches

tags: added: i22680
tags: added: i26980
removed: i22680
Revision history for this message
Raghavendra D Prabhu (raghavendra-prabhu) wrote :

A typo here:

 create table t1(s auto_increment primary key); should be create table t1(s int auto_increment primary key);

Revision history for this message
Raghavendra D Prabhu (raghavendra-prabhu) wrote :

The bug seems to be similar to http://bugs.mysql.com/bug.php?id=61548 reported upstream about 18 months back. (as per the last comment by Valerii in 67504)

Revision history for this message
Raghavendra D Prabhu (raghavendra-prabhu) wrote :

Quick update on this, bug mentioned in #2 - 61548 has been fixed
in 5.5.30, so there is a good chance that it is fixed for the
reported bug.

We cannot test it at the moment since the upstream bzr tree has not been
updated yet.

no longer affects: percona-server/5.6
tags: added: upstream
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-34

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.