diff options
| -rw-r--r-- | mysql-test/r/ps_ddl.result | 66 | ||||
| -rw-r--r-- | mysql-test/r/ps_ddl1.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/sp-error.result | 5 | ||||
| -rw-r--r-- | mysql-test/t/ps_ddl.test | 48 | ||||
| -rw-r--r-- | mysql-test/t/ps_ddl1.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/sp-error.test | 4 | ||||
| -rw-r--r-- | sql/sp_cache.cc | 13 | ||||
| -rw-r--r-- | sql/sp_cache.h | 1 | ||||
| -rw-r--r-- | sql/sql_prepare.cc | 33 |
9 files changed, 111 insertions, 63 deletions
diff --git a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result index a47c01cca17..58542f23cf2 100644 --- a/mysql-test/r/ps_ddl.result +++ b/mysql-test/r/ps_ddl.result @@ -290,7 +290,7 @@ SUCCESS # Test 7-b: dependent FUNCTION has changed # -# Note, this scenario is not supported, subject of Bug#12093 +# Note, this scenario is supported, subject of Bug#12093 # drop trigger t1_ai; create trigger t1_ai after insert on t1 for each row @@ -305,8 +305,7 @@ select @var; drop function f1; create function f1 (a int) returns int return 0; execute stmt using @var; -ERROR 42000: FUNCTION test.f1 does not exist -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); SUCCESS drop function f1; @@ -359,8 +358,14 @@ a drop view v1; create view v1 as select a from t2; set @var=8; +# XXX: bug, the SQL statement in the trigger is still +# pointing at table 't3', since the view was expanded +# at first statement execution. +# Repreparation of the main statement doesn't cause repreparation +# of trigger statements. execute stmt using @var; -call p_verify_reprepare_count(0); +ERROR 42S02: Table 'test.t3' doesn't exist +call p_verify_reprepare_count(1); SUCCESS # @@ -377,7 +382,6 @@ select * from t3; a 6 7 -8 flush table t1; set @var=9; execute stmt using @var; @@ -392,7 +396,6 @@ select * from t3; a 6 7 -8 drop view v1; drop table t1,t2,t3; # Test 7-d: dependent TABLE has changed @@ -798,14 +801,17 @@ SUCCESS drop function f1; create function f1() returns int return 2; -# XXX: Bug#12093. We only get a different error +# XXX: Used to be another manifestation of Bug#12093. +# We only used to get a different error # message because the non-existing procedure error is masked # by the view. execute stmt; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +f1() +2 execute stmt; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -call p_verify_reprepare_count(0); +f1() +2 +call p_verify_reprepare_count(1); SUCCESS # Part 18b: dependent procedure has changed (referred to via a function) @@ -831,19 +837,20 @@ SUCCESS drop procedure p1; create procedure p1(out x int) select max(a) from t2 into x; -# XXX: bug. The prelocked list is not invalidated -# and we keep opening table t1, whereas the procedure +# XXX: used to be a bug. The prelocked list was not invalidated +# and we kept opening table t1, whereas the procedure # is now referring to table t2 execute stmt; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -call p_verify_reprepare_count(0); +f1() +6 +call p_verify_reprepare_count(1); SUCCESS flush table t1; execute stmt; f1() 6 -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); SUCCESS execute stmt; @@ -1528,7 +1535,6 @@ drop view v_27690_1; drop table v_27690_2; deallocate prepare stmt; #===================================================================== -# TODO: fix the below two bugs and modify their tests # # Bug#21294 Executing a prepared statement that executes # a stored function which was recreat @@ -1541,12 +1547,14 @@ f1() drop function f1; create function f1() returns int return 10; execute stmt; -ERROR 42000: FUNCTION test.f1 does not exist +f1() +10 drop function f1; create function f1() returns int return 20; execute stmt; -ERROR 42000: FUNCTION test.f1 does not exist -call p_verify_reprepare_count(0); +f1() +20 +call p_verify_reprepare_count(2); SUCCESS drop function f1; @@ -1573,19 +1581,21 @@ execute stmt_sp; a drop function f_12093_unrelated; drop procedure p_12093_unrelated; -# XXX: bug +# XXX: used to be a bug execute stmt_sf; -ERROR 42000: FUNCTION test.f_12093 does not exist -# XXX: bug +f_12093() +0 +# XXX: used to be a bug execute stmt_sp; -ERROR 42000: PROCEDURE test.p_12093 does not exist -# XXX: bug +a +# XXX: used to be a bug execute stmt_sf; -ERROR 42000: FUNCTION test.f_12093 does not exist -# XXX: bug +f_12093() +0 +# XXX: used to be a bug execute stmt_sp; -ERROR 42000: PROCEDURE test.p_12093 does not exist -call p_verify_reprepare_count(0); +a +call p_verify_reprepare_count(2); SUCCESS drop table t_12093; diff --git a/mysql-test/r/ps_ddl1.result b/mysql-test/r/ps_ddl1.result index 87abcd90590..e41a72ceb96 100644 --- a/mysql-test/r/ps_ddl1.result +++ b/mysql-test/r/ps_ddl1.result @@ -460,7 +460,7 @@ create schema mysqltest; end| execute stmt; ERROR 42000: PROCEDURE test.p1 does not exist -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); SUCCESS execute stmt; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 5ac61821cea..cc383d003e9 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -796,7 +796,7 @@ bug11834_2() 10 drop function bug11834_1; execute stmt; -ERROR 42000: FUNCTION test.bug11834_2 does not exist +ERROR 42000: FUNCTION test.bug11834_1 does not exist deallocate prepare stmt; drop function bug11834_2; DROP FUNCTION IF EXISTS bug12953| @@ -1045,7 +1045,8 @@ select bug12329(); bug12329() 101 execute stmt1; -ERROR 42S02: Table 'test.t2' doesn't exist +bug12329() +101 deallocate prepare stmt1; drop function bug12329; drop table t1, t2; diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test index cbfdaaffa0f..8899ca967ca 100644 --- a/mysql-test/t/ps_ddl.test +++ b/mysql-test/t/ps_ddl.test @@ -299,7 +299,7 @@ call p_verify_reprepare_count(0); --echo # Test 7-b: dependent FUNCTION has changed --echo # ---echo # Note, this scenario is not supported, subject of Bug#12093 +--echo # Note, this scenario is supported, subject of Bug#12093 --echo # drop trigger t1_ai; create trigger t1_ai after insert on t1 for each row @@ -311,9 +311,8 @@ execute stmt using @var; select @var; drop function f1; create function f1 (a int) returns int return 0; ---error ER_SP_DOES_NOT_EXIST execute stmt using @var; -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); drop function f1; deallocate prepare stmt; @@ -353,8 +352,14 @@ select * from t2; drop view v1; create view v1 as select a from t2; set @var=8; +--echo # XXX: bug, the SQL statement in the trigger is still +--echo # pointing at table 't3', since the view was expanded +--echo # at first statement execution. +--echo # Repreparation of the main statement doesn't cause repreparation +--echo # of trigger statements. +--error ER_NO_SUCH_TABLE execute stmt using @var; -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); --echo # --echo # Sic: the insert went into t3, even though the view now --echo # points at t2. This is because neither the merged view @@ -703,14 +708,13 @@ execute stmt; call p_verify_reprepare_count(0); drop function f1; create function f1() returns int return 2; ---echo # XXX: Bug#12093. We only get a different error +--echo # XXX: Used to be another manifestation of Bug#12093. +--echo # We only used to get a different error --echo # message because the non-existing procedure error is masked --echo # by the view. ---error ER_VIEW_INVALID execute stmt; ---error ER_VIEW_INVALID execute stmt; -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); --echo # Part 18b: dependent procedure has changed (referred to via a function) @@ -734,15 +738,14 @@ execute stmt; call p_verify_reprepare_count(0); drop procedure p1; create procedure p1(out x int) select max(a) from t2 into x; ---echo # XXX: bug. The prelocked list is not invalidated ---echo # and we keep opening table t1, whereas the procedure +--echo # XXX: used to be a bug. The prelocked list was not invalidated +--echo # and we kept opening table t1, whereas the procedure --echo # is now referring to table t2 ---error ER_VIEW_INVALID execute stmt; -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); flush table t1; execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); execute stmt; --echo # Test 18-c: dependent VIEW has changed @@ -1326,7 +1329,6 @@ drop table v_27690_2; deallocate prepare stmt; --echo #===================================================================== ---echo # TODO: fix the below two bugs and modify their tests --echo # --echo # Bug#21294 Executing a prepared statement that executes --echo # a stored function which was recreat @@ -1341,15 +1343,13 @@ drop function f1; create function f1() returns int return 10; # might pass or fail, implementation dependent ---error ER_SP_DOES_NOT_EXIST execute stmt; drop function f1; create function f1() returns int return 20; ---error ER_SP_DOES_NOT_EXIST execute stmt; -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(2); drop function f1; deallocate prepare stmt; @@ -1388,20 +1388,16 @@ drop procedure p_12093_unrelated; connection default; ---echo # XXX: bug ---error ER_SP_DOES_NOT_EXIST +--echo # XXX: used to be a bug execute stmt_sf; ---echo # XXX: bug ---error ER_SP_DOES_NOT_EXIST +--echo # XXX: used to be a bug execute stmt_sp; ---echo # XXX: bug ---error ER_SP_DOES_NOT_EXIST +--echo # XXX: used to be a bug execute stmt_sf; ---echo # XXX: bug ---error ER_SP_DOES_NOT_EXIST +--echo # XXX: used to be a bug execute stmt_sp; -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(2); disconnect con1; diff --git a/mysql-test/t/ps_ddl1.test b/mysql-test/t/ps_ddl1.test index 0145d445a14..379ed576b5f 100644 --- a/mysql-test/t/ps_ddl1.test +++ b/mysql-test/t/ps_ddl1.test @@ -363,7 +363,7 @@ end| delimiter ;| --error ER_SP_DOES_NOT_EXIST execute stmt; -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); --error ER_SP_DOES_NOT_EXIST execute stmt; call p_verify_reprepare_count(0); diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 6896a1ab832..1e329f7fe79 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1470,10 +1470,6 @@ 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 execute stmt1; deallocate prepare stmt1; drop function bug12329; diff --git a/sql/sp_cache.cc b/sql/sp_cache.cc index cc6ba9ef1d0..64898915b7e 100644 --- a/sql/sp_cache.cc +++ b/sql/sp_cache.cc @@ -210,6 +210,19 @@ void sp_cache_flush_obsolete(sp_cache **cp) } +/** + Return the current version of the cache. +*/ + +ulong sp_cache_version(sp_cache **cp) +{ + sp_cache *c= *cp; + if (c) + return c->version; + return 0; +} + + /************************************************************************* Internal functions *************************************************************************/ diff --git a/sql/sp_cache.h b/sql/sp_cache.h index 9d34c9a2fb5..f4d44a1f29f 100644 --- a/sql/sp_cache.h +++ b/sql/sp_cache.h @@ -58,5 +58,6 @@ void sp_cache_insert(sp_cache **cp, sp_head *sp); sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name); void sp_cache_invalidate(); void sp_cache_flush_obsolete(sp_cache **cp); +ulong sp_cache_version(sp_cache **cp); #endif /* _SP_CACHE_H_ */ diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 73ab28a233c..16e4812655e 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -169,6 +169,8 @@ private: SELECT_LEX and other classes). */ MEM_ROOT main_mem_root; + /* Version of the stored functions cache at the time of prepare. */ + ulong m_sp_cache_version; private: bool set_db(const char *db, uint db_length); bool set_parameters(String *expanded_query, @@ -2819,7 +2821,8 @@ Prepared_statement::Prepared_statement(THD *thd_arg, Protocol *protocol_arg) param_array(0), param_count(0), last_errno(0), - flags((uint) IS_IN_USE) + flags((uint) IS_IN_USE), + m_sp_cache_version(0) { init_sql_alloc(&main_mem_root, thd_arg->variables.query_alloc_block_size, thd_arg->variables.query_prealloc_size); @@ -3072,6 +3075,20 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len) init_stmt_after_parse(lex); state= Query_arena::PREPARED; flags&= ~ (uint) IS_IN_USE; + /* + This is for prepared statement validation purposes. + A statement looks up and pre-loads all its stored functions + at prepare. Later on, if a function is gone from the cache, + execute may fail. + Remember the cache version to be able to invalidate the prepared + statement at execute if it changes. + We only need to care about version of the stored functions cache: + if a prepared statement uses a stored procedure, it's indirect, + via a stored function. The only exception is SQLCOM_CALL, + but the latter one looks up the stored procedure each time + it's invoked, rather than once at prepare. + */ + m_sp_cache_version= sp_cache_version(&thd->sp_func_cache); /* Log COM_EXECUTE to the general log. Note, that in case of SQL @@ -3383,6 +3400,7 @@ Prepared_statement::swap_prepared_statement(Prepared_statement *copy) swap_variables(LEX_STRING, name, copy->name); /* Ditto */ swap_variables(char *, db, copy->db); + swap_variables(ulong, m_sp_cache_version, copy->m_sp_cache_version); DBUG_ASSERT(db_length == copy->db_length); DBUG_ASSERT(param_count == copy->param_count); @@ -3443,6 +3461,19 @@ bool Prepared_statement::execute(String *expanded_query, bool open_cursor) } /* + Reprepare the statement if we're using stored functions + and the version of the stored routines cache has changed. + */ + if (lex->uses_stored_routines() && + m_sp_cache_version != sp_cache_version(&thd->sp_func_cache) && + thd->m_reprepare_observer && + thd->m_reprepare_observer->report_error(thd)) + { + return TRUE; + } + + + /* For SHOW VARIABLES lex->result is NULL, as it's a non-SELECT command. For such queries we don't return an error and don't open a cursor -- the client library will recognize this case and |