Debian Codegraph @

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

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