1# Can't test with embedded server 2-- source include/not_embedded.inc 3# Disable concurrent inserts to avoid test failures 4set @old_concurrent_insert= @@global.concurrent_insert; 5set @@global.concurrent_insert= 0; 6 7# Save the initial number of concurrent sessions 8--source include/count_sessions.inc 9 10--disable_warnings 11drop table if exists t1,t3; 12--enable_warnings 13delimiter |; 14 15 16# 17# Bug#4902 Stored procedure with SHOW WARNINGS leads to packet error 18# 19# Added tests for show grants command 20--disable_warnings 21drop procedure if exists bug4902| 22--enable_warnings 23create procedure bug4902() 24begin 25 show grants for 'root'@'localhost'; 26end| 27--disable_parsing 28show binlog events| 29show storage engines| 30show master status| 31show slave hosts| 32show slave status| 33--enable_parsing 34 35call bug4902()| 36call bug4902()| 37 38drop procedure bug4902| 39 40# We need separate SP for SHOW PROCESSLIST since we want use replace_column 41--disable_warnings 42drop procedure if exists bug4902_2| 43--enable_warnings 44create procedure bug4902_2() 45begin 46 show processlist; 47end| 48--disable_result_log 49call bug4902_2()| 50--enable_result_log 51show warnings| 52--disable_result_log 53call bug4902_2()| 54--enable_result_log 55show warnings| 56drop procedure bug4902_2| 57 58# 59# Bug#6807 Stored procedure crash if CREATE PROCEDURE ... KILL QUERY 60# 61--disable_warnings 62drop procedure if exists bug6807| 63--enable_warnings 64create procedure bug6807() 65begin 66 declare a int; 67 68 set a = connection_id(); 69 kill query a; 70 select 'Not reached'; 71end| 72 73--error ER_QUERY_INTERRUPTED 74call bug6807()| 75--error ER_QUERY_INTERRUPTED 76call bug6807()| 77 78drop procedure bug6807| 79 80 81# 82# Bug#10100 function (and stored procedure?) recursivity problem 83# 84--disable_warnings 85drop function if exists bug10100f| 86drop procedure if exists bug10100p| 87drop procedure if exists bug10100t| 88drop procedure if exists bug10100pt| 89drop procedure if exists bug10100pv| 90drop procedure if exists bug10100pd| 91drop procedure if exists bug10100pc| 92--enable_warnings 93# routines with simple recursion 94create function bug10100f(prm int) returns int 95begin 96 if prm > 1 then 97 return prm * bug10100f(prm - 1); 98 end if; 99 return 1; 100end| 101set statement sql_mode = '' for 102create procedure bug10100p(prm int, inout res int) 103begin 104 set res = res * prm; 105 if prm > 1 then 106 call bug10100p(prm - 1, res); 107 end if; 108end| 109set statement sql_mode = '' for 110create procedure bug10100t(prm int) 111begin 112 declare res int; 113 set res = 1; 114 call bug10100p(prm, res); 115 select res; 116end| 117 118# a procedure which use tables and recursion 119create table t3 (a int)| 120insert into t3 values (0)| 121create view v1 as select a from t3| 122create procedure bug10100pt(level int, lim int) 123begin 124 if level < lim then 125 update t3 set a=level; 126 FLUSH TABLES; 127 call bug10100pt(level+1, lim); 128 else 129 select * from t3; 130 end if; 131end| 132# view & recursion 133create procedure bug10100pv(level int, lim int) 134begin 135 if level < lim then 136 update v1 set a=level; 137 FLUSH TABLES; 138 call bug10100pv(level+1, lim); 139 else 140 select * from v1; 141 end if; 142end| 143# dynamic sql & recursion 144prepare stmt2 from "select * from t3;"; 145create procedure bug10100pd(level int, lim int) 146begin 147 if level < lim then 148 select level; 149 prepare stmt1 from "update t3 set a=a+2"; 150 execute stmt1; 151 FLUSH TABLES; 152 execute stmt1; 153 FLUSH TABLES; 154 execute stmt1; 155 FLUSH TABLES; 156 deallocate prepare stmt1; 157 execute stmt2; 158 select * from t3; 159 call bug10100pd(level+1, lim); 160 else 161 execute stmt2; 162 end if; 163end| 164# cursor & recursion 165create procedure bug10100pc(level int, lim int) 166begin 167 declare lv int; 168 declare c cursor for select a from t3; 169 open c; 170 if level < lim then 171 select level; 172 fetch c into lv; 173 select lv; 174 update t3 set a=level+lv; 175 FLUSH TABLES; 176 call bug10100pc(level+1, lim); 177 else 178 select * from t3; 179 end if; 180 close c; 181end| 182 183# end of the stack checking 184set @@max_sp_recursion_depth=255| 185set @var=1| 186# disable log because error about stack overrun contains numbers which 187# depend on a system 188-- disable_result_log 189-- error ER_STACK_OVERRUN_NEED_MORE 190call bug10100p(255, @var)| 191-- error ER_STACK_OVERRUN_NEED_MORE 192call bug10100pt(1,255)| 193-- error ER_STACK_OVERRUN_NEED_MORE 194call bug10100pv(1,255)| 195-- error ER_STACK_OVERRUN_NEED_MORE 196call bug10100pd(1,255)| 197-- error ER_STACK_OVERRUN_NEED_MORE 198call bug10100pc(1,255)| 199-- enable_result_log 200set @@max_sp_recursion_depth=0| 201 202deallocate prepare stmt2| 203 204drop function bug10100f| 205drop procedure bug10100p| 206drop procedure bug10100t| 207drop procedure bug10100pt| 208drop procedure bug10100pv| 209drop procedure bug10100pd| 210drop procedure bug10100pc| 211drop view v1| 212drop table t3| 213 214delimiter ;| 215 216 217# 218# Bug#15298 SHOW GRANTS FOR CURRENT_USER: Incorrect output in DEFINER context 219# 220--disable_warnings 221drop procedure if exists bug15298_1; 222drop procedure if exists bug15298_2; 223--enable_warnings 224create user 'mysqltest_1'@'localhost'; 225grant all privileges on test.* to 'mysqltest_1'@'localhost'; 226create procedure 15298_1 () sql security definer show grants for current_user; 227create procedure 15298_2 () sql security definer show grants; 228 229connect (con1,localhost,mysqltest_1,,test); 230call 15298_1(); 231call 15298_2(); 232 233connection default; 234disconnect con1; 235drop user mysqltest_1@localhost; 236drop procedure 15298_1; 237drop procedure 15298_2; 238 239# 240# Bug#29936 Stored Procedure DML ignores low_priority_updates setting 241# 242 243--disable_warnings 244drop table if exists t1; 245drop procedure if exists p1; 246--enable_warnings 247 248create table t1 (value varchar(15)); 249create procedure p1() update t1 set value='updated' where value='old'; 250 251# load the procedure into sp cache and execute once 252call p1(); 253 254insert into t1 (value) values ("old"),("irrelevant"); 255 256connect (rl_holder, localhost, root,,); 257connect (rl_acquirer, localhost, root,,); 258connect (rl_contender, localhost, root,,); 259connect (rl_wait, localhost, root,,); 260 261connection rl_holder; 262select get_lock('b26162',120); 263 264connection rl_acquirer; 265--send select 'rl_acquirer', value from t1 where get_lock('b26162',120); 266 267# we must wait till this select opens and locks the tables 268connection rl_wait; 269let $wait_condition= 270 select count(*) = 1 from information_schema.processlist 271 where state = "User lock" and 272 info = "select 'rl_acquirer', value from t1 where get_lock('b26162',120)"; 273--source include/wait_condition.inc 274 275connection default; 276set session low_priority_updates=on; 277--send call p1(); 278 279connection rl_wait; 280let $wait_condition= 281 select count(*) = 1 from information_schema.processlist 282 where state = "Waiting for table level lock" and 283 info = "update t1 set value='updated' where value='old'"; 284--source include/wait_condition.inc 285 286connection rl_contender; 287select 'rl_contender', value from t1; 288 289connection rl_holder; 290select release_lock('b26162'); 291 292connection rl_acquirer; 293--reap 294connection default; 295--reap 296 297disconnect rl_holder; 298disconnect rl_acquirer; 299disconnect rl_wait; 300disconnect rl_contender; 301drop procedure p1; 302drop table t1; 303set session low_priority_updates=default; 304 305# 306# Bug#44798 MySQL engine crashes when creating stored procedures with execute_priv=N 307# 308--source include/switch_to_mysql_user.inc 309INSERT IGNORE INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, 310Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, 311Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, 312Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, 313Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, 314Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, 315max_updates, max_connections, max_user_connections) 316VALUES('%', 'mysqltest_1', password(''), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 317'N', 'N', 'N', 'Y', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y', 'Y', 'N', '', 318'', '', '', '0', '0', '0', '0'); 319FLUSH PRIVILEGES; 320 321connect (con1, localhost, mysqltest_1,,); 322connection con1; 323CREATE PROCEDURE p1(i INT) BEGIN END; 324disconnect con1; 325connection default; 326DROP PROCEDURE p1; 327--source include/switch_to_mysql_global_priv.inc 328 329--echo # 330--echo # Bug#44521 Prepared Statement: CALL p() - crashes: `! thd->main_da.is_sent' failed et.al. 331--echo # 332SELECT GET_LOCK('Bug44521', 0); 333--connect (con1,localhost,root,,) 334delimiter $; 335CREATE PROCEDURE p() 336BEGIN 337 SELECT 1; 338 SELECT GET_LOCK('Bug44521', 100); 339 SELECT 2; 340END$ 341delimiter ;$ 342--send CALL p(); 343--connection default 344let $wait_condition= 345 SELECT count(*) = 1 FROM information_schema.processlist 346 WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)"; 347--source include/wait_condition.inc 348let $conid = 349 `SELECT id FROM information_schema.processlist 350 WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)"`; 351dirty_close con1; 352SELECT RELEASE_LOCK('Bug44521'); 353let $wait_condition= 354 SELECT count(*) = 0 FROM information_schema.processlist 355 WHERE id = $conid; 356--source include/wait_condition.inc 357DROP PROCEDURE p; 358 359# 360# Bug#47736 killing a select from a view when the view is processing a function, asserts 361# 362CREATE TABLE t1(a int); 363INSERT INTO t1 VALUES (1); 364CREATE FUNCTION f1 (inp TEXT) RETURNS INT NO SQL RETURN sleep(60); 365CREATE VIEW v1 AS SELECT f1('a') FROM t1; 366 367--connect (con1, localhost, root,,) 368--let $ID_1= `SELECT connection_id()` 369--send SELECT * FROM v1; 370 371--connect (con2, localhost, root,,) 372--let $ID_2= `SELECT connection_id()` 373--send SELECT * FROM v1 374 375--connection default 376let $wait_condition= 377 select count(*) = 2 from information_schema.processlist where state = "User sleep"; 378--source include/wait_condition.inc 379--disable_query_log 380--eval KILL QUERY $ID_2 381--eval KILL QUERY $ID_1 382--enable_query_log 383 384--connection con1 385--error ER_QUERY_INTERRUPTED 386--reap 387--connection con2 388--error ER_QUERY_INTERRUPTED 389--reap 390 391--connection default 392DROP VIEW v1; 393DROP TABLE t1; 394DROP FUNCTION f1; 395--disconnect con1 396--disconnect con2 397 398--echo # ------------------------------------------------------------------ 399--echo # -- End of 5.1 tests 400--echo # ------------------------------------------------------------------ 401 402--echo # 403--echo # Test for bug#11763757 "56510: ERROR 42000: FUNCTION DOES NOT EXIST 404--echo # IF NOT-PRIV USER RECONNECTS ". 405--echo # 406--echo # The real problem was that server was unable handle properly stored 407--echo # functions in databases which names contained dot. 408--echo # 409 410connection default; 411 412--disable_warnings 413DROP DATABASE IF EXISTS `my.db`; 414--enable_warnings 415 416create database `my.db`; 417use `my.db`; 418 419CREATE FUNCTION f1(a int) RETURNS INT RETURN a; 420 421connect (addcon, localhost, root,,); 422connection addcon; 423USE `my.db`; 424SELECT f1(1); 425SELECT `my.db`.f1(2); 426 427connection default; 428disconnect addcon; 429DROP DATABASE `my.db`; 430USE test; 431 432 433--echo # 434--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE 435--echo # 436SET @@SQL_MODE = ''; 437DELIMITER $; 438 439CREATE EVENT teste_bug11763507 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR 440DO SELECT 1 $ 441 442DELIMITER ;$ 443# EVENTS 444--replace_column 6 # 7 # 8 # 9 # 445SHOW EVENTS LIKE 'teste_bug11763507'; 446--replace_column 6 # 7 # 8 # 9 # 447SHOW EVENTS LIKE 'TESTE_bug11763507'; 448 449--replace_column 4 # 450SHOW CREATE EVENT teste_bug11763507; 451--replace_column 4 # 452SHOW CREATE EVENT TESTE_bug11763507; 453 454DROP EVENT teste_bug11763507; 455--echo #END OF BUG#11763507 test. 456 457--echo # ------------------------------------------------------------------ 458--echo # -- End of 5.1 tests 459--echo # ------------------------------------------------------------------ 460 461# 462# A case of SHOW GRANTS 463# (creating a new procedure changes the password) 464# 465--source include/switch_to_mysql_user.inc 466grant create routine on test.* to foo1@localhost identified by 'foo'; 467update mysql.user set authentication_string = replace(authentication_string, '*', '-') where user='foo1'; 468--connect (foo,localhost,foo1,foo) 469show grants; 470--connection default 471flush privileges; 472--connection foo 473show grants; 474create procedure spfoo() select 1; 475show grants; 476 477--connection default 478--disconnect foo 479drop procedure spfoo; 480drop user foo1@localhost; 481--source include/switch_to_mysql_global_priv.inc 482 483# 484# Restore global concurrent_insert value. Keep in the end of the test file. 485# 486 487set @@global.concurrent_insert= @old_concurrent_insert; 488# Wait till all disconnects are completed 489--source include/wait_until_count_sessions.inc 490