MySQL Bugs: #33688: Falcon: replication fails on duplicate key error

Bug #33688	Falcon: replication fails on duplicate key error
Submitted:	4 Jan 2008 11:33	Modified:	13 Mar 2008 5:52
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	6.0.4	OS:	Any
Assigned to:	Antony Curtis	CPU Architecture:	Any
Tags:	duplicate key, falcon, replication, sbr

Description:
It appears that if the master gets a duplicate key error, replication will fail.

Replication works fine if the query which results in the duplicate key error is removed. Test passes with Innodb.

How to repeat:
Method #1. Run 

perl mysql-test-run.pl --force --mysqld=--skip-innodb --mysqld=--default-storage-engine=falcon rpl_err_ignoredtable

Method #2.

1. Start a master and a slave in a replication setup

2. On the master, issue:

mysql> use test;
Database changed
mysql> create table t1 (a int primary key);
Query OK, 0 rows affected (0.03 sec)
mysql> create table t4 (a int primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values (1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into t4 values (1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

3. On the slave, issue:

mysql> use test;
Database changed
mysql> select * from t4;
Empty set (0.00 sec)

Thank you for the bug report.

Chris, could you determine if this is an issue with Falcon or Replication.

Hakan,  Would you be able to drill down on this bug and determine if the source of the problem is Falcon or replication?  Duplicate Key error is pretty common. If the problem is replication, we need to get it assigned correctly.

The problem here seems to be in duplicate key error handling. If a statement generates a duplicate key error, then the next statement will not be replicated. The statement after the next statement will replicated. Whenever a duplicate key error happens, then the next statement after that duplicate key error is not replicated.

For instance, on master:
[20:53] root@test>create table t1 (a int primary key);
Query OK, 0 rows affected (0.26 sec)

[20:54] root@test>create table t4 (a int primary key);
Query OK, 0 rows affected (0.01 sec)

[20:54] root@test>insert into t1 values (1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

[20:54] root@test>insert into t4 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

On slave:
[20:54] root@test>select * from t4;
Empty set (0.01 sec)

Now on master again:
[20:55] root@test>insert into t4 values (3);
Query OK, 1 row affected (0.00 sec)

Will get us on slave:
[20:54] root@test>select * from t4;
+---+
| a |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

Now, if we provoke a duplicate key error again on master:
[20:55] root@test>insert into t1 values (1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

[20:55] root@test>insert into t4 values (6);
Query OK, 1 row affected (0.00 sec)

We will see that value 6 is missing on slave:
[20:55] root@test>select * from t4;
+---+
| a |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

Best regards,

Hakan

This seems like a replication problem according to Hakan's analysis.

This bug produces wrong results, indicating that it is D2, not D3.  And since the problem occurs after a duplicate key error occurs on the master, it is more widespread;  I2, not I3

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42082

ChangeSet@1.2625, 2008-02-11 17:40:06-08:00, acurtis@xiphis.org +6 -0
  Bug#33688
    "Falcon: replication fails on duplicate key error"
    I was able to reproduce this bug on InnoDB storage engine simply by removing 
    table flag HA_BINLOG_STMT_CAPABLE.
    Bug affects any transactional storage engine which does not use statement based 
    logs and is caused by thd->binlog_table_maps not being reset when rollback occurs.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42177

ChangeSet@1.2813, 2008-02-12 16:37:27-08:00, acurtis@xiphis.org +5 -0
  Bug#33688
    "Falcon: replication fails on duplicate key error"
    I was able to reproduce this bug on InnoDB storage engine simply by
    removing table flag HA_BINLOG_STMT_CAPABLE.
    Bug affects any transactional storage engine which does not use statement 
    based logs and is caused by thd->binlog_table_maps not being reset when
    rollback occurs.

Patch looks OK, but needs to be redone for mysql-6.0-falcon-team since the change in ha_falcon.cpp is basically already done.  I did not evaluate the testcase, but Hakan tried the changes to sql/sql_class.h and sql/log.cc in a copy of mysql-6.0-falcon-team and verified that it fixes the bug.

queued in mysql-6.0-falcon-team repository

Pushed into 6.0.4-alpha

Documented bugfix in the 6.0.4 changelog as follows:

        When using a transactional storage engine not using
        statement-based logging, a duplicate key error on the master caused
        replication to fail. This issue was first observed when using the
        Falcon storage engine.