MySQL Bugs: #39675: rename tables on innodb tables with pending transactions causes slave data issue

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	Email Updates:
Status:	Closed	Impact on me:	None
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

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.

i wonder if this is related to bug #989 ?

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;

Will be fixed by WL#4284

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

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.

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.

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.

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.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090723102221-ps4uaphwbxzj8p0q) (version source revid:jon.hauglid@sun.com-20090706071401-fmmqttomxw0v0gjd) (merge vers: 5.4.4-alpha) (pib:11)

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

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

Ignore previous comment about 5.4.2.

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

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091208135954-h6ipvx9mpzmpuipk) (pib:16)

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

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)

Also documented in the 5.5.3 changelog. Closed.