From: Jon Olav Hauglid
Date: October 27 2010 3:11pm
Subject: bzr commit into mysql-5.5-runtime branch (jon.hauglid:3174) Bug#57663
List-Archive: http://lists.mysql.com/commits/122127
X-Bug: 57663
Message-Id: <201010271511.o9RBavwl001611@acsinet15.oracle.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1725112091169806743=="

--===============1725112091169806743==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#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


--===============1725112091169806743==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/jon.hauglid@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: jon.hauglid@stripped
# target_branch: file:///export/home/x/mysql-5.5-runtime-bug57663/
# testament_sha1: f65b4eeaf2c035c408f6c05aef4dcf6837fa5194
# timestamp: 2010-10-27 17:11:26 +0200
# source_branch: file:///export/home/x/mysql-5.5-bugfixing/
# base_revision_id: jon.hauglid@stripped\
#   mvim43og2dd51exu
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZqK9ckABGR/gBCwCABd9///
//f/6r////pgCx1m0761jR6AG9Hcdt2u7du2uzkNLWoKl2twSREm0plM02mk09U2QwpsKPTUGmg9
RoaDIAAAkkmqeBpGQwp6npTTEaA9R6QNAGmIaGgaA0GqbBIFNpPSDQGgMmI2kAAMgGhoA0AkSEBN
TCTzVT9T01TYKfqj1ND01Damj0R5QADJ6anqekOGhk00NMjQ0yMgyMjQyAxNGTQBkyMQwkkCDICa
ATCAkzKaajyg2o0BoDIAPKKApraeD9105H34xpXS9/Xr2ctd0F7fb68h5O85DjuDieVtWfEcUfz7
uqBdjfotwmcYYv6LW9JpZpQQylO5uQCtP28YR0sYYT0PBkVsCgSJwn9HNHm6JdktzY3Qj1Wvlkzy
VTS7wJDxGgZOGA2wXPFJpNeXnh+u6sVvpmYGY3qs9YX0unpkanFVJb/Wboo9UEYlVjKObK9eyNOj
fPYxuJhHShZRlQqRsQ9mjYXZHN+3PaT4dqvQaC2cy2kHD6McSkpz5K8Lxpx4HsiSapGMh7om698f
vaz/ckFItZcKKiygbTORiMrQX4RWSXaPel3Z55UfM4EqRodIlg68BLmVhSOZdC6MH2Qqvp1YY3GI
rkCJkPYTsnxGtc5sxiAWPe7HDhTS6d6hsV+TYvo3Ok8RUMiCyRUKqThEiNjnDOkmNAQBLjg3rsiz
li2wot3Cbhgo57tIpsS29DY222MaabHvwxnYx681U5cE1ONY9hKjrEueyx5zis6s244BHQugLRKt
G87vEUgaFnyzU1llgCPh8xuRIO4A9Radsih3n+xne4Ss6iNrvarVUdngV+TepjWS8TdN9jUXdu7s
r8sLclrLahLNDhKjSvn5uvWpg6/o6KyNE6xepPMpID44Zj4LlA7AehDklwjNQ1AxFozafJ59xWBR
hgF3lRNYVBrKoc6bzQ0EdDLqavB8hrwcYReFRUSciUUPRqvANVbhL+BLoZ/TvEbjDVGUCoWsRUqO
J4651TkXF61jSch72kUY7b9xMRKoLl7JnbmcHrA2G0ZmJitPNpVK65ONoRZKZ3QsHTZYhGWfAwYL
WAKC0tWtYhNigMhZu3m+SMFbgYMdsRES2jGN8TbnEUj5mW1/tXy3FxtuYktk9gfk1EobSpwxUdpE
rOUioaRpthBfgeds/QlkctM1g3ZW8xusYWbncbrzjBOt9FnDgMWUXeMXMQLA6AxI5j95kcfm44F1
lNB/HdeCtUF4aQz66EnptRgHWt7x50XZs26pTOfKhtMzeRrPiSEO1UJ4DiJQsNt9kKBePtUIDmsO
0p0vHHy3UD0sL9pM3ZojywoWj+JW/OLnPZk1Qjy8IFFToVvNhuHZzCsqaU7MUYmMiXWzvtkYiKiO
W84DjXhAzGVxdhF46rGfQnIktld0wrM6pjhoBIi2rQg/XaXlZA0mTMKVETv6Bddba5ewfuuZoWbC
l7najNCKoQ63MLRVWOfo2fTzohnN0eMpkKmutwzqGJcoVS1R5Zc8xKDjs0VxQTCy0Zuda8SUZ+bS
UCWbxCDqPVpt9RQ5hBsEH1hgtaY2/4EF6nuuX+d9ZJhRYBYVFElKcCDw7EQqxn3NcUoVXZgs4NC0
M74RnoHsqPbWvFl7T22XBPA/TQ77yhTGIMw/+eDF7EotkoK565wg2aL+JlhQfhubJA2DiM39sHii
iCUBh15fALx4nADmnWAfMxtMg7OqmaxrqBMHBuW3WATyJHUtGHJ4gahY7BPMx6KZjFcCsQYjA8wi
KoiPCj7BmZiV4Z4YBwurjw2VYLy8+MidF57OGmTbcSZtWxjo09Y7GuK5FGcdKYHxPXibVyEsYnsu
GYviBijznGTgXQlOU4C84/KTnT9uBnP92yIl/OBqGxKw+0wImUqxfcHSBXsZhy+JtwuJZhVyVGO6
EMZWniRhLOgtwKrooO3x4VIfGEwUiijanhkEs6DZyGL+CukOUYSk+OI4eY80e6M1VwuCQxj54tNm
QOxOZ0vu7l1kuzIQHnZxEJGDIxVUdPY6svUXLSpzLrMZa+RcJCmK83IVMWbRwVWwXZm4YRJhPEqz
7zJAaWhwZTJ72ZDx77KK1MknAyBqE1SRkkEHKxEMcQ+VcKkthfdoOAvtLC5TZKiYRtFSIIg7dlJU
IrJgz8jjQE7IPviXlPA6GpWBzJfRaIh08w1Hfhe8n5h0VJyKjTuLyO3XVuZNLRj3ccTgC/RDjGhQ
+1OlBSpoGsOgLhWYtdG9U6JzXjzSVVU7zna0yxjLY3DqlQKtxaWRC4iEnMFEmCJGRtcFTGMB2yEM
HuS8uzrv3N48a0GqjaVdbTZO2/0OLdwhChP0Ivu4jYWmRMDIwTI6kTrRgnL6X525lV9uzVcN+Vmo
3ozvK+DqyZW0xlIoq3hMHvqLByUxFvlymBzUS2Xiu4iZlh6I7eR0PcO5+RqkeR3yIXd9pYg6vIBw
WYcu8+S3rUGBxHBRMeyDw7RGCLDLPanK2SdOuB4Bw6cnG4pklNhpLuaJhG3azEwYwGTo4xKl0sgx
e8hGxE5GqadM0QVWczpvAqXDMRtI1uzdD0ScJO1ldv7+4MyEUEU4KoI8fXtHmgpLJ5i8ZYxod5kW
eBTP3U8OOGCQYDAzDAyDDpV2syGMVoJ2jZdoOaJrJkzY4K/h1PQyX3HvBk7Tn1MGQmZi9jIRuPq2
uFDxTKpHE0aDQGQJClpkFhG62FXHXVsGVK5XiUCg1tWnR3Kqu3r+FJQxgpS0JrNvBwzDUoQUZuqB
ZGrIox6+nRrMoMMAaJtr2oMHp8vVClbIqQ7m0AY38r6d1tgYcfmEU1rLV4G9Mt3r4GDaSYyGyHe5
Bi0BdGEt2X3pdTqhiSCdQU7EROQH3wQ7kBpEMGyL7yXEIaoCMVbSQFpYBUwlQgHjIZbz65Hnt9YL
M9rHe3v7JYKbCZAMaF7+Csj5BB7q84IbD7RJqYDF86g+O4hSBjE0TAatDMqByEsROXFwDOD1vTab
7xvF5kaprdKAtbI3HIgPFb1ZMhgkH1xYuTrrSkPY+JziRK7A+lmXspVIfM09Rppkl3VuN+Ab88xn
jDixHmxAP6wBHpAhkoKp4+ySN0kEacFWDMjZQJWhPPz1ryrNvBwX0alvCj33BdCo/Ligkc52YqLD
3lYV0L9GyL6RD1nvrGyrMRMm3MiU5w5NTkpyIUNrjEZiH2OjE7+zPGJlZ7xGd1Q4rfnt4MnNNcJV
BKgoKLJzZ0Y2Ty5DoJOBrjciIblyCZQ1vAYQwKxPMKAMH8o/o82Fa+GyraU5hZK8UbKbnC4mE65y
jVuyiEjKp/0N74uYpNrSpRqEzyIfVhUDEUoBQaSlyMJtIQITidW5FjdLCzDLzURbcxotK1ny1pBa
yG4ge/ArrdRgiglJUjhaJmnplUTuyyRGSeZrt8q3C5UUqY19kPrqXsBrkk+413vFQmTOQV4WwjUO
xRDpwCtLMqrGXP4D8DyNDwLGvOIVT7FL8w/VRqTCwYB0GnLfdjaCI2ve4jYI2Bt9yRTcMOkiBBlo
KzhoeZkzQRMVZzWW1AW+V7aLMsIi1mP+LuSKcKEhNRXrkg==


--===============1725112091169806743==--