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