Percona Server with XtraDB

Duplicate error in replication with slave triggers and auto increment

Reported by Raghavendra D Prabhu on 2012-10-18
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
High
George Ormond Lorch III
5.5
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)'"

tags: added: i22680
tags: added: i26980
removed: i22680

A typo here:

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

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)

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
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

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