From: Marc Alff
Date: March 10 2011 8:47am
Subject: bzr commit into mysql-5.5 branch (marc.alff:3360)
List-Archive: http://lists.mysql.com/commits/132738
Message-Id: <201103100848.p2A73hKD011610@acsinet15.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///Users/malff/BZR_TREE/mysql-5.5-bug11766528/ based on revid:marc.alff@stripped

 3360 Marc Alff	2011-03-10 [merge]
      Local merge

    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)
   {

No bundle (reason: revision is a merge).