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 17:48	Modified:	27 Jul 12:51
Reporter:	Raj Thukral
Status:	Closed
Category:	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	Target Version:
Tags:	rename table, innodb, binlog order, replication data integrity
Triage:	Triaged: D2 (Serious) / R4 (High) / E5 (Major)

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.