1drop table if exists gap1,gap2,gap3;
2CREATE DATABASE mysqlslap;
3CREATE TABLE gap1 (id1 INT, id2 INT, id3 INT, c1 INT, value INT,
4PRIMARY KEY (id1, id2, id3),
5INDEX i (c1)) ENGINE=rocksdb;
6CREATE TABLE gap2 like gap1;
7CREATE TABLE gap3 (id INT, value INT,
8PRIMARY KEY (id),
9UNIQUE KEY ui(value)) ENGINE=rocksdb;
10insert into gap3 values (1,1), (2,2),(3,3),(4,4),(5,5);
11create table gap4 (
12pk int primary key,
13a int,
14b int,
15key(a)
16) ENGINE=rocksdb;
17insert into gap4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4);
18create table gap5 like gap4;
19insert into gap5 values (1,1,1), (2,2,2), (3,3,3), (4,4,4);
20set session gap_lock_raise_error=1;
21set session gap_lock_write_log=1;
22set @save_gap_lock_write_log = @@gap_lock_write_log;
23set @save_gap_lock_raise_error = @@gap_lock_raise_error;
24set gap_lock_write_log = 1;
25set gap_lock_raise_error = 0;
26begin;
27update gap4 set a= (select 1+max(a) from gap5 where gap5.pk between 1 and 3 and gap5.b=gap4.b);
281
29update gap4 set a= (select 2+max(a) from gap5 where gap5.pk between 1 and 3 and gap5.b=gap4.b);
30update gap4 set a= (select 3+max(a) from gap5 where gap5.pk between 1 and 3 and gap5.b=gap4.b);
311
321
330
34flush logs;
350
36rollback;
37set gap_lock_write_log = @save_gap_lock_write_log;
38set gap_lock_raise_error = @save_gap_lock_raise_error;
39set global gap_lock_write_log = 1;
40set global gap_lock_write_log = 0;
411000
42set session autocommit=0;
43select * from gap1 limit 1 for update;
44ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 limit 1 for update
45select * from gap1 where value != 100 limit 1 for update;
46ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where value != 100 limit 1 for update
47select * from gap1 where id1=1 for update;
48ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 for update
49select * from gap1 where id1=1 and id2= 1 for update;
50ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 for update
51select * from gap1 where id1=1 and id2= 1 and id3 != 1 for update;
52ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 and id3 != 1 for update
53select * from gap1 where id1=1 and id2= 1 and id3
54between 1 and 3 for update;
55ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 and id3
56between 1 and 3 for update
57select * from gap1 where id1=1 and id2= 1 order by id3 asc
58limit 1 for update;
59ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 order by id3 asc
60limit 1 for update
61select * from gap1 where id1=1 and id2= 1 order by id3 desc
62limit 1 for update;
63ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 order by id3 desc
64limit 1 for update
65select * from gap1 order by id1 asc limit 1 for update;
66ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 order by id1 asc limit 1 for update
67select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 for update;
68ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 for update
69select * from gap1 order by id1 desc limit 1 for update;
70ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 order by id1 desc limit 1 for update
71select * from gap1 order by id1 desc, id2 desc, id3 desc
72limit 1 for update;
73ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 order by id1 desc, id2 desc, id3 desc
74limit 1 for update
75select * from gap1 force index(i) where c1=1 for update;
76ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 force index(i) where c1=1 for update
77select * from gap3 force index(ui) where value=1 for update;
78id	value
791	1
80select * from gap1 where id1=1 and id2=1 and id3=1 for update;
81id1	id2	id3	c1	value
82select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) for update;
83id1	id2	id3	c1	value
84select * from gap1 where id1=1 and id2=1 and id3=1 and value=1
85order by c1 for update;
86id1	id2	id3	c1	value
87select * from gap3 where id=1 for update;
88id	value
891	1
90set session autocommit=1;
91select * from gap1 limit 1 for update;
92id1	id2	id3	c1	value
930	0	1	1	1
94select * from gap1 where value != 100 limit 1 for update;
95id1	id2	id3	c1	value
960	0	1	1	1
97select * from gap1 where id1=1 for update;
98id1	id2	id3	c1	value
991	0	2	2	2
1001	0	3	3	3
101select * from gap1 where id1=1 and id2= 1 for update;
102id1	id2	id3	c1	value
103select * from gap1 where id1=1 and id2= 1 and id3 != 1 for update;
104id1	id2	id3	c1	value
105select * from gap1 where id1=1 and id2= 1 and id3
106between 1 and 3 for update;
107id1	id2	id3	c1	value
108select * from gap1 where id1=1 and id2= 1 order by id3 asc
109limit 1 for update;
110id1	id2	id3	c1	value
111select * from gap1 where id1=1 and id2= 1 order by id3 desc
112limit 1 for update;
113id1	id2	id3	c1	value
114select * from gap1 order by id1 asc limit 1 for update;
115id1	id2	id3	c1	value
1160	0	1	1	1
117select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 for update;
118id1	id2	id3	c1	value
1190	0	1	1	1
120select * from gap1 order by id1 desc limit 1 for update;
121id1	id2	id3	c1	value
122500	100	1000	1000	1000
123select * from gap1 order by id1 desc, id2 desc, id3 desc
124limit 1 for update;
125id1	id2	id3	c1	value
126500	100	1000	1000	1000
127select * from gap1 force index(i) where c1=1 for update;
128id1	id2	id3	c1	value
1290	0	1	1	1
130select * from gap3 force index(ui) where value=1 for update;
131id	value
1321	1
133select * from gap1 where id1=1 and id2=1 and id3=1 for update;
134id1	id2	id3	c1	value
135select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) for update;
136id1	id2	id3	c1	value
137select * from gap1 where id1=1 and id2=1 and id3=1 and value=1
138order by c1 for update;
139id1	id2	id3	c1	value
140select * from gap3 where id=1 for update;
141id	value
1421	1
143set session autocommit=0;
144select * from gap1 limit 1 lock in share mode;
145ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 limit 1 lock in share mode
146select * from gap1 where value != 100 limit 1 lock in share mode;
147ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where value != 100 limit 1 lock in share mode
148select * from gap1 where id1=1 lock in share mode;
149ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 lock in share mode
150select * from gap1 where id1=1 and id2= 1 lock in share mode;
151ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 lock in share mode
152select * from gap1 where id1=1 and id2= 1 and id3 != 1 lock in share mode;
153ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 and id3 != 1 lock in share mode
154select * from gap1 where id1=1 and id2= 1 and id3
155between 1 and 3 lock in share mode;
156ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 and id3
157between 1 and 3 lock in share mode
158select * from gap1 where id1=1 and id2= 1 order by id3 asc
159limit 1 lock in share mode;
160ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 order by id3 asc
161limit 1 lock in share mode
162select * from gap1 where id1=1 and id2= 1 order by id3 desc
163limit 1 lock in share mode;
164ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where id1=1 and id2= 1 order by id3 desc
165limit 1 lock in share mode
166select * from gap1 order by id1 asc limit 1 lock in share mode;
167ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 order by id1 asc limit 1 lock in share mode
168select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 lock in share mode;
169ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 lock in share mode
170select * from gap1 order by id1 desc limit 1 lock in share mode;
171ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 order by id1 desc limit 1 lock in share mode
172select * from gap1 order by id1 desc, id2 desc, id3 desc
173limit 1 lock in share mode;
174ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 order by id1 desc, id2 desc, id3 desc
175limit 1 lock in share mode
176select * from gap1 force index(i) where c1=1 lock in share mode;
177ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 force index(i) where c1=1 lock in share mode
178select * from gap3 force index(ui) where value=1 lock in share mode;
179id	value
1801	1
181select * from gap1 where id1=1 and id2=1 and id3=1 lock in share mode;
182id1	id2	id3	c1	value
183select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) lock in share mode;
184id1	id2	id3	c1	value
185select * from gap1 where id1=1 and id2=1 and id3=1 and value=1
186order by c1 lock in share mode;
187id1	id2	id3	c1	value
188select * from gap3 where id=1 lock in share mode;
189id	value
1901	1
191set session autocommit=1;
192select * from gap1 limit 1 lock in share mode;
193id1	id2	id3	c1	value
1940	0	1	1	1
195select * from gap1 where value != 100 limit 1 lock in share mode;
196id1	id2	id3	c1	value
1970	0	1	1	1
198select * from gap1 where id1=1 lock in share mode;
199id1	id2	id3	c1	value
2001	0	2	2	2
2011	0	3	3	3
202select * from gap1 where id1=1 and id2= 1 lock in share mode;
203id1	id2	id3	c1	value
204select * from gap1 where id1=1 and id2= 1 and id3 != 1 lock in share mode;
205id1	id2	id3	c1	value
206select * from gap1 where id1=1 and id2= 1 and id3
207between 1 and 3 lock in share mode;
208id1	id2	id3	c1	value
209select * from gap1 where id1=1 and id2= 1 order by id3 asc
210limit 1 lock in share mode;
211id1	id2	id3	c1	value
212select * from gap1 where id1=1 and id2= 1 order by id3 desc
213limit 1 lock in share mode;
214id1	id2	id3	c1	value
215select * from gap1 order by id1 asc limit 1 lock in share mode;
216id1	id2	id3	c1	value
2170	0	1	1	1
218select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 lock in share mode;
219id1	id2	id3	c1	value
2200	0	1	1	1
221select * from gap1 order by id1 desc limit 1 lock in share mode;
222id1	id2	id3	c1	value
223500	100	1000	1000	1000
224select * from gap1 order by id1 desc, id2 desc, id3 desc
225limit 1 lock in share mode;
226id1	id2	id3	c1	value
227500	100	1000	1000	1000
228select * from gap1 force index(i) where c1=1 lock in share mode;
229id1	id2	id3	c1	value
2300	0	1	1	1
231select * from gap3 force index(ui) where value=1 lock in share mode;
232id	value
2331	1
234select * from gap1 where id1=1 and id2=1 and id3=1 lock in share mode;
235id1	id2	id3	c1	value
236select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) lock in share mode;
237id1	id2	id3	c1	value
238select * from gap1 where id1=1 and id2=1 and id3=1 and value=1
239order by c1 lock in share mode;
240id1	id2	id3	c1	value
241select * from gap3 where id=1 lock in share mode;
242id	value
2431	1
244set session autocommit=0;
245select * from gap1 limit 1 ;
246id1	id2	id3	c1	value
2470	0	1	1	1
248select * from gap1 where value != 100 limit 1 ;
249id1	id2	id3	c1	value
2500	0	1	1	1
251select * from gap1 where id1=1 ;
252id1	id2	id3	c1	value
2531	0	2	2	2
2541	0	3	3	3
255select * from gap1 where id1=1 and id2= 1 ;
256id1	id2	id3	c1	value
257select * from gap1 where id1=1 and id2= 1 and id3 != 1 ;
258id1	id2	id3	c1	value
259select * from gap1 where id1=1 and id2= 1 and id3
260between 1 and 3 ;
261id1	id2	id3	c1	value
262select * from gap1 where id1=1 and id2= 1 order by id3 asc
263limit 1 ;
264id1	id2	id3	c1	value
265select * from gap1 where id1=1 and id2= 1 order by id3 desc
266limit 1 ;
267id1	id2	id3	c1	value
268select * from gap1 order by id1 asc limit 1 ;
269id1	id2	id3	c1	value
2700	0	1	1	1
271select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 ;
272id1	id2	id3	c1	value
2730	0	1	1	1
274select * from gap1 order by id1 desc limit 1 ;
275id1	id2	id3	c1	value
276500	100	1000	1000	1000
277select * from gap1 order by id1 desc, id2 desc, id3 desc
278limit 1 ;
279id1	id2	id3	c1	value
280500	100	1000	1000	1000
281select * from gap1 force index(i) where c1=1 ;
282id1	id2	id3	c1	value
2830	0	1	1	1
284select * from gap3 force index(ui) where value=1 ;
285id	value
2861	1
287select * from gap1 where id1=1 and id2=1 and id3=1 ;
288id1	id2	id3	c1	value
289select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) ;
290id1	id2	id3	c1	value
291select * from gap1 where id1=1 and id2=1 and id3=1 and value=1
292order by c1 ;
293id1	id2	id3	c1	value
294select * from gap3 where id=1 ;
295id	value
2961	1
297set session autocommit=1;
298select * from gap1 limit 1 ;
299id1	id2	id3	c1	value
3000	0	1	1	1
301select * from gap1 where value != 100 limit 1 ;
302id1	id2	id3	c1	value
3030	0	1	1	1
304select * from gap1 where id1=1 ;
305id1	id2	id3	c1	value
3061	0	2	2	2
3071	0	3	3	3
308select * from gap1 where id1=1 and id2= 1 ;
309id1	id2	id3	c1	value
310select * from gap1 where id1=1 and id2= 1 and id3 != 1 ;
311id1	id2	id3	c1	value
312select * from gap1 where id1=1 and id2= 1 and id3
313between 1 and 3 ;
314id1	id2	id3	c1	value
315select * from gap1 where id1=1 and id2= 1 order by id3 asc
316limit 1 ;
317id1	id2	id3	c1	value
318select * from gap1 where id1=1 and id2= 1 order by id3 desc
319limit 1 ;
320id1	id2	id3	c1	value
321select * from gap1 order by id1 asc limit 1 ;
322id1	id2	id3	c1	value
3230	0	1	1	1
324select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 ;
325id1	id2	id3	c1	value
3260	0	1	1	1
327select * from gap1 order by id1 desc limit 1 ;
328id1	id2	id3	c1	value
329500	100	1000	1000	1000
330select * from gap1 order by id1 desc, id2 desc, id3 desc
331limit 1 ;
332id1	id2	id3	c1	value
333500	100	1000	1000	1000
334select * from gap1 force index(i) where c1=1 ;
335id1	id2	id3	c1	value
3360	0	1	1	1
337select * from gap3 force index(ui) where value=1 ;
338id	value
3391	1
340select * from gap1 where id1=1 and id2=1 and id3=1 ;
341id1	id2	id3	c1	value
342select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) ;
343id1	id2	id3	c1	value
344select * from gap1 where id1=1 and id2=1 and id3=1 and value=1
345order by c1 ;
346id1	id2	id3	c1	value
347select * from gap3 where id=1 ;
348id	value
3491	1
350set session autocommit=0;
351insert into gap1 (id1, id2, id3) values (-1,-1,-1);
352insert into gap1 (id1, id2, id3) values (-1,-1,-1)
353on duplicate key update value=100;
354update gap1 set value=100 where id1=1;
355ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: update gap1 set value=100 where id1=1
356update gap1 set value=100 where id1=1 and id2=1 and id3=1;
357delete from gap1 where id1=2;
358ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: delete from gap1 where id1=2
359delete from gap1 where id1=-1 and id2=-1 and id3=-1;
360commit;
361set session autocommit=1;
362insert into gap1 (id1, id2, id3) values (-1,-1,-1);
363insert into gap1 (id1, id2, id3) values (-1,-1,-1)
364on duplicate key update value=100;
365update gap1 set value=100 where id1=1;
366update gap1 set value=100 where id1=1 and id2=1 and id3=1;
367delete from gap1 where id1=2;
368delete from gap1 where id1=-1 and id2=-1 and id3=-1;
369commit;
370set session autocommit=1;
371insert into gap2 select * from gap1;
372ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: insert into gap2 select * from gap1
373insert into gap2 select * from gap1 where id1=1;
374ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: insert into gap2 select * from gap1 where id1=1
375insert into gap2 select * from gap1 where id1=1 and id2=1 and id3=1;
376create table t4 select * from gap1 where id1=1 and id2=1 and id3=1;
377drop table t4;
378create table t4 select * from gap1;
379ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: create table t4 select * from gap1
380create table t4 select * from gap1 where id1=1;
381ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: create table t4 select * from gap1 where id1=1
382update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 set gap1.value=100 where gap2.id1=3
383and gap2.id2=3 and gap2.id3=3;
384update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 set gap1.value=100 where gap2.id1=3;
385ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 set gap1.value=100 where gap2.id1=3
386update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 join gap3 on gap1.id1=gap3.id
387set gap1.value=100 where gap2.id1=3;
388ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: update gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 join gap3 on gap1.id1=gap3.id
389set gap1.value=100 where gap2.id1=3
390update gap1 set gap1.value= (select count(*) from gap2);
391ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: update gap1 set gap1.value= (select count(*) from gap2)
392delete gap1 from gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 where gap2.id1=3
393and gap2.id2=3 and gap2.id3=3;
394delete gap1 from gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 where gap2.id1=3;
395ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: delete gap1 from gap1 join gap2 on gap1.id1 and gap1.id2=gap2.id2 where gap2.id1=3
396select * from gap1, gap2 limit 1 for update;
397ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1, gap2 limit 1 for update
398select * from gap1 a, gap1 b limit 1 for update;
399ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 a, gap1 b limit 1 for update
400create table u1(
401c1 int,
402c2 int,
403c3 int,
404c4 int,
405primary key (c1, c2, c3),
406unique key (c3, c1)
407);
408set session gap_lock_raise_error=1;
409begin;
410insert into u1 values (1,1,1,1);
411commit;
412begin;
413insert into u1 values (1,2,1,1) on duplicate key update c4=10;
414commit;
415begin;
416select * from u1 where c3=1 and c1 = 1 for update;
417c1	c2	c3	c4
4181	1	1	10
419select * from u1 where c3=1 for update;
420ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from u1 where c3=1 for update
421commit;
422drop table u1;
423set global gap_lock_write_log= 0;
424set global gap_lock_raise_error= 0;
425drop table if exists gap1, gap2, gap3, gap4, gap5;
426DROP DATABASE mysqlslap;
4270
428SET GLOBAL gap_lock_log_file='<GAP_LOCK_ORIG>';
429SET GLOBAL gap_lock_log_file='<GAP_LOCK>';
430flush general logs;
431SET @save_gap_lock_exceptions = @@global.gap_lock_exceptions;
432SET GLOBAL gap_lock_exceptions="t.*";
433drop table if exists gap1,gap2,gap3;
434CREATE DATABASE mysqlslap;
435CREATE TABLE gap1 (id1 INT, id2 INT, id3 INT, c1 INT, value INT,
436PRIMARY KEY (id1, id2, id3),
437INDEX i (c1)) ENGINE=rocksdb;
438CREATE TABLE gap2 like gap1;
439CREATE TABLE gap3 (id INT, value INT,
440PRIMARY KEY (id),
441UNIQUE KEY ui(value)) ENGINE=rocksdb;
442insert into gap3 values (1,1), (2,2),(3,3),(4,4),(5,5);
443create table gap4 (
444pk int primary key,
445a int,
446b int,
447key(a)
448) ENGINE=rocksdb;
449insert into gap4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4);
450create table gap5 like gap4;
451insert into gap5 values (1,1,1), (2,2,2), (3,3,3), (4,4,4);
452set session gap_lock_raise_error=1;
453set session gap_lock_write_log=1;
454set session autocommit=0;
455select * from gap1 limit 1 for update;
456ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 limit 1 for update
457select * from gap1 where value != 100 limit 1 for update;
458ERROR HY000: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need either 1: Execute 'SET SESSION gap_lock_raise_error=0' if you are sure that your application does not rely on Gap Lock. 2: Rewrite queries to use all unique key columns in WHERE equal conditions. 3: Rewrite to single-table, single-statement transaction.  Query: select * from gap1 where value != 100 limit 1 for update
459set global gap_lock_write_log= 0;
460set global gap_lock_raise_error= 0;
461drop table if exists gap1, gap2, gap3, gap4, gap5;
462DROP DATABASE mysqlslap;
4630
464SET GLOBAL gap_lock_log_file='<GAP_LOCK_ORIG>';
465SET GLOBAL gap_lock_log_file='<GAP_LOCK>';
466flush general logs;
467SET GLOBAL gap_lock_exceptions="gap.*";
468drop table if exists gap1,gap2,gap3;
469CREATE DATABASE mysqlslap;
470CREATE TABLE gap1 (id1 INT, id2 INT, id3 INT, c1 INT, value INT,
471PRIMARY KEY (id1, id2, id3),
472INDEX i (c1)) ENGINE=rocksdb;
473CREATE TABLE gap2 like gap1;
474CREATE TABLE gap3 (id INT, value INT,
475PRIMARY KEY (id),
476UNIQUE KEY ui(value)) ENGINE=rocksdb;
477insert into gap3 values (1,1), (2,2),(3,3),(4,4),(5,5);
478create table gap4 (
479pk int primary key,
480a int,
481b int,
482key(a)
483) ENGINE=rocksdb;
484insert into gap4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4);
485create table gap5 like gap4;
486insert into gap5 values (1,1,1), (2,2,2), (3,3,3), (4,4,4);
487set session gap_lock_raise_error=1;
488set session gap_lock_write_log=1;
489set session autocommit=0;
490select * from gap1 limit 1 for update;
491id1	id2	id3	c1	value
4920	0	1	1	1
493select * from gap1 where value != 100 limit 1 for update;
494id1	id2	id3	c1	value
4950	0	1	1	1
496set global gap_lock_write_log= 0;
497set global gap_lock_raise_error= 0;
498drop table if exists gap1, gap2, gap3, gap4, gap5;
499DROP DATABASE mysqlslap;
5000
501SET GLOBAL gap_lock_log_file='<GAP_LOCK_ORIG>';
502SET GLOBAL gap_lock_log_file='<GAP_LOCK>';
503flush general logs;
504SET GLOBAL gap_lock_exceptions=@save_gap_lock_exceptions;
505