diff options
Diffstat (limited to 'mysql-test/main/sp_notembedded.test')
| -rw-r--r-- | mysql-test/main/sp_notembedded.test | 491 |
1 files changed, 491 insertions, 0 deletions
diff --git a/mysql-test/main/sp_notembedded.test b/mysql-test/main/sp_notembedded.test new file mode 100644 index 00000000..12bacff4 --- /dev/null +++ b/mysql-test/main/sp_notembedded.test @@ -0,0 +1,491 @@ +# Can't test with embedded server +-- source include/not_embedded.inc +# Disable concurrent inserts to avoid test failures +set @old_concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert= 0; + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--disable_warnings +drop table if exists t1,t3; +--enable_warnings +delimiter |; + + +# +# Bug#4902 Stored procedure with SHOW WARNINGS leads to packet error +# +# Added tests for show grants command +--disable_warnings +drop procedure if exists bug4902| +--enable_warnings +create procedure bug4902() +begin + show grants for 'root'@'localhost'; +end| +--disable_parsing +show binlog events| +show storage engines| +show master status| +show slave hosts| +show slave status| +--enable_parsing + +call bug4902()| +call bug4902()| + +drop procedure bug4902| + +# We need separate SP for SHOW PROCESSLIST since we want use replace_column +--disable_warnings +drop procedure if exists bug4902_2| +--enable_warnings +create procedure bug4902_2() +begin + show processlist; +end| +--disable_result_log +call bug4902_2()| +--enable_result_log +show warnings| +--disable_result_log +call bug4902_2()| +--enable_result_log +show warnings| +drop procedure bug4902_2| + +# +# Bug#6807 Stored procedure crash if CREATE PROCEDURE ... KILL QUERY +# +--disable_warnings +drop procedure if exists bug6807| +--enable_warnings +create procedure bug6807() +begin + declare a int; + + set a = connection_id(); + kill query a; + select 'Not reached'; +end| + +--error ER_QUERY_INTERRUPTED +call bug6807()| +--error ER_QUERY_INTERRUPTED +call bug6807()| + +drop procedure bug6807| + + +# +# Bug#10100 function (and stored procedure?) recursivity problem +# +--disable_warnings +drop function if exists bug10100f| +drop procedure if exists bug10100p| +drop procedure if exists bug10100t| +drop procedure if exists bug10100pt| +drop procedure if exists bug10100pv| +drop procedure if exists bug10100pd| +drop procedure if exists bug10100pc| +--enable_warnings +# routines with simple recursion +create function bug10100f(prm int) returns int +begin + if prm > 1 then + return prm * bug10100f(prm - 1); + end if; + return 1; +end| +set statement sql_mode = '' for +create procedure bug10100p(prm int, inout res int) +begin + set res = res * prm; + if prm > 1 then + call bug10100p(prm - 1, res); + end if; +end| +set statement sql_mode = '' for +create procedure bug10100t(prm int) +begin + declare res int; + set res = 1; + call bug10100p(prm, res); + select res; +end| + +# a procedure which use tables and recursion +create table t3 (a int)| +insert into t3 values (0)| +create view v1 as select a from t3| +create procedure bug10100pt(level int, lim int) +begin + if level < lim then + update t3 set a=level; + FLUSH TABLES; + call bug10100pt(level+1, lim); + else + select * from t3; + end if; +end| +# view & recursion +create procedure bug10100pv(level int, lim int) +begin + if level < lim then + update v1 set a=level; + FLUSH TABLES; + call bug10100pv(level+1, lim); + else + select * from v1; + end if; +end| +# dynamic sql & recursion +prepare stmt2 from "select * from t3;"; +create procedure bug10100pd(level int, lim int) +begin + if level < lim then + select level; + prepare stmt1 from "update t3 set a=a+2"; + execute stmt1; + FLUSH TABLES; + execute stmt1; + FLUSH TABLES; + execute stmt1; + FLUSH TABLES; + deallocate prepare stmt1; + execute stmt2; + select * from t3; + call bug10100pd(level+1, lim); + else + execute stmt2; + end if; +end| +# cursor & recursion +create procedure bug10100pc(level int, lim int) +begin + declare lv int; + declare c cursor for select a from t3; + open c; + if level < lim then + select level; + fetch c into lv; + select lv; + update t3 set a=level+lv; + FLUSH TABLES; + call bug10100pc(level+1, lim); + else + select * from t3; + end if; + close c; +end| + +# end of the stack checking +set @@max_sp_recursion_depth=255| +set @var=1| +# disable log because error about stack overrun contains numbers which +# depend on a system +-- disable_ps_protocol +-- disable_result_log +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100p(255, @var)| +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100pt(1,255)| +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100pv(1,255)| +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100pd(1,255)| +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100pc(1,255)| +-- enable_result_log +-- enable_ps_protocol +set @@max_sp_recursion_depth=0| + +deallocate prepare stmt2| + +drop function bug10100f| +drop procedure bug10100p| +drop procedure bug10100t| +drop procedure bug10100pt| +drop procedure bug10100pv| +drop procedure bug10100pd| +drop procedure bug10100pc| +drop view v1| +drop table t3| + +delimiter ;| + + +# +# Bug#15298 SHOW GRANTS FOR CURRENT_USER: Incorrect output in DEFINER context +# +--disable_warnings +drop procedure if exists bug15298_1; +drop procedure if exists bug15298_2; +--enable_warnings +create user 'mysqltest_1'@'localhost'; +grant all privileges on test.* to 'mysqltest_1'@'localhost'; +create procedure 15298_1 () sql security definer show grants for current_user; +create procedure 15298_2 () sql security definer show grants; + +connect (con1,localhost,mysqltest_1,,test); +call 15298_1(); +call 15298_2(); + +connection default; +disconnect con1; +drop user mysqltest_1@localhost; +drop procedure 15298_1; +drop procedure 15298_2; + +# +# Bug#29936 Stored Procedure DML ignores low_priority_updates setting +# + +--disable_warnings +drop table if exists t1; +drop procedure if exists p1; +--enable_warnings + +create table t1 (value varchar(15)); +create procedure p1() update t1 set value='updated' where value='old'; + +# load the procedure into sp cache and execute once +call p1(); + +insert into t1 (value) values ("old"),("irrelevant"); + +connect (rl_holder, localhost, root,,); +connect (rl_acquirer, localhost, root,,); +connect (rl_contender, localhost, root,,); +connect (rl_wait, localhost, root,,); + +connection rl_holder; +select get_lock('b26162',120); + +connection rl_acquirer; +--send select 'rl_acquirer', value from t1 where get_lock('b26162',120); + +# we must wait till this select opens and locks the tables +connection rl_wait; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "User lock" and + info = "select 'rl_acquirer', value from t1 where get_lock('b26162',120)"; +--source include/wait_condition.inc + +connection default; +set session low_priority_updates=on; +--send call p1(); + +connection rl_wait; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set value='updated' where value='old'"; +--source include/wait_condition.inc + +connection rl_contender; +select 'rl_contender', value from t1; + +connection rl_holder; +select release_lock('b26162'); + +connection rl_acquirer; +--reap +connection default; +--reap + +disconnect rl_holder; +disconnect rl_acquirer; +disconnect rl_wait; +disconnect rl_contender; +drop procedure p1; +drop table t1; +set session low_priority_updates=default; + +# +# Bug#44798 MySQL engine crashes when creating stored procedures with execute_priv=N +# +--source include/switch_to_mysql_user.inc +INSERT IGNORE INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, +Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, +Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, +Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, +Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, +Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, +max_updates, max_connections, max_user_connections) +VALUES('%', 'mysqltest_1', password(''), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', +'N', 'N', 'N', 'Y', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y', 'Y', 'N', '', +'', '', '', '0', '0', '0', '0'); +FLUSH PRIVILEGES; + +connect (con1, localhost, mysqltest_1,,); +connection con1; +CREATE PROCEDURE p1(i INT) BEGIN END; +disconnect con1; +connection default; +DROP PROCEDURE p1; +--source include/switch_to_mysql_global_priv.inc + +--echo # +--echo # Bug#44521 Prepared Statement: CALL p() - crashes: `! thd->main_da.is_sent' failed et.al. +--echo # +SELECT GET_LOCK('Bug44521', 0); +--connect (con1,localhost,root,,) +delimiter $; +CREATE PROCEDURE p() +BEGIN + SELECT 1; + SELECT GET_LOCK('Bug44521', 100); + SELECT 2; +END$ +delimiter ;$ +--send CALL p(); +--connection default +let $wait_condition= + SELECT count(*) = 1 FROM information_schema.processlist + WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)"; +--source include/wait_condition.inc +let $conid = + `SELECT id FROM information_schema.processlist + WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)"`; +dirty_close con1; +SELECT RELEASE_LOCK('Bug44521'); +let $wait_condition= + SELECT count(*) = 0 FROM information_schema.processlist + WHERE id = $conid; +--source include/wait_condition.inc +DROP PROCEDURE p; + +# +# Bug#47736 killing a select from a view when the view is processing a function, asserts +# +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1); +CREATE FUNCTION f1 (inp TEXT) RETURNS INT NO SQL RETURN sleep(60); +CREATE VIEW v1 AS SELECT f1('a') FROM t1; + +--connect (con1, localhost, root,,) +--let $ID_1= `SELECT connection_id()` +--send SELECT * FROM v1; + +--connect (con2, localhost, root,,) +--let $ID_2= `SELECT connection_id()` +--send SELECT * FROM v1 + +--connection default +let $wait_condition= + select count(*) = 2 from information_schema.processlist where state = "User sleep"; +--source include/wait_condition.inc +--disable_query_log +--eval KILL QUERY $ID_2 +--eval KILL QUERY $ID_1 +--enable_query_log + +--connection con1 +--error ER_QUERY_INTERRUPTED +--reap +--connection con2 +--error ER_QUERY_INTERRUPTED +--reap + +--connection default +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION f1; +--disconnect con1 +--disconnect con2 + +--echo # ------------------------------------------------------------------ +--echo # -- End of 5.1 tests +--echo # ------------------------------------------------------------------ + +--echo # +--echo # Test for bug#11763757 "56510: ERROR 42000: FUNCTION DOES NOT EXIST +--echo # IF NOT-PRIV USER RECONNECTS ". +--echo # +--echo # The real problem was that server was unable handle properly stored +--echo # functions in databases which names contained dot. +--echo # + +connection default; + +--disable_warnings +DROP DATABASE IF EXISTS `my.db`; +--enable_warnings + +create database `my.db`; +use `my.db`; + +CREATE FUNCTION f1(a int) RETURNS INT RETURN a; + +connect (addcon, localhost, root,,); +connection addcon; +USE `my.db`; +SELECT f1(1); +SELECT `my.db`.f1(2); + +connection default; +disconnect addcon; +DROP DATABASE `my.db`; +USE test; + + +--echo # +--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE +--echo # +SET @@SQL_MODE = ''; +DELIMITER $; + +CREATE EVENT teste_bug11763507 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR +DO SELECT 1 $ + +DELIMITER ;$ +# EVENTS +--replace_column 6 # 7 # 8 # 9 # +SHOW EVENTS LIKE 'teste_bug11763507'; +--replace_column 6 # 7 # 8 # 9 # +SHOW EVENTS LIKE 'TESTE_bug11763507'; + +--replace_column 4 # +SHOW CREATE EVENT teste_bug11763507; +--replace_column 4 # +SHOW CREATE EVENT TESTE_bug11763507; + +DROP EVENT teste_bug11763507; +--echo #END OF BUG#11763507 test. + +--echo # ------------------------------------------------------------------ +--echo # -- End of 5.1 tests +--echo # ------------------------------------------------------------------ + +# +# A case of SHOW GRANTS +# (creating a new procedure changes the password) +# +--source include/switch_to_mysql_user.inc +grant create routine on test.* to foo1@localhost identified by 'foo'; +update mysql.user set authentication_string = replace(authentication_string, '*', '-') where user='foo1'; +--connect (foo,localhost,foo1,foo) +show grants; +--connection default +flush privileges; +--connection foo +show grants; +create procedure spfoo() select 1; +show grants; + +--connection default +--disconnect foo +drop procedure spfoo; +drop user foo1@localhost; +--source include/switch_to_mysql_global_priv.inc + +# +# Restore global concurrent_insert value. Keep in the end of the test file. +# + +set @@global.concurrent_insert= @old_concurrent_insert; +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc |