bzr commit into mysql-5.5-runtime branch (jon.hauglid:3174) Bug#57663

[Jon Olav Hauglid]

#At file:///export/home/x/mysql-5.5-runtime-bug57663/ based on revid:jon.hauglid@stripped

 3174 Jon Olav Hauglid	2010-10-27
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      This is a preliminary version of the patch.
      
      The problem was that DROP DATABASE ignores any metadata locks on stored
      functions and procedures held by other connections. This makes it
      possible for DROP DATABASE to drop functions/procedures that are in use
      by other connections and therefore break statement based replication.
      (DROP DATABASE can appear in the binlog before a statement using a
      dropped function/procedure.)
      
      This problem was an issue left unresolved by the patch for Bug#30977
      where metadata locks for stored functions/procedures were introduced.
      
      This patch fixes the problem by making sure DROP DATABASE takes an
      exclusive metadata lock on all stored functions/procedures to be
      dropped.
      
      Test case added to sp-lock.test.
      
      Questions to the reviewer:
      - Should we have two iterations in sp_drop_db_routines() so that we
        can try to take all locks before deleting any stored routine?
      - The return value from sp_drop_db_routines() is currently ignored
        by mysql_rm_db(). Should this be addressed in the scope of this patch?

    modified:
      mysql-test/r/sp-lock.result
      mysql-test/t/sp-lock.test
      sql/sp.cc
=== modified file 'mysql-test/r/sp-lock.result'
--- a/mysql-test/r/sp-lock.result	2010-08-06 11:29:37 +0000
+++ b/mysql-test/r/sp-lock.result	2010-10-27 15:11:22 +0000
@@ -735,5 +735,26 @@ END	latin1	latin1_swedish_ci	latin1_swed
 # Connection default;
 DROP PROCEDURE p1;
 #
+# Bug#57663 Concurrent statement using stored function and DROP DATABASE
+#           breaks SBR
+#
+DROP DATABASE IF EXISTS db1;
+# Connection default
+CREATE DATABASE db1;
+CREATE FUNCTION db1.f1() RETURNS INTEGER RETURN 1;
+START TRANSACTION;
+SELECT db1.f1();
+db1.f1()
+1
+# Connection con1
+# Sending:
+DROP DATABASE db1;
+# Connection default
+# Check that DROP DATABASE blocks as f1 is used by an active transaction.
+COMMIT;
+# Connection con1
+# Reaping: DROP DATABASE db1
+# Connection default
+#
 # End of 5.5 tests
 #

=== modified file 'mysql-test/t/sp-lock.test'
--- a/mysql-test/t/sp-lock.test	2010-08-06 11:29:37 +0000
+++ b/mysql-test/t/sp-lock.test	2010-10-27 15:11:22 +0000
@@ -972,5 +972,48 @@ DROP PROCEDURE p1;
 
 
 --echo #
+--echo # Bug#57663 Concurrent statement using stored function and DROP DATABASE
+--echo #           breaks SBR
+--echo #
+
+--disable_warnings
+DROP DATABASE IF EXISTS db1;
+--enable_warnings
+
+connect(con1, localhost, root);
+
+--echo # Connection default
+connection default;
+CREATE DATABASE db1;
+CREATE FUNCTION db1.f1() RETURNS INTEGER RETURN 1;
+START TRANSACTION;
+SELECT db1.f1();
+
+--echo # Connection con1
+connection con1;
+--echo # Sending:
+--send DROP DATABASE db1
+
+--echo # Connection default
+connection default;
+--echo # Check that DROP DATABASE blocks as f1 is used by an active transaction.
+let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
+  WHERE state= 'Waiting for stored function metadata lock'
+  AND info='DROP DATABASE db1';
+--source include/wait_condition.inc
+COMMIT;
+
+--echo # Connection con1
+connection con1;
+--echo # Reaping: DROP DATABASE db1
+--reap
+disconnect con1;
+--source include/wait_until_disconnected.inc
+
+--echo # Connection default
+connection default;
+
+
+--echo #
 --echo # End of 5.5 tests
 --echo #

=== modified file 'sql/sp.cc'
--- a/sql/sp.cc	2010-10-21 08:41:13 +0000
+++ b/sql/sp.cc	2010-10-27 15:11:22 +0000
@@ -1371,6 +1371,10 @@ sp_drop_db_routines(THD *thd, char *db)
   int ret;
   uint key_len;
   MDL_ticket *mdl_savepoint= thd->mdl_context.mdl_savepoint();
+  longlong type;
+  char *name;
+  char buff[65];
+  String str(buff, sizeof(buff), &my_charset_bin);
   DBUG_ENTER("sp_drop_db_routines");
   DBUG_PRINT("enter", ("db: %s", db));
 
@@ -1392,6 +1396,22 @@ sp_drop_db_routines(THD *thd, char *db)
 
     do
     {
+      /* Grab an exclusive MDL lock. */
+      table->field[MYSQL_PROC_FIELD_NAME]->val_str(&str, &str);
+      name= thd->strmake(str.ptr(), str.length());
+      type= table->field[MYSQL_PROC_MYSQL_TYPE]->val_int();
+      MDL_request mdl_request;
+      mdl_request.init(type == TYPE_ENUM_FUNCTION ?
+                       MDL_key::FUNCTION : MDL_key::PROCEDURE,
+                       db, name, MDL_EXCLUSIVE);
+      if (thd->mdl_context.acquire_lock(&mdl_request,
+                                        thd->variables.lock_wait_timeout))
+      {
+	ret= SP_DELETE_ROW_FAILED;
+	nxtres= 0;
+	break;
+      }
+
       if (! table->file->ha_delete_row(table->record[0]))
 	deleted= TRUE;		/* We deleted something */
       else


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