MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Dao-Gang.Qu Date:April 9 2010 3:45am
Subject:bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3008) Bug#51839
View as plain text  
#At file:///home/daogangqu/mysql/bzrwork/bug51839/mysql-trunk-bugfixing/ based on revid:alik@stripped

 3008 Dao-Gang.Qu@stripped	2010-04-09
      Bug #51839   mixup of DDL causes slave to stop
      
      The 'ER_LOCK_OR_ACTIVE_TRANSACTION' error happened and returned
      if a table is locked when creating a routine. The state of binlog
      format can't be restored before the return. So the following DDL
      or DML statements are binlogged with a wrong binlog format, which
      causes the slave to stop.
      
      The problem can be resolved by restoring the binlog format before
      the return. But it's better to grab an exclusive MDL lock before
      clear the current binlog format as the lock grab is not affected
      by binlog format. The same way is taken to open a proc table for
      update.
     @ mysql-test/suite/rpl/r/rpl_mixed_ddl_mixup.result
        The test result of BUG #51839.
     @ mysql-test/suite/rpl/t/rpl_mixed_ddl_mixup.test
        Added test file to verify if the execution of a DML statement 
        after create a routine when a table is locked will cause the
        slave to stop.
     @ sql/sp.cc
        Grab an exclusive MDL lock and open a proc table for update before
        clear the current binlog format when creating a routine.

    added:
      mysql-test/suite/rpl/r/rpl_mixed_ddl_mixup.result
      mysql-test/suite/rpl/t/rpl_mixed_ddl_mixup.test
    modified:
      sql/sp.cc
=== added file 'mysql-test/suite/rpl/r/rpl_mixed_ddl_mixup.result'
--- a/mysql-test/suite/rpl/r/rpl_mixed_ddl_mixup.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_mixed_ddl_mixup.result	2010-04-09 03:45:08 +0000
@@ -0,0 +1,14 @@
+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;
+CREATE TABLE t1 ( col1 BIGINT, pk INT, PRIMARY KEY (pk));
+SET SESSION BINLOG_FORMAT = ROW;
+CREATE TEMPORARY TABLE t2 ( col1 BIGINT, pk INT , PRIMARY KEY (pk));
+LOCK TABLE t1 WRITE;
+CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+UPDATE t2 SET col1 = pk;
+DROP TABLE t1, t2;

=== added file 'mysql-test/suite/rpl/t/rpl_mixed_ddl_mixup.test'
--- a/mysql-test/suite/rpl/t/rpl_mixed_ddl_mixup.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_mixed_ddl_mixup.test	2010-04-09 03:45:08 +0000
@@ -0,0 +1,20 @@
+#
+# BUG #51839
+# The test verifies if the execution of a DML statement after create a routine
+# when a table is locked will cause the slave to stop.
+#
+
+--source include/master-slave.inc
+--source include/have_binlog_format_mixed.inc
+
+--disable_abort_on_error
+
+CREATE TABLE t1 ( col1 BIGINT, pk INT, PRIMARY KEY (pk));
+SET SESSION BINLOG_FORMAT = ROW;
+CREATE TEMPORARY TABLE t2 ( col1 BIGINT, pk INT , PRIMARY KEY (pk));
+LOCK TABLE t1 WRITE;
+CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13;
+UPDATE t2 SET col1 = pk;
+
+DROP TABLE t1, t2;
+--source include/master-slave-end.inc

=== modified file 'sql/sp.cc'
--- a/sql/sp.cc	2010-02-24 13:52:27 +0000
+++ b/sql/sp.cc	2010-04-09 03:45:08 +0000
@@ -923,6 +923,11 @@ sp_create_routine(THD *thd, int type, sp
   /* Reset sql_mode during data dictionary operations. */
   thd->variables.sql_mode= 0;
 
+  /* Grab an exclusive MDL lock. */
+  if (lock_routine_name(thd, type == TYPE_ENUM_FUNCTION,
+                        sp->m_db.str, sp->m_name.str))
+    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
+
   /*
     This statement will be replicated as a statement, even when using
     row-based replication.  The flag will be reset at the end of the
@@ -931,11 +936,6 @@ sp_create_routine(THD *thd, int type, sp
   if ((save_binlog_row_based= thd->is_current_stmt_binlog_format_row()))
     thd->clear_current_stmt_binlog_format_row();
 
-  /* Grab an exclusive MDL lock. */
-  if (lock_routine_name(thd, type == TYPE_ENUM_FUNCTION,
-                        sp->m_db.str, sp->m_name.str))
-    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
-
   saved_count_cuted_fields= thd->count_cuted_fields;
   thd->count_cuted_fields= CHECK_FIELD_WARN;
 
@@ -1179,6 +1179,14 @@ sp_drop_routine(THD *thd, int type, sp_n
   DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE ||
               type == TYPE_ENUM_FUNCTION);
 
+  /* Grab an exclusive MDL lock. */
+  if (lock_routine_name(thd, type == TYPE_ENUM_FUNCTION,
+                        name->m_db.str, name->m_name.str))
+    DBUG_RETURN(SP_DELETE_ROW_FAILED);
+
+  if (!(table= open_proc_table_for_update(thd)))
+    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
+
   /*
     This statement will be replicated as a statement, even when using
     row-based replication.  The flag will be reset at the end of the
@@ -1187,13 +1195,6 @@ sp_drop_routine(THD *thd, int type, sp_n
   if ((save_binlog_row_based= thd->is_current_stmt_binlog_format_row()))
     thd->clear_current_stmt_binlog_format_row();
 
-  /* Grab an exclusive MDL lock. */
-  if (lock_routine_name(thd, type == TYPE_ENUM_FUNCTION,
-                        name->m_db.str, name->m_name.str))
-    DBUG_RETURN(SP_DELETE_ROW_FAILED);
-
-  if (!(table= open_proc_table_for_update(thd)))
-    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
   if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
   {
     if (table->file->ha_delete_row(table->record[0]))
@@ -1265,6 +1266,9 @@ sp_update_routine(THD *thd, int type, sp
                         name->m_db.str, name->m_name.str))
     DBUG_RETURN(SP_OPEN_TABLE_FAILED);
 
+  if (!(table= open_proc_table_for_update(thd)))
+    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
+
   /*
     This statement will be replicated as a statement, even when using
     row-based replication. The flag will be reset at the end of the
@@ -1273,8 +1277,6 @@ sp_update_routine(THD *thd, int type, sp
   if ((save_binlog_row_based= thd->is_current_stmt_binlog_format_row()))
     thd->clear_current_stmt_binlog_format_row();
 
-  if (!(table= open_proc_table_for_update(thd)))
-    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
   if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
   {
     if (type == TYPE_ENUM_FUNCTION && ! trust_function_creators &&


Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20100409034508-tnxocgvycl4frhj0.bundle
Thread
bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3008) Bug#51839Dao-Gang.Qu9 Apr