From: Alexander Nozdrin
Date: March 10 2011 8:08am
Subject: bzr commit into mysql-5.5 branch (alexander.nozdrin:3383) Bug#11765684
List-Archive: http://lists.mysql.com/commits/132730
X-Bug: 11765684
Message-Id: <201103100809.p2A1HLsA020173@rcsinet13.oracle.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1934249000277661075=="

--===============1934249000277661075==
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:anitha.gopi@stripped

 3383 Alexander Nozdrin	2011-03-10
      Patch for Bug#11765684 (58674: SP-cache does not detect changes in
      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-03-10 08:07:57 +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-03-10 08:07:57 +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-03-10 08:07:57 +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-03-10 08:07:57 +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	2011-03-07 09:08:10 +0000
+++ b/sql/sql_trigger.cc	2011-03-10 08:07:57 +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
 
 /*************************************************************************/
 
@@ -517,6 +518,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)
   {


--===============1934249000277661075==
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\
#   70kwfw9pbta0doaa
# target_branch: file:///home/alik/MySQL/bzr/00/bug58674/mysql-5.5-\
#   bug58674/
# testament_sha1: f7772e168d457c0fb53bb75d8b8cc05bc1b61c98
# timestamp: 2011-03-10 11:08:04 +0300
# base_revision_id: anitha.gopi@stripped\
#   5nynjjmtra8ellb9
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWXKed38ABRp/gEAQIABY9///
f+XfwL////BgCm+eNbZrZoABRjY2agAA1kC2CEiRIU8Ue0KP1TZpHimp6bVNGg0DRiGRoAOMmTTT
CZGQMCMTRgjCDRpgAEOMmTTTCZGQMCMTRgjCDRpgAEEiSGhTaJNhGU0PTRpqeoZNMRtJtRoBo0Dj
Jk00wmRkDAjE0YIwg0aYABBJIE0ACNDQTTQJoJomJo9TE9RkDZSQajsplIDuapWkq1viXxywmZK/
ofAw/Ozh95r6qbrThtw6/53yhvc5GyUpSkLVqsHV76itTZkOczOCnVwPhQUYkEFbAZN9GA4q2Lov
08HSc/XGjlLOX65I2jJFIZ2KeEOwuxQGaug0AdhK1HzRjQ202httvd9CWGzwqucn9Dgih42TqrkZ
IplN+cy75VKih1tNtqd1QTPC/RnMIdYGI6wHKG9ItorGG0oGbA9SsMrmKgYxNg7zkFu8gYL9Cm12
sJg2iPXRSFRTy8U2pMw1caGi0dN87gZqMc0U5hkCu3+nHP+V35hafoMXGIOHkiA8eOHmVojKy920
c375/Ft3l75q926Kg80nyjv3ox4vsugRNpBY16YbbHwO9zmRhSriDyDHDchdnO9wU8spFcEHNKRC
OSudJaaQoGDGMVyFcGIYfkSJnCZlBmEtxmcN/uSuvJjgVh726ZCYOnd1B7Cn3epz51erq2lu4vEL
FgUwRrzqJUAHg2M0RDGfDOcT7WieSxGNGyk4sKFDKmNiGOqRJVg2JgiRAQxBIHfg4hFiFBqPIBAQ
VDhGlLygnSKRNJTcJPaE5C0ocLuGmuJWXCUiqNRSJP4l9TAQNZBQGkZWKmZrtrJO1WjEmdLrEGjj
McYhREGbB+NRsKJ22i0LacParN0v1JhVbvHqG9JiobPfPC6YyqFIK2U5GZ+Et2bfDrUZcLAwDUzQ
wbx6MadcICPqVVsaMzWOwsFWewAJWmh7aRTNjZHDXhfbeZMt3KBQWUDTpE7SMXgZlBAsP+mLygxO
JMj9BrKG29CQWG1goGagcgLcLyRJvgPDYQr3NpvN5WWlOFAQrgxeGwwLR2ZMut9CR0KB5i7AKrR9
7OCB0IielnGysgWuJJ5J+BX1HDzly2SUyMzloD1CJcnNQJzhuBAgQIy0Mz+IjuYzvLSzjSOht7cZ
vMqmoNKS4+nmZkqaAvIFZlVE2BxIQRYOEsigpuLGPPnnzJn1OA8p5crCJ05lBoUmqoMzCxz9atKT
dpGHfM4dxSPkVmJqaxJEnq8YqcarvKgjirg3OMcUtpkR3bCJU4mYngeRDqby5djhU1QljuoEthJ4
nBuTzQ1+T5GwdI3Fg850rYGsKRKwPQbhA28My3gk43mpyJ8CscPMzIgaczqHKY7N6+WlmJuIwhIS
plgnQ2ZFMsU2WMiyVYwvhCg0riPoKqyjOviSrhUbcTMhasJGkziQrjYOvSgKRlhDLAoLpFpTgWYn
CRuCB8BK9Ta4V73D0RsfhJokrnihF6TUNIUgI0N8ZMkMMzBaDigq5xcfAUJtlyNUpECGrOZG3BRc
oSXKW+wS6A8B3B/BSEtufMwaGx4D8CVEKQdIphAEhgDJi9x1iZkEmEATCBLFkFyH6MIxdYjxFWB4
gMvF4uJgUgWhUBeAc/024APoA+8LhKoRgFQkUXiqE4GEv3EwREsEoCywIAwGI4YMBKkD5i+glMKA
CAH7iHpWgWCHieJXCVw8TwLgGzTYirE4WYMoFgVe0K7kSFJgQdnbAL1qZznSJiYu4KlJFQlAKkma
zoJHPqO5WHxQpj7+orOnevX9ktgLFcCLCZwWkh5C+YKpgXz/Gr3HSJoj+7Qg+/55o2/IxHW/qgJH
MVKwMdEKo/KQ32Q5lbEI/ocQ2FB1UaO1xHechQYDjGbTvOLjNae6Waiu0vG8cuGgoPEtqKV8yg46
HL5molTavtQE+gxgWe07DGZUOI41pMVlDKys9yr3niuPEy+Z1L1iQ8ifnfowWgsUhocxOBEmMEkb
zhQEFwdtGAee9ECzSXM7DjaTNxhelYVJTf3RBPGDkdTWJhucSfVIpQFmgONTgLn0NDMSoD3oC2Rx
76OjugyOyOX93jQc917/MTSBDMjUzMceVu06F5F5uwheNPGB0Kiw9So0RmizoK1zZIzsAcWGRkRW
hMi5ImFsctFi/SBTTx7UHLfva/s/QkS6HCytqf4cd629+QvcmStNuzVMAMJn76CMQ7Mu4MuT+/up
ENYKwGAoChAdpJEqSbbBG47jsdhtNFqXHY+zyl0OpZ5lXgFpUB1IGQtaz4I9Eb0e2ayesbNh0H4n
wEupNJm8zqPY4EF594wHZCeG1Co8TfP0W48yZy1LDFUMeK2AnCd8oep4EYRchwwe43uPUynRxPQk
kMRONW3qIwPAfV67nEzYuRu37EiYnUdegqELcbaC1JWTJhQzxwA79uoEcqXixvLvupwPLjVzAsxH
0GdxvAiK1e79qkQ9CQ0lFRPEt01H+e3uc5zh54lZyL+xuAmLIpKC49RLN5cr2ilzGjtIBJPVKMgI
YjOvlcAeG+AuZxlYoxbO+r53C0khUhhh3Fo+48hI1+J0H71kOKhr0bMznbH1lIVLh4zOqMQWIm3h
TIMpCIAHki+jSWkkDSCVMtIekhBKkUFt8kuhVh6gDAb9VllwhtJ7WjqeCwPHRhHPfIS7zoK26AYr
3mJyQLxaGEGS6xdSUq4v+oIwQOV7l6hp4FOp/aIBaLAW1gckpHAch/eyGSb7RLE8SYrbZjlWXF/h
RWejHzuiE/uKThuLXnmOQYl155Gyb3bveOELgxeWKQJWPqE+aF4ix2bBVKytC6oDmVLbWfCVFtW8
rDsNUG3mNblFQ+rgviQeraiHA4aF56E7EQ0mMHsYzRIBo3DUXI32a83VaNjRsbbbbZl4WaDmqw1Q
QGmlDE2jg0Ad5SFvUsZaWmpeVWBjkStIIuHDDHAi6wBajAfcURREzEMSEpOIi7iLsA4bxWBMQ2ac
vSjCrM6DyVqC9ZmZ+J3LatdRmKQnayWFNufQuZs+HxPyWumygQ4Y+JZ7MYhuh6TOpmlebfsOgFRa
fF4UiWRqREp1F9x3/fiXlC1tQHsG9h6uSJZHTgmNxmYr3HejeQPEp1MkBijWbCPOUlmjMUn/F3JF
OFCQcp53fw==


--===============1934249000277661075==--