List:Commits« Previous MessageNext Message »
From:marc.alff Date:December 4 2007 3:06am
Subject:bk commit into 5.1 tree (malff:1.2671) BUG#12093
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of malff. When malff does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-12-03 20:06:04-07:00, malff@stripped. +34 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  TEMPORARY PATCH FOR INTERNAL DISCUSSION
  
  This patch is the 4th iteration for this fix.
  Remaining issues:
  - Re validation of views under LOCK_open / view definition cache
  
  The following bugs are fixed and tested:
  - Bug 12093 (SP not found on second PS execution if another thread drops other
    SP in between)
  - Bug 21294 (executing a prepared statement that executes a stored function
    which was recreat)
  - Bug 27420 (A combination of PS and view operations cause error + assertion
    on shutdown)
  - Bug 27430 Crash in subquery code when in PS and table DDL changed after
    PREPARE()
  - Bug 27690 (Re-execution of prepared statement after table was replaced with
    a view crashes)

  libmysqld/Makefile.am@stripped, 2007-12-03 20:05:48-07:00, malff@stripped. +1 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/r/ps.result@stripped, 2007-12-03 20:05:48-07:00, malff@stripped. +14 -27
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/r/ps_1general.result@stripped, 2007-12-03 20:05:48-07:00, malff@stripped. +5 -9
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/r/ps_ddl.result@stripped, 2007-12-03 20:05:58-07:00, malff@stripped. +1307 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION
    

  mysql-test/r/ps_ddl.result@stripped, 2007-12-03 20:05:58-07:00, malff@stripped. +0 -0

  mysql-test/r/sp-error.result@stripped, 2007-12-03 20:05:48-07:00, malff@stripped. +2 -2
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/r/trigger.result@stripped, 2007-12-03 20:05:48-07:00, malff@stripped. +1 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/ps.test@stripped, 2007-12-03 20:05:48-07:00, malff@stripped. +22 -37
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/ps_1general.test@stripped, 2007-12-03 20:05:49-07:00, malff@stripped. +12 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/ps_ddl.test@stripped, 2007-12-03 20:05:58-07:00, malff@stripped. +1593 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION
    

  mysql-test/t/ps_ddl.test@stripped, 2007-12-03 20:05:58-07:00, malff@stripped. +0 -0

  mysql-test/t/sp-error.test@stripped, 2007-12-03 20:05:49-07:00, malff@stripped. +2 -9
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/sp.test@stripped, 2007-12-03 20:05:49-07:00, malff@stripped. +4 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/trigger.test@stripped, 2007-12-03 20:05:49-07:00, malff@stripped. +1 -4
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/Makefile.am@stripped, 2007-12-03 20:05:49-07:00, malff@stripped. +2 -2
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/bostream.cc@stripped, 2007-12-03 20:05:58-07:00, malff@stripped. +118 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION
    

  sql/bostream.cc@stripped, 2007-12-03 20:05:58-07:00, malff@stripped. +0 -0

  sql/bostream.h@stripped, 2007-12-03 20:05:58-07:00, malff@stripped. +58 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION
    

  sql/bostream.h@stripped, 2007-12-03 20:05:58-07:00, malff@stripped. +0 -0

  sql/item.h@stripped, 2007-12-03 20:05:49-07:00, malff@stripped. +0 -12
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/mysql_priv.h@stripped, 2007-12-03 20:05:49-07:00, malff@stripped. +1 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/parse_file.cc@stripped, 2007-12-03 20:05:50-07:00, malff@stripped. +1 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/share/errmsg.txt@stripped, 2007-12-03 20:05:57-07:00, malff@stripped. +7 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sp.cc@stripped, 2007-12-03 20:05:50-07:00, malff@stripped. +168 -50
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sp.h@stripped, 2007-12-03 20:05:50-07:00, malff@stripped. +1 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sp_head.cc@stripped, 2007-12-03 20:05:50-07:00, malff@stripped. +64 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sp_head.h@stripped, 2007-12-03 20:05:55-07:00, malff@stripped. +8 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_base.cc@stripped, 2007-12-03 20:05:56-07:00, malff@stripped. +53 -14
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_class.cc@stripped, 2007-12-03 20:05:56-07:00, malff@stripped. +323 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_class.h@stripped, 2007-12-03 20:05:56-07:00, malff@stripped. +252 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_prepare.cc@stripped, 2007-12-03 20:05:56-07:00, malff@stripped. +498 -14
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_trigger.cc@stripped, 2007-12-03 20:05:57-07:00, malff@stripped. +48 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_trigger.h@stripped, 2007-12-03 20:05:57-07:00, malff@stripped. +11 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_view.cc@stripped, 2007-12-03 20:05:57-07:00, malff@stripped. +51 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_view.h@stripped, 2007-12-03 20:05:57-07:00, malff@stripped. +2 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/table.cc@stripped, 2007-12-03 20:05:57-07:00, malff@stripped. +228 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/table.h@stripped, 2007-12-03 20:05:57-07:00, malff@stripped. +9 -9
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  tests/mysql_client_test.c@stripped, 2007-12-03 20:05:58-07:00, malff@stripped. +68 -14
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

diff -Nrup a/libmysqld/Makefile.am b/libmysqld/Makefile.am
--- a/libmysqld/Makefile.am	2007-08-30 17:23:07 -06:00
+++ b/libmysqld/Makefile.am	2007-12-03 20:05:48 -07:00
@@ -76,7 +76,7 @@ sqlsources = derror.cc field.cc field_co
 	rpl_filter.cc sql_partition.cc sql_builtin.cc sql_plugin.cc \
 	sql_tablespace.cc \
 	rpl_injector.cc my_user.c partition_info.cc \
-	sql_servers.cc
+	sql_servers.cc bostream.cc
 
 libmysqld_int_a_SOURCES= $(libmysqld_sources)
 nodist_libmysqld_int_a_SOURCES= $(libmysqlsources) $(sqlsources)
diff -Nrup a/mysql-test/r/ps.result b/mysql-test/r/ps.result
--- a/mysql-test/r/ps.result	2007-11-02 05:47:15 -06:00
+++ b/mysql-test/r/ps.result	2007-12-03 20:05:48 -07:00
@@ -491,12 +491,15 @@ prepare stmt from "delete t1 from t1 whe
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 drop temporary table t1;
 deallocate prepare stmt;
 create table t1 (a varchar(20));
@@ -905,20 +908,6 @@ show status like 'prepared_stmt_count';
 Variable_name	Value
 Prepared_stmt_count	0
 set global max_prepared_stmt_count= @old_max_prepared_stmt_count;
