2
# Tests of prelocking-free execution of stored procedures.
3
# Currently two properties of prelocking-free SP execution are checked:
4
# - It is possible to execute DDL statements in prelocking-free stored
6
# - The same procedure can be called in prelocking-free mode and
7
# in prelocked mode (from within a function).
10
drop database if exists mysqltest;
11
drop table if exists t1, t2, t3, t4;
12
drop procedure if exists sp1;
13
drop procedure if exists sp2;
14
drop procedure if exists sp3;
15
drop procedure if exists sp4;
16
drop function if exists f1;
17
drop function if exists f2;
18
drop function if exists f3;
23
create database mysqltest;
26
create procedure sp1 ()
28
drop table if exists t1;
43
drop procedure mysqltest.sp1;
44
drop database mysqltest;
49
create procedure sp1()
51
create table t1 (a int);
52
insert into t1 values (10);
55
create procedure sp2()
57
create table t2(a int);
58
insert into t2 values(1);
62
create function f1() returns int
64
return (select max(a) from t1);
67
create procedure sp3()
76
select 't1',a from t1;
80
select 't1',a from t1;
81
select 't2',a from t2;
85
select 't1',a from t1;
95
create procedure sp1()
97
create temporary table t2(a int);
98
insert into t2 select * from t1;
101
create procedure sp2()
103
create temporary table t1 (a int);
104
insert into t1 values(1);
106
select 't1', a from t1;
107
select 't2', a from t2;
119
create table t1 (a int);
120
insert into t1 values(1),(2);
121
create table t2 as select * from t1;
122
create table t3 as select * from t1;
123
create table t4 as select * from t1;
125
create procedure sp1(a int)
130
create function f1() returns int
132
return (select max(a) from t1);
141
create procedure sp2(a int)
147
create procedure sp3()
153
create procedure sp4()
168
# Test that prelocking state restoration works with cursors
170
drop view if exists v1;
174
create function f1(ab int) returns int
177
set i= (select max(a) from t1 where a < ab) ;
181
create function f2(ab int) returns int
184
set i= (select max(a) from t2 where a < ab) ;
189
select t3.a as x, t4.a as y, f2(3) as z
190
from t3, t4 where t3.a = t4.a //
192
create procedure sp1()
195
set a= (select f1(4) + count(*) A from t1, v1);
199
create function f3() returns int
216
create procedure sp1()
219
declare c cursor for select f1(3) + count(*) from v1;
224
create function f3() returns int
238
drop table t1,t2,t3,t4;
245
# Bug#15683 "crash, Function on nested VIEWs, Prepared statement"
246
# Check that when creating the prelocking list a nested view
247
# is not merged until it's used.
250
drop table if exists t1;
251
drop view if exists v1, v2, v3;
252
drop function if exists bug15683;
254
create table t1 (f1 bigint, f2 varchar(20), f3 bigint);
255
insert into t1 set f1 = 1, f2 = 'schoenenbourg', f3 = 1;
256
create view v1 as select 1 from t1 union all select 1;
257
create view v2 as select 1 from v1;
258
create view v3 as select 1 as f1 from v2;
261
create function bug15683() returns bigint
263
return (select count(*) from v3);
267
prepare stmt from "select bug15683()";
270
deallocate prepare stmt;
272
drop view v1, v2, v3;
273
drop function bug15683;
277
# Bug#19634 "Re-execution of multi-delete which involve trigger/stored
278
# function crashes server"
281
drop table if exists t1, t2, t3;
282
drop function if exists bug19634;
284
create table t1 (id int, data int);
285
create table t2 (id int);
286
create table t3 (data int);
287
create function bug19634() returns int return (select count(*) from t3);
288
prepare stmt from "delete t1 from t1, t2 where t1.id = t2.id and bug19634()";
289
# This should not crash server
292
deallocate prepare stmt;
294
create trigger t1_bi before delete on t1 for each row insert into t3 values (old.data);
295
prepare stmt from "delete t1 from t1, t2 where t1.id = t2.id";
299
deallocate prepare stmt;
301
drop function bug19634;
302
drop table t1, t2, t3;
305
# Bug #27907 Misleading error message when opening/locking tables
309
drop table if exists bug_27907_logs;
310
drop table if exists bug_27907_t1;
313
create table bug_27907_logs (a int);
314
create table bug_27907_t1 (a int);
318
create trigger bug_27907_t1_ai after insert on bug_27907_t1
321
insert into bug_27907_logs (a) values (1);
326
drop table bug_27907_logs;
329
# was failing before with error ER_NOT_LOCKED
331
--error ER_NO_SUCH_TABLE
332
insert into bug_27907_t1(a) values (1);
334
drop table bug_27907_t1;
337
--echo Bug#22427 create table if not exists + stored function results in
338
--echo inconsistent behavior
340
--echo Add a test case, the bug itself was fixed by the patch for
344
drop table if exists t1;
345
drop function if exists f_bug22427;
347
create table t1 (i int);
348
insert into t1 values (1);
349
create function f_bug22427() returns int return (select max(i) from t1);
351
# Until this bug was fixed, the following emitted error
352
# ERROR 1213: Deadlock found when trying to get lock
353
create table if not exists t1 select f_bug22427() as i;
354
--error ER_TABLE_EXISTS_ERROR
355
create table t1 select f_bug22427() as i;
357
drop function f_bug22427;
360
--echo # Bug #29929 LOCK TABLES does not pre-lock tables used in triggers of the locked tables
363
DROP table IF EXISTS t1,t2;
365
CREATE TABLE t1 (c1 INT);
366
CREATE TABLE t2 (c2 INT);
367
INSERT INTO t1 VALUES (1);
368
INSERT INTO t2 VALUES (2);
370
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
372
UPDATE t2 SET c2= c2 + 1;
375
--echo # Take a table lock on t1.
376
--echo # This should pre-lock t2 through the trigger.
378
INSERT INTO t1 VALUES (3);
381
--error ER_TABLE_NOT_LOCKED
382
INSERT INTO t2 values(4);
389
--echo End of 5.0 tests