summaryrefslogtreecommitdiff
path: root/mysql-test/t/sp-threads.test
blob: 7a6d12583317db9a0236df2c131392edfe9ac4e4 (plain)
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
# This test should work in embedded server after mysqltest is fixed
-- source include/not_embedded.inc
#
# Testing stored procedures with multiple connections,
# except security/privilege tests, they go to sp-security.test
#

# Save the initial number of concurrent sessions
--source include/count_sessions.inc

connect (con1root,localhost,root,,);
connect (con2root,localhost,root,,);
connect (con3root,localhost,root,,);

connection con1root;
use test;

--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (s1 int, s2 int, s3 int);

delimiter //;
create procedure bug4934()
begin
   insert into t1 values (1,0,1);
end//
delimiter ;//


connection con2root;
use test;

call bug4934();
select * from t1;


connection con1root;

drop table t1;
create table t1 (s1 int, s2 int, s3 int);

drop procedure bug4934;
delimiter //;
create procedure bug4934()
begin
end//
delimiter ;//


connection con2root;

select * from t1;
call bug4934();
select * from t1;

connection con1root;

drop table t1;
drop procedure bug4934;


#
# Bug#9486 Can't perform multi-update in stored procedure
#
--disable_warnings
drop procedure if exists bug9486;
drop table if exists t1, t2;
--enable_warnings
create table t1 (id1 int, val int);
create table t2 (id2 int);

create procedure bug9486()
  update t1, t2 set val= 1 where id1=id2;
call bug9486();
# Let us check that SP invocation requires write lock for t2.
connection con2root;
lock tables t2 write;
connection con1root;
let $con1root_id=`SELECT CONNECTION_ID()`;
send call bug9486();
connection con2root;
# There should be call statement in locked state.
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist WHERE
                    id=$con1root_id AND state='Waiting for table metadata lock';
--source include/wait_condition.inc
--replace_result $con1root_id con1root_id
eval SELECT state,info FROM information_schema.processlist WHERE id=$con1root_id;
unlock tables;
connection con1root;
reap;

drop procedure bug9486;
drop table t1, t2;


#
# Bug#11158 Can't perform multi-delete in stored procedure
#
--disable_warnings
drop procedure if exists bug11158;
--enable_warnings
create procedure bug11158() delete t1 from t1, t2 where t1.id = t2.id;
create table t1 (id int, j int);
insert into t1 values (1, 1), (2, 2);
create table t2 (id int);
insert into t2 values (1);
# Procedure should work and cause proper effect (delete only first row)
call bug11158();
select * from t1;
# Also let us test that we obtain only read (and thus non exclusive) lock
# for table from which we are not going to delete rows.
connection con2root;
lock tables t2 read;
connection con1root;
call bug11158();
connection con2root;
unlock tables;
connection con1root;
# Clean-up
drop procedure bug11158;
drop table t1, t2;


#
# Bug#11554 Server crashes on statement indirectly using non-cached function
#
--disable_warnings
drop function if exists bug11554;
drop view if exists v1;
--enable_warnings
create table t1 (i int);
create function bug11554 () returns int return 1;
create view v1 as select bug11554() as f;
connection con2root;
# This should not crash server
insert into t1 (select f from v1);
# Clean-up
connection con1root;
drop function bug11554;
drop table t1;
drop view v1;


# Bug#12228 Crash happens during calling specific SP in multithread environment
--disable_warnings
drop procedure if exists p1;
drop procedure if exists p2;
--enable_warnings

connection con1root;
delimiter |;
create table t1 (s1 int)|
create procedure p1() select * from t1|
create procedure p2()
begin
  insert into t1 values (1);
  call p1();
  select * from t1;
end|
delimiter ;|

connection con2root;
use test;
lock table t1 write;

connection con1root;
send call p2();

connection con3root;
use test;
drop procedure p1;
create procedure p1() select * from t1;

connection con2root;
unlock tables;

connection con1root;
# Crash will be here if we hit Bug#12228
reap;

drop procedure p1;
drop procedure p2;
drop table t1;


#
# Bug#NNNN New bug synopsis
#
#--disable_warnings
#drop procedure if exists bugNNNN;
#--enable_warnings
#create procedure bugNNNN...

connection default;
disconnect con1root;
disconnect con2root;
disconnect con3root;

# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc