From: Alexander Nozdrin
Date: March 9 2011 12:29pm
Subject: bzr commit into mysql-5.5 branch (alexander.nozdrin:3378) Bug#11765684
List-Archive: http://lists.mysql.com/commits/132666
X-Bug: 11765684
Message-Id: <201103091229.p29CTgKE001196@acsinet15.oracle.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============7436520852413961479=="

--===============7436520852413961479==
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:marc.alff@stripped

 3378 Alexander Nozdrin	2011-03-09
      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-09 12:29:01 +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-09 12:29:01 +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-09 12:29:01 +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-09 12:29:01 +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-09 12:29:01 +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)
   {


--===============7436520852413961479==
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\
#   c3afzn8m7ulske7z
# target_branch: file:///home/alik/MySQL/bzr/00/bug58674/mysql-5.5-\
#   bug58674/
# testament_sha1: 911b1cd4e5dea611980e81e42fb05fd7d4d9985e
# timestamp: 2011-03-09 15:29:08 +0300
# base_revision_id: marc.alff@stripped\
#   94sl2jmraho7yxzp
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWbDlVfMABR3/gEAQIABY9///
f+XfwL////BgCm75E2ySgAFIQqgUAAoCgYaIoAD1A0MR6gaNAABoGgAA4yZNNMJkZAwIxNGCMING
mAAQ4yZNNMJkZAwIxNGCMINGmAAQSIk0SekaZMajSeRJ7ST9U009qGUPKHqaeppo9CY4yZNNMJkZ
AwIxNGCMINGmAAQSSCZEwCaaAmICZE0IeiNgUZNDxRIFMc1JSAO5kopwYZOqT0hBGI7T4/CG+tvU
GeCq8ENrydHxwrTCIRyVrWtRczyXxfhwF6ubRENwGLmgJpaSwIINTAdW+7AcFbF0X7O51Dn78J8a
MqfnijMZIiHRhSyD9S3W4N84HaB+o+pHzRehmZMyGZmb4+hLNi868Ll+Rnij0strdJyxZNr2HNgV
mlHc022reGoWnnxU6TkDuAzncA5hvyF4ixMPEoM8A9y6DG6CsGMTYPceoXXtCu7MqNfFhQG0R7IR
Cgj49ZMnsZUdyGRUNG0+AMeRnJEd4w4Vm3y4Zf1t+IWHyGLTAHDyggPHjh5jYIxrudmOb/cyhZuB
08LTjiGAL6TvA3cpCNE7E8CEAyIBZlkQZmYPlePeaWFi4SDKMcNyLv3vsFbqmS6CDsmSEdc5RKjx
CAwMMMKxCsDMYP5EiRyGNhosTbtiJexKy0eOBaj8G54CYOfTqD2FO30OHCr0dqai3j+oYLgWRXAj
1uJAPNsZ0xDGfrrNj/Zot0YkbUejKbi1ChmZjYhwnGmTnVBg2JqT8xJ4PYQSBzEGYdI3muBMIiDU
UvSpeTKYiiRlKEVF7qBebhKh4ug0U/iUjnThIdPiW0sBA1pFAdYy4LRJlS09d+JVDtdog6rNpnnM
KgQYsH6VGJKdMxKwTlrsqd61X1fh6fMqCu7aQIpNQNlpKZYqaWRQD2UsDA91GzJvb24GOotYvDcZ
IYI6GrhQire4RyISY0Y1OnULMevMAKI1lpd66gREyZygbd+66202cXlBTQSmCH250UBADEfMvcWL
/o/SMSUzLI4HFVlX2L6uTqewkFhtYKSTgcwlMt1sUEYO6kA2kdRTvN5sKicrpg+otDWXEiAk/IhI
22eRM5kx5g68KbB9rOIG0iJ6WUaujELaU8eUXm0YguXLZcTIzOWgPUIjWyExvIDx5F8NC0+tBvT+
wZ/hpXYM/XnvdjwqdI0CkuPt4mRRVK0gXGuBrDgPrRWayRTbOLq2PHliUn3NxKBx42ETiRNZMiXl
l7n6dmMzPKMOszS7mVEJE6yg2kSg2q4Y1OM11NKgo15LANHGV6UTIjpmRGJGB3HgQ6HMsXab8G0d
mOEwON2g/h+1BkSv4mhfWRJalIOEK15DOJ5lWxJxmbjkZlJSPLziQNeZzDUJYKRL1Ge/XxmMvwbi
M4WRWtXECtwVxCtZVjnYjA7SgpxHUFRqxq7CrZUaYEh5E5CXIwpjUPEuzJJwmEpmO59KwKivWVaz
VhgYlBXOi9XJzWi5JgkpZOWrsKhxSKlkpO11FUCy176tIY2wvCCw0e8XpwLE2zBGaZIENdPsR6K2
cqhJfgbO0S7w4j5B9BSJbMuRg0Njqf6JWQpD9xUCAJGAMoLuPETLxJhAFAgSuvFtPMkML4iPqKYH
1AYrKywkBECoKAKwGl/LMzgD7APuC0SqEXhUJErhVCcDCX8CYIiV6UBY3kAYDAcMF4lSB9BfYSmE
gCAH8CHpWAViHieJWiVo8TwLQGyTZRXigWQMYHQGn3hz+lEilgQeX9wC3qh4AvgFNALyiTPUfiPP
E/oQPl+ZEvnc7/ALD3giRQZ3Ej4y8AsqBeNf+bew3iaI+uleASfx8ORGf/DKOs+SAoOAsDb7ltQq
h0RvXDgVMQj8DiHuIH5Q59Gc75nvKolxH3jj3nyO3vO/44nzNRqKzyeWniVzLV9DYduhy+hOtfkg
JcxjiteB8yYl3DjWcxxlFJisoZajyWW88FxOevidTAvIeBPxt0YLAV6Q0OYnAihi9JG84SCC3u4D
APN6IFezsMyZmTOpVe65J5Uk7jEFSMFHE6nU0YjhqiVICvYDjebhbMhoKh+KAx2Gr7q641jR+SOf
4SOkTHFPUJ1BDZG4xLzDI4ci0jAtfYNK+ByMCrUO8zoJajYjJG7kK5zZI12gOLTEyIrQmRckTC2O
vRYP0tiVcOsjjpua/TYUFHIfVraz6uNfPAXrTJTNeW5MAMJn6SIxDJlyC/e/nxpENWKsGAkEkB0o
SKKSbbxGw27Doczw07fA4mBTxPb4yOhURXeFY4C2ik2KwyFvvPwR5I3I9KVk9Y3ZnYP0PUJdCaTN
4HQexwIKPQYDqhPDWhS7jfPM7yRy2mouXixiCYXf+0PUdCMIuQ4YPQ0ceoxnLeeRQkWJxQe3Vn0E
UHgPq79HEziabckiQnUdOwUkLYa5FaS1TJhJnjgB379AI4UvFfcW/nTeeG+riBXgPkZWmgERev96
UP8iI2igd5ZpuH+Ozsc5zh53lRUVCVvU2AUiwKiZIvcWK1oCOI0ciAUK5FQIYRev25pBl5ODox4G
4Tp7/J/MWQ8EWhhdyKx9h3iRu/E5HoJQyW5ZDysbBEcjlbHzooFT1TyAxDMKUSbzpkHMZRKUSAPV
GRHWcBKBpBNk7Q3EIHxE41XEg8wC80hOVYhmSb4MjoWndmwjjo5dDkKuykL16y84IF3NC6DJbYup
Ky77gi9A5XOXoGfaT3HviAViuFmwOSVBtHIfzZDJNsO0kLJWWTNRaXdJ6jwY+lsQn3lJt0K3ncOQ
Ylt52mub3aeY4QtzFxEEtarfJVCi5C7RY55iqVeCFzQHIqWzUelErKrzUHUaoM+Q1mMVD7uC6JB6
sqIbjdsLj3E60Q2TGR4tkh4DI1GIl7DwVyOVm7n7cY2NHe2222zVrZ1HsrorBAaMyGJtG/WBSni9
SfUdTqcF5rqFTx+0cRcOGGN5F1YC4DAfiSiiJkIYixAXQg68N20VYTEMy86LqcTmPKK0FixMT3GS
3bhmJhKtkts68eRYzY7fafqt+eXmJTEPGPaWemMQ0h50nQ1pXmz8DsAqLDweGRwJUl1p1/TAtJLh
qQHmN6j0ckZKWJz3pjQyPYetG04qAyF4FOoxQGCNxRyHuLjnyEsfJcSaP4u5IpwoSFhyqvmA


--===============7436520852413961479==--