MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
unsafe_binlog.inc
1 # include/unsafe_binlog.inc
2 #
3 # The variable
4 # $engine_type -- storage engine to be tested
5 # has to be set before sourcing this script.
6 #
7 # Notes:
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.
13 #
14 # Last update:
15 # 2006-08-02 ML test refactored
16 # old name was innodb_unsafe_binlog.test
17 # main code went into include/unsafe_binlog.inc
18 #
19 
20 #
21 # Test cases for bug#15650
22 # DELETE with LEFT JOIN crashes server with innodb_locks_unsafe_for_binlog
23 #
24 
25 --disable_warnings
26 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
27 --enable_warnings
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, '');
36 commit;
37 DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
38 WHERE mm.id IS NULL;
39 select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
40 where mm.id is null lock in share mode;
41 drop table t1,t2;
42 
43 #
44 # Test case for unlock row bug where unlock releases all locks granted for
45 # a row. Only the latest lock should be released.
46 #
47 
48 connect (a,localhost,root,,);
49 connect (b,localhost,root,,);
50 connection a;
51 eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
52 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
53 commit;
54 set autocommit = 0;
55 select * from t1 lock in share mode;
56 update t1 set b = 5 where b = 1;
57 connection b;
58 set autocommit = 0;
59 #
60 # S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
61 #
62 --error ER_LOCK_WAIT_TIMEOUT
63 select * from t1 where a = 2 and b = 2 for update;
64 connection a;
65 commit;
66 connection b;
67 commit;
68 drop table t1;
69 connection default;
70 disconnect a;
71 disconnect b;
72 
73 #
74 # unlock row test
75 #
76 
77 connect (a,localhost,root,,);
78 connect (b,localhost,root,,);
79 connection a;
80 eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
81 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
82 commit;
83 set autocommit = 0;
84 update t1 set b = 5 where b = 1;
85 connection b;
86 set autocommit = 0;
87 #
88 # X-lock to record (7,3) should be released in a update
89 #
90 select * from t1 where a = 7 and b = 3 for update;
91 commit;
92 connection a;
93 commit;
94 drop table t1;
95 connection default;
96 disconnect a;
97 disconnect b;
98 
99 
100 #
101 # Consistent read should be used in following selects
102 #
103 # 1) INSERT INTO ... SELECT
104 # 2) UPDATE ... = ( SELECT ...)
105 # 3) CREATE ... SELECT
106 
107 connect (a,localhost,root,,);
108 connect (b,localhost,root,,);
109 connection a;
110 eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
111 insert into t1 values (1,2),(5,3),(4,2);
112 eval create table t2(d int not null, e int, primary key(d)) engine = $engine_type;
113 insert into t2 values (8,6),(12,1),(3,1);
114 commit;
115 set autocommit = 0;
116 select * from t2 for update;
117 connection b;
118 set autocommit = 0;
119 insert into t1 select * from t2;
120 update t1 set b = (select e from t2 where a = d);
121 eval create table t3(d int not null, e int, primary key(d)) engine = $engine_type
122 select * from t2;
123 commit;
124 connection a;
125 commit;
126 connection default;
127 disconnect a;
128 disconnect b;
129 drop table t1, t2, t3;
130 
131 #
132 # Consistent read should not be used if
133 #
134 # (a) isolation level is serializable OR
135 # (b) select ... lock in share mode OR
136 # (c) select ... for update
137 #
138 # in following queries:
139 #
140 # 1) INSERT INTO ... SELECT
141 # 2) UPDATE ... = ( SELECT ...)
142 # 3) CREATE ... SELECT
143 
144 connect (a,localhost,root,,);
145 connect (b,localhost,root,,);
146 connect (c,localhost,root,,);
147 connect (d,localhost,root,,);
148 eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
149 connect (e,localhost,root,,);
150 connect (f,localhost,root,,);
151 connect (g,localhost,root,,);
152 eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
153 connect (h,localhost,root,,);
154 connect (i,localhost,root,,);
155 connect (j,localhost,root,,);
156 eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
157 connection a;
158 eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
159 insert into t1 values (1,2),(5,3),(4,2);
160 eval create table t2(a int not null, b int, primary key(a)) engine = $engine_type;
161 insert into t2 values (8,6),(12,1),(3,1);
162 eval create table t3(d int not null, b int, primary key(d)) engine = $engine_type;
163 insert into t3 values (8,6),(12,1),(3,1);
164 eval create table t5(a int not null, b int, primary key(a)) engine = $engine_type;
165 insert into t5 values (1,2),(5,3),(4,2);
166 eval create table t6(d int not null, e int, primary key(d)) engine = $engine_type;
167 insert into t6 values (8,6),(12,1),(3,1);
168 eval create table t8(a int not null, b int, primary key(a)) engine = $engine_type;
169 insert into t8 values (1,2),(5,3),(4,2);
170 eval create table t9(d int not null, e int, primary key(d)) engine = $engine_type;
171 insert into t9 values (8,6),(12,1),(3,1);
172 commit;
173 set autocommit = 0;
174 select * from t2 for update;
175 connection b;
176 set autocommit = 0;
177 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
178 --send
179 insert into t1 select * from t2;
180 connection c;
181 set autocommit = 0;
182 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
183 --send
184 update t3 set b = (select b from t2 where a = d);
185 connection d;
186 set autocommit = 0;
187 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
188 --send
189 create table t4(a int not null, b int, primary key(a)) select * from t2;
190 connection e;
191 set autocommit = 0;
192 --send
193 insert into t5 (select * from t2 lock in share mode);
194 connection f;
195 set autocommit = 0;
196 --send
197 update t6 set e = (select b from t2 where a = d lock in share mode);
198 connection g;
199 set autocommit = 0;
200 --send
201 create table t7(a int not null, b int, primary key(a)) select * from t2 lock in share mode;
202 connection h;
203 set autocommit = 0;
204 --send
205 insert into t8 (select * from t2 for update);
206 connection i;
207 set autocommit = 0;
208 --send
209 update t9 set e = (select b from t2 where a = d for update);
210 connection j;
211 set autocommit = 0;
212 --send
213 create table t10(a int not null, b int, primary key(a)) select * from t2 for update;
214 
215 connection b;
216 --error ER_LOCK_WAIT_TIMEOUT
217 reap;
218 
219 connection c;
220 --error ER_LOCK_WAIT_TIMEOUT
221 reap;
222 
223 connection d;
224 --error ER_LOCK_WAIT_TIMEOUT
225 reap;
226 
227 connection e;
228 --error ER_LOCK_WAIT_TIMEOUT
229 reap;
230 
231 connection f;
232 --error ER_LOCK_WAIT_TIMEOUT
233 reap;
234 
235 connection g;
236 --error ER_LOCK_WAIT_TIMEOUT
237 reap;
238 
239 connection h;
240 --error ER_LOCK_WAIT_TIMEOUT
241 reap;
242 
243 connection i;
244 --error ER_LOCK_WAIT_TIMEOUT
245 reap;
246 
247 connection j;
248 --error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
249 reap;
250 
251 connection a;
252 commit;
253 
254 connection default;
255 disconnect a;
256 disconnect b;
257 disconnect c;
258 disconnect d;
259 disconnect e;
260 disconnect f;
261 disconnect g;
262 disconnect h;
263 disconnect i;
264 disconnect j;
265 drop table t1, t2, t3, t5, t6, t8, t9;