1delimiter |; 2 3--disable_warnings 4drop procedure if exists p1| 5drop procedure if exists p2| 6--enable_warnings 7 8###################################################################### 9# Test Dynamic SQL in stored procedures. ############################# 10###################################################################### 11# 12# A. Basics 13# 14create procedure p1() 15begin 16 prepare stmt from "select 1"; 17 execute stmt; 18 execute stmt; 19 execute stmt; 20 deallocate prepare stmt; 21end| 22call p1()| 23call p1()| 24call p1()| 25drop procedure p1| 26# 27# B. Recursion. Recusion is disabled in SP, and recursive use of PS is not 28# possible as well. 29# 30create procedure p1() 31begin 32 execute stmt; 33end| 34prepare stmt from "call p1()"| 35# Allow SP resursion to be show that it has not influence here 36set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth| 37set @@max_sp_recursion_depth=100| 38--error ER_PS_NO_RECURSION 39execute stmt| 40--error ER_PS_NO_RECURSION 41execute stmt| 42--error ER_PS_NO_RECURSION 43execute stmt| 44--error ER_PS_NO_RECURSION 45call p1()| 46--error ER_PS_NO_RECURSION 47call p1()| 48--error ER_PS_NO_RECURSION 49call p1()| 50set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS| 51--error ER_SP_RECURSION_LIMIT 52call p1()| 53--error ER_SP_RECURSION_LIMIT 54call p1()| 55--error ER_SP_RECURSION_LIMIT 56call p1()| 57 58drop procedure p1| 59# 60# C. Create/drop a stored procedure in Dynamic SQL. 61# One cannot create stored procedure from a stored procedure because of 62# the way MySQL SP cache works: it's important that this limitation is not 63# possible to circumvent by means of Dynamic SQL. 64# 65create procedure p1() 66begin 67 prepare stmt from "create procedure p2() begin select 1; end"; 68 execute stmt; 69 deallocate prepare stmt; 70end| 71--error ER_UNSUPPORTED_PS 72call p1()| 73--error ER_UNSUPPORTED_PS 74call p1()| 75drop procedure p1| 76create procedure p1() 77begin 78 prepare stmt from "drop procedure p2"; 79 execute stmt; 80 deallocate prepare stmt; 81end| 82--error ER_UNSUPPORTED_PS 83call p1()| 84--error ER_UNSUPPORTED_PS 85call p1()| 86drop procedure p1| 87# 88# D. Create/Drop/Alter a table (a DDL that issues a commit) in Dynamic SQL. 89# (should work ok). 90# 91create procedure p1() 92begin 93 prepare stmt_drop from "drop table if exists t1"; 94 execute stmt_drop; 95 prepare stmt from "create table t1 (a int)"; 96 execute stmt; 97 insert into t1 (a) values (1); 98 select * from t1; 99 prepare stmt_alter from "alter table t1 add (b int)"; 100 execute stmt_alter; 101 insert into t1 (a,b) values (2,1); 102 deallocate prepare stmt_alter; 103 deallocate prepare stmt; 104 deallocate prepare stmt_drop; 105end| 106call p1()| 107call p1()| 108drop procedure p1| 109# 110# A more real example (a case similar to submitted by 24/7). 111# 112create procedure p1() 113begin 114 set @tab_name=concat("tab_", replace(curdate(), '-', '_')); 115 set @drop_sql=concat("drop table if exists ", @tab_name); 116 set @create_sql=concat("create table ", @tab_name, " (a int)"); 117 set @insert_sql=concat("insert into ", @tab_name, " values (1), (2), (3)"); 118 set @select_sql=concat("select * from ", @tab_name); 119 select @tab_name; 120 select @drop_sql; 121 select @create_sql; 122 select @insert_sql; 123 select @select_sql; 124 prepare stmt_drop from @drop_sql; 125 execute stmt_drop; 126 prepare stmt from @create_sql; 127 execute stmt; 128 prepare stmt from @insert_sql; 129 execute stmt; 130 prepare stmt from @select_sql; 131 execute stmt; 132 execute stmt_drop; 133 deallocate prepare stmt; 134 deallocate prepare stmt_drop; 135end| 136--disable_result_log 137call p1()| 138call p1()| 139--enable_result_log 140drop procedure p1| 141# 142# E. Calling a stored procedure with Dynamic SQL 143# from a stored function (currently disabled). 144# 145create procedure p1() 146begin 147 prepare stmt_drop from "drop table if exists t1"; 148 execute stmt_drop; 149 prepare stmt from "create table t1 (a int)"; 150 execute stmt; 151 deallocate prepare stmt; 152 deallocate prepare stmt_drop; 153end| 154--disable_warnings 155drop function if exists f1| 156--enable_warnings 157create function f1(a int) returns int 158begin 159 call p1(); 160 return 1; 161end| 162 163# Every stored procedure that contains Dynamic SQL is marked as 164# such. Stored procedures that contain Dynamic SQL are not 165# allowed in a stored function or trigger, and here we get the 166# corresponding error message. 167 168--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 169select f1(0)| 170--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 171select f1(f1(0))| 172--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 173select f1(f1(f1(0)))| 174drop function f1| 175drop procedure p1| 176# 177# F. Rollback and cleanup lists management in Dynamic SQL. 178# 179create procedure p1() 180begin 181 drop table if exists t1; 182 create table t1 (id integer not null primary key, 183 name varchar(20) not null); 184 insert into t1 (id, name) values (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); 185 prepare stmt from "select name from t1"; 186 execute stmt; 187 select name from t1; 188 execute stmt; 189 prepare stmt from 190 "select name from t1 where name=(select name from t1 where id=2)"; 191 execute stmt; 192 select name from t1 where name=(select name from t1 where id=2); 193 execute stmt; 194end| 195call p1()| 196call p1()| 197drop procedure p1| 198# 199# H. Executing a statement prepared externally in SP. 200# 201prepare stmt from "select * from t1"| 202create procedure p1() 203begin 204 execute stmt; 205 deallocate prepare stmt; 206end| 207call p1()| 208--error ER_UNKNOWN_STMT_HANDLER 209call p1()| 210drop procedure p1| 211# 212# I. Use of an SP variable in Dynamic SQL is not possible and 213# this limitation is necessary for correct binary logging: prepared 214# statements do not substitute SP variables with their values for binlog, so 215# SP variables must be not accessible in Dynamic SQL. 216# 217set sql_mode= ''| 218create procedure p1() 219begin 220 declare a char(10); 221 set a="sp-variable"; 222 set @a="mysql-variable"; 223 prepare stmt from "select 'dynamic sql:', @a, a"; 224 execute stmt; 225end| 226--error ER_BAD_FIELD_ERROR 227call p1()| 228--error ER_BAD_FIELD_ERROR 229call p1()| 230set sql_mode= DEFAULT| 231drop procedure p1| 232# 233# J. Use of placeholders in Dynamic SQL. 234# 235create procedure p1() 236begin 237 prepare stmt from 'select ? as a'; 238 execute stmt using @a; 239end| 240set @a=1| 241call p1()| 242call p1()| 243drop procedure p1| 244# 245# K. Use of continue handlers with Dynamic SQL. 246# 247drop table if exists t1| 248drop table if exists t2| 249create table t1 (id integer primary key auto_increment, 250 stmt_text char(35), status varchar(20))| 251insert into t1 (stmt_text) values 252 ("select 1"), ("flush tables"), ("handler t1 open as ha"), 253 ("analyze table t1"), ("check table t1"), ("checksum table t1"), 254 ("check table t1"), ("optimize table t1"), ("repair table t1"), 255 ("describe extended select * from t1"), 256 ("help help"), ("show databases"), ("show tables"), 257 ("show table status"), ("show open tables"), ("show storage engines"), 258 ("insert into t1 (id) values (1)"), ("update t1 set status=''"), 259 ("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar"), 260 ("create view v1 as select 1"), ("alter view v1 as select 2"), 261 ("drop view v1"),("create table t2 (a int)"),("alter table t2 add (b int)"), 262 ("drop table t2")| 263create procedure p1() 264begin 265 declare v_stmt_text varchar(255); 266 declare v_id integer; 267 declare done int default 0; 268 declare c cursor for select id, stmt_text from t1; 269 declare continue handler for 1295 -- ER_UNSUPPORTED_PS 270 set @status='not supported'; 271 declare continue handler for 1064 -- ER_SYNTAX_ERROR 272 set @status='syntax error'; 273 declare continue handler for sqlstate '02000' set done = 1; 274 275 prepare update_stmt from "update t1 set status=? where id=?"; 276 open c; 277 repeat 278 if not done then 279 fetch c into v_id, v_stmt_text; 280 set @id=v_id, @stmt_text=v_stmt_text; 281 set @status="supported"; 282 prepare stmt from @stmt_text; 283 execute update_stmt using @status, @id; 284 end if; 285 until done end repeat; 286 deallocate prepare update_stmt; 287end| 288call p1()| 289select * from t1| 290drop procedure p1| 291drop table t1| 292# 293# Bug#7115 "Prepared Statements: packet error if execution within stored 294# procedure". 295# 296prepare stmt from 'select 1'| 297create procedure p1() execute stmt| 298call p1()| 299call p1()| 300drop procedure p1| 301# 302# Bug#10975 "Prepared statements: crash if function deallocates" 303# Check that a prepared statement that is currently in use 304# can't be deallocated. 305# 306# a) Prepared statements and stored procedure cache: 307# 308# TODO: add when the corresponding bug (Bug #12093 "SP not found on second 309# PS execution if another thread drops other SP in between") is fixed. 310# 311# b) attempt to deallocate a prepared statement that is being executed 312--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG 313create function f1() returns int 314begin 315 deallocate prepare stmt; 316 return 1; 317end| 318 319# b)-2 a crash (#1) spotted by Sergey Petrunia during code review 320create procedure p1() 321begin 322 prepare stmt from 'select 1 A'; 323 execute stmt; 324end| 325prepare stmt from 'call p1()'| 326--error ER_PS_NO_RECURSION 327execute stmt| 328--error ER_PS_NO_RECURSION 329execute stmt| 330drop procedure p1| 331 332# 333# Bug#10605 "Stored procedure with multiple SQL prepared statements 334# disconnects client" 335# 336--disable_warnings 337drop table if exists t1, t2| 338--enable_warnings 339create procedure p1 (a int) language sql deterministic 340begin 341 declare rsql varchar(100); 342 drop table if exists t1, t2; 343 set @rsql= "create table t1 (a int)"; 344 select @rsql; 345 prepare pst from @rsql; 346 execute pst; 347 set @rsql= null; 348 set @rsql= "create table t2 (a int)"; 349 select @rsql; 350 prepare pst from @rsql; 351 execute pst; 352 drop table if exists t1, t2; 353end| 354set @a:=0| 355call p1(@a)| 356select @a| 357call p1(@a)| 358select @a| 359drop procedure if exists p1| 360 361# End of the test 362delimiter ;| 363