Bug #39675 rename tables on innodb tables with pending transactions causes slave data issue

• Submitted: 26 Sep 2008 15:48
• Modified: 8 Mar 2010 18:55
• Reporter: Raj Thukral
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S1 (Critical)
• Version: 5.0.51a, 4.1, 5.0, 5.1, 6.0 bzr
• OS: Any
• Assigned to: Jon Olav Hauglid
• CPU Architecture: Any
• Tags: binlog order, innodb, rename table, replication data integrity

[26 Sep 2008 15:48] Raj Thukral

Description:
a rename table succeeds when a transaction is pending on an innodb table in a different session.  However, when a commit happens on that pending transaction, the data goes into different tables on the master and the slave since the order of the statement in the binlog is different from what happens on the master.

On the master, the commit causes the data to go into the original (pre-rename) table, but it is recorded in the binlog after the rename, hence goes into the new (post-rename) table on the slave.

I would assume that rename table being ddl is not transaction-safe, however, the documentation does say that "When you execute RENAME, you cannot have any locked tables or active transactions." (ref: http://dev.mysql.com/doc/refman/5.0/en/rename-table.html).  This should either be clarified as only applicable to the current session, which makes it kind of pointless, or the rename should check for *any* pending transactions on the tables in question before succeeding.

How to repeat:
testcase:

create the following tables:

CREATE TABLE `raj` (
  `id` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

CREATE TABLE `raj_next` (
  `id` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

run the following inserts:

insert into raj (b) values (1),(2),(3);

run the following transaction but do not commit!

begin;
insert into raj(b) values (4);

now, in a different session, run this:

rename table raj to raj_backup, raj_next to raj;

now, back in the original session, commit it:

commit;

see that the transaction made it to raj_backup:

select * from raj;
Empty set (0.00 sec)

select * from raj_backup;
mysql> select * From raj_backup;;
+----+------+
| id | b    |
+----+------+
|  1 |    1 | 
|  2 |    2 | 
|  3 |    3 | 
|  4 |    4 | 
+----+------+
4 rows in set (0.00 sec)

check the binlog and you will see that this will actually go into 'raj' on a slave instead of raj_backup.

#080926 11:45:36 server id 3  end_log_pos 2356  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1222443936/*!*/;
CREATE TABLE `raj` (
  `id` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB/*!*/;
# at 2356
#080926 11:45:36 server id 3  end_log_pos 2550  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1222443936/*!*/;
CREATE TABLE `raj_next` (
  `id` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB/*!*/;
# at 2550
#080926 11:45:40 server id 3  end_log_pos 2578  Intvar
SET INSERT_ID=1/*!*/;
# at 2578
#080926 11:45:40 server id 3  end_log_pos 2679  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1222443940/*!*/;
insert into raj (b) values (1),(2),(3)/*!*/;
# at 2679
#080926 11:45:40 server id 3  end_log_pos 2706  Xid = 74
COMMIT/*!*/;
# at 2706
#080926 11:45:51 server id 3  end_log_pos 2816  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1222443951/*!*/;
rename table raj to raj_backup, raj_next to raj/*!*/;
# at 2816
#080926 11:45:59 server id 3  end_log_pos 2884  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1222443959/*!*/;
BEGIN/*!*/;
# at 2884
#080926 11:45:46 server id 3  end_log_pos 2912  Intvar
SET INSERT_ID=4/*!*/;
# at 2912
#080926 11:45:46 server id 3  end_log_pos 3004  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1222443946/*!*/;
insert into raj(b) values (4)/*!*/;
# at 3004
#080926 11:45:59 server id 3  end_log_pos 3031  Xid = 76
COMMIT/*!*/;
DELIMITER ;

This is a data integrity issue + causes slave replication issues since an autoincrement id of 4 exists on the slave in the table 'raj' whereas
it doesn't on the master, hence when autoincrement id 4 is inserted on the master, the slave will stop with a duplicate entry error.

Suggested fix:
2 possible fixes:

1. check for any pending transactions on any of the involved tables before allowing a rename table to succeed.

2. make rename table inherently transaction-unsafe and not check for any pending transaction or locks before succeeding.

The first is obviously preferable.  The current situation where it checks for pending transactions or locks only in the current session seems kind of pointless if the rename can succeed when there's pending transactions in other sessions on the table(s) being renamed.

[27 Sep 2008 7:04] MySQL Verification Team

i wonder if this is related to bug #989 ?

[27 Sep 2008 15:47] Sveta Smirnova

Thank you for the report.

Verified as described.

Test case for our test suite:

--source include/master-slave.inc
--source include/have_innodb.inc

CREATE TABLE `raj` (
  `id` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

CREATE TABLE `raj_next` (
  `id` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

insert into raj (b) values (1),(2),(3);

begin;

insert into raj(b) values (4);

connect (addconroot, localhost, root,,);
connection addconroot;

rename table raj to raj_backup, raj_next to raj;

connection master;

commit;

select * from raj;

select * from raj_backup;

sleep 1;

connection slave;

select * from raj;

select * from raj_backup;

[27 Sep 2008 17:11] Davi Arnaut

Will be fixed by WL#4284

[27 Jun 2009 17:12] Konstantin Osipov

Jon Olav, please add the test case and close the bug (fixed in scope of Bug#989)

[29 Jun 2009 9:41] Bugs System

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/77435

2812 Jon Olav Hauglid	2009-06-29
      Bug #39675 rename tables on innodb tables with pending 
                 transactions causes slave data issue
      
      Bug was already fixed as part of patch for Bug#989.
      Test case added to rename.test.

[1 Jul 2009 8:22] Bugs System

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/77621

2812 Jon Olav Hauglid	2009-07-01
      Bug #39675 rename tables on innodb tables with pending 
                 transactions causes slave data issue
      
      Bug was already fixed as part of patch for Bug#989.
      Test case added to rpl_innodb.test.

[1 Jul 2009 8:46] Bugs System

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/77626

2812 Jon Olav Hauglid	2009-07-01
      Bug #39675 rename tables on innodb tables with pending 
                 transactions causes slave data issue
      
      Bug was already fixed as part of patch for Bug#989 
      (If DROP TABLE while there's an active transaction, 
      wrong binlog order) 
      
      Test case added to rpl_innodb.test.

[2 Jul 2009 9:41] Bugs System

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/77752

2812 Jon Olav Hauglid	2009-07-02
      Bug #39675 rename tables on innodb tables with pending 
                 transactions causes slave data issue
      
      Bug was already fixed as part of patch for Bug#989 
      (If DROP TABLE while there's an active transaction, 
      wrong binlog order) 
      
      Test case added to rpl_innodb.test.

[23 Jul 2009 10:24] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090723102221-ps4uaphwbxzj8p0q) (version source revid:[e-mail]-20090706071401-fmmqttomxw0v0gjd) (merge vers: 5.4.4-alpha) (pib:11)

[27 Jul 2009 10:51] Jon Stephens

Documented fix in the 5.4.4 and 6.0.11 changelogs. See Bug #989 for details.

[12 Aug 2009 21:53] Paul DuBois

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

[14 Aug 2009 22:47] Paul DuBois

Ignore previous comment about 5.4.2.

[8 Dec 2009 13:23] Jon Olav Hauglid

Pushed to mysql-next-4284 (5.6.0-beta).

[16 Feb 2010 16:46] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100216101445-2ofzkh48aq2e0e8o) (version source revid:[e-mail]-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)

[16 Feb 2010 16:55] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100216101208-33qkfwdr0tep3pf2) (version source revid:[e-mail]-20091208135954-h6ipvx9mpzmpuipk) (pib:16)

[16 Feb 2010 19:39] Jon Stephens

Already documented in changelogs for all applicable release series. Closed.

[6 Mar 2010 11:06] Bugs System

Pushed into 5.5.3-m3 (revid:[e-mail]-20100306103849-hha31z2enhh7jwt3) (version source revid:[e-mail]-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)

[8 Mar 2010 18:55] Jon Stephens

Also documented in the 5.5.3 changelog. Closed.