bzr commit into mysql-trunk-bugfixing branch (jon.hauglid:3089) Bug#50124

[Jon Olav Hauglid]

#At file:///export/home/x/mysql-trunk-bugfixing-bug50124/ based on revid:alfranio.correia@stripped

 3089 Jon Olav Hauglid	2010-06-02
      Bug #50124 Rpl failure on DROP table with concurrent txn/non-txn
                 DML flow and SAVEPOINT
      
      The problem was that replication could break if a transaction involving
      both transactional and non-transactional tables was rolled back to a
      savepoint. It broke if a concurrent connection tried to drop a
      transactional table which was locked after the savepoint was set.
      This DROP TABLE completed when ROLLBACK TO SAVEPOINT was executed as the
      lock on the table was dropped by the transaction. When the slave later
      tried to apply the binlog, it would fail as the table would already
      have been dropped.
      
      The reason for the problem is that transactions involving both
      transactional and non-transactional tables are written fully to the
      binlog during ROLLBACK TO SAVEPOINT. At the same time, metadata locks
      acquired after a savepoint, were released during ROLLBACK TO SAVEPOINT.
      This allowed a second connection to drop a table only used between
      SAVEPOINT and ROLLBACK TO SAVEPOINT. Which caused the transaction binlog
      to refer to a non-existing table when it was written during ROLLBACK
      TO SAVEPOINT.
      
      This patch fixes the problem by not releasing metadata locks when
      ROLLBACK TO SAVEPOINT is executed if binlogging is enabled.
      
      The patch also makes sure that metadata locks taken inside a sub-statement
      are not released if the sub-statement does a ROLLBACK TO SAVEPOINT.
      This prevents items protected by these metadata locks from being
      altered/dropped before the sub-statement has completed.

    added:
      mysql-test/suite/rpl/r/rpl_savepoint.result
      mysql-test/suite/rpl/t/rpl_savepoint.test
    modified:
      mysql-test/r/innodb_mysql_lock.result
      mysql-test/t/innodb_mysql_lock.test
      sql/transaction.cc
=== modified file 'mysql-test/r/innodb_mysql_lock.result'
--- a/mysql-test/r/innodb_mysql_lock.result	2010-05-19 11:32:21 +0000
+++ b/mysql-test/r/innodb_mysql_lock.result	2010-06-02 10:48:08 +0000
@@ -116,3 +116,62 @@ Table	Op	Msg_type	Msg_text
 test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
 test.t1	optimize	status	OK
 DROP TABLE t1;
+#
+# Bug#50124 Rpl failure on DROP table with concurrent txn/non-txn
+#           DML flow and SAVEPOINT
+#
+CREATE FUNCTION func_rollback_to_svp1() RETURNS INT
+BEGIN
+SAVEPOINT svp;
+ROLLBACK TO SAVEPOINT svp;
+RETURN 1;
+END|
+CREATE FUNCTION func_rollback_to_svp2() RETURNS INT
+BEGIN
+SAVEPOINT svp;
+INSERT INTO t1 VALUES (2);
+ROLLBACK TO SAVEPOINT svp;
+RETURN 3;
+END|
+#
+# Test 1: Check that rollback to savepoint inside a function
+#         does not release the metadata lock on the function.
+#
+# Connection default
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+START TRANSACTION;
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (func_rollback_to_svp1());
+# Connection con1
+# It should not be possible to drop func_rollback_to_svp1()
+# Sending:
+DROP FUNCTION func_rollback_to_svp1;
+# Connection default;
+# func_rollback_to_svp1() should still exist
+INSERT INTO t1 VALUES (func_rollback_to_svp1());
+COMMIT;
+# Connection con1
+# Reaping: DROP FUNCTION func_rollback_to_svp1
+#
+# Test 2: Check that rollback to savepoint inside a function
+#         does not release metadata locks taken inside the function.
+#
+# Connection default
+DELETE FROM t1;
+START TRANSACTION;
+SELECT func_rollback_to_svp2();
+func_rollback_to_svp2()
+3
+# Connection con1
+# It should not be possible to drop table t1
+# Sending:
+DROP TABLE t1;
+# Connection default;
+# table t1 should still exist
+SELECT * FROM t1;
+a
+COMMIT;
+# Connection con1
+# Reaping: DROP TABLE t1
+# Connection default
+DROP FUNCTION func_rollback_to_svp2;

=== added file 'mysql-test/suite/rpl/r/rpl_savepoint.result'
--- a/mysql-test/suite/rpl/r/rpl_savepoint.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_savepoint.result	2010-06-02 10:48:08 +0000
@@ -0,0 +1,32 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+#
+# Bug#50124 Rpl failure on DROP table with concurrent txn/non-txn
+#           DML flow and SAVEPOINT
+#
+# Connection master
+DROP TABLE IF EXISTS tt, nt;
+CREATE TABLE tt (i INT) ENGINE = InnoDB;
+CREATE TABLE nt (i INT) ENGINE = MyISAM;
+FLUSH LOGS;
+START TRANSACTION;
+INSERT INTO nt VALUES (1);
+SAVEPOINT insert_statement;
+INSERT INTO tt VALUES (1);
+# Connection master1
+# Sending:
+DROP TABLE tt;
+# Connection master
+ROLLBACK TO SAVEPOINT insert_statement;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+COMMIT;
+# Connection master1
+# Reaping: DROP TABLE tt
+FLUSH LOGS;
+# Connection master
+DROP TABLE nt;