-drop table if exists t1;
-create temporary table if not exists t1 (a1 int);
-prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-execute stmt;
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-execute stmt;
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-execute stmt;
-drop temporary table t1;
-deallocate prepare stmt;
 CREATE TABLE t1(
 ID int(10) unsigned NOT NULL auto_increment,
 Member_ID varchar(15) NOT NULL default '',
@@ -1452,15 +1441,6 @@ prepare stmt_optimize from "optimize tab
 prepare stmt_show from "show tables like 't1'";
 prepare stmt_truncate from "truncate table t1";
 prepare stmt_drop from "drop table t1";
-drop table t1;
-use test;
-execute stmt_create;
-show tables like 't1';
-Tables_in_test (t1)
-use mysqltest_long_database_name_to_thrash_heap;
-show tables like 't1';
-Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
-t1
 use test;
 execute stmt_insert;
 select * from mysqltest_long_database_name_to_thrash_heap.t1;
@@ -1498,6 +1478,15 @@ Tables_in_test (t1)
 use mysqltest_long_database_name_to_thrash_heap;
 show tables like 't1';
 Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
+use test;
+execute stmt_create;
+show tables like 't1';
+Tables_in_test (t1)
+use mysqltest_long_database_name_to_thrash_heap;
+show tables like 't1';
+Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
+t1
+use mysqltest_long_database_name_to_thrash_heap;
 drop database mysqltest_long_database_name_to_thrash_heap;
 prepare stmt_create from "create table t1 (i int)";
 ERROR 3D000: No database selected
@@ -2648,16 +2637,14 @@ ALTER TABLE t2 ADD COLUMN j INT;
 INSERT INTO t1 VALUES (4, 5);
 INSERT INTO t2 VALUES (4, 5);
 EXECUTE stmt1;
-i
-1
-4
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 EXECUTE stmt2;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t2' has changed
 SELECT * FROM t2;
 i	j
 2	NULL
 3	NULL
 4	5
-3	NULL
 DROP TABLE t1, t2;
 drop table if exists t1;
 Warnings:
diff -Nrup a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
--- a/mysql-test/r/ps_1general.result	2007-09-20 02:56:25 -06:00
+++ b/mysql-test/r/ps_1general.result	2007-12-03 20:05:48 -07:00
@@ -143,8 +143,7 @@ b char(30)
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
 execute stmt2 ;
-a	b	c
-9	recreated table	9
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 create table t5
 (
@@ -155,8 +154,7 @@ d timestamp default current_timestamp
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
 execute stmt2 ;
-a	b	c
-9	recreated table	9
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 create table t5
 (
@@ -167,8 +165,7 @@ c int
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
 execute stmt2 ;
-a	b	c
-9	recreated table	9
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 create table t5
 (
@@ -178,8 +175,7 @@ c int
 );
 insert into t5( b, c) values( 'recreated table', 9);
 execute stmt2 ;
-a	b	c
-2004-02-29 18:01:59	recreated table	9
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 create table t5
 (
@@ -189,7 +185,7 @@ f3 int
 );
 insert into t5( f1, f2, f3) values( 9, 'recreated table', 9);
 execute stmt2 ;
-ERROR 42S22: Unknown column 'test.t5.a' in 'field list'
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 prepare stmt1 from ' select * from t1 where a <= 2 ' ;
 execute stmt1 ;
diff -Nrup a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/r/ps_ddl.result	2007-12-03 20:05:58 -07:00
@@ -0,0 +1,1307 @@
+=====================================================================
+Testing 1: NOTHING -> TABLE transitions
+=====================================================================
+drop table if exists t1;
+prepare stmt from 'select * from t1';
+ERROR 42S02: Table 'test.t1' doesn't exist
+=====================================================================
+Testing 2: NOTHING -> TEMPORARY TABLE transitions
+=====================================================================
+=====================================================================
+Testing 3: NOTHING -> VIEW transitions
+=====================================================================
+=====================================================================
+Testing 4: TABLE -> NOTHING transitions
+=====================================================================
+drop table if exists t4;
+create table t4(a int);
+prepare stmt from 'select * from t4';
+execute stmt;
+a
+execute stmt;
+a
+drop table t4;
+execute stmt;
+ERROR 42S02: Table 'test.t4' doesn't exist
+execute stmt;
+ERROR 42S02: Table 'test.t4' doesn't exist
+=====================================================================
+Testing 5: TABLE -> TABLE (DDL) transitions
+=====================================================================
+drop table if exists t5;
+create table t5(a int);
+prepare stmt from 'select * from t5';
+execute stmt;
+a
+execute stmt;
+a
+alter table t5 add column (b int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
+drop table t5;
+=====================================================================
+Testing 6: TABLE -> TABLE (TRIGGER) transitions
+=====================================================================
+drop table if exists t6;
+create table t6(a int);
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=1;
+execute stmt using @val;
+set @val=2;
+execute stmt using @val;
+create trigger t6_bi before insert on t6 for each row
+begin
+set @message= "t6_bi";
+end
+$$
+set @message="none";
+set @val=3;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because BEFORE INSERT TRIGGER 'test.t6_bi' has changed
+select @message;
+@message
+none
+set @val=4;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because BEFORE INSERT TRIGGER 'test.t6_bi' has changed
+select @message;
+@message
+none
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=5;
+execute stmt using @val;
+select @message;
+@message
+t6_bi
+set @message="none";
+set @val=6;
+execute stmt using @val;
+select @message;
+@message
+t6_bi
+create trigger t6_bd before delete on t6 for each row
+begin
+set @message= "t6_bd";
+end
+$$
+set @message="none";
+set @val=7;
+execute stmt using @val;
+select @message;
+@message
+t6_bi
+set @message="none";
+set @val=8;
+execute stmt using @val;
+select @message;
+@message
+t6_bi
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=9;
+execute stmt using @val;
+select @message;
+@message
+t6_bi
+set @message="none";
+set @val=10;
+execute stmt using @val;
+select @message;
+@message
+t6_bi
+drop trigger t6_bi;
+create trigger t6_bi before insert on t6 for each row
+begin
+set @message= "t6_bi (2)";
+end
+$$
+set @message="none";
+set @val=11;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because BEFORE INSERT TRIGGER 'test.t6_bi' has changed
+select @message;
+@message
+none
+set @val=12;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because BEFORE INSERT TRIGGER 'test.t6_bi' has changed
+select @message;
+@message
+none
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=13;
+execute stmt using @val;
+select @message;
+@message
+t6_bi (2)
+set @message="none";
+set @val=14;
+execute stmt using @val;
+select @message;
+@message
+t6_bi (2)
+drop trigger t6_bd;
+create trigger t6_bd before delete on t6 for each row
+begin
+set @message= "t6_bd (2)";
+end
+$$
+set @message="none";
+set @val=15;
+execute stmt using @val;
+select @message;
+@message
+t6_bi (2)
+set @message="none";
+set @val=16;
+execute stmt using @val;
+select @message;
+@message
+t6_bi (2)
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=17;
+execute stmt using @val;
+select @message;
+@message
+t6_bi (2)
+set @message="none";
+set @val=18;
+execute stmt using @val;
+select @message;
+@message
+t6_bi (2)
+drop trigger t6_bi;
+set @message="none";
+set @val=19;
+execute stmt using @val;
+select @message;
+@message
+none
+set @val=20;
+execute stmt using @val;
+select @message;
+@message
+none
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=21;
+execute stmt using @val;
+select @message;
+@message
+none
+set @val=22;
+execute stmt using @val;
+select @message;
+@message
+none
+drop trigger t6_bd;
+set @val=23;
+execute stmt using @val;
+select @message;
+@message
+none
+set @val=24;
+execute stmt using @val;
+select @message;
+@message
+none
+select * from t6 order by a;
+a
+1
+2
+5
+6
+7
+8
+9
+10
+13
+14
+15
+16
+17
+18
+19
+20
+21
+22
+23
+24
+drop table t6;
+=====================================================================
+Testing 7: TABLE -> TABLE (TRIGGER dependencies) transitions
+=====================================================================
+drop table if exists t7_proc;
+drop table if exists t7_func;
+drop table if exists t7_view;
+drop table if exists t7_table;
+drop table if exists t7_dependent_table;
+drop table if exists t7_table_trigger;
+drop table if exists t7_audit;
+drop procedure if exists audit_proc;
+drop function if exists audit_func;
+drop view if exists audit_view;
+create table t7_proc(a int);
+create table t7_func(a int);
+create table t7_view(a int);
+create table t7_table(a int);
+create table t7_table_trigger(a int);
+create table t7_audit(old_a int, new_a int, reason varchar(50));
+create table t7_dependent_table(old_a int, new_a int, reason varchar(50));
+create procedure audit_proc(a int)
+insert into t7_audit values (NULL, a, "proc v1");
+create function audit_func() returns varchar(50)
+return "func v1";
+create view audit_view as select "view v1" as reason from dual;
+create trigger t7_proc_bi before insert on t7_proc for each row
+call audit_proc(NEW.a);
+create trigger t7_func_bi before insert on t7_func for each row
+insert into t7_audit values (NULL, NEW.a, audit_func());
+create trigger t7_view_bi before insert on t7_view for each row
+insert into t7_audit values (NULL, NEW.a, (select reason from audit_view));
+create trigger t7_table_bi before insert on t7_table for each row
+insert into t7_dependent_table values (NULL, NEW.a, "dependent table");
+create trigger t7_table_trigger_bi before insert on t7_dependent_table
+for each row set @nested="trigger v1";
+prepare stmt_proc from 'insert into t7_proc(a) value (?)';
+set @val=101;
+execute stmt_proc using @val;
+set @val=102;
+execute stmt_proc using @val;
+drop procedure audit_proc;
+create procedure audit_proc(a int)
+insert into t7_audit values (NULL, a, "proc v2");
+set @val=103;
+execute stmt_proc using @val;
+ERROR HY000: Prepared statement has been invalidated, because PROCEDURE 'test.audit_proc' has changed
+set @val=104;
+execute stmt_proc using @val;
+ERROR HY000: Prepared statement has been invalidated, because PROCEDURE 'test.audit_proc' has changed
+prepare stmt_func from 'insert into t7_func(a) value (?)';
+set @val=201;
+execute stmt_func using @val;
+set @val=202;
+execute stmt_func using @val;
+drop function audit_func;
+create function audit_func() returns varchar(50)
+return "func v2";
+set @val=203;
+execute stmt_func using @val;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.audit_func' has changed
+set @val=204;
+execute stmt_func using @val;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.audit_func' has changed
+prepare stmt_view from 'insert into t7_view(a) value (?)';
+set @val=301;
+execute stmt_view using @val;
+set @val=302;
+execute stmt_view using @val;
+drop view audit_view;
+create view audit_view as select "view v2" as reason from dual;
+set @val=303;
+execute stmt_view using @val;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.audit_view' has changed
+set @val=304;
+execute stmt_view using @val;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.audit_view' has changed
+prepare stmt_table from 'insert into t7_table(a) value (?)';
+set @val=401;
+execute stmt_table using @val;
+set @val=402;
+execute stmt_table using @val;
+alter table t7_dependent_table add column comments varchar(100) default NULL;
+set @val=403;
+execute stmt_table using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t7_dependent_table' has changed
+set @val=404;
+execute stmt_table using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t7_dependent_table' has changed
+alter table t7_dependent_table drop column comments;
+prepare stmt_table_trigger from 'insert into t7_table(a) value (?)';
+set @val=501;
+execute stmt_table_trigger using @val;
+select @nested;
+@nested
+trigger v1
+set @val=502;
+execute stmt_table_trigger using @val;
+select @nested;
+@nested
+trigger v1
+drop trigger t7_table_trigger_bi;
+create trigger t7_table_trigger_bi before insert on t7_dependent_table
+for each row set @nested="trigger v2";
+set @nested="not executed";
+set @val=503;
+execute stmt_table_trigger using @val;
+ERROR HY000: Prepared statement has been invalidated, because BEFORE INSERT TRIGGER 'test.t7_table_trigger_bi' has changed
+select @nested;
+@nested
+not executed
+set @val=504;
+execute stmt_table_trigger using @val;
+ERROR HY000: Prepared statement has been invalidated, because BEFORE INSERT TRIGGER 'test.t7_table_trigger_bi' has changed
+select @nested;
+@nested
+not executed
+select * from t7_audit order by new_a;
+old_a	new_a	reason
+NULL	101	proc v1
+NULL	102	proc v1
+NULL	201	func v1
+NULL	202	func v1
+NULL	301	view v1
+NULL	302	view v1
+drop table t7_proc;
+drop table t7_func;
+drop table t7_view;
+drop table t7_table;
+drop table t7_dependent_table;
+drop table t7_table_trigger;
+drop table t7_audit;
+drop procedure audit_proc;
+drop function audit_func;
+drop view audit_view;
+=====================================================================
+Testing 8: TABLE -> TEMPORARY TABLE transitions
+=====================================================================
+drop table if exists t8;
+create table t8(a int);
+prepare stmt from 'select * from t8';
+execute stmt;
+a
+execute stmt;
+a
+drop table t8;
+create temporary table t8(a int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t8' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t8' has changed
+drop table t8;
+=====================================================================
+Testing 9: TABLE -> VIEW transitions
+=====================================================================
+drop table if exists t9;
+drop table if exists t9_b;
+create table t9(a int);
+create table t9_b(a int);
+prepare stmt from 'select * from t9';
+execute stmt;
+a
+execute stmt;
+a
+drop table t9;
+create view t9 as select * from t9_b;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t9' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t9' has changed
+drop view t9;
+drop table t9_b;
+=====================================================================
+Testing 10: TEMPORARY TABLE -> NOTHING transitions
+=====================================================================
+drop temporary table if exists t10;
+create temporary table t10(a int);
+prepare stmt from 'select * from t10';
+execute stmt;
+a
+execute stmt;
+a
+drop temporary table t10;
+execute stmt;
+ERROR 42S02: Table 'test.t10' doesn't exist
+execute stmt;
+ERROR 42S02: Table 'test.t10' doesn't exist
+=====================================================================
+Testing 11: TEMPORARY TABLE -> TABLE transitions
+=====================================================================
+drop table if exists t11;
+drop temporary table if exists t11;
+create table t11(a int);
+insert into t11(a) value (1);
+create temporary table t11(a int);
+prepare stmt from 'select * from t11';
+execute stmt;
+a
+execute stmt;
+a
+drop temporary table t11;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t11' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t11' has changed
+select * from t11;
+a
+1
+drop table t11;
+=====================================================================
+Testing 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions
+=====================================================================
+drop temporary table if exists t12;
+create temporary table t12(a int);
+prepare stmt from 'select * from t12';
+execute stmt;
+a
+execute stmt;
+a
+drop temporary table t12;
+create temporary table t12(a int, b int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t12' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t12' has changed
+select * from t12;
+a	b
+drop table t12;
+=====================================================================
+Testing 13: TEMPORARY TABLE -> VIEW transitions
+=====================================================================
+drop temporary table if exists t13;
+drop table if exists t13_b;
+create temporary table t13(a int);
+create table t13_b(a int);
+prepare stmt from 'select * from t13';
+execute stmt;
+a
+execute stmt;
+a
+drop temporary table t13;
+create view t13 as select * from t13_b;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t13' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t13' has changed
+drop view t13;
+drop table t13_b;
+=====================================================================
+Testing 14: VIEW -> NOTHING transitions
+=====================================================================
+drop view if exists t14;
+drop table if exists t14_b;
+create table t14_b(a int);
+create view t14 as select * from t14_b;
+prepare stmt from 'select * from t14';
+execute stmt;
+a
+execute stmt;
+a
+drop view t14;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t14' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t14' has changed
+drop table t14_b;
+=====================================================================
+Testing 15: VIEW -> TABLE transitions
+=====================================================================
+drop view if exists t15;
+drop table if exists t15_b;
+create table t15_b(a int);
+create view t15 as select * from t15_b;
+prepare stmt from 'select * from t15';
+execute stmt;
+a
+execute stmt;
+a
+drop view t15;
+create table t15(a int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t15' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t15' has changed
+drop table t15_b;
+drop table t15;
+=====================================================================
+Testing 16: VIEW -> TEMPORARY TABLE transitions
+=====================================================================
+drop view if exists t16;
+drop table if exists t16_b;
+create table t16_b(a int);
+create view t16 as select * from t16_b;
+prepare stmt from 'select * from t16';
+execute stmt;
+a
+execute stmt;
+a
+drop view t16;
+create temporary table t16(a int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t16' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t16' has changed
+drop table t16_b;
+drop temporary table t16;
+=====================================================================
+Testing 17: VIEW -> VIEW (DDL) transitions
+=====================================================================
+drop view if exists t17;
+drop table if exists t17_b;
+create table t17_b(a int);
+insert into t17_b values (10), (20), (30);
+create view t17 as select a, 2*a as b, 3*a as c from t17_b;
+select * from t17;
+a	b	c
+10	20	30
+20	40	60
+30	60	90
+prepare stmt from 'select * from t17';
+execute stmt;
+a	b	c
+10	20	30
+20	40	60
+30	60	90
+execute stmt;
+a	b	c
+10	20	30
+20	40	60
+30	60	90
+drop view t17;
+create view t17 as select a, 2*a as b, 10*a as c from t17_b;
+select * from t17;
+a	b	c
+10	20	100
+20	40	200
+30	60	300
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t17' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t17' has changed
+drop table t17_b;
+drop view t17;
+=====================================================================
+Testing 18: VIEW -> VIEW (VIEW dependencies) transitions
+=====================================================================
+drop table if exists t18;
+drop table if exists t18_dependent_table;
+drop view if exists t18_func;
+drop view if exists t18_view;
+drop view if exists t18_table;
+drop function if exists view_func;
+drop view if exists view_view;
+create table t18(a int);
+insert into t18 values (1), (2), (3);
+create function view_func(x int) returns int
+return x+1;
+create view view_view as select "view v1" as reason from dual;
+create table t18_dependent_table(a int);
+create view t18_func as select a, view_func(a) as b from t18;
+create view t18_view as select a, reason as b from t18, view_view;
+create view t18_table as select * from t18;
+prepare stmt_func from 'select * from t18_func';
+execute stmt_func;
+a	b
+1	2
+2	3
+3	4
+execute stmt_func;
+a	b
+1	2
+2	3
+3	4
+drop function view_func;
+create function view_func(x int) returns int
+return x*x;
+execute stmt_func;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.view_func' has changed
+execute stmt_func;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.view_func' has changed
+prepare stmt_view from 'select * from t18_view';
+execute stmt_view;
+a	b
+1	view v1
+2	view v1
+3	view v1
+execute stmt_view;
+a	b
+1	view v1
+2	view v1
+3	view v1
+drop view view_view;
+create view view_view as select "view v2" as reason from dual;
+execute stmt_view;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.view_view' has changed
+execute stmt_view;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.view_view' has changed
+prepare stmt_table from 'select * from t18_table';
+execute stmt_table;
+a
+1
+2
+3
+execute stmt_table;
+a
+1
+2
+3
+alter table t18 add column comments varchar(50) default NULL;
+execute stmt_table;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t18' has changed
+execute stmt_table;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t18' has changed
+drop table t18;
+drop table t18_dependent_table;
+drop view t18_func;
+drop view t18_view;
+drop view t18_table;
+drop function view_func;
+drop view view_view;
+=====================================================================
+Testing 19: Special tables (INFORMATION_SCHEMA)
+=====================================================================
+drop procedure if exists proc_19;
+prepare stmt from
+'select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
+ from INFORMATION_SCHEMA.ROUTINES where
+ routine_name=\'proc_19\'';
+create procedure proc_19() select "hi there";
+execute stmt;
+ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE
+test	proc_19	PROCEDURE
+execute stmt;
+ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE
+test	proc_19	PROCEDURE
+drop procedure proc_19;
+create procedure proc_19() select "hi there, again";
+execute stmt;
+ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE
+test	proc_19	PROCEDURE
+execute stmt;
+ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE
+test	proc_19	PROCEDURE
+drop procedure proc_19;
+=====================================================================
+Testing 20: Special tables (log tables)
+=====================================================================
+prepare stmt from
+'select * from mysql.general_log where argument=\'IMPOSSIBLE QUERY STRING\'';
+execute stmt;
+event_time	user_host	thread_id	server_id	command_type	argument
+execute stmt;
+event_time	user_host	thread_id	server_id	command_type	argument
+execute stmt;
+event_time	user_host	thread_id	server_id	command_type	argument
+execute stmt;
+event_time	user_host	thread_id	server_id	command_type	argument
+=====================================================================
+Testing 21: Special tables (system tables)
+=====================================================================
+drop procedure if exists proc_21;
+prepare stmt from
+'select type, db, name from mysql.proc where name=\'proc_21\'';
+create procedure proc_21() select "hi there";
+execute stmt;
+type	db	name
+PROCEDURE	test	proc_21
+execute stmt;
+type	db	name
+PROCEDURE	test	proc_21
+drop procedure proc_21;
+create procedure proc_21() select "hi there, again";
+execute stmt;
+type	db	name
+PROCEDURE	test	proc_21
+execute stmt;
+type	db	name
+PROCEDURE	test	proc_21
+drop procedure proc_21;
+=====================================================================
+Testing 22: Special tables (views temp tables)
+=====================================================================
+drop table if exists t22_b;
+drop view if exists t22;
+create table t22_b(a int);
+create algorithm=temptable view t22 as select a*a as a2 from t22_b;
+show create view t22;
+View	Create View	character_set_client	collation_connection
+t22	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t22` AS select (`t22_b`.`a` * `t22_b`.`a`) AS `a2` from `t22_b`	latin1	latin1_swedish_ci
+prepare stmt from 'select * from t22';
+insert into t22_b values (1), (2), (3);
+execute stmt;
+a2
+1
+4
+9
+execute stmt;
+a2
+1
+4
+9
+insert into t22_b values (4), (5), (6);
+execute stmt;
+a2
+1
+4
+9
+16
+25
+36
+execute stmt;
+a2
+1
+4
+9
+16
+25
+36
+drop table t22_b;
+drop view t22;
+=====================================================================
+Testing 23: Special tables (internal join tables)
+=====================================================================
+drop table if exists t23_a;
+drop table if exists t23_b;
+create table t23_a(a int);
+create table t23_b(b int);
+prepare stmt from 'select * from t23_a join t23_b';
+insert into t23_a values (1), (2), (3);
+insert into t23_b values (10), (20), (30);
+execute stmt;
+a	b
+1	10
+2	10
+3	10
+1	20
+2	20
+3	20
+1	30
+2	30
+3	30
+execute stmt;
+a	b
+1	10
+2	10
+3	10
+1	20
+2	20
+3	20
+1	30
+2	30
+3	30
+insert into t23_a values (4);
+insert into t23_b values (40);
+execute stmt;
+a	b
+1	10
+2	10
+3	10
+4	10
+1	20
+2	20
+3	20
+4	20
+1	30
+2	30
+3	30
+4	30
+1	40
+2	40
+3	40
+4	40
+execute stmt;
+a	b
+1	10
+2	10
+3	10
+4	10
+1	20
+2	20
+3	20
+4	20
+1	30
+2	30
+3	30
+4	30
+1	40
+2	40
+3	40
+4	40
+drop table t23_a;
+drop table t23_b;
+=====================================================================
+Testing 24: Special statements
+=====================================================================
+drop table if exists t24_alter;
+create table t24_alter(a int);
+prepare stmt from 'alter table t24_alter add column b int';
+execute stmt;
+drop table t24_alter;
+create table t24_alter(a1 int, a2 int);
+execute stmt;
+alter table t24_alter drop column b;
+execute stmt;
+alter table t24_alter drop column b;
+execute stmt;
+drop table t24_alter;
+drop table if exists t24_repair;
+create table t24_repair(a int);
+insert into t24_repair values (1), (2), (3);
+prepare stmt from 'repair table t24_repair';
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_repair	repair	status	OK
+drop table t24_repair;
+create table t24_repair(a1 int, a2 int);
+insert into t24_repair values (1, 10), (2, 20), (3, 30);
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_repair	repair	status	OK
+alter table t24_repair add column b varchar(50) default NULL;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_repair	repair	status	OK
+alter table t24_repair drop column b;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_repair	repair	status	OK
+drop table t24_repair;
+drop table if exists t24_analyze;
+create table t24_analyze(a int);
+insert into t24_analyze values (1), (2), (3);
+prepare stmt from 'analyze table t24_analyze';
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_analyze	analyze	status	OK
+drop table t24_analyze;
+create table t24_analyze(a1 int, a2 int);
+insert into t24_analyze values (1, 10), (2, 20), (3, 30);
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_analyze	analyze	status	OK
+alter table t24_analyze add column b varchar(50) default NULL;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_analyze	analyze	status	OK
+alter table t24_analyze drop column b;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_analyze	analyze	status	OK
+drop table t24_analyze;
+drop table if exists t24_optimize;
+create table t24_optimize(a int);
+insert into t24_optimize values (1), (2), (3);
+prepare stmt from 'optimize table t24_optimize';
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_optimize	optimize	status	OK
+drop table t24_optimize;
+create table t24_optimize(a1 int, a2 int);
+insert into t24_optimize values (1, 10), (2, 20), (3, 30);
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_optimize	optimize	status	OK
+alter table t24_optimize add column b varchar(50) default NULL;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_optimize	optimize	status	OK
+alter table t24_optimize drop column b;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_optimize	optimize	status	OK
+drop table t24_optimize;
+drop procedure if exists changing_proc;
+prepare stmt from 'show create procedure changing_proc';
+execute stmt;
+ERROR 42000: PROCEDURE changing_proc does not exist
+execute stmt;
+ERROR 42000: PROCEDURE changing_proc does not exist
+create procedure changing_proc() begin end;
+execute stmt;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
+changing_proc		CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`()
+begin end	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
+changing_proc		CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`()
+begin end	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop procedure changing_proc;
+create procedure changing_proc(x int, y int) begin end;
+execute stmt;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
+changing_proc		CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`(x int, y int)
+begin end	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
+changing_proc		CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`(x int, y int)
+begin end	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop procedure changing_proc;
+execute stmt;
+ERROR 42000: PROCEDURE changing_proc does not exist
+execute stmt;
+ERROR 42000: PROCEDURE changing_proc does not exist
+drop function if exists changing_func;
+prepare stmt from 'show create function changing_func';
+execute stmt;
+ERROR 42000: FUNCTION changing_func does not exist
+execute stmt;
+ERROR 42000: FUNCTION changing_func does not exist
+create function changing_func() returns int return 0;
+execute stmt;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+changing_func		CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`() RETURNS int(11)
+return 0	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+changing_func		CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`() RETURNS int(11)
+return 0	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop function changing_func;
+create function changing_func(x int, y int) returns int return x+y;
+execute stmt;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+changing_func		CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`(x int, y int) RETURNS int(11)
+return x+y	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+changing_func		CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`(x int, y int) RETURNS int(11)
+return x+y	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop function changing_func;
+execute stmt;
+ERROR 42000: FUNCTION changing_func does not exist
+execute stmt;
+ERROR 42000: FUNCTION changing_func does not exist
+drop table if exists t24_trigger;
+create table t24_trigger(a int);
+prepare stmt from 'show create trigger t24_bi;';
+execute stmt;
+ERROR HY000: Trigger does not exist
+execute stmt;
+ERROR HY000: Trigger does not exist
+create trigger t24_bi before insert on t24_trigger for each row
+begin
+set @message= "t24_bi";
+end
+$$
+execute stmt;
+Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
+t24_bi		CREATE DEFINER=`root`@`localhost` trigger t24_bi before insert on t24_trigger for each row
+begin
+set @message= "t24_bi";
+end	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
+t24_bi		CREATE DEFINER=`root`@`localhost` trigger t24_bi before insert on t24_trigger for each row
+begin
+set @message= "t24_bi";
+end	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop trigger t24_bi;
+create trigger t24_bi before insert on t24_trigger for each row
+begin
+set @message= "t24_bi (2)";
+end
+$$
+execute stmt;
+Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
+t24_bi		CREATE DEFINER=`root`@`localhost` trigger t24_bi before insert on t24_trigger for each row
+begin
+set @message= "t24_bi (2)";
+end	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation
+t24_bi		CREATE DEFINER=`root`@`localhost` trigger t24_bi before insert on t24_trigger for each row
+begin
+set @message= "t24_bi (2)";
+end	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop trigger t24_bi;
+execute stmt;
+ERROR HY000: Trigger does not exist
+execute stmt;
+ERROR HY000: Trigger does not exist
+drop table t24_trigger;
+=====================================================================
+Testing 25: Testing the strength of TABLE_SHARE version
+=====================================================================
+drop table if exists t25_num_col;
+create table t25_num_col(a int);
+prepare stmt from 'select * from t25_num_col';
+execute stmt;
+a
+execute stmt;
+a
+alter table t25_num_col add column b varchar(50) default NULL;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_num_col' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_num_col' has changed
+drop table t25_num_col;
+drop table if exists t25_col_name;
+create table t25_col_name(a int);
+prepare stmt from 'select * from t25_col_name';
+execute stmt;
+a
+execute stmt;
+a
+alter table t25_col_name change a b int;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_col_name' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_col_name' has changed
+drop table t25_col_name;
+drop table if exists t25_col_type;
+create table t25_col_type(a int);
+prepare stmt from 'select * from t25_col_type';
+execute stmt;
+a
+execute stmt;
+a
+alter table t25_col_type change a a varchar(10);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_col_type' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_col_type' has changed
+drop table t25_col_type;
+drop table if exists t25_col_type_length;
+create table t25_col_type_length(a varchar(10));
+prepare stmt from 'select * from t25_col_type_length';
+execute stmt;
+a
+execute stmt;
+a
+alter table t25_col_type_length change a a varchar(20);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_col_type_length' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_col_type_length' has changed
+drop table t25_col_type_length;
+drop table if exists t25_col_null;
+create table t25_col_null(a varchar(10));
+prepare stmt from 'select * from t25_col_null';
+execute stmt;
+a
+execute stmt;
+a
+alter table t25_col_null change a a varchar(10) NOT NULL;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_col_null' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_col_null' has changed
+drop table t25_col_null;
+drop table if exists t25_col_default;
+create table t25_col_default(a int, b int DEFAULT 10);
+prepare stmt from 'insert into t25_col_default(a) values (?)';
+set @val=1;
+execute stmt using @val;
+set @val=2;
+execute stmt using @val;
+alter table t25_col_default change b b int DEFAULT 20;
+set @val=3;
+execute stmt using @val;
+set @val=4;
+execute stmt using @val;
+select * from t25_col_default;
+a	b
+1	10
+2	10
+3	20
+4	20
+drop table t25_col_default;
+drop table if exists t25_index;
+create table t25_index(a varchar(10));
+prepare stmt from 'select * from t25_index';
+execute stmt;
+a
+execute stmt;
+a
+create index i1 on t25_index(a);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_index' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_index' has changed
+drop table t25_index;
+drop table if exists t25_index_unique;
+create table t25_index_unique(a varchar(10), b varchar(10));
+create index i1 on t25_index_unique(a, b);
+show create table t25_index_unique;
+Table	Create Table
+t25_index_unique	CREATE TABLE `t25_index_unique` (
+  `a` varchar(10) DEFAULT NULL,
+  `b` varchar(10) DEFAULT NULL,
+  KEY `i1` (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+prepare stmt from 'select * from t25_index_unique';
+execute stmt;
+a	b
+execute stmt;
+a	b
+alter table t25_index_unique drop index i1;
+create unique index i1 on t25_index_unique(a, b);
+show create table t25_index_unique;
+Table	Create Table
+t25_index_unique	CREATE TABLE `t25_index_unique` (
+  `a` varchar(10) DEFAULT NULL,
+  `b` varchar(10) DEFAULT NULL,
+  UNIQUE KEY `i1` (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_index_unique' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t25_index_unique' has changed
+drop table t25_index_unique;
+=====================================================================
+Testing reported bugs
+=====================================================================
+drop table if exists table_12093;
+drop function if exists func_12093;
+drop function if exists func_12093_unrelated;
+drop procedure if exists proc_12093;
+create table table_12093(a int);
+create function func_12093()
+returns int
+begin
+return (select count(*) from table_12093);
+end//
+create procedure proc_12093(a int)
+begin
+select * from table_12093;
+end//
+create function func_12093_unrelated() returns int return 2;
+create procedure proc_12093_unrelated() begin end;
+prepare stmt_sf from 'select func_12093();';
+prepare stmt_sp from 'call proc_12093(func_12093())';
+execute stmt_sf;
+func_12093()
+0
+execute stmt_sp;
+a
+drop function func_12093_unrelated;
+drop procedure proc_12093_unrelated;
+execute stmt_sf;
+func_12093()
+0
+execute stmt_sp;
+a
+execute stmt_sf;
+func_12093()
+0
+execute stmt_sp;
+a
+deallocate prepare stmt_sf;
+deallocate prepare stmt_sp;
+drop table table_12093;
+drop function func_12093;
+drop procedure proc_12093;
+drop function if exists func_21294;
+create function func_21294() returns int return 10;
+prepare stmt from "select func_21294()";
+execute stmt;
+func_21294()
+10
+drop function func_21294;
+create function func_21294() returns int return 10;
+execute stmt;
+func_21294()
+10
+drop function func_21294;
+create function func_21294() returns int return 20;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.func_21294' has changed
+deallocate prepare stmt;
+drop function func_21294;
+drop table if exists t_27420_100;
+drop table if exists t_27420_101;
+drop view if exists v_27420;
+create table t_27420_100(a int);
+insert into t_27420_100 values (1), (2);
+create table t_27420_101(a int);
+insert into t_27420_101 values (1), (2);
+create view v_27420 as select t_27420_100.a X, t_27420_101.a Y
+from t_27420_100, t_27420_101
+where t_27420_100.a=t_27420_101.a;
+prepare stmt from 'select * from v_27420';
+execute stmt;
+X	Y
+1	1
+2	2
+drop view v_27420;
+create table v_27420(X int, Y int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.v_27420' has changed
+drop table v_27420;
+create table v_27420 (a int, b int, filler char(200));
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.v_27420' has changed
+deallocate prepare stmt;
+drop table t_27420_100;
+drop table t_27420_101;
+drop table v_27420;
+drop table if exists t_27430_1;
+drop table if exists t_27430_2;
+create table t_27430_1 (a int not null, oref int not null, key(a));
+insert into t_27430_1 values
+(1, 1),
+(1, 1234),
+(2, 3),
+(2, 1234),
+(3, 1234);
+create table t_27430_2 (a int not null, oref int not null);
+insert into t_27430_2 values
+(1, 1),
+(2, 2),
+(1234, 3),
+(1234, 4);
+prepare stmt from 
+'select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2';
+execute stmt;
+oref	a	Z
+1	1	1
+2	2	0
+3	1234	0
+4	1234	0
+drop table t_27430_1, t_27430_2;
+create table t_27430_1 (a int, oref int, key(a));
+insert into t_27430_1 values 
+(1, 1),
+(1, NULL),
+(2, 3),
+(2, NULL),
+(3, NULL);
+create table t_27430_2 (a int, oref int);
+insert into t_27430_2 values
+(1, 1),
+(2,2),
+(NULL, 3),
+(NULL, 4);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t_27430_2' has changed
+deallocate prepare stmt;
+drop table t_27430_1;
+drop table t_27430_2;
+drop table if exists t_27690_1;
+drop view if exists v_27690_1;
+drop table if exists v_27690_2;
+create table t_27690_1 (a int, b int);
+insert into t_27690_1 values (1,1),(2,2);
+create table v_27690_1 as select * from t_27690_1;
+create table v_27690_2 as select * from t_27690_1;
+prepare stmt from 'select * from v_27690_1, v_27690_2';
+execute stmt;
+a	b	a	b
+1	1	1	1
+2	2	1	1
+1	1	2	2
+2	2	2	2
+execute stmt;
+a	b	a	b
+1	1	1	1
+2	2	1	1
+1	1	2	2
+2	2	2	2
+drop table v_27690_1;
+execute stmt;
+ERROR 42S02: Table 'test.v_27690_1' doesn't exist
+execute stmt;
+ERROR 42S02: Table 'test.v_27690_1' doesn't exist
+create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.v_27690_1' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.v_27690_1' has changed
+deallocate prepare stmt;
+drop table t_27690_1;
+drop view v_27690_1;
+drop table v_27690_2;
diff -Nrup a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
--- a/mysql-test/r/sp-error.result	2007-11-01 14:52:50 -06:00
+++ b/mysql-test/r/sp-error.result	2007-12-03 20:05:48 -07:00
@@ -796,7 +796,7 @@ bug11834_2()
 10
 drop function bug11834_1;
 execute stmt;
-ERROR 42000: FUNCTION test.bug11834_2 does not exist
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.bug11834_1' has changed
 deallocate prepare stmt;
 drop function bug11834_2;
 DROP FUNCTION IF EXISTS bug12953|
@@ -1045,7 +1045,7 @@ select bug12329();
 bug12329()
 101
 execute stmt1;
-ERROR 42S02: Table 'test.t2' doesn't exist
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.bug12329' has changed
 deallocate prepare stmt1;
 drop function bug12329;
 drop table t1, t2;
diff -Nrup a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
--- a/mysql-test/r/trigger.result	2007-11-16 10:05:28 -07:00
+++ b/mysql-test/r/trigger.result	2007-12-03 20:05:48 -07:00
@@ -820,7 +820,7 @@ 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;
-ERROR 42S02: Table 'test.t3' doesn't exist
+ERROR HY000: Prepared statement has been invalidated, because AFTER INSERT TRIGGER 'test.t1_bi' has changed
 call p1();
 ERROR 42S02: Table 'test.t3' doesn't exist
 deallocate prepare stmt1;
diff -Nrup a/mysql-test/t/ps.test b/mysql-test/t/ps.test
--- a/mysql-test/t/ps.test	2007-11-02 05:47:15 -06:00
+++ b/mysql-test/t/ps.test	2007-12-03 20:05:48 -07:00
@@ -497,6 +497,7 @@ drop table t1, t2;
 # Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating
 #            tables"
 # Check that multi-delete tables are also cleaned up before re-execution.
+# Update: can not be tested anymore due to ER_PS_INVALIDATED
 # 
 --disable_warnings
 drop table if exists t1;
@@ -507,13 +508,16 @@ prepare stmt from "delete t1 from t1 whe
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 # the server crashed on  the next statement without the fix
+--error ER_PS_INVALIDATED
 execute stmt;
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 # the problem was in memory corruption: repeat the test just in case
+--error ER_PS_INVALIDATED
 execute stmt;
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
+--error ER_PS_INVALIDATED
 execute stmt;
 drop temporary table t1;
 deallocate prepare stmt;
@@ -970,33 +974,6 @@ show status like 'prepared_stmt_count';
 set global max_prepared_stmt_count= @old_max_prepared_stmt_count;
 --enable_ps_protocol
 
-
-#
-# Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating
-#            tables"
-# Check that multi-delete tables are also cleaned up before re-execution.
-# 
---disable_warnings
-drop table if exists t1;
-create temporary table if not exists t1 (a1 int);
---enable_warnings
-# exact delete syntax is essential
-prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-# the server crashed on  the next statement without the fix
-execute stmt;
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-# the problem was in memory corruption: repeat the test just in case
-execute stmt;
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-execute stmt;
-drop temporary table t1;
-deallocate prepare stmt;
-
-
 #
 # BUG#22085: Crash on the execution of a prepared statement that
 #            uses an IN subquery with aggregate functions in HAVING 
@@ -1493,19 +1470,15 @@ prepare stmt_optimize from "optimize tab
 prepare stmt_show from "show tables like 't1'";
 prepare stmt_truncate from "truncate table t1";
 prepare stmt_drop from "drop table t1";
-# Drop the table that was used to prepare INSERT/UPDATE/DELETE: we will
-# create a new one by executing stmt_create
-drop table t1;
-# Switch the current database
-use test;
+
 # Check that all prepared statements operate on the database that was
 # active at PREPARE
-execute stmt_create;
-# should return empty set
-show tables like 't1';
-use mysqltest_long_database_name_to_thrash_heap;
-show tables like 't1';
+
+# Switch the current database
 use test;
+
+# We can't test stmt_create now,
+# since this would cause ER_PS_INVALIDATED errors
 execute stmt_insert;
 select * from mysqltest_long_database_name_to_thrash_heap.t1;
 execute stmt_update;
@@ -1524,10 +1497,20 @@ execute stmt_drop;
 show tables like 't1';
 use mysqltest_long_database_name_to_thrash_heap;
 show tables like 't1';
+
+# Switch the current database
+use test;
+execute stmt_create;
+# should return empty set
+show tables like 't1';
+use mysqltest_long_database_name_to_thrash_heap;
+show tables like 't1';
+
 #
 # Attempt a statement PREPARE when there is no current database:
 # is expected to return an error.
 #
+use mysqltest_long_database_name_to_thrash_heap;
 drop database mysqltest_long_database_name_to_thrash_heap;
 --error ER_NO_DB_ERROR
 prepare stmt_create from "create table t1 (i int)";
@@ -2751,7 +2734,9 @@ INSERT INTO t2 VALUES (4, 5);
 connection conn1;
 
 reap;
+--error ER_PS_INVALIDATED
 EXECUTE stmt1;
+--error ER_PS_INVALIDATED
 EXECUTE stmt2;
 SELECT * FROM t2;
 
diff -Nrup a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
--- a/mysql-test/t/ps_1general.test	2007-08-15 02:18:40 -06:00
+++ b/mysql-test/t/ps_1general.test	2007-12-03 20:05:49 -07:00
@@ -161,6 +161,8 @@ create table t5
   c int
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
+# table is the same: pass or fail is implementation dependent
+# --error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 2. drop + create table (same column names/types but different order)
@@ -172,6 +174,8 @@ create table t5
   b char(30)
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 3. drop + create table (same column names/types/order+extra column) 
@@ -184,6 +188,8 @@ create table t5
   d timestamp default current_timestamp
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 4. drop + create table (same column names/types, different order +
@@ -196,6 +202,8 @@ create table t5
   c int
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 5. drop + create table (same column names/order, different types)
@@ -207,6 +215,8 @@ create table t5
   c int
 );
 insert into t5( b, c) values( 'recreated table', 9);
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 6. drop + create table (same column types/order, different names) 
@@ -218,7 +228,8 @@ create table t5
   f3 int
 );
 insert into t5( f1, f2, f3) values( 9, 'recreated table', 9);
---error 1054
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 
diff -Nrup a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/t/ps_ddl.test	2007-12-03 20:05:58 -07:00
@@ -0,0 +1,1593 @@
+#
+# Testing the behavior of 'PREPARE', 'DDL', 'EXECUTE' scenarios
+#
+# Background:
+# In a statement like "select * from t1", t1 can be:
+# - nothing (the table does not exist)
+# - a real table
+# - a temporary table
+# - a view
+#
+# Changing the nature of "t1" between a PREPARE and an EXECUTE
+# can invalidate the internal state of a prepared statement, so that,
+# during the execute, the server should:
+# - at the very minimum, detect state changes and fail to execute a
+# statement, instead of crashing the server or returning wrong results
+# - optionally, "RE-PREPARE" the statement to restore a valid internal
+# state.
+#
+# Also, changing the physical structure of "t1", by:
+# - changing the definition of t1 itself (DDL on tables, views)
+# - changing TRIGGERs associated with a table
+# - changing PROCEDURE, FUNCTION referenced by a TRIGGER body,
+# - changing PROCEDURE, FUNCTION referenced by a VIEW body,
+# impacts the internal structure of a prepared statement, and should
+# cause the same verifications at execute time to be performed.
+#
+# This test provided in this file cover the different state transitions
+# between a PREPARE and an EXECUTE, and are organized as follows:
+# - Part  1: NOTHING -> TABLE
+# - Part  2: NOTHING -> TEMPORARY TABLE
+# - Part  3: NOTHING -> VIEW
+# - Part  4: TABLE -> NOTHING
+# - Part  5: TABLE -> TABLE (DDL)
+# - Part  6: TABLE -> TABLE (TRIGGER)
+# - Part  7: TABLE -> TABLE (TRIGGER dependencies)
+# - Part  8: TABLE -> TEMPORARY TABLE
+# - Part  9: TABLE -> VIEW
+# - Part 10: TEMPORARY TABLE -> NOTHING
+# - Part 11: TEMPORARY TABLE -> TABLE
+# - Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL)
+# - Part 13: TEMPORARY TABLE -> VIEW
+# - Part 14: VIEW -> NOTHING
+# - Part 15: VIEW -> TABLE
+# - Part 16: VIEW -> TEMPORARY TABLE
+# - Part 17: VIEW -> VIEW (DDL)
+# - Part 18: VIEW -> VIEW (VIEW dependencies)
+# - Part 19: Special tables (INFORMATION_SCHEMA)
+# - Part 20: Special tables (log tables)
+# - Part 21: Special tables (system tables)
+# - Part 22: Special tables (views temp tables)
+# - Part 23: Special tables (internal join tables)
+# - Part 24: Special statements
+# - Part 25: Testing the strength of TABLE_SHARE version
+
+--echo =====================================================================
+--echo Testing 1: NOTHING -> TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+# can not be tested since prepare failed
+--error ER_NO_SUCH_TABLE
+prepare stmt from 'select * from t1';
+
+--echo =====================================================================
+--echo Testing 2: NOTHING -> TEMPORARY TABLE transitions
+--echo =====================================================================
+
+# can not be tested
+
+--echo =====================================================================
+--echo Testing 3: NOTHING -> VIEW transitions
+--echo =====================================================================
+
+# can not be tested
+
+--echo =====================================================================
+--echo Testing 4: TABLE -> NOTHING transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t4;
+--enable_warnings
+
+create table t4(a int);
+
+prepare stmt from 'select * from t4';
+execute stmt;
+execute stmt;
+
+drop table t4;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+
+--echo =====================================================================
+--echo Testing 5: TABLE -> TABLE (DDL) transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t5;
+--enable_warnings
+
+create table t5(a int);
+
+prepare stmt from 'select * from t5';
+execute stmt;
+execute stmt;
+
+alter table t5 add column (b int);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t5;
+
+--echo =====================================================================
+--echo Testing 6: TABLE -> TABLE (TRIGGER) transitions
+--echo =====================================================================
+
+#
+# Test 6-a: adding a relevant trigger
+# Test 6-b: adding an irrelevant trigger
+# Test 6-c: changing a relevant trigger
+# Test 6-d: changing an irrelevant trigger
+# Test 6-e: removing a relevant trigger
+# Test 6-f: removing an irrelevant trigger
+#
+
+--disable_warnings
+drop table if exists t6;
+--enable_warnings
+
+create table t6(a int);
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=1;
+execute stmt using @val;
+set @val=2;
+execute stmt using @val;
+
+# Relevant trigger: execute should fail
+delimiter $$;
+create trigger t6_bi before insert on t6 for each row
+  begin
+    set @message= "t6_bi";
+  end
+$$
+delimiter ;$$
+
+set @message="none";
+set @val=3;
+--error ER_PS_TRG_INVALIDATED
+execute stmt using @val;
+select @message;
+set @val=4;
+--error ER_PS_TRG_INVALIDATED
+execute stmt using @val;
+select @message;
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=5;
+execute stmt using @val;
+select @message;
+set @message="none";
+set @val=6;
+execute stmt using @val;
+select @message;
+
+# Unrelated trigger: execute can pass of fail, implementation dependent
+delimiter $$;
+create trigger t6_bd before delete on t6 for each row
+  begin
+    set @message= "t6_bd";
+  end
+$$
+delimiter ;$$
+
+set @message="none";
+set @val=7;
+execute stmt using @val;
+select @message;
+set @message="none";
+set @val=8;
+execute stmt using @val;
+select @message;
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=9;
+execute stmt using @val;
+select @message;
+set @message="none";
+set @val=10;
+execute stmt using @val;
+select @message;
+
+# Relevant trigger: execute should fail
+drop trigger t6_bi;
+delimiter $$;
+create trigger t6_bi before insert on t6 for each row
+  begin
+    set @message= "t6_bi (2)";
+  end
+$$
+delimiter ;$$
+
+set @message="none";
+set @val=11;
+--error ER_PS_TRG_INVALIDATED
+execute stmt using @val;
+select @message;
+set @val=12;
+--error ER_PS_TRG_INVALIDATED
+execute stmt using @val;
+select @message;
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=13;
+execute stmt using @val;
+select @message;
+set @message="none";
+set @val=14;
+execute stmt using @val;
+select @message;
+
+# Unrelated trigger: execute can pass of fail, implementation dependent
+drop trigger t6_bd;
+delimiter $$;
+create trigger t6_bd before delete on t6 for each row
+  begin
+    set @message= "t6_bd (2)";
+  end
+$$
+delimiter ;$$
+
+set @message="none";
+set @val=15;
+execute stmt using @val;
+select @message;
+set @message="none";
+set @val=16;
+execute stmt using @val;
+select @message;
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=17;
+execute stmt using @val;
+select @message;
+set @message="none";
+set @val=18;
+execute stmt using @val;
+select @message;
+
+drop trigger t6_bi;
+
+set @message="none";
+set @val=19;
+# safe to re-execute
+execute stmt using @val;
+select @message;
+set @val=20;
+# safe to re-execute
+execute stmt using @val;
+select @message;
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @message="none";
+set @val=21;
+execute stmt using @val;
+select @message;
+set @val=22;
+execute stmt using @val;
+select @message;
+
+drop trigger t6_bd;
+
+set @val=23;
+# safe to re-execute
+execute stmt using @val;
+select @message;
+set @val=24;
+# safe to re-execute
+execute stmt using @val;
+select @message;
+
+select * from t6 order by a;
+drop table t6;
+
+--echo =====================================================================
+--echo Testing 7: TABLE -> TABLE (TRIGGER dependencies) transitions
+--echo =====================================================================
+
+#
+# Test 7-a: dependent PROCEDURE has changed
+# Test 7-b: dependent FUNCTION has changed
+# Test 7-c: dependent VIEW has changed
+# Test 7-d: dependent TABLE has changed
+# Test 7-e: dependent TABLE TRIGGER has changed
+#
+
+--disable_warnings
+drop table if exists t7_proc;
+drop table if exists t7_func;
+drop table if exists t7_view;
+drop table if exists t7_table;
+drop table if exists t7_dependent_table;
+drop table if exists t7_table_trigger;
+drop table if exists t7_audit;
+drop procedure if exists audit_proc;
+drop function if exists audit_func;
+drop view if exists audit_view;
+--enable_warnings
+
+create table t7_proc(a int);
+create table t7_func(a int);
+create table t7_view(a int);
+create table t7_table(a int);
+create table t7_table_trigger(a int);
+
+create table t7_audit(old_a int, new_a int, reason varchar(50));
+create table t7_dependent_table(old_a int, new_a int, reason varchar(50));
+
+create procedure audit_proc(a int)
+  insert into t7_audit values (NULL, a, "proc v1");
+
+create function audit_func() returns varchar(50)
+  return "func v1";
+
+create view audit_view as select "view v1" as reason from dual;
+
+create trigger t7_proc_bi before insert on t7_proc for each row
+  call audit_proc(NEW.a);
+
+create trigger t7_func_bi before insert on t7_func for each row
+  insert into t7_audit values (NULL, NEW.a, audit_func());
+
+create trigger t7_view_bi before insert on t7_view for each row
+  insert into t7_audit values (NULL, NEW.a, (select reason from audit_view));
+
+create trigger t7_table_bi before insert on t7_table for each row
+  insert into t7_dependent_table values (NULL, NEW.a, "dependent table");
+
+create trigger t7_table_trigger_bi before insert on t7_dependent_table
+  for each row set @nested="trigger v1";
+
+prepare stmt_proc from 'insert into t7_proc(a) value (?)';
+set @val=101;
+execute stmt_proc using @val;
+set @val=102;
+execute stmt_proc using @val;
+
+drop procedure audit_proc;
+
+create procedure audit_proc(a int)
+  insert into t7_audit values (NULL, a, "proc v2");
+
+set @val=103;
+--error ER_PS_INVALIDATED
+execute stmt_proc using @val;
+set @val=104;
+--error ER_PS_INVALIDATED
+execute stmt_proc using @val;
+
+
+prepare stmt_func from 'insert into t7_func(a) value (?)';
+set @val=201;
+execute stmt_func using @val;
+set @val=202;
+execute stmt_func using @val;
+
+drop function audit_func;
+
+create function audit_func() returns varchar(50)
+  return "func v2";
+
+set @val=203;
+--error ER_PS_INVALIDATED
+execute stmt_func using @val;
+set @val=204;
+--error ER_PS_INVALIDATED
+execute stmt_func using @val;
+
+prepare stmt_view from 'insert into t7_view(a) value (?)';
+set @val=301;
+execute stmt_view using @val;
+set @val=302;
+execute stmt_view using @val;
+
+drop view audit_view;
+
+create view audit_view as select "view v2" as reason from dual;
+
+set @val=303;
+--error ER_PS_INVALIDATED
+execute stmt_view using @val;
+set @val=304;
+--error ER_PS_INVALIDATED
+execute stmt_view using @val;
+
+
+prepare stmt_table from 'insert into t7_table(a) value (?)';
+set @val=401;
+execute stmt_table using @val;
+set @val=402;
+execute stmt_table using @val;
+
+alter table t7_dependent_table add column comments varchar(100) default NULL;
+
+set @val=403;
+--error ER_PS_INVALIDATED
+execute stmt_table using @val;
+set @val=404;
+--error ER_PS_INVALIDATED
+execute stmt_table using @val;
+
+alter table t7_dependent_table drop column comments;
+
+prepare stmt_table_trigger from 'insert into t7_table(a) value (?)';
+set @val=501;
+execute stmt_table_trigger using @val;
+select @nested;
+set @val=502;
+execute stmt_table_trigger using @val;
+select @nested;
+
+drop trigger t7_table_trigger_bi;
+
+create trigger t7_table_trigger_bi before insert on t7_dependent_table
+  for each row set @nested="trigger v2";
+
+set @nested="not executed";
+
+set @val=503;
+--error ER_PS_TRG_INVALIDATED
+execute stmt_table_trigger using @val;
+select @nested;
+set @val=504;
+--error ER_PS_TRG_INVALIDATED
+execute stmt_table_trigger using @val;
+select @nested;
+
+select * from t7_audit order by new_a;
+
+drop table t7_proc;
+drop table t7_func;
+drop table t7_view;
+drop table t7_table;
+drop table t7_dependent_table;
+drop table t7_table_trigger;
+drop table t7_audit;
+drop procedure audit_proc;
+drop function audit_func;
+drop view audit_view;
+
+--echo =====================================================================
+--echo Testing 8: TABLE -> TEMPORARY TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t8;
+--enable_warnings
+
+create table t8(a int);
+
+prepare stmt from 'select * from t8';
+execute stmt;
+execute stmt;
+
+drop table t8;
+create temporary table t8(a int);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t8;
+
+--echo =====================================================================
+--echo Testing 9: TABLE -> VIEW transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t9;
+drop table if exists t9_b;
+--enable_warnings
+
+create table t9(a int);
+create table t9_b(a int);
+
+prepare stmt from 'select * from t9';
+execute stmt;
+execute stmt;
+
+drop table t9;
+create view t9 as select * from t9_b;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop view t9;
+drop table t9_b;
+
+--echo =====================================================================
+--echo Testing 10: TEMPORARY TABLE -> NOTHING transitions
+--echo =====================================================================
+
+--disable_warnings
+drop temporary table if exists t10;
+--enable_warnings
+
+create temporary table t10(a int);
+
+prepare stmt from 'select * from t10';
+execute stmt;
+execute stmt;
+
+drop temporary table t10;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+
+--echo =====================================================================
+--echo Testing 11: TEMPORARY TABLE -> TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t11;
+drop temporary table if exists t11;
+--enable_warnings
+
+create table t11(a int);
+insert into t11(a) value (1);
+create temporary table t11(a int);
+
+prepare stmt from 'select * from t11';
+execute stmt;
+execute stmt;
+
+drop temporary table t11;
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+select * from t11;
+drop table t11;
+
+--echo =====================================================================
+--echo Testing 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions
+--echo =====================================================================
+
+--disable_warnings
+drop temporary table if exists t12;
+--enable_warnings
+
+create temporary table t12(a int);
+
+prepare stmt from 'select * from t12';
+execute stmt;
+execute stmt;
+
+drop temporary table t12;
+create temporary table t12(a int, b int);
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+select * from t12;
+drop table t12;
+
+--echo =====================================================================
+--echo Testing 13: TEMPORARY TABLE -> VIEW transitions
+--echo =====================================================================
+
+--disable_warnings
+drop temporary table if exists t13;
+drop table if exists t13_b;
+--enable_warnings
+
+create temporary table t13(a int);
+create table t13_b(a int);
+
+prepare stmt from 'select * from t13';
+execute stmt;
+execute stmt;
+
+drop temporary table t13;
+create view t13 as select * from t13_b;
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop view t13;
+drop table t13_b;
+
+--echo =====================================================================
+--echo Testing 14: VIEW -> NOTHING transitions
+--echo =====================================================================
+
+--disable_warnings
+drop view if exists t14;
+drop table if exists t14_b;
+--enable_warnings
+
+create table t14_b(a int);
+create view t14 as select * from t14_b;
+
+prepare stmt from 'select * from t14';
+execute stmt;
+execute stmt;
+
+drop view t14;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t14_b;
+
+--echo =====================================================================
+--echo Testing 15: VIEW -> TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop view if exists t15;
+drop table if exists t15_b;
+--enable_warnings
+
+create table t15_b(a int);
+create view t15 as select * from t15_b;
+
+prepare stmt from 'select * from t15';
+execute stmt;
+execute stmt;
+
+drop view t15;
+create table t15(a int);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t15_b;
+drop table t15;
+
+--echo =====================================================================
+--echo Testing 16: VIEW -> TEMPORARY TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop view if exists t16;
+drop table if exists t16_b;
+--enable_warnings
+
+create table t16_b(a int);
+create view t16 as select * from t16_b;
+
+prepare stmt from 'select * from t16';
+execute stmt;
+execute stmt;
+
+drop view t16;
+create temporary table t16(a int);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t16_b;
+drop temporary table t16;
+
+--echo =====================================================================
+--echo Testing 17: VIEW -> VIEW (DDL) transitions
+--echo =====================================================================
+
+--disable_warnings
+drop view if exists t17;
+drop table if exists t17_b;
+--enable_warnings
+
+create table t17_b(a int);
+insert into t17_b values (10), (20), (30);
+
+create view t17 as select a, 2*a as b, 3*a as c from t17_b;
+select * from t17;
+
+prepare stmt from 'select * from t17';
+execute stmt;
+execute stmt;
+
+drop view t17;
+create view t17 as select a, 2*a as b, 10*a as c from t17_b;
+select * from t17;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t17_b;
+drop view t17;
+
+--echo =====================================================================
+--echo Testing 18: VIEW -> VIEW (VIEW dependencies) transitions
+--echo =====================================================================
+
+#
+# Test 18-a: dependent PROCEDURE has changed (via a trigger)
+# Test 18-b: dependent FUNCTION has changed
+# Test 18-c: dependent VIEW has changed
+# Test 18-d: dependent TABLE has changed
+# Test 18-e: dependent TABLE TRIGGER has changed
+#
+
+--disable_warnings
+drop table if exists t18;
+drop table if exists t18_dependent_table;
+drop view if exists t18_func;
+drop view if exists t18_view;
+drop view if exists t18_table;
+drop function if exists view_func;
+drop view if exists view_view;
+--enable_warnings
+
+# TODO: insertable view -> trigger
+# TODO: insertable view -> trigger -> proc ?
+
+create table t18(a int);
+insert into t18 values (1), (2), (3);
+
+create function view_func(x int) returns int
+  return x+1;
+
+create view view_view as select "view v1" as reason from dual;
+
+create table t18_dependent_table(a int);
+
+create view t18_func as select a, view_func(a) as b from t18;
+create view t18_view as select a, reason as b from t18, view_view;
+create view t18_table as select * from t18;
+
+prepare stmt_func from 'select * from t18_func';
+execute stmt_func;
+execute stmt_func;
+
+drop function view_func;
+create function view_func(x int) returns int
+  return x*x;
+
+--error ER_PS_INVALIDATED
+execute stmt_func;
+--error ER_PS_INVALIDATED
+execute stmt_func;
+
+prepare stmt_view from 'select * from t18_view';
+execute stmt_view;
+execute stmt_view;
+
+drop view view_view;
+create view view_view as select "view v2" as reason from dual;
+
+--error ER_PS_INVALIDATED
+execute stmt_view;
+--error ER_PS_INVALIDATED
+execute stmt_view;
+
+prepare stmt_table from 'select * from t18_table';
+execute stmt_table;
+execute stmt_table;
+
+alter table t18 add column comments varchar(50) default NULL;
+
+--error ER_PS_INVALIDATED
+execute stmt_table;
+--error ER_PS_INVALIDATED
+execute stmt_table;
+
+drop table t18;
+drop table t18_dependent_table;
+drop view t18_func;
+drop view t18_view;
+drop view t18_table;
+drop function view_func;
+drop view view_view;
+
+--echo =====================================================================
+--echo Testing 19: Special tables (INFORMATION_SCHEMA)
+--echo =====================================================================
+
+--disable_warnings
+drop procedure if exists proc_19;
+--enable_warnings
+
+# Using a temporary table internally should not confuse the prepared
+# statement code, and should not raise ER_PS_INVALIDATED errors
+prepare stmt from
+ 'select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
+ from INFORMATION_SCHEMA.ROUTINES where
+ routine_name=\'proc_19\'';
+
+create procedure proc_19() select "hi there";
+
+execute stmt;
+execute stmt;
+
+drop procedure proc_19;
+create procedure proc_19() select "hi there, again";
+
+execute stmt;
+execute stmt;
+
+drop procedure proc_19;
+
+--echo =====================================================================
+--echo Testing 20: Special tables (log tables)
+--echo =====================================================================
+
+prepare stmt from
+ 'select * from mysql.general_log where argument=\'IMPOSSIBLE QUERY STRING\'';
+
+execute stmt;
+execute stmt;
+execute stmt;
+execute stmt;
+
+--echo =====================================================================
+--echo Testing 21: Special tables (system tables)
+--echo =====================================================================
+
+--disable_warnings
+drop procedure if exists proc_21;
+--enable_warnings
+
+prepare stmt from
+ 'select type, db, name from mysql.proc where name=\'proc_21\'';
+
+create procedure proc_21() select "hi there";
+
+execute stmt;
+execute stmt;
+
+drop procedure proc_21;
+create procedure proc_21() select "hi there, again";
+
+execute stmt;
+execute stmt;
+
+drop procedure proc_21;
+
+--echo =====================================================================
+--echo Testing 22: Special tables (views temp tables)
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t22_b;
+drop view if exists t22;
+--enable_warnings
+
+create table t22_b(a int);
+
+create algorithm=temptable view t22 as select a*a as a2 from t22_b;
+
+# Using a temporary table internally should not confuse the prepared
+# statement code, and should not raise ER_PS_INVALIDATED errors
+show create view t22;
+
+prepare stmt from 'select * from t22';
+
+insert into t22_b values (1), (2), (3);
+execute stmt;
+execute stmt;
+
+insert into t22_b values (4), (5), (6);
+execute stmt;
+execute stmt;
+
+drop table t22_b;
+drop view t22;
+
+--echo =====================================================================
+--echo Testing 23: Special tables (internal join tables)
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t23_a;
+drop table if exists t23_b;
+--enable_warnings
+
+create table t23_a(a int);
+create table t23_b(b int);
+
+# Using a temporary table internally should not confuse the prepared
+# statement code, and should not raise ER_PS_INVALIDATED errors
+prepare stmt from 'select * from t23_a join t23_b';
+
+insert into t23_a values (1), (2), (3);
+insert into t23_b values (10), (20), (30);
+execute stmt;
+execute stmt;
+
+insert into t23_a values (4);
+insert into t23_b values (40);
+execute stmt;
+execute stmt;
+
+drop table t23_a;
+drop table t23_b;
+
+--echo =====================================================================
+--echo Testing 24: Special statements
+--echo =====================================================================
+
+# SQLCOM_ALTER_TABLE:
+
+--disable_warnings
+drop table if exists t24_alter;
+--enable_warnings
+
+create table t24_alter(a int);
+
+prepare stmt from 'alter table t24_alter add column b int';
+execute stmt;
+
+drop table t24_alter;
+create table t24_alter(a1 int, a2 int);
+
+# t24_alter has changed, and it's not a problem
+execute stmt;
+
+alter table t24_alter drop column b;
+execute stmt;
+
+alter table t24_alter drop column b;
+execute stmt;
+
+drop table t24_alter;
+
+# SQLCOM_REPAIR:
+
+--disable_warnings
+drop table if exists t24_repair;
+--enable_warnings
+
+create table t24_repair(a int);
+insert into t24_repair values (1), (2), (3);
+
+prepare stmt from 'repair table t24_repair';
+execute stmt;
+
+drop table t24_repair;
+create table t24_repair(a1 int, a2 int);
+insert into t24_repair values (1, 10), (2, 20), (3, 30);
+
+# t24_repair has changed, and it's not a problem
+execute stmt;
+
+alter table t24_repair add column b varchar(50) default NULL;
+execute stmt;
+
+alter table t24_repair drop column b;
+execute stmt;
+
+drop table t24_repair;
+
+# SQLCOM_ANALYZE:
+
+--disable_warnings
+drop table if exists t24_analyze;
+--enable_warnings
+
+create table t24_analyze(a int);
+insert into t24_analyze values (1), (2), (3);
+
+prepare stmt from 'analyze table t24_analyze';
+execute stmt;
+
+drop table t24_analyze;
+create table t24_analyze(a1 int, a2 int);
+insert into t24_analyze values (1, 10), (2, 20), (3, 30);
+
+# t24_analyze has changed, and it's not a problem
+execute stmt;
+
+alter table t24_analyze add column b varchar(50) default NULL;
+execute stmt;
+
+alter table t24_analyze drop column b;
+execute stmt;
+
+drop table t24_analyze;
+
+# SQLCOM_OPTIMIZE:
+
+--disable_warnings
+drop table if exists t24_optimize;
+--enable_warnings
+
+create table t24_optimize(a int);
+insert into t24_optimize values (1), (2), (3);
+
+prepare stmt from 'optimize table t24_optimize';
+execute stmt;
+
+drop table t24_optimize;
+create table t24_optimize(a1 int, a2 int);
+insert into t24_optimize values (1, 10), (2, 20), (3, 30);
+
+# t24_optimize has changed, and it's not a problem
+execute stmt;
+
+alter table t24_optimize add column b varchar(50) default NULL;
+execute stmt;
+
+alter table t24_optimize drop column b;
+execute stmt;
+
+drop table t24_optimize;
+
+# SQLCOM_SHOW_CREATE_PROC:
+
+--disable_warnings
+drop procedure if exists changing_proc;
+--enable_warnings
+
+prepare stmt from 'show create procedure changing_proc';
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+create procedure changing_proc() begin end;
+
+# changing_proc has changed, and it's not a problem
+execute stmt;
+execute stmt;
+
+drop procedure changing_proc;
+create procedure changing_proc(x int, y int) begin end;
+
+execute stmt;
+execute stmt;
+
+drop procedure changing_proc;
+
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+# SQLCOM_SHOW_CREATE_FUNC:
+
+--disable_warnings
+drop function if exists changing_func;
+--enable_warnings
+
+prepare stmt from 'show create function changing_func';
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+create function changing_func() returns int return 0;
+
+# changing_proc has changed, and it's not a problem
+execute stmt;
+execute stmt;
+
+drop function changing_func;
+create function changing_func(x int, y int) returns int return x+y;
+
+execute stmt;
+execute stmt;
+
+drop function changing_func;
+
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+# SQLCOM_SHOW_CREATE_TRIGGER:
+
+--disable_warnings
+drop table if exists t24_trigger;
+--enable_warnings
+
+create table t24_trigger(a int);
+
+prepare stmt from 'show create trigger t24_bi;';
+--error ER_TRG_DOES_NOT_EXIST
+execute stmt;
+--error ER_TRG_DOES_NOT_EXIST
+execute stmt;
+
+delimiter $$;
+create trigger t24_bi before insert on t24_trigger for each row
+  begin
+    set @message= "t24_bi";
+  end
+$$
+delimiter ;$$
+
+# t24_bi has changed, and it's not a problem
+execute stmt;
+execute stmt;
+
+drop trigger t24_bi;
+delimiter $$;
+create trigger t24_bi before insert on t24_trigger for each row
+  begin
+    set @message= "t24_bi (2)";
+  end
+$$
+delimiter ;$$
+
+# t24_bi has changed, and it's not a problem
+execute stmt;
+execute stmt;
+
+drop trigger t24_bi;
+
+--error ER_TRG_DOES_NOT_EXIST
+execute stmt;
+--error ER_TRG_DOES_NOT_EXIST
+execute stmt;
+
+drop table t24_trigger;
+
+--echo =====================================================================
+--echo Testing 25: Testing the strength of TABLE_SHARE version
+--echo =====================================================================
+
+# Test 25-a: number of columns
+
+--disable_warnings
+drop table if exists t25_num_col;
+--enable_warnings
+
+create table t25_num_col(a int);
+
+prepare stmt from 'select * from t25_num_col';
+execute stmt;
+execute stmt;
+
+alter table t25_num_col add column b varchar(50) default NULL;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t25_num_col;
+
+# Test 25-b: column name
+
+--disable_warnings
+drop table if exists t25_col_name;
+--enable_warnings
+
+create table t25_col_name(a int);
+
+prepare stmt from 'select * from t25_col_name';
+execute stmt;
+execute stmt;
+
+alter table t25_col_name change a b int;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t25_col_name;
+
+# Test 25-c: column type
+
+--disable_warnings
+drop table if exists t25_col_type;
+--enable_warnings
+
+create table t25_col_type(a int);
+
+prepare stmt from 'select * from t25_col_type';
+execute stmt;
+execute stmt;
+
+alter table t25_col_type change a a varchar(10);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t25_col_type;
+
+# Test 25-d: column type length
+
+--disable_warnings
+drop table if exists t25_col_type_length;
+--enable_warnings
+
+create table t25_col_type_length(a varchar(10));
+
+prepare stmt from 'select * from t25_col_type_length';
+execute stmt;
+execute stmt;
+
+alter table t25_col_type_length change a a varchar(20);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t25_col_type_length;
+
+# Test 25-e: column NULL property
+
+--disable_warnings
+drop table if exists t25_col_null;
+--enable_warnings
+
+create table t25_col_null(a varchar(10));
+
+prepare stmt from 'select * from t25_col_null';
+execute stmt;
+execute stmt;
+
+alter table t25_col_null change a a varchar(10) NOT NULL;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t25_col_null;
+
+# Test 25-f: column DEFAULT
+
+--disable_warnings
+drop table if exists t25_col_default;
+--enable_warnings
+
+create table t25_col_default(a int, b int DEFAULT 10);
+
+prepare stmt from 'insert into t25_col_default(a) values (?)';
+set @val=1;
+execute stmt using @val;
+set @val=2;
+execute stmt using @val;
+
+alter table t25_col_default change b b int DEFAULT 20;
+
+set @val=3;
+# Must insert the correct default value for b
+execute stmt using @val;
+
+set @val=4;
+# Must insert the correct default value for b
+execute stmt using @val;
+
+select * from t25_col_default;
+
+drop table t25_col_default;
+
+# Test 25-g: number of keys
+
+--disable_warnings
+drop table if exists t25_index;
+--enable_warnings
+
+create table t25_index(a varchar(10));
+
+prepare stmt from 'select * from t25_index';
+execute stmt;
+execute stmt;
+
+create index i1 on t25_index(a);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t25_index;
+
+# Test 25-h: changing index uniqueness
+
+--disable_warnings
+drop table if exists t25_index_unique;
+--enable_warnings
+
+create table t25_index_unique(a varchar(10), b varchar(10));
+create index i1 on t25_index_unique(a, b);
+
+show create table t25_index_unique;
+
+prepare stmt from 'select * from t25_index_unique';
+execute stmt;
+execute stmt;
+
+alter table t25_index_unique drop index i1;
+create unique index i1 on t25_index_unique(a, b);
+
+show create table t25_index_unique;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t25_index_unique;
+
+--echo =====================================================================
+--echo Testing reported bugs
+--echo =====================================================================
+
+#
+# Bug#12093 (SP not found on second PS execution if another thread drops
+# other SP in between)
+#
+
+--disable_warnings
+drop table if exists table_12093;
+drop function if exists func_12093;
+drop function if exists func_12093_unrelated;
+drop procedure if exists proc_12093;
+--enable_warnings
+
+connect (con1,localhost,root,,);
+
+connection default;
+
+create table table_12093(a int);
+
+delimiter //;
+
+create function func_12093()
+returns int
+begin
+  return (select count(*) from table_12093);
+end//
+
+create procedure proc_12093(a int)
+begin
+  select * from table_12093;
+end//
+
+delimiter ;//
+
+create function func_12093_unrelated() returns int return 2;
+create procedure proc_12093_unrelated() begin end;
+
+prepare stmt_sf from 'select func_12093();';
+prepare stmt_sp from 'call proc_12093(func_12093())';
+
+execute stmt_sf;
+execute stmt_sp;
+
+connection con1;
+
+drop function func_12093_unrelated;
+drop procedure proc_12093_unrelated;
+
+connection default;
+
+# previously, failed with --error 1305
+execute stmt_sf;
+# previously, failed with --error 1305
+execute stmt_sp;
+
+# previously, failed with --error 1305
+execute stmt_sf;
+# previously, failed with --error 1305
+execute stmt_sp;
+
+deallocate prepare stmt_sf;
+deallocate prepare stmt_sp;
+
+disconnect con1;
+
+drop table table_12093;
+drop function func_12093;
+drop procedure proc_12093;
+
+#
+# Bug#21294 (executing a prepared statement that executes a stored function
+# which was recreat)
+#
+
+--disable_warnings
+drop function if exists func_21294;
+--enable_warnings
+
+create function func_21294() returns int return 10;
+
+prepare stmt from "select func_21294()";
+execute stmt;
+
+drop function func_21294;
+create function func_21294() returns int return 10;
+
+# might pass or fail, implementation dependent
+execute stmt;
+
+drop function func_21294;
+create function func_21294() returns int return 20;
+
+# should fail
+--error ER_PS_INVALIDATED
+execute stmt;
+
+deallocate prepare stmt;
+drop function func_21294;
+
+#
+# Bug#27420 (A combination of PS and view operations cause error + assertion
+# on shutdown)
+#
+
+--disable_warnings
+drop table if exists t_27420_100;
+drop table if exists t_27420_101;
+drop view if exists v_27420;
+--enable_warnings
+
+connect (con1,localhost,root,,);
+
+connection default;
+
+create table t_27420_100(a int);
+insert into t_27420_100 values (1), (2);
+
+create table t_27420_101(a int);
+insert into t_27420_101 values (1), (2);
+
+create view v_27420 as select t_27420_100.a X, t_27420_101.a Y
+  from t_27420_100, t_27420_101
+  where t_27420_100.a=t_27420_101.a;
+
+prepare stmt from 'select * from v_27420';
+
+execute stmt;
+
+connection con1;
+
+drop view v_27420;
+create table v_27420(X int, Y int);
+
+connection default;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+connection con1;
+
+drop table v_27420;
+# passes in 5.0, fails in 5.1, should pass
+create table v_27420 (a int, b int, filler char(200));
+
+connection default;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+disconnect con1;
+
+deallocate prepare stmt;
+drop table t_27420_100;
+drop table t_27420_101;
+drop table v_27420;
+
+#
+# Bug#27430 (Crash in subquery code when in PS and table DDL changed after
+# PREPARE)
+#
+
+--disable_warnings
+drop table if exists t_27430_1;
+drop table if exists t_27430_2;
+--enable_warnings
+
+create table t_27430_1 (a int not null, oref int not null, key(a));
+insert into t_27430_1 values
+  (1, 1),
+  (1, 1234),
+  (2, 3),
+  (2, 1234),
+  (3, 1234);
+
+create table t_27430_2 (a int not null, oref int not null);
+insert into t_27430_2 values
+  (1, 1),
+  (2, 2),
+  (1234, 3),
+  (1234, 4);
+
+prepare stmt from 
+ 'select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2';
+
+execute stmt; 
+
+drop table t_27430_1, t_27430_2;
+
+create table t_27430_1 (a int, oref int, key(a));
+insert into t_27430_1 values 
+  (1, 1),
+  (1, NULL),
+  (2, 3),
+  (2, NULL),
+  (3, NULL);
+
+create table t_27430_2 (a int, oref int);
+insert into t_27430_2 values
+  (1, 1),
+  (2,2),
+  (NULL, 3),
+  (NULL, 4);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+deallocate prepare stmt;
+drop table t_27430_1;
+drop table t_27430_2;
+
+#
+# Bug#27690 (Re-execution of prepared statement after table was replaced
+# with a view crashes)
+#
+
+--disable_warnings
+drop table if exists t_27690_1;
+drop view if exists v_27690_1;
+drop table if exists v_27690_2;
+--enable_warnings
+
+create table t_27690_1 (a int, b int);
+insert into t_27690_1 values (1,1),(2,2);
+
+create table v_27690_1 as select * from t_27690_1;
+create table v_27690_2 as select * from t_27690_1;
+
+prepare stmt from 'select * from v_27690_1, v_27690_2'; 
+execute stmt;
+execute stmt;
+
+drop table v_27690_1;
+
+--error ER_NO_SUCH_TABLE
+execute stmt;
+
+--error ER_NO_SUCH_TABLE
+execute stmt;
+
+create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+deallocate prepare stmt;
+drop table t_27690_1;
+drop view v_27690_1;
+drop table v_27690_2;
+
diff -Nrup a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
--- a/mysql-test/t/sp-error.test	2007-11-01 14:52:50 -06:00
+++ b/mysql-test/t/sp-error.test	2007-12-03 20:05:49 -07:00
@@ -1114,11 +1114,7 @@ execute stmt;
 drop function bug11834_1;
 # Attempt to execute statement should return proper error and 
 # should not crash server.
-
-# NOTE! The error we get from the below query indicates that the sp bug11834_2
-# does not exist(this is wrong but can be accepted)
-# This behaviour has been reported as bug#21294
---error ER_SP_DOES_NOT_EXIST
+--error ER_PS_INVALIDATED
 execute stmt;
 deallocate prepare stmt;
 drop function bug11834_2;
@@ -1470,10 +1466,7 @@ execute stmt1;
 drop function bug12329;
 create function bug12329() returns int return (select a+100 from t2);
 select bug12329();
-# Until we implement proper mechanism for invalidation of PS/SP when table
-# or SP's are changed the following statement will fail with 'Table ... was
-# not locked' error (this mechanism should be based on the new TDC).
---error ER_NO_SUCH_TABLE 
+--error ER_PS_INVALIDATED 
 execute stmt1;
 deallocate prepare stmt1;
 drop function bug12329; 
diff -Nrup a/mysql-test/t/sp.test b/mysql-test/t/sp.test
--- a/mysql-test/t/sp.test	2007-10-29 08:37:17 -06:00
+++ b/mysql-test/t/sp.test	2007-12-03 20:05:49 -07:00
@@ -1392,8 +1392,12 @@ create function f12_2() returns int
   return (select count(*) from t3)|
 
 drop temporary table t3|
+# Prepared statements: the function invalidates themselves
+# by performing DDL on table t3
+--disable_ps_protocol
 select f12_1()|
 select f12_1() from t1 limit 1|
+--enable_ps_protocol
 
 # Cleanup
 drop function f0|
diff -Nrup a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
--- a/mysql-test/t/trigger.test	2007-11-16 10:05:28 -07:00
+++ b/mysql-test/t/trigger.test	2007-12-03 20:05:49 -07:00
@@ -997,10 +997,7 @@ call p1();
 # Altering trigger forcing it use different set of tables
 drop trigger t1_bi;
 create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id);
-# Until we implement proper mechanism for invalidation of PS/SP when table
-# or SP's are changed these two statements will fail with 'Table ... was
-# not locked' error (this mechanism should be based on the new TDC).
---error ER_NO_SUCH_TABLE 
+--error ER_PS_TRG_INVALIDATED 
 execute stmt1;
 --error ER_NO_SUCH_TABLE 
 call p1();
diff -Nrup a/sql/Makefile.am b/sql/Makefile.am
--- a/sql/Makefile.am	2007-08-30 17:23:07 -06:00
+++ b/sql/Makefile.am	2007-12-03 20:05:49 -07:00
@@ -74,7 +74,7 @@ noinst_HEADERS =	item.h item_func.h item
 			sql_plugin.h authors.h \
 			event_data_objects.h event_scheduler.h \
 			sql_partition.h partition_info.h partition_element.h \
-			contributors.h sql_servers.h
+			contributors.h sql_servers.h bostream.h
 
 mysqld_SOURCES =	sql_lex.cc sql_handler.cc sql_partition.cc \
 			item.cc item_sum.cc item_buff.cc item_func.cc \
@@ -117,7 +117,7 @@ mysqld_SOURCES =	sql_lex.cc sql_handler.
                         event_queue.cc event_db_repository.cc events.cc \
 			sql_plugin.cc sql_binlog.cc \
 			sql_builtin.cc sql_tablespace.cc partition_info.cc \
-			sql_servers.cc
+			sql_servers.cc bostream.cc
 
 nodist_mysqld_SOURCES =	mini_client_errors.c pack.c client.c my_time.c my_user.c 
 
diff -Nrup a/sql/bostream.cc b/sql/bostream.cc
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/sql/bostream.cc	2007-12-03 20:05:58 -07:00
@@ -0,0 +1,118 @@
+/* Copyright (C) 2000-2006 MySQL AB
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+
+
+#include "mysql_priv.h"
+#include "bostream.h"
+
+Buffer_output_stream::Buffer_output_stream()
+  : m_mem_root(NULL),
+  m_buffer(NULL),
+  m_size(0),
+  m_max_size(0),
+  m_error(FALSE)
+{}
+
+Buffer_output_stream::~Buffer_output_stream()
+{
+  cleanup();
+}
+
+int Buffer_output_stream::init(MEM_ROOT *mem_root, size_t size)
+{
+  int rc;
+
+  m_mem_root= mem_root;
+  m_buffer= alloc_root(m_mem_root, size);
+  if (m_buffer)
+  {
+    m_max_size= size;
+    rc= 0;
+  }
+  else
+    rc= 1;
+
+  return rc;
+}
+
+void Buffer_output_stream::cleanup()
+{
+}
+
+void Buffer_output_stream::write_uint(uint value)
+{
+  if (m_size + sizeof(uint) < m_max_size)
+  {
+    void *current_ptr= ((uchar*) m_buffer) + m_size;
+    memcpy(current_ptr, & value, sizeof(uint));
+    m_size+= sizeof(uint);
+  }
+  else
+  {
+    m_error= TRUE;
+  }
+}
+
+void Buffer_output_stream::write_uint16(uint16 value)
+{
+  if (m_size + sizeof(uint16) < m_max_size)
+  {
+    void *current_ptr= ((uchar*) m_buffer) + m_size;
+    memcpy(current_ptr, & value, sizeof(uint16));
+    m_size+= sizeof(uint16);
+  }
+  else
+  {
+    m_error= TRUE;
+  }
+}
+
+void Buffer_output_stream::write_LEX_STRING(const LEX_STRING *value)
+{
+  if (m_size + sizeof(value->length) + value->length < m_max_size)
+  {
+    write_uint(value->length);
+    if (value->length)
+    {
+      void *current_ptr= ((uchar*) m_buffer) + m_size;
+      memcpy(current_ptr, value->str, value->length);
+      m_size+= value->length;
+    }
+  }
+  else
+  {
+    m_error= TRUE;
+  }
+}
+
+void Buffer_output_stream::write_string(const char *value)
+{
+  write_buffer(value, strlen(value)+1);
+}
+
+void Buffer_output_stream::write_buffer(const void *ptr, size_t size)
+{
+  if (m_size + size < m_max_size)
+  {
+    void *current_ptr= ((uchar*) m_buffer) + m_size;
+    memcpy(current_ptr, ptr, size);
+    m_size+= size;
+  }
+  else
+  {
+    m_error= TRUE;
+  }
+}
+
diff -Nrup a/sql/bostream.h b/sql/bostream.h
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/sql/bostream.h	2007-12-03 20:05:58 -07:00
@@ -0,0 +1,58 @@
+/* Copyright (C) 2000-2006 MySQL AB
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+
+
+#ifndef BUFFER_OUTPUT_STREAM_H
+#define BUFFER_OUTPUT_STREAM_H
+
+/**
+  Buffer output stream.
+  This class implements a rudimentary buffer output stream,
+  as a replacement for the C++ library classes (which we don't use).
+*/
+class Buffer_output_stream
+{
+public:
+  Buffer_output_stream();
+  ~Buffer_output_stream();
+
+  int init(MEM_ROOT *mem_root, size_t max_size);
+  void cleanup();
+
+  void *get_buffer()
+  { return m_buffer; }
+
+  size_t get_size()
+  { return m_size; }
+
+  bool get_error()
+  { return m_error; }
+
+  void write_uint(uint value);
+  void write_uint16(uint16 value);
+  void write_LEX_STRING(const LEX_STRING *str);
+  void write_string(const char *str);
+  void write_buffer(const void *ptr, size_t size);
+
+private:
+  MEM_ROOT *m_mem_root;
+  void *m_buffer;
+  size_t m_size;
+  size_t m_max_size;
+  bool m_error;
+};
+
+#endif
+
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h	2007-11-20 21:00:58 -07:00
+++ b/sql/item.h	2007-12-03 20:05:49 -07:00
@@ -2519,18 +2519,6 @@ public:
 };
 
 
-/*
-  We need this two enums here instead of sql_lex.h because
-  at least one of them is used by Item_trigger_field interface.
-
-  Time when trigger is invoked (i.e. before or after row actually
-  inserted/updated/deleted).
-*/
-enum trg_action_time_type
-{
-  TRG_ACTION_BEFORE= 0, TRG_ACTION_AFTER= 1, TRG_ACTION_MAX
-};
-
 class Table_triggers_list;
 
 /*
diff -Nrup a/sql/mysql_priv.h b/sql/mysql_priv.h
--- a/sql/mysql_priv.h	2007-11-28 07:34:09 -07:00
+++ b/sql/mysql_priv.h	2007-12-03 20:05:49 -07:00
@@ -984,6 +984,7 @@ void table_cache_free(void);
 bool table_def_init(void);
 void table_def_free(void);
 void assign_new_table_id(TABLE_SHARE *share);
+int compute_table_share_md5(TABLE_SHARE *share);
 uint cached_open_tables(void);
 uint cached_table_definitions(void);
 void kill_mysql(void);
diff -Nrup a/sql/parse_file.cc b/sql/parse_file.cc
--- a/sql/parse_file.cc	2007-07-30 02:33:44 -06:00
+++ b/sql/parse_file.cc	2007-12-03 20:05:50 -07:00
@@ -429,7 +429,7 @@ sql_parse_prepare(const LEX_STRING *file
   File file;
   DBUG_ENTER("sql_parse_prepare");
 
-  if (!my_stat(file_name->str, &stat_info, MYF(MY_WME)))
+  if (!my_stat(file_name->str, &stat_info, MYF(0)))
   {
     DBUG_RETURN(0);
   }
diff -Nrup a/sql/share/errmsg.txt b/sql/share/errmsg.txt
--- a/sql/share/errmsg.txt	2007-11-14 06:28:21 -07:00
+++ b/sql/share/errmsg.txt	2007-12-03 20:05:57 -07:00
@@ -6114,3 +6114,10 @@ ER_TRG_CANT_OPEN_TABLE
 
 ER_CANT_CREATE_SROUTINE
   eng "Cannot create stored routine `%-.64s`. Check warnings"
+
+ER_PS_INVALIDATED
+  eng "Prepared statement has been invalidated, because %s '%s' has changed"
+
+ER_PS_TRG_INVALIDATED
+  eng "Prepared statement has been invalidated, because %s %s %s '%s' has changed"
+
diff -Nrup a/sql/sp.cc b/sql/sp.cc
--- a/sql/sp.cc	2007-10-30 11:08:11 -06:00
+++ b/sql/sp.cc	2007-12-03 20:05:50 -07:00
@@ -1798,6 +1798,157 @@ static void sp_update_stmt_used_routines
 }
 
 
+/**
+  This helper ensures that a given Stored Function or Stored Procedure
+  is present in the corresponding per session cache,
+  and loads the Stored Function/Procedure from storage into the cache,
+  if necessary.
+  @param [in] thd Current thread
+  @param [in] type Type of stored object to lookup/load
+  @param [in] name Name of the stored object
+  @param [out] sphp Stored object refreshed in the cache, if found
+  @return An error status
+    @retval 0 Success, sphp will contain the stored object fetched, if any
+*/
+
+static int
+sp_cache_load(THD *thd, int type, sp_name *name, sp_head **sphp)
+{
+  sp_cache **cp;
+  sp_head *sp;
+  int ret;
+  Object_version sp_version;
+
+  DBUG_ENTER("sp_cache_load");
+
+  DBUG_ASSERT(name);
+  DBUG_ASSERT(sphp);
+  DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE ||
+              type == TYPE_ENUM_TRIGGER ||
+              type == TYPE_ENUM_FUNCTION);
+
+  /* Triggers are cached in the stored procedure cache. */
+  cp= (type == TYPE_ENUM_FUNCTION ? &thd->sp_func_cache : &thd->sp_proc_cache);
+
+  sp= sp_cache_lookup(cp, name);
+
+  if (! sp)
+  {
+    switch ((ret= db_find_routine(thd, type, name, &sp)))
+    {
+    case SP_OK:
+      sp_cache_insert(cp, sp);
+
+      if (thd->m_object_observer && type != TYPE_ENUM_TRIGGER)
+      {
+        sp->make_object_version(& sp_version);
+
+        if (thd->m_object_observer->notify(thd, sp_version))
+          DBUG_RETURN(1);
+      }
+
+      *sphp= sp;
+      ret= 0;
+      break;
+    case SP_KEY_NOT_FOUND:
+      if (thd->m_object_observer && type != TYPE_ENUM_TRIGGER)
+      {
+        sp_head::make_object_null_version(& sp_version,
+                                          type,
+                                          name->m_db,
+                                          name->m_name);
+        if (thd->m_object_observer->notify(thd, sp_version))
+          DBUG_RETURN(1);
+      }
+
+      *sphp= NULL;
+      ret= 0;
+      break;
+    default:
+      /*
+        Any error when loading an existing routine is either some problem
+        with the mysql.proc table, or a parse error because the contents
+        has been tampered with (in which case we clear that error).
+      */
+      if (ret == SP_PARSE_ERROR)
+        thd->clear_error();
+      /*
+        If we cleared the parse error, or when db_find_routine() flagged
+        an error with it's return value without calling my_error(), we
+        set the generic "mysql.proc table corrupt" error here.
+       */
+      if (! thd->is_error())
+      {
+        /*
+          SP allows full NAME_LEN chars thus he have to allocate enough
+          size in bytes. Otherwise there is stack overrun could happen
+          if multibyte sequence is `name`. `db` is still safe because the
+          rest of the server checks against NAME_LEN bytes and not chars.
+          Hence, the overrun happens only if the name is in length > 32 and
+          uses multibyte (cyrillic, greek, etc.)
+        */
+        char n[NAME_LEN*2+2];
+
+        /* m_qname.str is not always \0 terminated */
+        memcpy(n, name->m_qname.str, name->m_qname.length);
+        n[name->m_qname.length]= '\0';
+        my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), n, ret);
+        ret= 1;
+      }
+      break;
+    }
+  }
+  else
+  {
+    if (thd->m_object_observer && type != TYPE_ENUM_TRIGGER)
+    {
+      sp->make_object_version(& sp_version);
+
+      if (thd->m_object_observer->notify(thd, sp_version))
+        DBUG_RETURN(2);
+    }
+
+    ret= 0;
+    *sphp= sp;
+  }
+
+  DBUG_RETURN(ret);
+}
+
+
+/**
+  Refresh all the routines used by a statement in the session cache.
+  Note that routines that can be found are reloaded,
+  while routines that can not be found are ignored.
+  @param [in] thd Current thread
+  @param [in] lex Statement semantic tree
+  @return An error status
+    @retval 0 Success
+*/
+int
+sp_refresh_routines(THD *thd, LEX *lex)
+{
+  Sroutine_hash_entry *start;
+  int ret= 0;
+
+  DBUG_ENTER("sp_recache_routines");
+  start= (Sroutine_hash_entry *)lex->sroutines_list.first;
+
+  for (Sroutine_hash_entry *rt= start; rt; rt= rt->next)
+  {
+    sp_name name(thd, rt->key.str, rt->key.length);
+    int type= rt->key.str[0];
+    sp_head *sp;
+
+    ret= sp_cache_load(thd, type, &name, &sp);
+
+    if (ret)
+      break;
+  }
+  DBUG_RETURN(ret);
+}
+
+
 /*
   Cache sub-set of routines used by statement, add tables used by these
   routines to statement table list. Do the same for all routines used
@@ -1836,56 +1987,11 @@ sp_cache_routines_and_add_tables_aux(THD
     int type= rt->key.str[0];
     sp_head *sp;
 
-    if (!(sp= sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
-                              &thd->sp_func_cache : &thd->sp_proc_cache),
-                              &name)))
-    {
-      switch ((ret= db_find_routine(thd, type, &name, &sp)))
-      {
-      case SP_OK:
-        {
-          if (type == TYPE_ENUM_FUNCTION)
-            sp_cache_insert(&thd->sp_func_cache, sp);
-          else
-            sp_cache_insert(&thd->sp_proc_cache, sp);
-        }
-        break;
-      case SP_KEY_NOT_FOUND:
-        ret= SP_OK;
-        break;
-      default:
-        /*
-          Any error when loading an existing routine is either some problem
-          with the mysql.proc table, or a parse error because the contents
-          has been tampered with (in which case we clear that error).
-        */
-        if (ret == SP_PARSE_ERROR)
-          thd->clear_error();
-        /*
-          If we cleared the parse error, or when db_find_routine() flagged
-          an error with it's return value without calling my_error(), we
-          set the generic "mysql.proc table corrupt" error here.
-         */
-        if (! thd->is_error())
-        {
-          /*
-            SP allows full NAME_LEN chars thus he have to allocate enough
-            size in bytes. Otherwise there is stack overrun could happen
-            if multibyte sequence is `name`. `db` is still safe because the
-            rest of the server checks agains NAME_LEN bytes and not chars.
-            Hence, the overrun happens only if the name is in length > 32 and
-            uses multibyte (cyrillic, greek, etc.)
-          */
-          char n[NAME_LEN*2+2];
-
-          /* m_qname.str is not always \0 terminated */
-          memcpy(n, name.m_qname.str, name.m_qname.length);
-          n[name.m_qname.length]= '\0';
-          my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), n, ret);
-        }
-        break;
-      }
-    }
+    ret= sp_cache_load(thd, type, &name, &sp);
+
+    if (ret)
+      break;
+
     if (sp)
     {
       if (!(first && first_no_prelock))
@@ -1998,6 +2104,18 @@ sp_cache_routines_and_add_tables_for_tri
               add_used_routine(lex, thd->stmt_arena, &trigger->m_sroutines_key,
                                table->belong_to_view))
           {
+            if (thd->m_object_observer)
+            {
+              Object_version trigger_version;
+              Trigger::make_object_version(trigger,
+                                           (trg_event_type) i,
+                                           (trg_action_time_type) j,
+                                           & trigger_version);
+              ret= thd->m_object_observer->notify(thd, trigger_version);
+              if (ret)
+                return ret;
+            }
+
             trigger->add_used_tables_to_table_list(thd, &lex->query_tables_last,
                                                    table->belong_to_view);
             trigger->propagate_attributes(lex);
diff -Nrup a/sql/sp.h b/sql/sp.h
--- a/sql/sp.h	2007-10-17 02:13:53 -06:00
+++ b/sql/sp.h	2007-12-03 20:05:50 -07:00
@@ -70,6 +70,7 @@ void sp_add_used_routine(LEX *lex, Query
                          sp_name *rt, char rt_type);
 void sp_remove_not_own_routines(LEX *lex);
 void sp_update_sp_used_routines(HASH *dst, HASH *src);
+int sp_refresh_routines(THD *thd, LEX *lex);
 int sp_cache_routines_and_add_tables(THD *thd, LEX *lex,
                                      bool first_no_prelock);
 int sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
diff -Nrup a/sql/sp_head.cc b/sql/sp_head.cc
--- a/sql/sp_head.cc	2007-11-28 09:08:25 -07:00
+++ b/sql/sp_head.cc	2007-12-03 20:05:50 -07:00
@@ -22,6 +22,7 @@
 #include "sp_pcontext.h"
 #include "sp_rcontext.h"
 #include "sp_cache.h"
+#include "my_md5.h"
 
 /*
   Sufficient max length of printed destinations and frame offsets (all uints).
@@ -535,6 +536,56 @@ sp_head::sp_head()
   DBUG_VOID_RETURN;
 }
 
+int sp_head::make_object_version(Object_version * version)
+{
+  int rc;
+
+  switch(m_type)
+  {
+  case TYPE_ENUM_FUNCTION:
+    rc= version->m_oid.set_function(m_db, m_name);
+    break;
+  case TYPE_ENUM_PROCEDURE:
+    rc= version->m_oid.set_procedure(m_db, m_name);
+    break;
+  case TYPE_ENUM_TRIGGER:
+  default:
+    DBUG_ASSERT(false);
+    rc= 1;
+  }
+
+  version->m_vid.set_md5(m_md5_value);
+
+  return rc;
+}
+
+int sp_head::make_object_null_version(
+  Object_version * version,
+  int type,
+  const LEX_STRING& db,
+  const LEX_STRING& name)
+{
+  int rc;
+
+  switch(type)
+  {
+  case TYPE_ENUM_FUNCTION:
+    rc= version->m_oid.set_function(db, name);
+    break;
+  case TYPE_ENUM_PROCEDURE:
+    rc= version->m_oid.set_procedure(db, name);
+    break;
+  case TYPE_ENUM_TRIGGER:
+  default:
+    DBUG_ASSERT(false);
+    rc= 1;
+  }
+
+  version->m_vid.set_null();
+
+  return rc;
+}
+
 
 void
 sp_head::init(LEX *lex)
@@ -632,6 +683,8 @@ sp_head::set_stmt_end(THD *thd)
 {
   Lex_input_stream *lip= thd->m_lip; /* shortcut */
   const char *end_ptr= lip->get_cpp_ptr(); /* shortcut */
+  my_MD5_CTX context;
+  uchar digest[16];
 
   /* Make the string of parameters. */
 
@@ -667,6 +720,17 @@ sp_head::set_stmt_end(THD *thd)
   m_defstr.length= end_ptr - lip->get_cpp_buf();
   m_defstr.str= thd->strmake(lip->get_cpp_buf(), m_defstr.length);
   trim_whitespace(thd->charset(), & m_defstr);
+
+  my_MD5Init(&context);
+  my_MD5Update(&context,(uchar *) m_defstr.str, m_defstr.length);
+  my_MD5Final(digest, &context);
+
+  sprintf(m_md5_value,
+          "%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x",
+          digest[0], digest[1], digest[2], digest[3],
+          digest[4], digest[5], digest[6], digest[7],
+          digest[8], digest[9], digest[10], digest[11],
+          digest[12], digest[13], digest[14], digest[15]);
 }
 
 
diff -Nrup a/sql/sp_head.h b/sql/sp_head.h
--- a/sql/sp_head.h	2007-11-23 06:35:36 -07:00
+++ b/sql/sp_head.h	2007-12-03 20:05:55 -07:00
@@ -194,6 +194,14 @@ public:
   LEX_STRING m_defstr;
   LEX_STRING m_definer_user;
   LEX_STRING m_definer_host;
+  char m_md5_value[33];
+
+  int make_object_version(Object_version * version);
+  static int make_object_null_version(
+    Object_version * version,
+    int type,
+    const LEX_STRING& db,
+    const LEX_STRING& name);
 
 private:
   Stored_program_creation_ctx *m_creation_ctx;
diff -Nrup a/sql/sql_base.cc b/sql/sql_base.cc
--- a/sql/sql_base.cc	2007-11-28 07:34:10 -07:00
+++ b/sql/sql_base.cc	2007-12-03 20:05:56 -07:00
@@ -416,6 +416,7 @@ TABLE_SHARE *get_table_share(THD *thd, T
     DBUG_RETURN(0);
   }
   share->ref_count++;				// Mark in use
+  compute_table_share_md5(share);
   DBUG_PRINT("exit", ("share: 0x%lx  ref_count: %u",
                       (ulong) share, share->ref_count));
   (void) pthread_mutex_unlock(&share->mutex);
@@ -2544,7 +2545,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
                       (ulong) table->query_id, (uint) thd->server_id,
                       (ulong) thd->variables.pseudo_thread_id));
 	  my_error(ER_CANT_REOPEN_TABLE, MYF(0), table->alias);
-	  DBUG_RETURN(0);
+          goto end_null;
 	}
 	table->query_id= thd->query_id;
 	thd->thread_specific_used= TRUE;
@@ -2557,7 +2558,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
   if (flags & MYSQL_OPEN_TEMPORARY_ONLY)
   {
     my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db, table_list->table_name);
-    DBUG_RETURN(0);
+    goto end_null;
   }
 
   /*
@@ -2590,7 +2591,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
           */
           my_error(ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG, MYF(0),
                    table->s->table_name.str);
-          DBUG_RETURN(0);
+          goto end_null;
         }
         /*
           When looking for a usable TABLE, ignore MERGE children, as they
@@ -2665,7 +2666,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
         {
           DBUG_ASSERT(table_list->view != 0);
           VOID(pthread_mutex_unlock(&LOCK_open));
-          DBUG_RETURN(0); // VIEW
+          goto end_null; // VIEW
         }
         VOID(pthread_mutex_unlock(&LOCK_open));
       }
@@ -2681,7 +2682,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
       my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db, table_list->alias);
     else
       my_error(ER_TABLE_NOT_LOCKED, MYF(0), alias);
-    DBUG_RETURN(0);
+    goto end_null;
   }
 
   /*
@@ -2719,7 +2720,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
     if (refresh)
       *refresh=1;
     VOID(pthread_mutex_unlock(&LOCK_open));
-    DBUG_RETURN(0);
+    goto end_null;
   }
 
   /*
@@ -2786,7 +2787,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
       {
 	VOID(pthread_mutex_unlock(&LOCK_open));
         my_error(ER_UPDATE_TABLE_USED, MYF(0), table->s->table_name.str);
-        DBUG_RETURN(0);
+        goto end_null;
       }
 
       /*
@@ -2833,7 +2834,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
       */
       if (refresh)
 	*refresh=1;
-      DBUG_RETURN(0);
+      goto end_null;
     }
   }
   if (table)
@@ -2867,7 +2868,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
       if (check_if_table_exists(thd, table_list, &exists))
       {
         VOID(pthread_mutex_unlock(&LOCK_open));
-        DBUG_RETURN(NULL);
+        goto end_null;
       }
 
       if (!exists)
@@ -2878,7 +2879,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
         if (!(table= table_cache_insert_placeholder(thd, key, key_length)))
         {
           VOID(pthread_mutex_unlock(&LOCK_open));
-          DBUG_RETURN(NULL);
+          goto end_null;
         }
         /*
           Link placeholder to the open tables list so it will be automatically
@@ -2889,7 +2890,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
         table->next= thd->open_tables;
         thd->open_tables= table;
         VOID(pthread_mutex_unlock(&LOCK_open));
-        DBUG_RETURN(table);
+        goto end_table;
       }
       /* Table exists. Let us try to open it. */
     }
@@ -2898,7 +2899,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
     if (!(table=(TABLE*) my_malloc(sizeof(*table),MYF(MY_WME))))
     {
       VOID(pthread_mutex_unlock(&LOCK_open));
-      DBUG_RETURN(NULL);
+      goto end_null;
     }
 
     error= open_unireg_entry(thd, table, table_list, alias, key, key_length,
@@ -2907,7 +2908,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
     {
       my_free((uchar*)table, MYF(0));
       VOID(pthread_mutex_unlock(&LOCK_open));
-      DBUG_RETURN(NULL);
+      goto end_null;
     }
     if (table_list->view || error < 0)
     {
@@ -2920,7 +2921,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
 
       my_free((uchar*)table, MYF(0));
       VOID(pthread_mutex_unlock(&LOCK_open));
-      DBUG_RETURN(0); // VIEW
+      goto end_null; // VIEW
     }
     DBUG_PRINT("info", ("inserting table '%s'.'%s' 0x%lx into the cache",
                         table->s->db.str, table->s->table_name.str,
@@ -2931,6 +2932,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
   check_unused();				// Debugging call
 
   VOID(pthread_mutex_unlock(&LOCK_open));
+
   if (refresh)
   {
     table->next=thd->open_tables;		/* Link into simple list */
@@ -2970,7 +2972,40 @@ TABLE *open_table(THD *thd, TABLE_LIST *
   table_list->updatable= 1; // It is not derived table nor non-updatable VIEW
   table->clear_column_bitmaps();
   DBUG_ASSERT(table->key_read == 0);
+
+end_table:
+  if (thd->m_object_observer)
+  {
+    Object_version table_version;
+    table->s->make_object_version(& table_version);
+    if (thd->m_object_observer->notify(thd, table_version))
+    {
+      *refresh= FALSE;
+      DBUG_RETURN(NULL);
+    }
+
+    if (table->triggers &&
+        table_list->trg_event_map &&
+        table->triggers->visit_all_triggers(thd, table_list->trg_event_map))
+    {
+      *refresh= FALSE;
+      DBUG_RETURN(NULL);
+    }
+  }
   DBUG_RETURN(table);
+
+end_null:
+  if (thd->m_object_observer)
+  {
+    // DBUG_ASSERT(table_list->view == NULL);
+    Object_version table_version;
+
+    TABLE_SHARE::make_object_null_version(& table_version,
+                                          table_list->db,
+                                          table_list->table_name);
+    thd->m_object_observer->notify(thd, table_version);
+  }
+  DBUG_RETURN(NULL);
 }
 
 
@@ -4338,6 +4373,10 @@ int open_tables(THD *thd, TABLE_LIST **s
       query_tables_last_own= save_query_tables_last;
       *start= thd->lex->query_tables;
     }
+  }
+  else
+  {
+    sp_refresh_routines(thd, thd->lex);
   }
 
   /*
diff -Nrup a/sql/sql_class.cc b/sql/sql_class.cc
--- a/sql/sql_class.cc	2007-11-27 09:02:59 -07:00
+++ b/sql/sql_class.cc	2007-12-03 20:05:56 -07:00
@@ -50,6 +50,327 @@ char empty_c_string[1]= {0};    /* used 
 
 const char * const THD::DEFAULT_WHERE= "field list";
 
+const LEX_STRING object_type_names[]=
+{
+  { C_STRING_WITH_LEN("NONE") },
+  { C_STRING_WITH_LEN("TABLE") },
+  { C_STRING_WITH_LEN("VIEW") },
+  { C_STRING_WITH_LEN("PROCEDURE") },
+  { C_STRING_WITH_LEN("FUNCTION") },
+  { C_STRING_WITH_LEN("TRIGGER") }
+};
+
+Object_id::Object_id()
+{
+  m_type= NO_OBJECT_TYPE;
+  m_key[0]= '\0';
+  m_key_length= 0;
+}
+
+Object_id::Object_id(const Object_id& oid)
+{
+  m_type= oid.m_type;
+  memcpy(m_key, oid.m_key, sizeof(m_key));
+  m_key_length= oid.m_key_length;
+}
+
+bool Object_id::is_equal(const Object_id& oid) const
+{
+  return ((m_type == oid.m_type) &&
+         (m_key_length == oid.m_key_length) &&
+         (memcmp(m_key, oid.m_key, m_key_length) == 0));
+}
+
+int
+Object_id::set(const Object_id& oid)
+{
+  m_type= oid.m_type;
+  m_key_length= oid.m_key_length;
+  memcpy(m_key, oid.m_key, m_key_length);
+  return 0;
+}
+
+void
+Object_id::reset_type(Object_type type)
+{
+  DBUG_ASSERT(m_type != NO_OBJECT_TYPE);
+  DBUG_ASSERT(type != NO_OBJECT_TYPE);
+
+  m_type= type;
+  switch(m_type)
+  {
+  case OBJECT_TYPE_TABLE:
+    m_key[0]= 'T';
+    break;
+  case OBJECT_TYPE_VIEW:
+    m_key[0]= 'V';
+    break;
+  default:
+    DBUG_ASSERT(FALSE);
+    break;
+  }
+}
+
+int
+Object_id::set_table(const char* db, const char* name)
+{
+  /* FIXME: Fix TABLE_SHARE::db, TABLE_SHARE::table_name */
+  LEX_STRING lex_db;
+  LEX_STRING lex_name;
+  lex_db.str= (char*) db;
+  lex_db.length= strlen(lex_db.str);
+  lex_name.str= (char*) name;
+  lex_name.length= strlen(lex_name.str);
+
+  return set_table(lex_db, lex_name);
+}
+
+int
+Object_id::set_view(const char* db, const char* name)
+{
+  /* FIXME: Fix TABLE_LIST::db, TABLE_LIST::table_name */
+  LEX_STRING lex_db;
+  LEX_STRING lex_name;
+  lex_db.str= (char*) db;
+  lex_db.length= strlen(lex_db.str);
+  lex_name.str= (char*) name;
+  lex_name.length= strlen(lex_name.str);
+
+  return set_view(lex_db, lex_name);
+}
+
+int
+Object_id::set_trigger(
+  const LEX_STRING& db, const LEX_STRING& name,
+  trg_event_type event_type, trg_action_time_type action_time)
+{
+  DBUG_ASSERT(db.length <= NAME_LEN);
+  DBUG_ASSERT(name.length <= NAME_LEN);
+
+  m_type= OBJECT_TYPE_TRIGGER;
+  m_trigger_event= event_type;
+  m_trigger_action= action_time;
+
+  /* format: <type> <db> . <name> \0 <action> <event> \0 */
+  m_key_length= sprintf(m_key, "G%.*s.%.*s%c%c%c",
+                        (int) db.length, db.str,
+                        (int) name.length, name.str,
+                        '\0',
+                        (int) action_time,
+                        (int) event_type);
+  DBUG_ASSERT(m_key_length <= sizeof(m_key));
+
+  return 0;
+}
+
+
+int
+Object_id::set_key(
+  char kind, const LEX_STRING& db, const LEX_STRING& name)
+{
+  DBUG_ASSERT(db.length <= NAME_LEN);
+  DBUG_ASSERT(name.length <= NAME_LEN);
+
+  /* format: <type> <db> . <name> \0 */
+  m_key_length= sprintf(m_key, "%c%.*s.%.*s",
+                        kind,
+                        (int) db.length, db.str,
+                        (int) name.length, name.str);
+  DBUG_ASSERT(m_key_length <= sizeof(m_key));
+
+  return 0;
+}
+
+const char*
+Object_id::get_printable_type() const
+{
+  DBUG_ASSERT((uint) m_type < array_elements(object_type_names));
+
+  return object_type_names[(int) m_type].str;
+}
+
+const char*
+Object_id::get_printable_name() const
+{
+  return & m_key[1];
+}
+
+Version_id::Version_id()
+  : m_kind(NULL_VERSION),
+    m_long_value(0)
+{
+  m_md5_value[0]= 0;
+}
+
+Version_id::Version_id(const Version_id& vid)
+{
+  m_kind= vid.m_kind;
+  switch(m_kind)
+  {
+  case NULL_VERSION:
+    break;
+  case LONG_VERSION:
+    m_long_value= vid.m_long_value;
+    break;
+  case MD5_VERSION:
+    memcpy(m_md5_value, vid.m_md5_value, sizeof(m_md5_value));
+    break;
+  }
+}
+
+Version_id::~Version_id()
+{}
+
+bool
+Version_id::is_equal(const Version_id& vid) const
+{
+  if (m_kind != vid.m_kind)
+    return false;
+
+  switch(m_kind)
+  {
+    case NULL_VERSION:
+      return true;
+    case LONG_VERSION:
+      return (m_long_value == vid.m_long_value);
+    case MD5_VERSION:
+      return (memcmp(m_md5_value, vid.m_md5_value, sizeof(m_md5_value)) == 0);
+    default:
+      DBUG_ASSERT(false);
+  }
+}
+
+bool
+Version_id::is_null_or_equal(const Version_id& vid) const
+{
+  return is_null() || vid.is_null() || is_equal(vid);
+}
+
+bool
+Version_id::is_null() const
+{
+  return (m_kind == NULL_VERSION);
+}
+
+void
+Version_id::set_null()
+{
+  m_kind= NULL_VERSION;
+}
+
+void
+Version_id::set_long(longlong value)
+{
+  m_kind= LONG_VERSION;
+  m_long_value= value;
+}
+
+void
+Version_id::set_md5(const char* md5)
+{
+  m_kind= MD5_VERSION;
+  memcpy(m_md5_value, md5, sizeof(m_md5_value));
+}
+
+extern "C" uchar*
+get_obj_version_hash_key(const uchar *buff, size_t *length,
+                         my_bool /* unused */)
+{
+  Object_version *over= (Object_version*) buff;
+  return over->m_oid.get_hash_key(length);
+}
+
+void* Object_version::operator new(size_t size, MEM_ROOT* mem_root) throw()
+{
+  void *ptr;
+  DBUG_ENTER("Object_version::operator new");
+  ptr= alloc_root(mem_root, size);
+  DBUG_RETURN(ptr);
+}
+
+void
+Object_version::operator delete(void *ptr, MEM_ROOT* mem_root) throw()
+{
+}
+
+int
+Object_version_collection::init(MEM_ROOT *mem_root)
+{
+  DBUG_ENTER("Object_version_collection::init");
+  int rc;
+
+  DBUG_ASSERT(m_mem_root == NULL);
+  m_mem_root= mem_root;
+
+  rc= hash_init(& m_hash,
+                & my_charset_bin,
+                0,                             /* initial size */
+                0,
+                0,
+                (hash_get_key) get_obj_version_hash_key,
+                NULL,                          /* Nothing to free */
+                MYF(0));
+
+  DBUG_RETURN(rc);
+}
+
+Object_version_collection::~Object_version_collection()
+{
+  if (m_mem_root)
+  {
+    (void) hash_free(& m_hash);
+    m_mem_root= NULL;
+  }
+}
+
+int
+Object_version_collection::add(
+  const Object_id& oid,
+  const Version_id& vid)
+{
+  Object_version *over;
+  size_t hash_key_length;
+  uchar* hash_key;
+
+  DBUG_ENTER("Object_version_collection::add()");
+
+  hash_key= oid.get_hash_key(& hash_key_length);
+  over= (Object_version*) hash_search(& m_hash, hash_key, hash_key_length);
+
+  if (over)
+  {
+    if (! over->m_vid.is_equal(vid))
+      DBUG_RETURN(1);
+  }
+  else
+  {
+    over= new (m_mem_root) Object_version(oid, vid);
+    if (my_hash_insert(& m_hash, (uchar*) over))
+      DBUG_RETURN(2);
+  }
+
+  DBUG_PRINT("info",("key: %s  length: %d",
+                    hash_key, (int) hash_key_length));
+  DBUG_RETURN(0);
+}
+
+int
+Object_version_collection::find(
+  const Object_id& oid,
+  const Object_version ** over) const
+{
+  Object_version *v;
+  size_t hash_key_length;
+  uchar* hash_key;
+
+  hash_key= oid.get_hash_key(& hash_key_length);
+  v= (Object_version*) hash_search(& m_hash, hash_key, hash_key_length);
+
+  *over= v;
+  return 0;
+}
+
+
 
 /*****************************************************************************
 ** Instansiate templates
@@ -375,7 +696,8 @@ THD::THD()
    bootstrap(0),
    derived_tables_processing(FALSE),
    spcont(NULL),
-   m_lip(NULL)
+   m_lip(NULL),
+   m_object_observer(NULL)
 {
   ulong tmp;
 
diff -Nrup a/sql/sql_class.h b/sql/sql_class.h
--- a/sql/sql_class.h	2007-11-27 11:30:50 -07:00
+++ b/sql/sql_class.h	2007-12-03 20:05:56 -07:00
@@ -23,6 +23,256 @@
 #include "log.h"
 #include "rpl_tblmap.h"
 
+#define MD5_CHAR_LENGTH 32
+
+/**
+  The type of a MySQL object.
+*/
+enum Object_type
+{
+  NO_OBJECT_TYPE= 0,
+  OBJECT_TYPE_TABLE= 1,
+  OBJECT_TYPE_VIEW= 2,
+  OBJECT_TYPE_PROCEDURE= 3,
+  OBJECT_TYPE_FUNCTION= 4,
+  OBJECT_TYPE_TRIGGER= 5
+
+  /*
+    TODO: To be extended:
+    - OBJECT_TYPE_UDF,
+    - OBJECT_TYPE_EVENT,
+    - OBJECT_TYPE_USER,
+    - OBJECT_TYPE_TABLESPACE,
+    - OBJECT_TYPE_PARTITION,
+    etc, as needed.
+  */
+};
+
+extern const LEX_STRING object_type_names[];
+
+/** Time when a trigger action is invoked. */
+enum trg_action_time_type
+{
+  TRG_ACTION_BEFORE= 0,
+  TRG_ACTION_AFTER= 1,
+  TRG_ACTION_MAX
+};
+
+
+/** Event on which trigger is invoked. */
+enum trg_event_type
+{
+  TRG_EVENT_INSERT= 0,
+  TRG_EVENT_UPDATE= 1,
+  TRG_EVENT_DELETE= 2,
+  TRG_EVENT_MAX
+};
+
+/*
+  Format: <type> <NAME> <.> <NAME> <\0>
+  (<trigger action> <trigger event> <\0>) */
+#define MAX_OBJ_ID_KEY_LEN (2*NAME_LEN + 6)
+
+class Object_id
+{
+public:
+  Object_id();
+  Object_id(const Object_id& oid);
+  ~Object_id() {}
+
+  bool is_equal(const Object_id& oid) const;
+
+  Object_type get_type() const
+  { return m_type; }
+
+  void reset_type(Object_type type);
+
+  int set(const Object_id& oid);
+
+  int set_table(const char* db, const char* name);
+
+  int set_table(const LEX_STRING& db, const LEX_STRING& name)
+  {
+    m_type= OBJECT_TYPE_TABLE;
+    return set_key('T', db, name);
+  }
+
+  int set_view(const char* db, const char* name);
+
+  int set_view(const LEX_STRING& db, const LEX_STRING& name)
+  {
+    m_type= OBJECT_TYPE_VIEW;
+    return set_key('V', db, name);
+  }
+
+  int set_procedure(const LEX_STRING& db, const LEX_STRING& name)
+  {
+    m_type= OBJECT_TYPE_PROCEDURE;
+    return set_key('P', db, name);
+  }
+
+  int set_function(const LEX_STRING& db, const LEX_STRING& name)
+  {
+    m_type= OBJECT_TYPE_FUNCTION;
+    return set_key('F', db, name);
+  }
+
+  int set_trigger(const LEX_STRING& db, const LEX_STRING& name,
+                  trg_event_type event_type, trg_action_time_type action_time);
+
+  trg_event_type get_trigger_event() const
+  { return m_trigger_event; }
+
+  trg_action_time_type get_trigger_action() const
+  { return m_trigger_action; }
+
+  uchar* get_hash_key(size_t *length) const
+  {
+    *length= m_key_length;
+    return (uchar*) & m_key[0];
+  }
+
+  const char* get_printable_type() const;
+
+  const char* get_printable_name() const;
+
+private:
+  int set_key(char kind, const LEX_STRING& db, const LEX_STRING& name);
+
+private:
+  Object_type m_type;
+  char m_key[MAX_OBJ_ID_KEY_LEN];
+  uint m_key_length;
+  trg_event_type m_trigger_event;
+  trg_action_time_type m_trigger_action;
+
+private: // Not implemented, use set()
+  Object_id& operator = (const Object_id&);
+};
+
+/**
+  A Version_id identifies a version of a given object.
+  Different objects (TABLE, VIEW, FUNCTION, PROCEDURE, ...)
+  can use version ids of different spaces (MD5, timestamp, counter, ...),
+  so that a Version_id is an heterogeneous type.
+  Versions can be compared for equality, but don't define any order.
+*/
+
+class Version_id
+{
+public:
+  Version_id();
+  Version_id(const Version_id& vid);
+  ~Version_id();
+
+  bool is_null() const;
+  /**
+    Test for equality.
+    Equality is defined by:
+    - having the same namespace
+    - having the same value within that namespace
+  */
+  bool is_equal(const Version_id& v) const;
+  bool is_null_or_equal(const Version_id& v) const;
+
+  void set_null();
+
+  void set_long(longlong value);
+
+  void set_md5(const char* md5);
+
+private:
+  enum kind {
+    NULL_VERSION,
+    LONG_VERSION,
+    MD5_VERSION
+  };
+
+  kind m_kind;
+  longlong m_long_value;
+  char m_md5_value[MD5_CHAR_LENGTH];
+
+private: // Non implemented
+  Version_id& operator = (const Version_id&);
+};
+
+
+class Object_version
+{
+public:
+  static void* operator new(size_t size, MEM_ROOT* mem_root) throw();
+  static void operator delete(void *ptr, MEM_ROOT* mem_root) throw();
+
+  Object_version()
+  {}
+
+  Object_version(const Object_id& oid, const Version_id& vid)
+   : m_oid(oid), m_vid(vid)
+  {}
+
+  ~Object_version() {}
+
+  Object_id m_oid;
+  Version_id m_vid;
+
+private: // Non implemented
+  Object_version(const Object_version&);
+  Object_version& operator = (const Object_version&);
+};
+
+
+class Object_version_collection
+{
+public:
+  Object_version_collection()
+    : m_mem_root(NULL)
+  {}
+
+  ~Object_version_collection();
+
+  int init(MEM_ROOT *mem_root);
+  int add(const Object_id& oid, const Version_id & vid);
+  int find(const Object_id& oid, const Object_version ** over) const;
+
+private:
+  HASH m_hash;
+  MEM_ROOT *m_mem_root;
+
+private: // Non implemented
+  Object_version_collection(const Object_version_collection&);
+  Object_version_collection& operator=(const Object_version_collection&);
+};
+
+/**
+  Interface class for observers, used to inspect objects referenced
+  by a statement.
+  An Object_observer can inspect existing objects,
+  as well as get notified that some objects used in a statement are missing.
+  Observers can cause the current operation to fail, by returning an error
+  from the notification methods.
+*/
+
+class Object_observer
+{
+protected:
+  Object_observer() {}
+  virtual ~Object_observer() {}
+
+public:
+  /**
+    Notify that an object is used in a statement.
+    @param [in] thd Current thread
+    @param [in] over object version
+    @return An error status
+      @retval 0 Success
+  */
+  virtual int notify(THD *thd, const Object_version& over) = 0;
+
+private: // Non implemented
+  Object_observer(const Object_observer&);
+  Object_observer& operator = (const Object_observer&);
+};
+
 class Relay_log_info;
 
 class Query_log_event;
@@ -1559,6 +1809,8 @@ public:
     and may point to invalid memory after that.
   */
   Lex_input_stream *m_lip;
+
+  Object_observer *m_object_observer;
 
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   partition_info *work_part_info;
diff -Nrup a/sql/sql_prepare.cc b/sql/sql_prepare.cc
--- a/sql/sql_prepare.cc	2007-11-21 12:57:24 -07:00
+++ b/sql/sql_prepare.cc	2007-12-03 20:05:56 -07:00
@@ -84,6 +84,7 @@ When one supplies long data for a placeh
 #include "sp_head.h"
 #include "sp.h"
 #include "sp_cache.h"
+#include "sql_trigger.h"
 #ifdef EMBEDDED_LIBRARY
 /* include MYSQL_BIND headers */
 #include <mysql.h>
@@ -109,6 +110,416 @@ public:
 #endif
 };
 
+/**
+  Collect the version number of all the objects seen during PREPARE.
+  The list of object versions recorded is stored with a prepared statement,
+  and is used during EXECUTE to re-validate the statement.
+*/
+class Prepare_observer : public Object_observer
+{
+public:
+  Prepare_observer(Object_version_collection* coll)
+    : m_coll(coll)
+  {}
+
+  virtual ~Prepare_observer() {}
+
+  virtual int notify(THD *thd, const Object_version& over);
+
+private:
+  Object_version_collection *m_coll;
+};
+
+/**
+  Enforce that the objects used during EXECUTE are valid.
+  This class enforces integrity constraints, and guarantees that the content
+  of a prepared statement is still valid and can be used to EXECUTE the
+  statement.
+*/
+class Execute_observer : public Object_observer
+{
+public:
+  Execute_observer(Object_version_collection* coll,
+                   const Object_id& expected_table_change)
+    : m_coll(coll),
+    m_expected_table_change(expected_table_change)
+  {}
+
+  virtual ~Execute_observer() {}
+
+  virtual int notify(THD *thd, const Object_version& over);
+
+private:
+  bool is_safe_table_operation(LEX *lex) const;
+  bool is_safe_sp_operation(LEX *lex) const;
+  bool is_safe_trigger_operation(LEX *lex) const;
+  int notify_table_or_view(THD *thd, const Object_version& over);
+  int notify_function_or_procedure(THD *thd, const Object_version& over);
+  int notify_trigger(THD *thd, const Object_version& over);
+
+private:
+  Object_version_collection *m_coll;
+  const Object_id& m_expected_table_change;
+};
+
+int visit_all_views(THD *thd, TABLE_LIST *start)
+{
+  Object_observer *observer;
+  TABLE_LIST *tables;
+  Object_version view_version;
+
+  DBUG_ASSERT(thd->m_object_observer);
+
+  observer= thd->m_object_observer;
+  for (tables= start; tables; tables= tables->next_global)
+  {
+    if (tables->view)
+    {
+      if (tables->make_object_version(& view_version))
+        return 1;
+
+      if (observer->notify(thd, view_version))
+        return 1;
+    }
+  }
+
+  return 0;
+}
+
+int recheck_all_views(THD *thd, TABLE_LIST *start)
+{
+  Object_observer *observer;
+  TABLE_LIST *tables;
+  TABLE_LIST dummy;
+  int rc;
+  Object_version view_version;
+
+  DBUG_ASSERT(thd->m_object_observer);
+
+  observer= thd->m_object_observer;
+
+  /*
+    FIXME: There is no equivalent of sroutines_list for views,
+    so we have to iterate in TABLE_LIST.
+    This is poor, since the same view might be expanded
+    in multiple places, and will cause multiple parsing
+  */
+  for (tables= start; tables; tables= tables->next_global)
+  {
+    if (tables->view)
+    {
+      /*
+        WARNING: [1] and [2] are due to the implementation of views,
+        where the view logical name is found:
+        - sometime in (db, table_name) if the view has not be processed,
+        - sometime in (view_db, view_name) if the view has been processed.
+        See the comments in mysql_make_view()
+      */
+
+      /* [1] mysql_load_view_md5 uses (db, table_name) */
+      dummy.db= tables->view_db.str;
+      dummy.table_name= tables->view_name.str;
+      rc= mysql_load_view_md5(thd, &dummy);
+
+      if (rc == 0)
+      {
+        /* [2] TABLE_LIST::make_object_version uses (view_db, view_name) */
+        LEX fake_lex;
+        dummy.view_db= tables->view_db;
+        dummy.view_name= tables->view_name;
+        dummy.view= & fake_lex;
+        rc= dummy.make_object_version(& view_version);
+      }
+      else
+        rc= TABLE_LIST::make_object_null_version(& view_version,
+                                                 tables->db,
+                                                 tables->table_name);
+
+      if (rc != 0)
+        return 1;
+
+      if (observer->notify(thd, view_version))
+        return 1;
+    }
+  }
+
+  return 0;
+}
+
+int
+Prepare_observer::notify(THD *thd, const Object_version& over)
+{
+  switch(over.m_oid.get_type())
+  {
+  case OBJECT_TYPE_TABLE:
+  case OBJECT_TYPE_VIEW:
+  case OBJECT_TYPE_PROCEDURE:
+  case OBJECT_TYPE_FUNCTION:
+  case OBJECT_TYPE_TRIGGER:
+    return m_coll->add(over.m_oid, over.m_vid);
+  default:
+    return 0;
+  }
+}
+
+/**
+  Indicate if DDL performed on a table is safe for a prepared statement.
+  Some statements can be applied to different tables at each execution,
+  and the re-validation code should allow them.
+*/
+bool
+Execute_observer::is_safe_table_operation(LEX *lex) const
+{
+  switch(lex->sql_command)
+  {
+  case SQLCOM_ALTER_TABLE:
+  case SQLCOM_REPAIR:
+  case SQLCOM_ANALYZE:
+  case SQLCOM_OPTIMIZE:
+    return TRUE;
+
+  case SQLCOM_SHOW_CREATE: // safe for tables, not views
+  default:
+    return FALSE;
+  }
+}
+
+/**
+  Indicate if DDL performed on a procedure or function is safe for a
+  prepared statement.
+  Some statements can be applied to different SP/SF at each execution,
+  and the re-validation code should allow them.
+*/
+bool
+Execute_observer::is_safe_sp_operation(LEX *lex) const
+{
+  switch(lex->sql_command)
+  {
+  case SQLCOM_SHOW_CREATE_PROC:
+  case SQLCOM_SHOW_CREATE_FUNC:
+    return TRUE;
+
+  default:
+    return FALSE;
+  }
+}
+
+/**
+  Indicate if DDL performed on a trigger is safe for a
+  prepared statement.
+  Some statements can be applied to different trigger at each execution,
+  and the re-validation code should allow them.
+*/
+bool
+Execute_observer::is_safe_trigger_operation(LEX *lex) const
+{
+  switch(lex->sql_command)
+  {
+  case SQLCOM_SHOW_CREATE_TRIGGER:
+    return TRUE;
+
+  default:
+    return FALSE;
+  }
+}
+
+int
+Execute_observer::notify(THD *thd, const Object_version& over)
+{
+  int rc;
+
+  DBUG_ENTER("Execute_observer::notify()");
+
+  switch(over.m_oid.get_type())
+  {
+  case OBJECT_TYPE_TABLE:
+  case OBJECT_TYPE_VIEW:
+    rc= notify_table_or_view(thd, over);
+    break;
+  case OBJECT_TYPE_PROCEDURE:
+  case OBJECT_TYPE_FUNCTION:
+    rc= notify_function_or_procedure(thd, over);
+    break;
+  case OBJECT_TYPE_TRIGGER:
+    rc= notify_trigger(thd, over);
+    break;
+  case NO_OBJECT_TYPE:
+  default:
+    rc= 0;
+    break;
+  }
+
+  DBUG_RETURN(rc);
+}
+
+int
+Execute_observer::notify_table_or_view(THD *thd, const Object_version& over)
+{
+  const Object_version *collected;
+  int rc;
+
+  DBUG_ENTER("Execute_observer::notify_table_or_view");
+
+  rc= m_coll->find(over.m_oid, & collected);
+  if (rc != 0)
+    goto err;
+
+  if (collected)
+  {
+    if (! collected->m_vid.is_equal(over.m_vid))
+    {
+      bool allow_change= FALSE;
+      if ((over.m_oid.get_type() == OBJECT_TYPE_TABLE) &&
+          m_expected_table_change.is_equal(over.m_oid))
+      {
+        allow_change= TRUE;
+      }
+
+      if (! allow_change &&
+          ! is_safe_table_operation(thd->lex))
+      {
+        my_error(ER_PS_INVALIDATED, MYF(0),
+                 over.m_oid.get_printable_type(),
+                 over.m_oid.get_printable_name());
+        rc= 1;
+      }
+    }
+  }
+  else
+  {
+    /*
+      See if:
+      - a TABLE was replaced by a VIEW,
+      - a VIEW was replaced by a TABLE
+    */
+    Object_id shadowing_oid(over.m_oid);
+    if (over.m_oid.get_type() == OBJECT_TYPE_TABLE)
+      shadowing_oid.reset_type(OBJECT_TYPE_VIEW);
+    else
+      shadowing_oid.reset_type(OBJECT_TYPE_TABLE);
+
+    rc= m_coll->find(shadowing_oid, & collected);
+    if (rc != 0)
+      goto err;
+
+    if (collected)
+    {
+      my_error(ER_PS_INVALIDATED, MYF(0),
+               shadowing_oid.get_printable_type(),
+               shadowing_oid.get_printable_name());
+      rc= 1;
+    }
+    else
+    {
+      /*
+        Here, the Prepared statement execute code is discovering
+        an object that was never found during the prepare phase.
+        For some statements, the prepare phase does not open and lock
+        tables (which is ok, performances), so that this is a normal case.
+        Example of such statements are:
+        - SHOW COLUMNS FROM <table>
+          --> open the table <table>
+        - INSTALL PLUGIN ... SONAME ...
+          --> open the table mysql.plugin
+        - SHOW CREATE TRIGGER t1_bi
+          --> open the table t1 (that the trigger relates to)
+        - SHOW COLUMNS FROM <view>
+          --> open the view <view>
+        Once a real object (version not null) is found by the first
+        execute(), it's added to the prepared statement internal state.
+        This is to properly detect further DDL changes for these late found
+        dependencies.
+        This is critical, since until Bug#27011 is fixed, the internal
+        state of the statement can contain a copy of view DDL,
+        which needs to be checked for changes later.
+      */
+      if (! over.m_vid.is_null())
+        rc= m_coll->add(over.m_oid, over.m_vid);
+    }
+  }
+
+err:
+  DBUG_RETURN(rc);
+}
+
+int
+Execute_observer::notify_function_or_procedure(THD *thd,
+                                               const Object_version& over)
+{
+  const Object_version *collected;
+  int rc;
+
+  DBUG_ENTER("Execute_observer::notify_function_or_procedure");
+
+  rc= m_coll->find(over.m_oid, & collected);
+  if (rc != 0)
+    goto err;
+
+  if (collected)
+  {
+    if (! collected->m_vid.is_equal(over.m_vid))
+    {
+      if (! is_safe_sp_operation(thd->lex))
+      {
+        /*
+          For these objects, we do require an exact match:
+          - Version_id::is_equal() means that:
+            - either the object did not exist, and still does not exist
+            - or the object existed and is unchanged.
+        */
+        my_error(ER_PS_INVALIDATED, MYF(0),
+                 over.m_oid.get_printable_type(),
+                 over.m_oid.get_printable_name());
+        rc= 1;
+      }
+    }
+  }
+  else
+  {
+    rc= m_coll->add(over.m_oid, over.m_vid);
+  }
+
+err:
+  DBUG_RETURN(rc);
+}
+
+int
+Execute_observer::notify_trigger(THD *thd,
+                                 const Object_version& over)
+{
+  const Object_version *collected;
+  int rc;
+
+  DBUG_ENTER("Execute_observer::notify_trigger");
+
+  rc= m_coll->find(over.m_oid, & collected);
+  if (rc != 0)
+    goto err;
+
+  if (! collected || ! collected->m_vid.is_equal(over.m_vid))
+  {
+    if (! is_safe_trigger_operation(thd->lex))
+    {
+      trg_event_type event= over.m_oid.get_trigger_event();
+      trg_action_time_type action= over.m_oid.get_trigger_action();
+      const LEX_STRING *action_name= & trg_action_time_type_names[action];
+      const LEX_STRING *event_name= & trg_event_type_names[event];
+
+      my_error(ER_PS_TRG_INVALIDATED, MYF(0),
+               action_name->str,
+               event_name->str,
+               over.m_oid.get_printable_type(),
+               over.m_oid.get_printable_name());
+      rc= 1;
+    }
+  }
+
+err:
+  DBUG_RETURN(rc);
+}
+
+
+
 /****************************************************************************/
 
 /**
@@ -153,6 +564,9 @@ public:
   bool execute(String *expanded_query, bool open_cursor);
   /* Destroy this statement */
   bool deallocate();
+
+  void expect_table_change(const Object_id& oid);
+
 private:
   /**
     Store the parsed tree of a prepared statement here.
@@ -163,6 +577,26 @@ private:
     SELECT_LEX and other classes).
   */
   MEM_ROOT main_mem_root;
+
+  /**
+    Collection of all objects versions this prepared statement depends on.
+    The prepared statement internal state contains optimizations based on
+    the objects seen during prepare. Should any of the objects listed change
+    between prepare() and execute(), the prepared statement will be declared
+    invalidated (with some exceptions, see Execute_observer).
+  */
+  Object_version_collection m_versions;
+
+  /**
+    Allow DDL for a table to occur between prepare and execute.
+    This is a special case for the CREATE TABLE ... SELECT statement,
+    which is hybrid (DDL + DML).
+    Optimizations performed for the DML part (that can contain sub selects)
+    are protected by m_versions.
+    Allowable exceptions for the DDL part (the table to create) are
+    recorded in this attribute.
+  */
+  Object_id m_expected_table_change;
 };
 
 
@@ -1495,6 +1929,16 @@ static bool mysql_test_create_table(Prep
       create_table->create= TRUE;
     }
 
+    /*
+      FIXME: instead, open_normal_and_derived_tables
+      should not open the table to create
+    */
+    Object_version table_version;
+    TABLE_SHARE::make_object_null_version(& table_version,
+                                          create_table->db,
+                                          create_table->table_name);
+    stmt->expect_table_change(table_version.m_oid);
+
     if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0))
       DBUG_RETURN(TRUE);
 
@@ -1506,7 +1950,7 @@ static bool mysql_test_create_table(Prep
     res= select_like_stmt_test(stmt, 0, 0);
   }
 
-  /* put tables back for PS rexecuting */
+  /* put tables back for PS re-executing */
   lex->link_first_table_back(create_table, link_to_local);
   DBUG_RETURN(res);
 }
@@ -2698,7 +3142,9 @@ Prepared_statement::Prepared_statement(T
   param_array(0),
   param_count(0),
   last_errno(0),
-  flags((uint) IS_IN_USE)
+  flags((uint) IS_IN_USE),
+  m_versions(),
+  m_expected_table_change()
 {
   init_sql_alloc(&main_mem_root, thd_arg->variables.query_alloc_block_size,
                   thd_arg->variables.query_prealloc_size);
@@ -2842,6 +3288,9 @@ bool Prepared_statement::prepare(const c
   */
   status_var_increment(thd->status_var.com_stmt_prepare);
 
+  if (m_versions.init(& main_mem_root))
+    DBUG_RETURN(TRUE);
+
   /*
     alloc_query() uses thd->memroot && thd->query, so we should call
     both of backup_statement() and backup_query_arena() here.
@@ -2898,6 +3347,10 @@ bool Prepared_statement::prepare(const c
   */
   DBUG_ASSERT(thd->change_list.is_empty());
 
+  Prepare_observer observer(& m_versions);
+  Object_observer *save_observer= thd->m_object_observer;
+  thd->m_object_observer= & observer;
+
   /* 
    The only case where we should have items in the thd->free_list is
    after stmt->set_params_from_vars(), which may in some cases create
@@ -2907,6 +3360,11 @@ bool Prepared_statement::prepare(const c
   if (error == 0)
     error= check_prepared_statement(this, name.str != 0);
 
+  if (error == 0)
+    error= visit_all_views(thd, thd->lex->query_tables);
+
+  thd->m_object_observer= save_observer;
+
   /*
     Currently CREATE PROCEDURE/TRIGGER/EVENT are prohibited in prepared
     statements: ensure we have no memory leak here if by someone tries
@@ -2952,6 +3410,12 @@ bool Prepared_statement::prepare(const c
   DBUG_RETURN(error);
 }
 
+void Prepared_statement::expect_table_change(const Object_id& oid)
+{
+  DBUG_ASSERT(m_expected_table_change.get_type() == NO_OBJECT_TYPE);
+  m_expected_table_change.set(oid);
+}
+
 /*
   Execute a prepared statement.
 
@@ -3083,22 +3547,42 @@ bool Prepared_statement::execute(String 
 
   /* Go! */
 
-  if (open_cursor)
-    error= mysql_open_cursor(thd, (uint) ALWAYS_MATERIALIZED_CURSOR,
-                             &result, &cursor);
-  else
-  {
+  /*
+    Try to find it in the query cache, if not, execute it.
+    Note that multi-statements cannot exist here (they are not supported in
+    prepared statements).
+  */
+  if (query_cache_send_result_to_client(thd, thd->query,
+                                        thd->query_length) <= 0)
+  {
+    int rc;
+    Execute_observer observer(& m_versions,
+                              m_expected_table_change);
+    Object_observer *save_observer= thd->m_object_observer;
+    thd->m_object_observer= & observer;
+
     /*
-      Try to find it in the query cache, if not, execute it.
-      Note that multi-statements cannot exist here (they are not supported in
-      prepared statements).
+      For views, there is no caching at all,
+      and the content of thd->lex->query_tables could be obsolete by now.
+      Making sure that the views in-lined in TABLE_LIST are still valid.
     */
-    if (query_cache_send_result_to_client(thd, thd->query,
-                                          thd->query_length) <= 0)
+    rc= recheck_all_views(thd, thd->lex->query_tables);
+
+    if (rc == 0)
     {
-      error= mysql_execute_command(thd);
-      query_cache_end_of_result(thd);
+      if (open_cursor)
+        error= mysql_open_cursor(thd, (uint) ALWAYS_MATERIALIZED_CURSOR,
+                                 &result, &cursor);
+      else
+      {
+        error= mysql_execute_command(thd);
+        query_cache_end_of_result(thd);
+      }
     }
+    else
+      error= 1;
+
+    thd->m_object_observer= save_observer;
   }
 
   /*
diff -Nrup a/sql/sql_trigger.cc b/sql/sql_trigger.cc
--- a/sql/sql_trigger.cc	2007-11-18 12:28:35 -07:00
+++ b/sql/sql_trigger.cc	2007-12-03 20:05:57 -07:00
@@ -2007,6 +2007,42 @@ void Table_triggers_list::mark_fields_us
   trigger_table->file->column_bitmaps_signal();
 }
 
+int Table_triggers_list::visit_all_triggers(THD *thd, uint8 trg_event_map)
+{
+  Object_observer *observer= thd->m_object_observer;
+  int rc= 0;
+  sp_head *trigger;
+  Object_version trigger_version;
+
+  DBUG_ENTER("Table_triggers_list::visit_all_triggers");
+  DBUG_ASSERT(observer);
+
+  for (int i= 0; i < (int)TRG_EVENT_MAX; i++)
+  {
+    if (trg_event_map &
+        static_cast<uint8>(1 << static_cast<int>(i)))
+    {
+      for (int j= 0; j < (int)TRG_ACTION_MAX; j++)
+      {
+
+        trigger= bodies[i][j];
+        if (trigger)
+        {
+          Trigger::make_object_version(trigger,
+                                       (trg_event_type) i,
+                                       (trg_action_time_type) j,
+                                       & trigger_version);
+          rc= observer->notify(thd, trigger_version);
+          if (rc)
+            DBUG_RETURN(rc);
+        }
+      }
+    }
+  }
+
+  DBUG_RETURN(rc);
+}
+
 
 /*
   Trigger BUG#14090 compatibility hook
@@ -2198,3 +2234,15 @@ bool load_table_name_for_trigger(THD *th
 
   DBUG_RETURN(FALSE);
 }
+
+int Trigger::make_object_version(sp_head *trigger,
+                                 trg_event_type event,
+                                 trg_action_time_type action,
+                                 Object_version *version)
+{
+  version->m_oid.set_trigger(trigger->m_db, trigger->m_name, event, action);
+  version->m_vid.set_md5(trigger->m_md5_value);
+
+  return 0;
+}
+
diff -Nrup a/sql/sql_trigger.h b/sql/sql_trigger.h
--- a/sql/sql_trigger.h	2007-07-16 13:02:46 -06:00
+++ b/sql/sql_trigger.h	2007-12-03 20:05:57 -07:00
@@ -14,6 +14,15 @@
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307 USA */
 
 
+class Trigger
+{
+public:
+  static int make_object_version(sp_head *trigger,
+                                 trg_event_type event,
+                                 trg_action_time_type action,
+                                 Object_version *version);
+};
+
 /*
   This class holds all information about triggers of table.
 
@@ -139,6 +148,8 @@ public:
   void set_table(TABLE *new_table);
 
   void mark_fields_used(trg_event_type event);
+
+  int visit_all_triggers(THD *thd, uint8 trg_event_map);
 
   friend class Item_trigger_field;
   friend int sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
diff -Nrup a/sql/sql_view.cc b/sql/sql_view.cc
--- a/sql/sql_view.cc	2007-10-30 11:08:13 -06:00
+++ b/sql/sql_view.cc	2007-12-03 20:05:57 -07:00
@@ -674,6 +674,19 @@ static File_option view_parameters[]=
   FILE_OPTIONS_STRING}
 };
 
+/* Short parsing, only to read MD5 */
+static File_option view_md5_parameters[]=
+{{{ C_STRING_WITH_LEN("query")},
+  my_offsetof(TABLE_LIST, select_stmt),
+  FILE_OPTIONS_ESTRING},
+ {{ C_STRING_WITH_LEN("md5")},
+  my_offsetof(TABLE_LIST, md5),
+  FILE_OPTIONS_STRING},
+ {{NullS, 0},			0,
+  FILE_OPTIONS_STRING}
+};
+
+
 static LEX_STRING view_file_type[]= {{(char*) STRING_WITH_LEN("VIEW") }};
 
 
@@ -1440,6 +1453,44 @@ err:
   table->view= 0;	// now it is not VIEW placeholder
   result= 1;
   goto end;
+}
+
+int mysql_load_view_md5(THD *thd, TABLE_LIST *view)
+{
+  int rc;
+  LEX_STRING pathstr;
+  File_parser *parser;
+  char path_buff[FN_REFLEN];
+  TABLE_LIST dummy;
+
+  DBUG_ENTER("mysql_load_view_md5");
+  DBUG_PRINT("info", ("view: (%s.%s)", view->db, view->table_name));
+
+  bzero(& dummy, sizeof(dummy));
+
+  pathstr.str= (char *) path_buff;
+  pathstr.length= build_table_filename(path_buff, sizeof(path_buff) - 1,
+                                       view->db, view->table_name,
+                                       reg_ext, 0);
+
+  parser= sql_parse_prepare(&pathstr, thd->mem_root, 0);
+  if (parser &&
+      parser->ok() &&
+      is_equal(&view_type, parser->type()) &&
+      parser->parse((uchar*) &dummy,
+                    thd->mem_root,
+                    view_md5_parameters,
+                    array_elements(view_md5_parameters)-1,
+                    &file_parser_dummy_hook) == 0)
+  {
+    view->md5.str= dummy.md5.str;
+    view->md5.length= dummy.md5.length;
+    rc= 0;
+  }
+  else
+    rc= 1;
+
+  DBUG_RETURN(rc);
 }
 
 
diff -Nrup a/sql/sql_view.h b/sql/sql_view.h
--- a/sql/sql_view.h	2006-12-23 12:19:56 -07:00
+++ b/sql/sql_view.h	2007-12-03 20:05:57 -07:00
@@ -31,6 +31,8 @@ frm_type_enum mysql_frm_type(THD *thd, c
 
 int view_checksum(THD *thd, TABLE_LIST *view);
 
+int mysql_load_view_md5(THD *thd, TABLE_LIST *view);
+
 extern TYPELIB updatable_views_with_limit_typelib;
 
 bool check_duplicate_names(List<Item>& item_list, bool gen_unique_view_names);
diff -Nrup a/sql/table.cc b/sql/table.cc
--- a/sql/table.cc	2007-11-15 12:25:41 -07:00
+++ b/sql/table.cc	2007-12-03 20:05:57 -07:00
@@ -20,6 +20,7 @@
 #include "sql_trigger.h"
 #include <m_ctype.h>
 #include "my_md5.h"
+#include "bostream.h"
 
 /* INFORMATION_SCHEMA name */
 LEX_STRING INFORMATION_SCHEMA_NAME= {C_STRING_WITH_LEN("information_schema")};
@@ -250,6 +251,41 @@ TABLE_CATEGORY get_table_category(const 
   return TABLE_CATEGORY_USER;
 }
 
+int TABLE_SHARE::make_object_version(Object_version * version)
+{
+  int rc;
+
+  rc= version->m_oid.set_table(db, table_name);
+
+  if ((tmp_table == INTERNAL_TMP_TABLE) ||
+      (tmp_table == NON_TRANSACTIONAL_TMP_TABLE))
+  {
+    /*
+      table_map_id is THD::query_id.
+      The version number is not persistent,
+      but the table is not persistent either,
+      so using table_map_id is ok here.
+    */
+    version->m_vid.set_long(table_map_id);
+  }
+  else
+  {
+    version->m_vid.set_md5(m_md5_value);
+  }
+
+  return rc;
+}
+
+int TABLE_SHARE::make_object_null_version(
+  Object_version * version, const char* db, const char* name)
+{
+  int rc;
+
+  rc= version->m_oid.set_table(db, name);
+  version->m_vid.set_null();
+
+  return rc;
+}
 
 /*
   Allocate a setup TABLE_SHARE structure
@@ -323,6 +359,176 @@ TABLE_SHARE *alloc_table_share(TABLE_LIS
   DBUG_RETURN(share);
 }
 
+static size_t serialized_field_size(Field *field)
+{
+  size_t size;
+
+  DBUG_ASSERT(field != NULL);
+  DBUG_ASSERT(field->field_name != NULL);
+
+  size= sizeof(Field) + strlen(field->field_name) + 1;
+
+  return size;
+}
+
+static void serialize_field(Buffer_output_stream *buff, Field *field)
+{
+  DBUG_ASSERT(field != NULL);
+
+  buff->write_string(field->field_name);
+  buff->write_uint((uint) field->unireg_check);
+  buff->write_uint(field->field_length);
+  buff->write_uint(field->flags);
+  buff->write_uint(field->type());
+}
+
+static size_t serialized_key_part_size(KEY_PART_INFO *key_part)
+{
+  size_t size;
+
+  DBUG_ASSERT(key_part != NULL);
+  DBUG_ASSERT(key_part->field != NULL);
+  DBUG_ASSERT(key_part->field->field_name != NULL);
+
+  size= sizeof(KEY_PART_INFO) + strlen(key_part->field->field_name) + 1;
+
+  return size;
+}
+
+static void serialize_key_part(Buffer_output_stream *buff,
+                               KEY_PART_INFO *key_part)
+{
+  DBUG_ASSERT(key_part != NULL);
+  DBUG_ASSERT(key_part->field != NULL);
+  DBUG_ASSERT(key_part->field->field_name != NULL);
+
+  buff->write_uint(key_part->offset);
+  buff->write_uint(key_part->null_offset);
+  buff->write_uint16(key_part->length);
+  buff->write_uint16(key_part->store_length);
+  buff->write_uint16(key_part->key_type);
+  buff->write_uint16(key_part->fieldnr);
+  buff->write_uint16(key_part->key_part_flag);
+  buff->write_uint(key_part->type);
+  buff->write_uint(key_part->null_bit);
+
+  buff->write_string(key_part->field->field_name);
+}
+
+static size_t serialized_key_size(KEY *key)
+{
+  KEY_PART_INFO *key_part;
+  uint i;
+  size_t size;
+
+  size= sizeof(KEY) + strlen(key->name) + 1;
+
+  for (i=0, key_part= key->key_part; i < key->key_parts; i++, key_part++)
+  {
+    size+= serialized_key_part_size(key_part);
+  }
+
+  return size;
+}
+
+static void serialize_key(Buffer_output_stream *buff, KEY *key)
+{
+  KEY_PART_INFO *key_part;
+  uint i;
+
+  buff->write_uint(key->key_length);
+  buff->write_uint(key->flags);
+  buff->write_uint(key->key_parts);
+  buff->write_uint(key->extra_length);
+  buff->write_uint(key->usable_key_parts);
+  buff->write_uint(key->block_size);
+  buff->write_uint((uint) key->algorithm);
+  buff->write_string(key->name);
+
+  for (i=0, key_part= key->key_part; i < key->key_parts; i++, key_part++)
+  {
+    serialize_key_part(buff, key_part);
+  }
+}
+
+int compute_table_share_md5(TABLE_SHARE *share)
+{
+  my_MD5_CTX context;
+  uchar digest[16];
+  size_t size;
+  uint i;
+  Field **field_ptr;
+  Field *field;
+  KEY  *key_info;
+  Buffer_output_stream serialized_share;
+  void *buffer;
+  handlerton *engine;
+
+  DBUG_ENTER("compute_table_share_md5");
+
+  if (share->is_view)
+    DBUG_RETURN(0);
+
+  size= sizeof(TABLE_SHARE)
+   + share->db.length
+   + share->table_name.length
+   + share->normalized_path.length
+   + share->connect_string.length;
+
+  for (i=0, field_ptr= share->field ; i<share->fields ; i++, field_ptr++)
+  {
+    field= *field_ptr;
+    size+= serialized_field_size(field);
+  }
+
+  for (i=0, key_info= share->key_info; i < share->keys; i++, key_info++)
+  {
+    size+= serialized_key_size(key_info);
+  }
+
+  if (serialized_share.init(& share->mem_root, size))
+    DBUG_RETURN(1);
+
+  serialized_share.write_LEX_STRING(& share->db);
+  serialized_share.write_LEX_STRING(& share->table_name);
+  serialized_share.write_LEX_STRING(& share->normalized_path);
+  serialized_share.write_LEX_STRING(& share->connect_string);
+  serialized_share.write_uint(share->fields);
+
+  engine= share->db_type();
+  DBUG_ASSERT(engine);
+  serialized_share.write_uint(engine->db_type);
+
+  for (i=0, field_ptr= share->field ; i<share->fields ; i++, field_ptr++)
+  {
+    field= *field_ptr;
+    serialize_field(& serialized_share, field);
+  }
+
+  for (i=0, key_info= share->key_info; i < share->keys; i++, key_info++)
+  {
+    serialize_key(& serialized_share, key_info);
+  }
+
+  DBUG_ASSERT(! serialized_share.get_error());
+
+  buffer= serialized_share.get_buffer();
+  size= serialized_share.get_size();
+
+  my_MD5Init(&context);
+  my_MD5Update(&context,(uchar *) buffer, size);
+  my_MD5Final(digest, &context);
+
+  sprintf(share->m_md5_value,
+          "%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x",
+          digest[0], digest[1], digest[2], digest[3],
+          digest[4], digest[5], digest[6], digest[7],
+          digest[8], digest[9], digest[10], digest[11],
+          digest[12], digest[13], digest[14], digest[15]);
+
+  DBUG_RETURN(0);
+}
+
 
 /*
   Initialize share for temporary tables
@@ -2928,6 +3134,28 @@ void st_table::reset_item_list(List<Item
     DBUG_ASSERT(item_field != 0);
     item_field->reset_field(*ptr);
   }
+}
+
+int TABLE_LIST::make_object_version(Object_version * version)
+{
+  int rc;
+
+  DBUG_ASSERT(view);
+  rc= version->m_oid.set_view(view_db, view_name);
+  version->m_vid.set_md5(md5.str);
+
+  return rc;
+}
+
+int TABLE_LIST::make_object_null_version(
+  Object_version * version, const char* db, const char* name)
+{
+  int rc;
+
+  rc= version->m_oid.set_view(db, name);
+  version->m_vid.set_null();
+
+  return rc;
 }
 
 /*
diff -Nrup a/sql/table.h b/sql/table.h
--- a/sql/table.h	2007-11-15 12:25:41 -07:00
+++ b/sql/table.h	2007-12-03 20:05:57 -07:00
@@ -24,6 +24,7 @@ class st_select_lex;
 class partition_info;
 class COND_EQUAL;
 class Security_context;
+class Object_version;
 
 /*************************************************************************/
 
@@ -85,15 +86,6 @@ enum tmp_table_type
   INTERNAL_TMP_TABLE, SYSTEM_TMP_TABLE
 };
 
-/** Event on which trigger is invoked. */
-enum trg_event_type
-{
-  TRG_EVENT_INSERT= 0,
-  TRG_EVENT_UPDATE= 1,
-  TRG_EVENT_DELETE= 2,
-  TRG_EVENT_MAX
-};
-
 enum frm_type_enum
 {
   FRMTYPE_ERROR= 0,
@@ -350,6 +342,7 @@ typedef struct st_table_share
   ulong table_map_id;                   /* for row-based replication */
   ulonglong table_map_version;
 
+  char m_md5_value[33];
   /*
     Cache for row-based replication table share checks that does not
     need to be repeated. Possible values are: -1 when cache value is
@@ -437,6 +430,9 @@ typedef struct st_table_share
     return table_map_id;
   }
 
+  int make_object_version(Object_version * version);
+  static int make_object_null_version(
+    Object_version * version, const char* db, const char* name);
 } TABLE_SHARE;
 
 
@@ -892,6 +888,10 @@ class Index_hint;
 struct TABLE_LIST
 {
   TABLE_LIST() {}                          /* Remove gcc warning */
+
+  int make_object_version(Object_version * version);
+  static int make_object_null_version(
+    Object_version * version, const char* db, const char* name);
 
   /**
     Prepare TABLE_LIST that consists of one table instance to use in
diff -Nrup a/tests/mysql_client_test.c b/tests/mysql_client_test.c
--- a/tests/mysql_client_test.c	2007-11-14 06:28:21 -07:00
+++ b/tests/mysql_client_test.c	2007-12-03 20:05:58 -07:00
@@ -5771,10 +5771,10 @@ static void test_prepare_alter()
 
   is_null= 1;
   rc= mysql_stmt_execute(stmt);
-  check_execute(stmt, rc);
+  check_execute_r(stmt, rc); // ER_PS_INVALIDATED
 
   rc= my_stmt_result("SELECT * FROM test_prep_alter");
-  DIE_UNLESS(rc == 4);
+  DIE_UNLESS(rc == 3);
 
   mysql_stmt_close(stmt);
 }
@@ -14354,34 +14354,88 @@ static void test_bug10760()
   }
   else
   {
+    bzero(my_bind, sizeof(my_bind));
+    my_bind[0].buffer_type= MYSQL_TYPE_STRING;
+    my_bind[0].buffer= (void*) id_buf;
+    my_bind[0].buffer_length= sizeof(id_buf);
+    my_bind[0].length= &id_len;
+
     stmt_text= "select id from t1 order by 1";
     rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
     check_execute(stmt, rc);
 
+    mysql_stmt_bind_result(stmt, my_bind);
+    check_execute(stmt, rc);
+
+    rc= mysql_stmt_execute(stmt);
+    check_execute(stmt, rc);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (before alter) row %s\n", id_buf);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (before alter) row %s\n", id_buf);
+
     rc= mysql_query(mysql, "alter table t1 engine=InnoDB");
     myquery(rc);
 
-    bzero(my_bind, sizeof(my_bind));
-    my_bind[0].buffer_type= MYSQL_TYPE_STRING;
-    my_bind[0].buffer= (void*) id_buf;
-    my_bind[0].buffer_length= sizeof(id_buf);
-    my_bind[0].length= &id_len;
+    rc= mysql_stmt_fetch(stmt);
     check_execute(stmt, rc);
-    mysql_stmt_bind_result(stmt, my_bind);
+    if (!opt_silent)
+      printf("Fetched (after alter) row %s\n", id_buf);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (after alter) row %s\n", id_buf);
 
     rc= mysql_stmt_execute(stmt);
+    check_execute_r(stmt, rc); // ER_PS_INVALIDATED
+
     rc= mysql_stmt_fetch(stmt);
+    check_execute_r(stmt, rc); // CR_NO_RESULT_SET
+
+    stmt_text= "select id from t1 order by 1";
+    rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
+    check_execute(stmt, rc);
+
+    mysql_stmt_bind_result(stmt, my_bind);
+    check_execute(stmt, rc);
+
+    rc= mysql_query(mysql, "update t1 set id=id+100");
     DIE_UNLESS(rc == 0);
+
+    rc= mysql_stmt_execute(stmt);
+    check_execute(stmt, rc);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (before rollback) row %s\n", id_buf);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
     if (!opt_silent)
-      printf("Fetched row %s\n", id_buf);
-    rc= mysql_rollback(mysql);                  /* should close the cursor */
+      printf("Fetched (before rollback) row %s\n", id_buf);
+
+    /* should close the cursor ... */
+    rc= mysql_rollback(mysql);
     myquery(rc);
-#if 0
+
+    /* ... but did not. */
     rc= mysql_stmt_fetch(stmt);
-    DIE_UNLESS(rc);
+    check_execute(stmt, rc);
     if (!opt_silent)
-      printf("Got error (as expected): %s\n", mysql_error(mysql));
-#endif
+      printf("Fetched (after rollback) row %s\n", id_buf);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (after rollback) row %s\n", id_buf);
   }
 
   mysql_stmt_close(stmt);
Thread
bk commit into 5.1 tree (malff:1.2671) BUG#12093marc.alff4 Dec