1
# include/unsafe_binlog.inc
4
# $engine_type -- storage engine to be tested
5
# has to be set before sourcing this script.
8
# 1. This test uses at least in case of InnoDB options
9
# innodb_locks_unsafe_for_binlog = true
10
# innodb_lock_timeout = 5
11
# 2. The comments/expectations refer to InnoDB.
12
# They might be not valid for other storage engines.
15
# 2006-08-02 ML test refactored
16
# old name was innodb_unsafe_binlog.test
17
# main code went into include/unsafe_binlog.inc
21
# Test cases for bug#15650
22
# DELETE with LEFT JOIN crashes server with innodb_locks_unsafe_for_binlog
26
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
28
eval create table t1 (id int not null, f_id int not null, f int not null,
29
primary key(f_id, id)) engine = $engine_type;
30
eval create table t2 (id int not null,s_id int not null,s varchar(200),
31
primary key(id)) engine = $engine_type;
32
INSERT INTO t1 VALUES (8, 1, 3);
33
INSERT INTO t1 VALUES (1, 2, 1);
34
INSERT INTO t2 VALUES (1, 0, '');
35
INSERT INTO t2 VALUES (8, 1, '');
37
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
38
where mm.id is null lock in share mode;
42
# Test case for unlock row bug where unlock releases all locks granted for
43
# a row. Only the latest lock should be released.
46
connect (a,localhost,root,,);
47
connect (b,localhost,root,,);
49
eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
50
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
53
select * from t1 lock in share mode;
54
update t1 set b = 5 where b = 1;
58
# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
60
--error ER_LOCK_WAIT_TIMEOUT
61
select * from t1 where a = 2 and b = 2 for update;
75
connect (a,localhost,root,,);
76
connect (b,localhost,root,,);
78
eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
79
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
82
update t1 set b = 5 where b = 1;
86
# X-lock to record (7,3) should be released in a update
88
select * from t1 where a = 7 and b = 3 for update;
99
# Consistent read should be used in following selects
101
# 1) INSERT INTO ... SELECT
102
# 2) UPDATE ... = ( SELECT ...)
103
# 3) CREATE ... SELECT
105
connect (a,localhost,root,,);
106
connect (b,localhost,root,,);
108
eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
109
insert into t1 values (1,2),(5,3),(4,2);
110
eval create table t2(d int not null, e int, primary key(d)) engine = $engine_type;
111
insert into t2 values (8,6),(12,1),(3,1);
114
select * from t2 for update;
117
insert into t1 select * from t2;
118
update t1 set b = (select e from t2 where a = d);
119
eval create table t3(d int not null, e int, primary key(d)) engine = $engine_type
127
drop table t1, t2, t3;
130
# Consistent read should not be used if
132
# (a) isolation level is serializable OR
133
# (b) select ... lock in share mode OR
134
# (c) select ... for update
136
# in following queries:
138
# 1) INSERT INTO ... SELECT
139
# 2) UPDATE ... = ( SELECT ...)
140
# 3) CREATE ... SELECT
142
connect (a,localhost,root,,);
143
connect (b,localhost,root,,);
144
connect (c,localhost,root,,);
145
connect (d,localhost,root,,);
146
eval SET SESSION STORAGE_ENGINE = $engine_type;
147
connect (e,localhost,root,,);
148
connect (f,localhost,root,,);
149
connect (g,localhost,root,,);
150
eval SET SESSION STORAGE_ENGINE = $engine_type;
151
connect (h,localhost,root,,);
152
connect (i,localhost,root,,);
153
connect (j,localhost,root,,);
154
eval SET SESSION STORAGE_ENGINE = $engine_type;
156
eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
157
insert into t1 values (1,2),(5,3),(4,2);
158
eval create table t2(a int not null, b int, primary key(a)) engine = $engine_type;
159
insert into t2 values (8,6),(12,1),(3,1);
160
eval create table t3(d int not null, b int, primary key(d)) engine = $engine_type;
161
insert into t3 values (8,6),(12,1),(3,1);
162
eval create table t5(a int not null, b int, primary key(a)) engine = $engine_type;
163
insert into t5 values (1,2),(5,3),(4,2);
164
eval create table t6(d int not null, e int, primary key(d)) engine = $engine_type;
165
insert into t6 values (8,6),(12,1),(3,1);
166
eval create table t8(a int not null, b int, primary key(a)) engine = $engine_type;
167
insert into t8 values (1,2),(5,3),(4,2);
168
eval create table t9(d int not null, e int, primary key(d)) engine = $engine_type;
169
insert into t9 values (8,6),(12,1),(3,1);
172
select * from t2 for update;
175
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
177
insert into t1 select * from t2;
180
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
182
update t3 set b = (select b from t2 where a = d);
185
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
187
create table t4(a int not null, b int, primary key(a)) select * from t2;
191
insert into t5 (select * from t2 lock in share mode);
195
update t6 set e = (select b from t2 where a = d lock in share mode);
199
create table t7(a int not null, b int, primary key(a)) select * from t2 lock in share mode;
203
insert into t8 (select * from t2 for update);
207
update t9 set e = (select b from t2 where a = d for update);
211
create table t10(a int not null, b int, primary key(a)) select * from t2 for update;
214
--error ER_LOCK_WAIT_TIMEOUT
218
--error ER_LOCK_WAIT_TIMEOUT
222
--error ER_LOCK_WAIT_TIMEOUT
226
--error ER_LOCK_WAIT_TIMEOUT
230
--error ER_LOCK_WAIT_TIMEOUT
234
--error ER_LOCK_WAIT_TIMEOUT
238
--error ER_LOCK_WAIT_TIMEOUT
242
--error ER_LOCK_WAIT_TIMEOUT
246
--error ER_LOCK_WAIT_TIMEOUT
263
drop table t1, t2, t3, t5, t6, t8, t9;