=== added file 'mysql-test/suite/rpl/t/rpl_savepoint.test'
--- a/mysql-test/suite/rpl/t/rpl_savepoint.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_savepoint.test	2010-06-02 10:48:08 +0000
@@ -0,0 +1,47 @@
+--source include/master-slave.inc
+--source include/have_innodb.inc
+
+--echo #
+--echo # Bug#50124 Rpl failure on DROP table with concurrent txn/non-txn
+--echo #           DML flow and SAVEPOINT
+--echo #
+
+--echo # Connection master
+connection master;
+
+--disable_warnings
+DROP TABLE IF EXISTS tt, nt;
+--enable_warnings
+
+CREATE TABLE tt (i INT) ENGINE = InnoDB;
+CREATE TABLE nt (i INT) ENGINE = MyISAM;
+FLUSH LOGS;
+START TRANSACTION;
+INSERT INTO nt VALUES (1);
+SAVEPOINT insert_statement;
+INSERT INTO tt VALUES (1);
+
+--echo # Connection master1
+connection master1;
+--echo # Sending:
+--send DROP TABLE tt
+
+--echo # Connection master
+connection master;
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table" AND info = "DROP TABLE tt";
+--source include/wait_condition.inc
+ROLLBACK TO SAVEPOINT insert_statement;
+COMMIT;
+
+--echo # Connection master1
+connection master1;
+--echo # Reaping: DROP TABLE tt
+--reap
+FLUSH LOGS;
+
+--echo # Connection master
+connection master;
+DROP TABLE nt;
+--source include/master-slave-end.inc

=== modified file 'mysql-test/t/innodb_mysql_lock.test'
--- a/mysql-test/t/innodb_mysql_lock.test	2010-05-19 11:32:21 +0000
+++ b/mysql-test/t/innodb_mysql_lock.test	2010-06-02 10:48:08 +0000
@@ -209,6 +209,101 @@ disconnect con1;
 DROP TABLE t1;
 
 
+--echo #
+--echo # Bug#50124 Rpl failure on DROP table with concurrent txn/non-txn
+--echo #           DML flow and SAVEPOINT
+--echo #
+
+delimiter |;
+CREATE FUNCTION func_rollback_to_svp1() RETURNS INT
+BEGIN
+        SAVEPOINT svp;
+        ROLLBACK TO SAVEPOINT svp;
+        RETURN 1;
+END|
+CREATE FUNCTION func_rollback_to_svp2() RETURNS INT
+BEGIN
+        SAVEPOINT svp;
+        INSERT INTO t1 VALUES (2);
+        ROLLBACK TO SAVEPOINT svp;
+        RETURN 3;
+END|
+delimiter ;|
+
+connect (con1, localhost, root);
+
+--echo #
+--echo # Test 1: Check that rollback to savepoint inside a function
+--echo #         does not release the metadata lock on the function.
+--echo #
+
+--echo # Connection default
+connection default;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+START TRANSACTION;
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (func_rollback_to_svp1());
+
+--echo # Connection con1
+connection con1;
+--echo # It should not be possible to drop func_rollback_to_svp1()
+--echo # Sending:
+--send DROP FUNCTION func_rollback_to_svp1
+
+--echo # Connection default;
+connection default;
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table"
+  AND info = "DROP FUNCTION func_rollback_to_svp1";
+--source include/wait_condition.inc
+--echo # func_rollback_to_svp1() should still exist
+INSERT INTO t1 VALUES (func_rollback_to_svp1());
+COMMIT;
+
+--echo # Connection con1
+connection con1;
+--echo # Reaping: DROP FUNCTION func_rollback_to_svp1
+--reap
+
+--echo #
+--echo # Test 2: Check that rollback to savepoint inside a function
+--echo #         does not release metadata locks taken inside the function.
+--echo #
+
+--echo # Connection default
+connection default;
+DELETE FROM t1;
+START TRANSACTION;
+SELECT func_rollback_to_svp2();
+
+--echo # Connection con1
+connection con1;
+--echo # It should not be possible to drop table t1
+--echo # Sending:
+--send DROP TABLE t1
+
+--echo # Connection default;
+connection default;
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table" AND info = "DROP TABLE t1";
+--source include/wait_condition.inc
+--echo # table t1 should still exist
+SELECT * FROM t1;
+COMMIT;
+
+--echo # Connection con1
+connection con1;
+--echo # Reaping: DROP TABLE t1
+--reap
+
+--echo # Connection default
+connection default;
+disconnect con1;
+DROP FUNCTION func_rollback_to_svp2;
+
+
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc

=== modified file 'sql/transaction.cc'
--- a/sql/transaction.cc	2010-05-05 22:02:08 +0000
+++ b/sql/transaction.cc	2010-06-02 10:48:08 +0000
@@ -394,9 +394,13 @@ bool trans_rollback_to_savepoint(THD *th
   thd->transaction.savepoints= sv;
 
   /*
-    Release metadata locks that were acquired during this savepoint unit.
+    Release metadata locks that were acquired during this savepoint unit
+    unless binlogging is on. Releasing locks with binlogging on can break
+    replication as it allows other connections to drop these tables before
+    rollback to savepoint is written to the binlog.
   */
-  if (!res)
+  bool binlog_on= mysql_bin_log.is_open() && thd->variables.sql_log_bin;
+  if (!res && !binlog_on && !thd->in_sub_stmt)
     thd->mdl_context.rollback_to_savepoint(sv->mdl_savepoint);
 
   DBUG_RETURN(test(res));


Attachment: [text/bzr-bundle] bzr/[e-mail]