MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Dao-Gang.Qu Date:April 9 2010 10:00am
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 grabbing an exclusive MDL lock firstly
      instead of clearing the current binlog format. So that the binlog
      format will not be affected when the lock grab returns directly with
      an error. 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 firstly
        instead of clearing the current binlog format.

    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 10:00:07 +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 10:00:07 +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 10:00:07 +0000
@@ -920,6 +920,11 @@ sp_create_routine(THD *thd, int type, sp
   DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE ||
               type == TYPE_ENUM_FUNCTION);
 
+  /* 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);
+
   /* Reset sql_mode during data dictionary operations. */
   thd->variables.sql_mode= 0;
 
@@ -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-20100409100007-eb8u0k6me1e7pv2p.bundle
Thread
bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3008) Bug#51839Dao-Gang.Qu9 Apr
  • Re: bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3008)Bug#51839Alfranio Correia9 Apr
    • Re: bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3008)Bug#51839Libing Song15 Apr
      • Re: bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3008)Bug#51839Alfranio Correia15 Apr
  • Re: bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3008)Bug#51839Libing Song15 Apr
    • Re: bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3008)Bug#51839Daogang Qu16 Apr