Debian Codegraph @

mysql-test/suite/innodb_fts/t/savepoint.test

raw
1 # This is the basic function tests for innodb FTS savepoint
2
3 -- source include/have_innodb.inc
4
5
6 CREATE TABLE articles (
7 id INT UNSIGNED NOT NULL PRIMARY KEY,
8 title VARCHAR(200),
9 FULLTEXT (title)
10 ) ENGINE= InnoDB;
11
12 # Test Part 1: ROLLBACK TO SAVEPOINT
13 # Test rollback to savepoint 1(S1,RB1)
14 TRUNCATE TABLE articles;
15
16 INSERT INTO articles(id, title) VALUES(1, 'mysql');
17
18 BEGIN;
19
20 INSERT INTO articles(id, title) VALUES(2, 'mysql');
21
22 SAVEPOINT sp1;
23
24 INSERT INTO articles(id, title) VALUES(3, 'mysql');
25
26 ROLLBACK TO SAVEPOINT sp1;
27
28 INSERT INTO articles(id, title) VALUES(4, 'mysql');
29
30 COMMIT;
31
32 INSERT INTO articles(id, title) VALUES(5, 'mysql');
33
34 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
35
36 # Test rollback to savepoint 2(S1,RB1,S2,RB2)
37 TRUNCATE TABLE articles;
38
39 INSERT INTO articles(id, title) VALUES(1, 'mysql');
40
41 BEGIN;
42
43 INSERT INTO articles(id, title) VALUES(2, 'mysql');
44
45 SAVEPOINT sp1;
46
47 INSERT INTO articles(id, title) VALUES(3, 'mysql');
48
49 ROLLBACK TO SAVEPOINT sp1;
50
51 INSERT INTO articles(id, title) VALUES(4, 'mysql');
52
53 SAVEPOINT sp2;
54
55 INSERT INTO articles(id, title) VALUES(5, 'mysql');
56
57 ROLLBACK TO SAVEPOINT sp2;
58
59 INSERT INTO articles(id, title) VALUES(6, 'mysql');
60
61 COMMIT;
62
63 INSERT INTO articles(id, title) VALUES(7, 'mysql');
64
65 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
66
67 # Test rollback to savepoint 3(S1,S2,RB1)
68 TRUNCATE TABLE articles;
69
70 INSERT INTO articles(id, title) VALUES(1, 'mysql');
71
72 BEGIN;
73
74 INSERT INTO articles(id, title) VALUES(2, 'mysql');
75
76 SAVEPOINT sp1;
77
78 INSERT INTO articles(id, title) VALUES(3, 'mysql');
79
80 SAVEPOINT sp2;
81
82 INSERT INTO articles(id, title) VALUES(4, 'mysql');
83
84 ROLLBACK TO SAVEPOINT sp1;
85
86 INSERT INTO articles(id, title) VALUES(5, 'mysql');
87
88 COMMIT;
89
90 INSERT INTO articles(id, title) VALUES(6, 'mysql');
91
92 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
93
94 # Test rollback to savepoint 4(S1,S2,RB2,RB1)
95 TRUNCATE TABLE articles;
96
97 INSERT INTO articles(id, title) VALUES(1, 'mysql');
98
99 BEGIN;
100
101 INSERT INTO articles(id, title) VALUES(2, 'mysql');
102
103 SAVEPOINT sp1;
104
105 INSERT INTO articles(id, title) VALUES(3, 'mysql');
106
107 SAVEPOINT sp2;
108
109 INSERT INTO articles(id, title) VALUES(4, 'mysql');
110
111 ROLLBACK TO SAVEPOINT sp2;
112
113 INSERT INTO articles(id, title) VALUES(5, 'mysql');
114
115 ROLLBACK TO SAVEPOINT sp1;
116
117 INSERT INTO articles(id, title) VALUES(6, 'mysql');
118
119 COMMIT;
120
121 INSERT INTO articles(id, title) VALUES(7, 'mysql');
122
123 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
124
125 # Test Part 2: RELEASE SAVEPOINT
126 # Test release savepoint 1(S1,RL1)
127 TRUNCATE TABLE articles;
128
129 INSERT INTO articles(id, title) VALUES(1, 'mysql');
130
131 BEGIN;
132
133 INSERT INTO articles(id, title) VALUES(2, 'mysql');
134
135 SAVEPOINT sp1;
136
137 INSERT INTO articles(id, title) VALUES(3, 'mysql');
138
139 RELEASE SAVEPOINT sp1;
140
141 INSERT INTO articles(id, title) VALUES(4, 'mysql');
142
143 COMMIT;
144
145 INSERT INTO articles(id, title) VALUES(5, 'mysql');
146
147 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
148
149 # Test release savepoint 2(S1,RL1,S2,RL2)
150 TRUNCATE TABLE articles;
151
152 INSERT INTO articles(id, title) VALUES(1, 'mysql');
153
154 BEGIN;
155
156 INSERT INTO articles(id, title) VALUES(2, 'mysql');
157
158 SAVEPOINT sp1;
159
160 INSERT INTO articles(id, title) VALUES(3, 'mysql');
161
162 RELEASE SAVEPOINT sp1;
163
164 INSERT INTO articles(id, title) VALUES(4, 'mysql');
165
166 SAVEPOINT sp2;
167
168 INSERT INTO articles(id, title) VALUES(5, 'mysql');
169
170 RELEASE SAVEPOINT sp2;
171
172 INSERT INTO articles(id, title) VALUES(6, 'mysql');
173
174 COMMIT;
175
176 INSERT INTO articles(id, title) VALUES(7, 'mysql');
177
178 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
179
180 # Test release savepoint 3(S1,S2,RL1)
181 TRUNCATE TABLE articles;
182
183 INSERT INTO articles(id, title) VALUES(1, 'mysql');
184
185 BEGIN;
186
187 INSERT INTO articles(id, title) VALUES(2, 'mysql');
188
189 SAVEPOINT sp1;
190
191 INSERT INTO articles(id, title) VALUES(3, 'mysql');
192
193 SAVEPOINT sp2;
194
195 INSERT INTO articles(id, title) VALUES(4, 'mysql');
196
197 RELEASE SAVEPOINT sp1;
198
199 INSERT INTO articles(id, title) VALUES(5, 'mysql');
200
201 COMMIT;
202
203 INSERT INTO articles(id, title) VALUES(6, 'mysql');
204
205 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
206
207 # Test release savepoint 4(S1,S2,RL2,RL1)
208 TRUNCATE TABLE articles;
209
210 INSERT INTO articles(id, title) VALUES(1, 'mysql');
211
212 BEGIN;
213
214 INSERT INTO articles(id, title) VALUES(2, 'mysql');
215
216 SAVEPOINT sp1;
217
218 INSERT INTO articles(id, title) VALUES(3, 'mysql');
219
220 SAVEPOINT sp2;
221
222 INSERT INTO articles(id, title) VALUES(4, 'mysql');
223
224 RELEASE SAVEPOINT sp2;
225
226 INSERT INTO articles(id, title) VALUES(5, 'mysql');
227
228 RELEASE SAVEPOINT sp1;
229
230 INSERT INTO articles(id, title) VALUES(6, 'mysql');
231
232 COMMIT;
233
234 INSERT INTO articles(id, title) VALUES(7, 'mysql');
235
236 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
237
238 # Test Part 3: RELEASE & ROLLBACK TO SAVEPOINT
239 # Test release & rollback to savepoint 1(S1,RB1,S2,RL2)
240 TRUNCATE TABLE articles;
241
242 INSERT INTO articles(id, title) VALUES(1, 'mysql');
243
244 BEGIN;
245
246 INSERT INTO articles(id, title) VALUES(2, 'mysql');
247
248 SAVEPOINT sp1;
249
250 INSERT INTO articles(id, title) VALUES(3, 'mysql');
251
252 ROLLBACK TO SAVEPOINT sp1;
253
254 INSERT INTO articles(id, title) VALUES(4, 'mysql');
255
256 SAVEPOINT sp2;
257
258 INSERT INTO articles(id, title) VALUES(5, 'mysql');
259
260 RELEASE SAVEPOINT sp2;
261
262 INSERT INTO articles(id, title) VALUES(6, 'mysql');
263
264 COMMIT;
265
266 INSERT INTO articles(id, title) VALUES(7, 'mysql');
267
268 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
269
270 # Test release & rollback to savepoint 2(S1,RL1,S2,RB2)
271 TRUNCATE TABLE articles;
272
273 INSERT INTO articles(id, title) VALUES(1, 'mysql');
274
275 BEGIN;
276
277 INSERT INTO articles(id, title) VALUES(2, 'mysql');
278
279 SAVEPOINT sp1;
280
281 INSERT INTO articles(id, title) VALUES(3, 'mysql');
282
283 RELEASE SAVEPOINT sp1;
284
285 INSERT INTO articles(id, title) VALUES(4, 'mysql');
286
287 SAVEPOINT sp2;
288
289 INSERT INTO articles(id, title) VALUES(5, 'mysql');
290
291 ROLLBACK TO SAVEPOINT sp2;
292
293 INSERT INTO articles(id, title) VALUES(6, 'mysql');
294
295 COMMIT;
296
297 INSERT INTO articles(id, title) VALUES(7, 'mysql');
298
299 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
300
301 # Test release & rollback to savepoint 3(S1,S2,RL2,RB1)
302 TRUNCATE TABLE articles;
303
304 INSERT INTO articles(id, title) VALUES(1, 'mysql');
305
306 BEGIN;
307
308 INSERT INTO articles(id, title) VALUES(2, 'mysql');
309
310 SAVEPOINT sp1;
311
312 INSERT INTO articles(id, title) VALUES(3, 'mysql');
313
314 SAVEPOINT sp2;
315
316 INSERT INTO articles(id, title) VALUES(4, 'mysql');
317
318 RELEASE SAVEPOINT sp2;
319
320 INSERT INTO articles(id, title) VALUES(5, 'mysql');
321
322 ROLLBACK TO SAVEPOINT sp1;
323
324 INSERT INTO articles(id, title) VALUES(6, 'mysql');
325
326 COMMIT;
327
328 INSERT INTO articles(id, title) VALUES(7, 'mysql');
329
330 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
331
332 # Test release & rollback to savepoint 4(S1,S2,RB2,RL1)
333 TRUNCATE TABLE articles;
334
335 INSERT INTO articles(id, title) VALUES(1, 'mysql');
336
337 BEGIN;
338
339 INSERT INTO articles(id, title) VALUES(2, 'mysql');
340
341 SAVEPOINT sp1;
342
343 INSERT INTO articles(id, title) VALUES(3, 'mysql');
344
345 SAVEPOINT sp2;
346
347 INSERT INTO articles(id, title) VALUES(4, 'mysql');
348
349 ROLLBACK TO SAVEPOINT sp2;
350
351 INSERT INTO articles(id, title) VALUES(5, 'mysql');
352
353 RELEASE SAVEPOINT sp1;
354
355 INSERT INTO articles(id, title) VALUES(6, 'mysql');
356
357 COMMIT;
358
359 INSERT INTO articles(id, title) VALUES(7, 'mysql');
360
361 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
362
363 # Test Part 4: ROLLBACK & SAVEPOINT
364 # Test rollback 1
365 TRUNCATE TABLE articles;
366
367 INSERT INTO articles(id, title) VALUES(1, 'mysql');
368
369 CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB;
370 BEGIN;
371 INSERT INTO t SET a=1;
372 SAVEPOINT t;
373 INSERT INTO articles(id, title) VALUES(2, 'mysql');
374 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
375 ROLLBACK TO SAVEPOINT t;
376 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
377 SELECT * FROM t;
378 COMMIT;
379
380 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
381
382 BEGIN;
383 INSERT INTO t SET a=2;
384 INSERT INTO articles(id, title) VALUES(2, 'mysql');
385
386 ROLLBACK;
387
388 SELECT * FROM t;
389 DROP TABLE t;
390
391 INSERT INTO articles(id, title) VALUES(3, 'mysql');
392
393 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
394
395 # Test rollback 2(S1)
396 TRUNCATE TABLE articles;
397
398 INSERT INTO articles(id, title) VALUES(1, 'mysql');
399
400 BEGIN;
401
402 INSERT INTO articles(id, title) VALUES(2, 'mysql');
403
404 SAVEPOINT sp1;
405
406 INSERT INTO articles(id, title) VALUES(3, 'mysql');
407
408 ROLLBACK;
409
410 INSERT INTO articles(id, title) VALUES(4, 'mysql');
411
412 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
413
414 # Test rollback 3(S1,RL1)
415 TRUNCATE TABLE articles;
416
417 INSERT INTO articles(id, title) VALUES(1, 'mysql');
418
419 BEGIN;
420
421 INSERT INTO articles(id, title) VALUES(2, 'mysql');
422
423 SAVEPOINT sp1;
424
425 INSERT INTO articles(id, title) VALUES(3, 'mysql');
426
427 RELEASE SAVEPOINT sp1;
428
429 INSERT INTO articles(id, title) VALUES(4, 'mysql');
430
431 ROLLBACK;
432
433 INSERT INTO articles(id, title) VALUES(5, 'mysql');
434
435 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
436
437 # Test rollback 4(S1,RB1)
438 TRUNCATE TABLE articles;
439
440 INSERT INTO articles(id, title) VALUES(1, 'mysql');
441
442 BEGIN;
443
444 INSERT INTO articles(id, title) VALUES(2, 'mysql');
445
446 SAVEPOINT sp1;
447
448 INSERT INTO articles(id, title) VALUES(3, 'mysql');
449
450 ROLLBACK TO SAVEPOINT sp1;
451
452 INSERT INTO articles(id, title) VALUES(4, 'mysql');
453
454 ROLLBACK;
455
456 INSERT INTO articles(id, title) VALUES(5, 'mysql');
457
458 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
459
460 # Test rollback 5(S1,S2,RB2,RL1)
461 TRUNCATE TABLE articles;
462
463 INSERT INTO articles(id, title) VALUES(1, 'mysql');
464
465 BEGIN;
466
467 INSERT INTO articles(id, title) VALUES(2, 'mysql');
468
469 SAVEPOINT sp1;
470
471 INSERT INTO articles(id, title) VALUES(3, 'mysql');
472
473 SAVEPOINT sp2;
474
475 INSERT INTO articles(id, title) VALUES(4, 'mysql');
476
477 ROLLBACK TO SAVEPOINT sp2;
478
479 INSERT INTO articles(id, title) VALUES(5, 'mysql');
480
481 RELEASE SAVEPOINT sp1;
482
483 INSERT INTO articles(id, title) VALUES(6, 'mysql');
484
485 ROLLBACK;
486
487 INSERT INTO articles(id, title) VALUES(7, 'mysql');
488
489 SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');
490
491 DROP TABLE articles;