From: Alexander Nozdrin
Date: February 24 2011 1:05pm
Subject: bzr commit into mysql-5.5 branch (alexander.nozdrin:3357) Bug#11765684
List-Archive: http://lists.mysql.com/commits/132017
X-Bug: 11765684
Message-Id: <201102241305.p1O0Nm8Y023846@acsinet15.oracle.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============7370351082030818850=="

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

#At file:///home/alik/MySQL/bzr/00/bug58674/mysql-5.5-bug58674/ based on revid:tor.didriksen@stripped

 3357 Alexander Nozdrin	2011-02-24
      Patch for Bug#11765684 (58674: SP-cache does not detect changes in
      --echo # -- pre-locking list caused by triggers).
      
      The thing is that CREATE TRIGGER / DROP TRIGGER may actually
      change pre-locking list of (some) stored routines.
      
      The SP-cache does not detect such changes. Thus if sp_head-instance
      is cached in SP-cache, subsequent executions of the cached
      sp_head will use inaccurate pre-locking list.
      
      The patch is to invalidate SP-cache on CREATE TRIGGER / DROP TRIGGER.

    modified:
      mysql-test/r/sp.result
      mysql-test/r/trigger.result
      mysql-test/t/sp.test
      mysql-test/t/trigger.test
      sql/sql_trigger.cc
=== modified file 'mysql-test/r/sp.result'
--- a/mysql-test/r/sp.result	2010-07-30 15:28:36 +0000
+++ b/mysql-test/r/sp.result	2011-02-24 13:05:31 +0000
@@ -7452,4 +7452,34 @@ c1
 # Cleanup
 drop table t1;
 drop procedure p1;
+
+# --
+# -- Bug 11765684 - 58674: SP-cache does not detect changes in
+# -- pre-locking list caused by triggers
+# --- 
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP TABLE IF EXISTS t3;
+DROP PROCEDURE IF EXISTS p1;
+CREATE TABLE t1(a INT);
+CREATE TABLE t2(a INT);
+CREATE TABLE t3(a INT);
+CREATE PROCEDURE p1()
+INSERT INTO t1(a) VALUES (1);
+
+CREATE TRIGGER t1_ai AFTER INSERT ON t1
+FOR EACH ROW
+INSERT INTO t2(a) VALUES (new.a);
+
+CALL p1();
+
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1
+FOR EACH ROW
+INSERT INTO t3(a) VALUES (new.a);
+
+CALL p1();
+
+DROP TABLE t1, t2, t3;
+DROP PROCEDURE p1;
+
 # End of 5.5 test

=== modified file 'mysql-test/r/trigger.result'
--- a/mysql-test/r/trigger.result	2010-10-26 11:48:08 +0000
+++ b/mysql-test/r/trigger.result	2011-02-24 13:05:31 +0000
@@ -821,7 +821,6 @@ drop trigger t1_bi;
 create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id);
 execute stmt1;
 call p1();
-ERROR 42S02: Table 'test.t3' doesn't exist
 deallocate prepare stmt1;
 drop procedure p1;
 drop table t1, t2, t3;

=== modified file 'mysql-test/t/sp.test'
--- a/mysql-test/t/sp.test	2010-07-30 15:28:36 +0000
+++ b/mysql-test/t/sp.test	2011-02-24 13:05:31 +0000
@@ -8713,4 +8713,45 @@ call p1(3, 2);
 drop table t1;
 drop procedure p1;
 
+--echo
+--echo # --
+--echo # -- Bug 11765684 - 58674: SP-cache does not detect changes in
+--echo # -- pre-locking list caused by triggers
+--echo # --- 
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP TABLE IF EXISTS t3;
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE TABLE t1(a INT);
+CREATE TABLE t2(a INT);
+CREATE TABLE t3(a INT);
+
+CREATE PROCEDURE p1()
+  INSERT INTO t1(a) VALUES (1);
+
+--echo
+CREATE TRIGGER t1_ai AFTER INSERT ON t1
+  FOR EACH ROW
+    INSERT INTO t2(a) VALUES (new.a);
+
+--echo
+CALL p1();
+
+--echo
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1
+  FOR EACH ROW
+    INSERT INTO t3(a) VALUES (new.a);
+
+--echo
+CALL p1();
+
+--echo
+DROP TABLE t1, t2, t3;
+DROP PROCEDURE p1;
+--echo
+
 --echo # End of 5.5 test

=== modified file 'mysql-test/t/trigger.test'
--- a/mysql-test/t/trigger.test	2010-08-18 09:35:41 +0000
+++ b/mysql-test/t/trigger.test	2011-02-24 13:05:31 +0000
@@ -998,10 +998,6 @@ call p1();
 drop trigger t1_bi;
 create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id);
 execute stmt1;
-# Until we implement proper mechanism for invalidation of SP statements
-# invoked whenever a table used in SP changes, this statement will fail with
-# 'Table ...  does not exist' error.
---error ER_NO_SUCH_TABLE 
 call p1();
 deallocate prepare stmt1;
 drop procedure p1;

=== modified file 'sql/sql_trigger.cc'
--- a/sql/sql_trigger.cc	2010-11-11 17:11:05 +0000
+++ b/sql/sql_trigger.cc	2011-02-24 13:05:31 +0000
@@ -30,6 +30,7 @@
 #include "sql_db.h"                        // get_default_db_collation
 #include "sql_acl.h"                       // *_ACL, is_acl_user
 #include "sql_handler.h"                        // mysql_ha_rm_tables
+#include "sp_cache.h"                     // sp_invalidate_cache
 
 /*************************************************************************/
 
@@ -518,6 +519,12 @@ bool mysql_create_or_drop_trigger(THD *t
   */
   thd->locked_tables_list.reopen_tables(thd);
 
+  /*
+    Invalidate SP-cache. That's needed because triggers may change list of
+    pre-locking tables.
+  */
+  sp_cache_invalidate();
+
 end:
   if (!result)
   {


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

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: alexander.nozdrin@stripped\
#   mignj7wm1ym3i96d
# target_branch: file:///home/alik/MySQL/bzr/00/bug58674/mysql-5.5-\
#   bug58674/
# testament_sha1: d65a42594dcdf18bd48e29d2e1714009c9d894de
# timestamp: 2011-02-24 16:05:37 +0300
# base_revision_id: tor.didriksen@stripped\
#   qmhcbs7oyzdgiyyy
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWUPJtYkABSZ/gGAQIABY9///
f+XfwL////BgCm76E292gAABGFDAFChpoaBholPKA0DRoaeoAaMgAAAAABzTIyGTBDRhMEaaNGIG
mTIwABAqUhMFTxoSYjRNo9TyiGjynqNNMnqek2pmgNIJEinqMhqaYjE1MyhieoPUDamEZDT1B6jQ
OaZGQyYIaMJgjTRoxA0yZGAAIJJAQAJoyEwQNRlNNJp6QaaMgAxEgSJ45spfADuZMlLE1Z0y1xkE
YiUOIf7BwqK0Be6PWaFHTvaduSk8kQjFShSlBZnXgi7krLquNoiG4CrNAShMniQQVsB4t+jAcR9s
Xtdr5xm574P8I68MUbRkiYaWKmFHUVYoDiuTPIg6iVaPojChtptDbbefQlhq3UvuT7zLE3lfCclc
icOdJlyOEpKc3bak21mv0C2br89JiDYBjNgDlDfeLuFUw7iYzsD3LQXnMVoYxNg9Rzi5uUKasKm1
tYTBtDveEQkR9es2T2NkvJDIqGjcfgDHc2zRHgmHCt178dn7bv5haf0GLjEHDyRAePHDzK0RlZe7
aOPtJar2g8HVuOImAWw8EodYIQoPVR6gQUZEBSMr6DM2nkd7WcDC0rCDfGOG5C6/a9YreaUi5BBr
lIiN192Zi3likUUUk0JNBsUPikXPCo4ms1090rLSY5CqPqbXAURKh4c6F06hBhS+Jw4V/F156L9z
wCpbytgiF54skAbmxmmIYzp4zkfxaK6KUZo4YHfwoQnsjVkGTaQHndRBzCZQHIcyAagIpziEGHiC
gpHkgqGSlKYl1fEpKV9BUHoVVPoJCgwWjKKsIkn2FQ9/EuoYBwy2icHoMRFQJSb4zVCPtkgHN5eZ
c5+QooC57sGD7qiinKKoFnY9+32K+s8hL8SYbGsuhqk0hjHSWFBeqRSSnibD6R3Ut8uuJXWXBvMU
MHqabGIojTDhVERuP1N3uJawZb2a+q8WA9S9gA47aeJmUICgWyRYaOOiA3GZHwvqxHF5KBZAunUl
DSLgMiZWVlR/0ryInLaMUkf0LqmoYwJBYbGCguErC8SeYwJD6x2RAHko2HPQltMxxhIHVFJeDF42
ROZpmJWdy85FI8xc2AWZkLGwiPURPS2RgJVFzMxeScMSbgZEDn137pkaDjgWkFFsR0xXnAtiQUDG
U8Ck/PrM5EHem6kdu8bOD+ZTtyauo30iU7DA/T1NpRbIeVGb6zcXOceJCOqIq0xvKjEuY9ecOxId
+p4G6J16yInQ3SNCkkZF2b3tZLcxDPPOMA89SgiUmpYaonqnnmXhVKhWBuvvSgsiG7IiNohSG2Hm
epHqdC1eRhjVrq0GW4xx/rAVEQycaGfD+UyysnoWmjyJUtF4NxjevQZxYa1FmKVrzeZHIwi81LCw
vmTNCJt6HU+aY9RLuVV6Yuq2s1rtoSdHKV+p3akrVLFe4cThBFUplewdtIl1FW/xH21G/cRIXlpI
6l8sgHCqHUmOBI4iV0CgrLiowC+6ZBTa0MXOGGjg6TYyaU7nihF6TUNIUgI0NNfZNpDG2F0IKGLo
FzYy0NjOVGCUiBDWn1o56eJiD0mjYJdgWH5DwFIS9G/YwaGx0P0JWoUg7RTCAJDAGTF0nzEy6JMI
AmECl/cTa+i5RPaj7JePsKM7O0LhmGJaM4pd8qUpUHaK8jSkxRqMUi7JMUqKJOxKGZJqksTZqWFB
rVKGpJgOtO1JeXBYOxFcmgZ0VpWk0pNKtKxYA8Se+K6KBbwXgNAcPSGfxokKTAg2bYCc0sf4T0nm
Wks5735PQsWv1dDM/29ayw01eomrqIuWuG9WpwLMwxfdu5nOlIq7ecqf11642djJVo6oLXImpx8M
TB6cynos8DGiz+3IfyrftZ6qU8HS63S8rW8qjpdTfuY8L3Pdi0vPU0PJ5mfBpn/ONbRaHO0osX2I
CXQzcmv2mJxNQ35+MSXMkO3mmCTGBQyvNF7LDXicSjzUTXoYjvMj6XcGCwFUCaHQTgRJi5JHI5zC
C5O5nMcBBwmyQ5EQt2+BRyGPyCk8DHBK0urS8CFt80jUtHBI10yJ2RH1IC7cDig91uW8XLobMikM
FQgGkcOvObnchkfJHL8XjQc92D/UTSBDMjeZmPnob+o64YutH7J4xPPsbD4lRaUo2I5UCtc2SNlg
DjCkYgtw65nAphbHHcsH7oFufHw8dGu8txYS6Fdfm1n5uMFs3eOQvdMlcSr3pgBhM/SZGIeTLmGN
b+3OkQ1grAm4ChdBttkW4L6bEbXkcG5xs7a282/FyvCo3GcagXUwFrefJHdG5Elg5XV7DiP7HImk
zeJyHsaEF35jAdEJ4bEKS7DhLbRmeJM36FZevRjIEwu339zqeSjCLkOGD2NXHqZ0z4noSBMRPCrd
0EYnYfV66uOf1mol0OHHckUhzT6ezxTQt4lwNb1kykmjBgWUrVBV8Nozd3CtODUy6MNbf4seUWYj
5mdxxAiLaf39FUiHzJGIk6B6ngN029nOc4eepM5F3maAUCxKSlUFhe4tXJuUUwyI5kAkpoqBDCMD
0b7/IA4sLucBOnx839RZDxWhhdzPIfzPUSKBL5nzEuhDesRx7WDsEZ7DBZCW/plRPVVUK/BIZKYV
oo3wJwZiERnTSURGBHmKh6BkgfF8e4ew9BCQnFVpqJUhzAO4/KNNFghmSb8GRmXnltZI51dToKy2
kMFxMDigXi0LxvsqwaWWU+5FIVTKqficPKv77knTaGhMk3MDklM3jkP6Mhkm0OxMVtsyouL+s6kf
lZAJ/aUG0qceI5B2WBbeZmTm2+w5PTJI0YvHglW6gT5oXiLH4ZipVeCF1BGfgVp1Z7ynbVqVh4jV
KbZ+A1uUlD9XBfEg9Zqmsj4m+JefAosR6KOlIwZ2Y0SAaLx6iwWV6s/RWNjR1tttts4uNnkfVLOT
hwZ4IYTMjfkBSni7p9R2OxwXxXUKnj+w4i4cMMbyLrAFwGA+ZOKImwQxFiAupB2Ab9RWBQIZl8JX
05HQeSerEFqyMj6Gxb94zFIT1tck6i3LkWs2Wv1n71w+OyYjcMeRbHGIaQ70GnQ8NqWJp8iAFZcO
DI4FBfcfdge4laWrhWgPgN3Pi5Ilgc96ZaGRtW4S9z2RqQV6kZLyLIorg5bY53iVcTW2cjW/8Xck
U4UJBDybWJA=


--===============7370351082030818850==--