Debian Codegraph @

mysql-test/suite/innodb_zip/r/undo_log_tmp_table.result

raw
1 call mtr.ADD_suppression(".*Resizing redo log.*");
2 call mtr.ADD_suppression(".*Starting to delete and rewrite redo log files.*");
3 call mtr.ADD_suppression(".*New redo log files created.*");
4 CREATE PROCEDURE populate_tables(IN id VARCHAR(10))
5 begin
6 declare n int default 20;
7 set global innodb_file_per_table=on;
8 DROP TABLE IF EXISTS t1,t2,t3,t4;
9 CREATE TEMPORARY TABLE t1_temp(c1 int NOT NULL,
10 c2 int NOT NULL,
11 c3 char(255) NOT NULL,
12 c4 text(600) NOT NULL,
13 c5 blob(600) NOT NULL,
14 c6 varchar(600) NOT NULL,
15 c7 varchar(600) NOT NULL,
16 c8 datetime,
17 c9 decimal(6,3),
18 PRIMARY KEY (c1),
19 INDEX (c3,c4(50),c5(50)),
20 INDEX (c2))
21 charset latin1 ENGINE=InnoDB ROW_FORMAT=redundant;
22 set @s = concat("CREATE TABLE t1",id," ( c1 int NOT NULL, c2 int NOT NULL, c3 char(255) NOT NULL, c4 text(600) NOT NULL, c5 blob(600) NOT NULL, c6 varchar(600) NOT NULL, c7 varchar(600) NOT NULL, c8 datetime, c9 decimal(6,3), PRIMARY KEY (c1), INDEX (c3,c4(50),c5(50)), INDEX (c2)) charset latin1 ENGINE=InnoDB ROW_FORMAT=redundant;");
23 PREPARE createTable FROM @s;
24 EXECUTE createTable;
25 DEALLOCATE PREPARE createTable;
26 CREATE TEMPORARY TABLE t2_temp(c1 int NOT NULL,
27 c2 int NOT NULL,
28 c3 char(255) NOT NULL,
29 c4 text(600) NOT NULL,
30 c5 blob(600) NOT NULL,
31 c6 varchar(600) NOT NULL,
32 c7 varchar(600) NOT NULL,
33 c8 datetime,
34 c9 decimal(6,3),
35 PRIMARY KEY (c1),
36 INDEX (c3,c4(50),c5(50)),
37 INDEX (c2))
38 charset latin1 ENGINE=InnoDB ROW_FORMAT=compact;
39 set @s = concat("CREATE TABLE t2",id," (c1 int NOT NULL, c2 int NOT NULL, c3 char(255) NOT NULL, c4 text(600) NOT NULL, c5 blob(600) NOT NULL, c6 varchar(600) NOT NULL, c7 varchar(600) NOT NULL, c8 datetime, c9 decimal(6,3), PRIMARY KEY (c1), INDEX (c3,c4(50),c5(50)), INDEX (c2)) charset latin1 ENGINE=InnoDB ROW_FORMAT=compact;");
40 PREPARE createTable FROM @s;
41 EXECUTE createTable;
42 DEALLOCATE PREPARE createTable;
43 set @s = concat("CREATE TABLE t3",id," (c1 int NOT NULL, c2 int NOT NULL, c3 char(255) NOT NULL, c4 text(600) NOT NULL, c5 blob(600) NOT NULL, c6 varchar(600) NOT NULL, c7 varchar(600) NOT NULL, c8 datetime, c9 decimal(6,3), PRIMARY KEY (c1), INDEX (c3,c4(50),c5(50)), INDEX (c2)) charset latin1 ENGINE=InnoDB ROW_FORMAT=compressed key_block_size=4;");
44 PREPARE createTable FROM @s;
45 EXECUTE createTable;
46 DEALLOCATE PREPARE createTable;
47 CREATE TEMPORARY TABLE t4_temp(c1 int NOT NULL,
48 c2 int NOT NULL,
49 c3 char(255) NOT NULL,
50 c4 text(600) NOT NULL,
51 c5 blob(600) NOT NULL,
52 c6 varchar(600) NOT NULL,
53 c7 varchar(600) NOT NULL,
54 c8 datetime,
55 c9 decimal(6,3),
56 PRIMARY KEY (c1),
57 INDEX (c3,c4(50),c5(50)),
58 INDEX (c2))
59 charset latin1 ENGINE=InnoDB ROW_FORMAT=dynamic;
60 set @s = concat("CREATE TABLE t4",id," (c1 int NOT NULL, c2 int NOT NULL, c3 char(255) NOT NULL, c4 text(600) NOT NULL, c5 blob(600) NOT NULL, c6 varchar(600) NOT NULL, c7 varchar(600) NOT NULL, c8 datetime, c9 decimal(6,3), PRIMARY KEY (c1), INDEX (c3,c4(50),c5(50)), INDEX (c2)) charset latin1 ENGINE=InnoDB ROW_FORMAT=dynamic;");
61 PREPARE createTable FROM @s;
62 EXECUTE createTable;
63 DEALLOCATE PREPARE createTable;
64 while (n > 0) do
65 START TRANSACTION;
66 set @s = concat("INSERT INTO t1",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30), REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70), REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70), NOW(),(100.55+",n,"));");
67 PREPARE insertIntoTable FROM @s;
68 EXECUTE insertIntoTable;
69 DEALLOCATE PREPARE insertIntoTable;
70 INSERT INTO t1_temp VALUES(n,n,REPEAT(concat(' tc3_',n),30),
71 REPEAT(concat(' tc4_',n),70),REPEAT(concat(' tc_',n),70),
72 REPEAT(concat(' tc6_',n),70),REPEAT(concat(' tc7_',n),70),
73 NOW(),(100.55+n));
74 set @s = concat("INSERT INTO t2",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30), REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70), REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70), NOW(),(100.55+",n,"));");
75 PREPARE insertIntoTable FROM @s;
76 EXECUTE insertIntoTable;
77 DEALLOCATE PREPARE insertIntoTable;
78 INSERT INTO t2_temp VALUES(n,n,REPEAT(concat(' tc3_',n),30),
79 REPEAT(concat(' tc4_',n),70),REPEAT(concat(' tc_',n),70),
80 REPEAT(concat(' tc6_',n),70),REPEAT(concat(' tc7_',n),70),
81 NOW(),(100.55+n));
82 savepoint a;
83 set @s = concat("INSERT INTO t3",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30), REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70), REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70), NOW(),(100.55+",n,"));");
84 PREPARE insertIntoTable FROM @s;
85 EXECUTE insertIntoTable;
86 DEALLOCATE PREPARE insertIntoTable;
87 savepoint b;
88 set @s = concat("INSERT INTO t4",id," VALUES(",n,",",n,",REPEAT(concat(' tc3_',",n,"),30), REPEAT(concat(' tc4_',",n,"),70),REPEAT(concat(' tc_',",n,"),70), REPEAT(concat(' tc6_',",n,"),70),REPEAT(concat(' tc7_',",n,"),70), NOW(),(100.55+",n,"));");
89 PREPARE insertIntoTable FROM @s;
90 EXECUTE insertIntoTable;
91 DEALLOCATE PREPARE insertIntoTable;
92 INSERT INTO t4_temp VALUES(n,n,REPEAT(concat(' tc3_',n),30),
93 REPEAT(concat(' tc4_',n),70),REPEAT(concat(' tc_',n),70),
94 REPEAT(concat(' tc6_',n),70),REPEAT(concat(' tc7_',n),70),
95 NOW(),(100.55+n));
96 if (n > 10) then
97 if (n > 10 and n <=12) then
98 ROLLBACK TO SAVEPOINT a;
99 COMMIT;
100 end if;
101 if (n > 12 and n < 15) then
102 ROLLBACK TO SAVEPOINT b;
103 COMMIT;
104 end if;
105 if (n > 15) then
106 COMMIT;
107 end if;
108 else
109 if (n > 5) then
110 START TRANSACTION;
111 DELETE FROM t1_temp WHERE c1 > 10 ;
112 DELETE FROM t2_temp WHERE c1 > 10 ;
113 DELETE FROM t4_temp WHERE c1 > 10 ;
114 rollback;
115 START TRANSACTION;
116 update t1_temp set c1 = c1 + 1000 WHERE c1 > 10;
117 update t2_temp set c1 = c1 + 1000 WHERE c1 > 10;
118 update t4_temp set c1 = c1 + 1000 WHERE c1 > 10;
119 rollback;
120 end if;
121 end if;
122 if (n < 5) then
123 rollback;
124 end if;
125 FLUSH logs;
126 ALTER TABLE t1_temp DROP PRIMARY KEY;
127 ALTER TABLE t1_temp ADD PRIMARY KEY (c1,c3(10),c4(10));
128 ALTER TABLE t2_temp DROP PRIMARY KEY;
129 ALTER TABLE t2_temp ADD PRIMARY KEY (c1,c3(10),c4(10));
130 ALTER TABLE t4_temp DROP PRIMARY KEY;
131 ALTER TABLE t4_temp ADD PRIMARY KEY (c1,c3(10),c4(10));
132 FLUSH tables;
133 START TRANSACTION;
134 set @s = concat("INSERT INTO t1",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
135 PREPARE insertIntoTable FROM @s;
136 EXECUTE insertIntoTable;
137 DEALLOCATE PREPARE insertIntoTable;
138 INSERT INTO t1_temp VALUES(n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
139 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
140 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
141 NOW(),(100.55+n+100));
142 set @s = concat("INSERT INTO t2",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
143 PREPARE insertIntoTable FROM @s;
144 EXECUTE insertIntoTable;
145 DEALLOCATE PREPARE insertIntoTable;
146 INSERT INTO t2_temp VALUES(n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
147 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
148 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
149 NOW(),(100.55+n+100));
150 set @s = concat("INSERT INTO t3",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
151 PREPARE insertIntoTable FROM @s;
152 EXECUTE insertIntoTable;
153 DEALLOCATE PREPARE insertIntoTable;
154 set @s = concat("INSERT INTO t4",id," VALUES(",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
155 PREPARE insertIntoTable FROM @s;
156 EXECUTE insertIntoTable;
157 DEALLOCATE PREPARE insertIntoTable;
158 INSERT INTO t4_temp VALUES(n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
159 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
160 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
161 NOW(),(100.55+n+100));
162 DELETE FROM t1_temp WHERE c1 between 100 and 110;
163 DELETE FROM t2_temp WHERE c1 between 100 and 110;
164 DELETE FROM t4_temp WHERE c1 between 100 and 110;
165 update t1_temp set c1 = c1+1 WHERE c1>110;
166 update t2_temp set c1 = c1+1 WHERE c1>110;
167 update t4_temp set c1 = c1+1 WHERE c1>110;
168 savepoint a;
169 set @s = concat("INSERT INTO t1",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
170 PREPARE insertIntoTable FROM @s;
171 EXECUTE insertIntoTable;
172 DEALLOCATE PREPARE insertIntoTable;
173 INSERT INTO t1_temp VALUES(300+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
174 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
175 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
176 NOW(),(100.55+n+100));
177 set @s = concat("INSERT INTO t2",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
178 PREPARE insertIntoTable FROM @s;
179 EXECUTE insertIntoTable;
180 DEALLOCATE PREPARE insertIntoTable;
181 INSERT INTO t2_temp VALUES(300+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
182 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
183 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
184 NOW(),(100.55+n+100));
185 set @s = concat("INSERT INTO t3",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
186 PREPARE insertIntoTable FROM @s;
187 EXECUTE insertIntoTable;
188 DEALLOCATE PREPARE insertIntoTable;
189 set @s = concat("INSERT INTO t4",id," VALUES(300+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
190 PREPARE insertIntoTable FROM @s;
191 EXECUTE insertIntoTable;
192 DEALLOCATE PREPARE insertIntoTable;
193 INSERT INTO t4_temp VALUES(300+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
194 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
195 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
196 NOW(),(100.55+n+100));
197 savepoint b;
198 set @s = concat("INSERT INTO t1",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
199 PREPARE insertIntoTable FROM @s;
200 EXECUTE insertIntoTable;
201 DEALLOCATE PREPARE insertIntoTable;
202 INSERT INTO t1_temp VALUES(400+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
203 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
204 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
205 NOW(),(100.55+n+100));
206 set @s = concat("INSERT INTO t2",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
207 PREPARE insertIntoTable FROM @s;
208 EXECUTE insertIntoTable;
209 DEALLOCATE PREPARE insertIntoTable;
210 INSERT INTO t2_temp VALUES(400+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
211 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
212 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
213 NOW(),(100.55+n+100));
214 set @s = concat("INSERT INTO t3",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
215 PREPARE insertIntoTable FROM @s;
216 EXECUTE insertIntoTable;
217 DEALLOCATE PREPARE insertIntoTable;
218 set @s = concat("INSERT INTO t4",id," VALUES(400+",n,"+100,",n,"+100,REPEAT(concat(' tc3_',",n,"+100),30), REPEAT(concat(' tc4_',",n,"+100),70),REPEAT(concat(' tc_',",n,"+100),70), REPEAT(concat(' tc6_',",n,"+100),60),REPEAT(concat(' tc7_',",n,"+100),60), NOW(),(100.55+",n,"+100));");
219 PREPARE insertIntoTable FROM @s;
220 EXECUTE insertIntoTable;
221 DEALLOCATE PREPARE insertIntoTable;
222 INSERT INTO t4_temp VALUES(400+n+100,n+100,REPEAT(concat(' tc3_',n+100),30),
223 REPEAT(concat(' tc4_',n+100),70),REPEAT(concat(' tc_',n+100),70),
224 REPEAT(concat(' tc6_',n+100),60),REPEAT(concat(' tc7_',n+100),60),
225 NOW(),(100.55+n+100));
226 savepoint c;
227 rollback to b;
228 rollback to a;
229 COMMIT;
230 COMMIT;
231 rollback;
232 set n = n - 1;
233 end while;
234 end|
235 #---client 1 : dml operation ---"
236 #---client 2 : dml operation ---"
237 # In connection 1
238 SELECT count(*) FROM t1_1;
239 count(*)
240 36
241 SELECT count(*) FROM t2_1;
242 count(*)
243 36
244 SELECT count(*) FROM t3_1;
245 count(*)
246 34
247 SELECT count(*) FROM t4_1;
248 count(*)
249 32
250 SELECT c1 FROM t1_1;
251 c1
252 5
253 6
254 7
255 8
256 9
257 10
258 11
259 12
260 13
261 14
262 15
263 16
264 17
265 18
266 19
267 20
268 101
269 102
270 103
271 104
272 105
273 106
274 107
275 108
276 109
277 110
278 111
279 112
280 113
281 114
282 115
283 116
284 117
285 118
286 119
287 120
288 SELECT c1 FROM t2_1;
289 c1
290 5
291 6
292 7
293 8
294 9
295 10
296 11
297 12
298 13
299 14
300 15
301 16
302 17
303 18
304 19
305 20
306 101
307 102
308 103
309 104
310 105
311 106
312 107
313 108
314 109
315 110
316 111
317 112
318 113
319 114
320 115
321 116
322 117
323 118
324 119
325 120
326 SELECT c1 FROM t3_1;
327 c1
328 5
329 6
330 7
331 8
332 9
333 10
334 13
335 14
336 15
337 16
338 17
339 18
340 19
341 20
342 101
343 102
344 103
345 104
346 105
347 106
348 107
349 108
350 109
351 110
352 111
353 112
354 113
355 114
356 115
357 116
358 117
359 118
360 119
361 120
362 SELECT c1 FROM t4_1;
363 c1
364 5
365 6
366 7
367 8
368 9
369 10
370 15
371 16
372 17
373 18
374 19
375 20
376 101
377 102
378 103
379 104
380 105
381 106
382 107
383 108
384 109
385 110
386 111
387 112
388 113
389 114
390 115
391 116
392 117
393 118
394 119
395 120
396 SELECT count(*) FROM t1_temp;
397 count(*)
398 26
399 SELECT count(*) FROM t2_temp;
400 count(*)
401 26
402 SELECT count(*) FROM t4_temp;
403 count(*)
404 22
405 SELECT c1 FROM t1_temp;
406 c1
407 5
408 6
409 7
410 8
411 9
412 10
413 11
414 12
415 13
416 14
417 15
418 16
419 17
420 18
421 19
422 20
423 122
424 124
425 126
426 128
427 130
428 132
429 134
430 136
431 138
432 140
433 SELECT c1 FROM t2_temp;
434 c1
435 5
436 6
437 7
438 8
439 9
440 10
441 11
442 12
443 13
444 14
445 15
446 16
447 17
448 18
449 19
450 20
451 122
452 124
453 126
454 128
455 130
456 132
457 134
458 136
459 138
460 140
461 SELECT c1 FROM t4_temp;
462 c1
463 5
464 6
465 7
466 8
467 9
468 10
469 15
470 16
471 17
472 18
473 19
474 20
475 122
476 124
477 126
478 128
479 130
480 132
481 134
482 136
483 138
484 140
485 # In connection 2
486 SELECT count(*) FROM t1_2;
487 count(*)
488 36
489 SELECT count(*) FROM t2_2;
490 count(*)
491 36
492 SELECT count(*) FROM t3_2;
493 count(*)
494 34
495 SELECT count(*) FROM t4_2;
496 count(*)
497 32
498 SELECT c1 FROM t1_2;
499 c1
500 5
501 6
502 7
503 8
504 9
505 10
506 11
507 12
508 13
509 14
510 15
511 16
512 17
513 18
514 19
515 20
516 101
517 102
518 103
519 104
520 105
521 106
522 107
523 108
524 109
525 110
526 111
527 112
528 113
529 114
530 115
531 116
532 117
533 118
534 119
535 120
536 SELECT c1 FROM t2_2;
537 c1
538 5
539 6
540 7
541 8
542 9
543 10
544 11
545 12
546 13
547 14
548 15
549 16
550 17
551 18
552 19
553 20
554 101
555 102
556 103
557 104
558 105
559 106
560 107
561 108
562 109
563 110
564 111
565 112
566 113
567 114
568 115
569 116
570 117
571 118
572 119
573 120
574 SELECT c1 FROM t3_2;
575 c1
576 5
577 6
578 7
579 8
580 9
581 10
582 13
583 14
584 15
585 16
586 17
587 18
588 19
589 20
590 101
591 102
592 103
593 104
594 105
595 106
596 107
597 108
598 109
599 110
600 111
601 112
602 113
603 114
604 115
605 116
606 117
607 118
608 119
609 120
610 SELECT c1 FROM t4_2;
611 c1
612 5
613 6
614 7
615 8
616 9
617 10
618 15
619 16
620 17
621 18
622 19
623 20
624 101
625 102
626 103
627 104
628 105
629 106
630 107
631 108
632 109
633 110
634 111
635 112
636 113
637 114
638 115
639 116
640 117
641 118
642 119
643 120
644 SELECT count(*) FROM t1_temp;
645 count(*)
646 26
647 SELECT count(*) FROM t2_temp;
648 count(*)
649 26
650 SELECT count(*) FROM t4_temp;
651 count(*)
652 22
653 SELECT c1 FROM t1_temp;
654 c1
655 5
656 6
657 7
658 8
659 9
660 10
661 11
662 12
663 13
664 14
665 15
666 16
667 17
668 18
669 19
670 20
671 122
672 124
673 126
674 128
675 130
676 132
677 134
678 136
679 138
680 140
681 SELECT c1 FROM t2_temp;
682 c1
683 5
684 6
685 7
686 8
687 9
688 10
689 11
690 12
691 13
692 14
693 15
694 16
695 17
696 18
697 19
698 20
699 122
700 124
701 126
702 128
703 130
704 132
705 134
706 136
707 138
708 140
709 SELECT c1 FROM t4_temp;
710 c1
711 5
712 6
713 7
714 8
715 9
716 10
717 15
718 16
719 17
720 18
721 19
722 20
723 122
724 124
725 126
726 128
727 130
728 132
729 134
730 136
731 138
732 140
733 # In connection 1
734 set AUTOCOMMIT = 0;
735 ALTER TABLE t1_temp DROP PRIMARY KEY;
736 ALTER TABLE t1_temp ADD PRIMARY KEY (c1);
737 ALTER TABLE t2_temp DROP PRIMARY KEY;
738 ALTER TABLE t2_temp ADD PRIMARY KEY (c1);
739 ALTER TABLE t4_temp DROP PRIMARY KEY;
740 ALTER TABLE t4_temp ADD PRIMARY KEY (c1);
741 INSERT INTO t1_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
742 ERROR 23000: Duplicate entry '20' for key 't1_temp.PRIMARY'
743 insert ignore into t1_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
744 Warnings:
745 Warning 1062 Duplicate entry '20' for key 't1_temp.PRIMARY'
746 INSERT INTO t2_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
747 ERROR 23000: Duplicate entry '20' for key 't2_temp.PRIMARY'
748 insert ignore into t2_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
749 Warnings:
750 Warning 1062 Duplicate entry '20' for key 't2_temp.PRIMARY'
751 INSERT INTO t4_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
752 ERROR 23000: Duplicate entry '20' for key 't4_temp.PRIMARY'
753 insert ignore into t4_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
754 Warnings:
755 Warning 1062 Duplicate entry '20' for key 't4_temp.PRIMARY'
756 INSERT INTO t1_temp VALUES (1,1,'a','a','a','a','a',NOW(),100.55),
757 (20,1,'a','a','a','a','a',NOW(),100.55);
758 ERROR 23000: Duplicate entry '20' for key 't1_temp.PRIMARY'
759 INSERT INTO t2_temp VALUES (1,1,'a','a','a','a','a',NOW(),100.55),
760 (20,1,'a','a','a','a','a',NOW(),100.55);
761 ERROR 23000: Duplicate entry '20' for key 't2_temp.PRIMARY'
762 INSERT INTO t4_temp VALUES (1,1,'a','a','a','a','a',NOW(),100.55),
763 (20,1,'a','a','a','a','a',NOW(),100.55);
764 ERROR 23000: Duplicate entry '20' for key 't4_temp.PRIMARY'
765 set AUTOCOMMIT = 1;
766 SELECT c1,c2 FROM t1_temp WHERE c1 in (20,1);
767 c1 c2
768 20 20
769 SELECT c1,c2 FROM t2_temp WHERE c1 in (20,1);
770 c1 c2
771 20 20
772 SELECT c1,c2 FROM t4_temp WHERE c1 in (20,1);
773 c1 c2
774 20 20
775 REPLACE INTO t1_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
776 REPLACE INTO t2_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
777 REPLACE INTO t4_temp VALUES (20,1,'a','a','a','a','a',NOW(),100.55);
778 SELECT c1,c2,c3,c4,c5,c6,c7,c9 FROM t1_temp WHERE c1 = 20;
779 c1 c2 c3 c4 c5 c6 c7 c9
780 20 1 a a a a a 100.550
781 SELECT c1,c2,c3,c4,c5,c6,c7,c9 FROM t2_temp WHERE c1 = 20;
782 c1 c2 c3 c4 c5 c6 c7 c9
783 20 1 a a a a a 100.550
784 SELECT c1,c2,c3,c4,c5,c6,c7,c9 FROM t4_temp WHERE c1 = 20;
785 c1 c2 c3 c4 c5 c6 c7 c9
786 20 1 a a a a a 100.550
787 update ignore t1_temp set c1 = 20 WHERE c1 = 140 ;
788 Warnings:
789 Warning 1062 Duplicate entry '20' for key 't1_temp.PRIMARY'
790 update ignore t2_temp set c1 = 20 WHERE c1 = 140 ;
791 Warnings:
792 Warning 1062 Duplicate entry '20' for key 't2_temp.PRIMARY'
793 update ignore t4_temp set c1 = 20 WHERE c1 = 140 ;
794 Warnings:
795 Warning 1062 Duplicate entry '20' for key 't4_temp.PRIMARY'
796 SELECT count(*) FROM t1_temp WHERE c1 = 140;
797 count(*)
798 1
799 SELECT count(*) FROM t2_temp WHERE c1 = 140;
800 count(*)
801 1
802 SELECT count(*) FROM t4_temp WHERE c1 = 140;
803 count(*)
804 1
805 ALTER TABLE t1_temp ADD COLUMN c10 int default 99 ,
806 ADD COLUMN c11 varchar(100) default 'test';
807 ALTER TABLE t1_temp DROP PRIMARY KEY;
808 ALTER TABLE t1_temp ADD PRIMARY KEY (c1);
809 INSERT INTO t1_temp (c1,c2,c3,c4,c5,c6,c7,c8,c9) VALUES (-1,-1,'a','a','a','a','a',NOW(),100.55);
810 SELECT c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 FROM t1_temp WHERE c1 < 0;
811 c1 c2 c3 c4 c5 c6 c7 c9 c10 c11
812 -1 -1 a a a a a 100.550 99 test
813 SELECT count(*) FROM t1_temp WHERE c10 = 99 and c11 like 'test';
814 count(*)
815 27
816 INSERT INTO t1_temp (c1,c2,c3,c4,c5,c6,c7,c8,c9) VALUES (-1,-1,'a','a','a','a','a',NOW(),100.55)
817 ON DUPLICATE KEY UPDATE c1=-2,c2=-2;
818 SELECT c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 FROM t1_temp WHERE c1 < 0;
819 c1 c2 c3 c4 c5 c6 c7 c9 c10 c11
820 -2 -2 a a a a a 100.550 99 test
821 DROP TABLE t1_1 ,t2_1 ,t3_1,t4_1;
822 DROP TABLE t1_2 ,t2_2 ,t3_2,t4_2;
823 # restart: --innodb_rollback_segments=20 --innodb_redo_log_capacity=20M
824 call populate_tables('_1');;
825 call populate_tables('_2');;
826 "#connection 1 - verify tables"
827 SELECT count(*) FROM t1_1;
828 count(*)
829 36
830 SELECT count(*) FROM t2_1;
831 count(*)
832 36
833 SELECT count(*) FROM t3_1;
834 count(*)
835 34
836 SELECT count(*) FROM t4_1;
837 count(*)
838 32
839 SELECT c1 FROM t1_1;
840 c1
841 5
842 6
843 7
844 8
845 9
846 10
847 11
848 12
849 13
850 14
851 15
852 16
853 17
854 18
855 19
856 20
857 101
858 102
859 103
860 104
861 105
862 106
863 107
864 108
865 109
866 110
867 111
868 112
869 113
870 114
871 115
872 116
873 117
874 118
875 119
876 120
877 SELECT c1 FROM t2_1;
878 c1
879 5
880 6
881 7
882 8
883 9
884 10
885 11
886 12
887 13
888 14
889 15
890 16
891 17
892 18
893 19
894 20
895 101
896 102
897 103
898 104
899 105
900 106
901 107
902 108
903 109
904 110
905 111
906 112
907 113
908 114
909 115
910 116
911 117
912 118
913 119
914 120
915 SELECT c1 FROM t3_1;
916 c1
917 5
918 6
919 7
920 8
921 9
922 10
923 13
924 14
925 15
926 16
927 17
928 18
929 19
930 20
931 101
932 102
933 103
934 104
935 105
936 106
937 107
938 108
939 109
940 110
941 111
942 112
943 113
944 114
945 115
946 116
947 117
948 118
949 119
950 120
951 SELECT c1 FROM t4_1;
952 c1
953 5
954 6
955 7
956 8
957 9
958 10
959 15
960 16
961 17
962 18
963 19
964 20
965 101
966 102
967 103
968 104
969 105
970 106
971 107
972 108
973 109
974 110
975 111
976 112
977 113
978 114
979 115
980 116
981 117
982 118
983 119
984 120
985 SELECT count(*) FROM t1_temp;
986 count(*)
987 26
988 SELECT count(*) FROM t2_temp;
989 count(*)
990 26
991 SELECT count(*) FROM t4_temp;
992 count(*)
993 22
994 SELECT c1 FROM t1_temp;
995 c1
996 5
997 6
998 7
999 8
1000 9
1001 10
1002 11
1003 12
1004 13
1005 14
1006 15
1007 16
1008 17
1009 18
1010 19
1011 20
1012 122
1013 124
1014 126
1015 128
1016 130
1017 132
1018 134
1019 136
1020 138
1021 140
1022 SELECT c1 FROM t2_temp;
1023 c1
1024 5
1025 6
1026 7
1027 8
1028 9
1029 10
1030 11
1031 12
1032 13
1033 14
1034 15
1035 16
1036 17
1037 18
1038 19
1039 20
1040 122
1041 124
1042 126
1043 128
1044 130
1045 132
1046 134
1047 136
1048 138
1049 140
1050 SELECT c1 FROM t4_temp;
1051 c1
1052 5
1053 6
1054 7
1055 8
1056 9
1057 10
1058 15
1059 16
1060 17
1061 18
1062 19
1063 20
1064 122
1065 124
1066 126
1067 128
1068 130
1069 132
1070 134
1071 136
1072 138
1073 140
1074 DROP TABLE t1_1 ,t2_1 ,t3_1,t4_1;
1075 "#connection 2 - verify tables"
1076 SELECT count(*) FROM t1_2;
1077 count(*)
1078 36
1079 SELECT count(*) FROM t2_2;
1080 count(*)
1081 36
1082 SELECT count(*) FROM t3_2;
1083 count(*)
1084 34
1085 SELECT count(*) FROM t4_2;
1086 count(*)
1087 32
1088 SELECT c1 FROM t1_2;
1089 c1
1090 5
1091 6
1092 7
1093 8
1094 9
1095 10
1096 11
1097 12
1098 13
1099 14
1100 15
1101 16
1102 17
1103 18
1104 19
1105 20
1106 101
1107 102
1108 103
1109 104
1110 105
1111 106
1112 107
1113 108
1114 109
1115 110
1116 111
1117 112
1118 113
1119 114
1120 115
1121 116
1122 117
1123 118
1124 119
1125 120
1126 SELECT c1 FROM t2_2;
1127 c1
1128 5
1129 6
1130 7
1131 8
1132 9
1133 10
1134 11
1135 12
1136 13
1137 14
1138 15
1139 16
1140 17
1141 18
1142 19
1143 20
1144 101
1145 102
1146 103
1147 104
1148 105
1149 106
1150 107
1151 108
1152 109
1153 110
1154 111
1155 112
1156 113
1157 114
1158 115
1159 116
1160 117
1161 118
1162 119
1163 120
1164 SELECT c1 FROM t3_2;
1165 c1
1166 5
1167 6
1168 7
1169 8
1170 9
1171 10
1172 13
1173 14
1174 15
1175 16
1176 17
1177 18
1178 19
1179 20
1180 101
1181 102
1182 103
1183 104
1184 105
1185 106
1186 107
1187 108
1188 109
1189 110
1190 111
1191 112
1192 113
1193 114
1194 115
1195 116
1196 117
1197 118
1198 119
1199 120
1200 SELECT c1 FROM t4_2;
1201 c1
1202 5
1203 6
1204 7
1205 8
1206 9
1207 10
1208 15
1209 16
1210 17
1211 18
1212 19
1213 20
1214 101
1215 102
1216 103
1217 104
1218 105
1219 106
1220 107
1221 108
1222 109
1223 110
1224 111
1225 112
1226 113
1227 114
1228 115
1229 116
1230 117
1231 118
1232 119
1233 120
1234 SELECT count(*) FROM t1_temp;
1235 count(*)
1236 26
1237 SELECT count(*) FROM t2_temp;
1238 count(*)
1239 26
1240 SELECT count(*) FROM t4_temp;
1241 count(*)
1242 22
1243 SELECT c1 FROM t1_temp;
1244 c1
1245 5
1246 6
1247 7
1248 8
1249 9
1250 10
1251 11
1252 12
1253 13
1254 14
1255 15
1256 16
1257 17
1258 18
1259 19
1260 20
1261 122
1262 124
1263 126
1264 128
1265 130
1266 132
1267 134
1268 136
1269 138
1270 140
1271 SELECT c1 FROM t2_temp;
1272 c1
1273 5
1274 6
1275 7
1276 8
1277 9
1278 10
1279 11
1280 12
1281 13
1282 14
1283 15
1284 16
1285 17
1286 18
1287 19
1288 20
1289 122
1290 124
1291 126
1292 128
1293 130
1294 132
1295 134
1296 136
1297 138
1298 140
1299 SELECT c1 FROM t4_temp;
1300 c1
1301 5
1302 6
1303 7
1304 8
1305 9
1306 10
1307 15
1308 16
1309 17
1310 18
1311 19
1312 20
1313 122
1314 124
1315 126
1316 128
1317 130
1318 132
1319 134
1320 136
1321 138
1322 140
1323 DROP TABLE t1_2 ,t2_2 ,t3_2,t4_2;
1324 # restart: --innodb_rollback_segments=30 --innodb_redo_log_capacity=20M
1325 call populate_tables('_1');;
1326 call populate_tables('_2');;
1327 "#connection 1 - verify tables"
1328 SELECT count(*) FROM t1_1;
1329 count(*)
1330 36
1331 SELECT count(*) FROM t2_1;
1332 count(*)
1333 36
1334 SELECT count(*) FROM t3_1;
1335 count(*)
1336 34
1337 SELECT count(*) FROM t4_1;
1338 count(*)
1339 32
1340 SELECT c1 FROM t1_1;
1341 c1
1342 5
1343 6
1344 7
1345 8
1346 9
1347 10
1348 11
1349 12
1350 13
1351 14
1352 15
1353 16
1354 17
1355 18
1356 19
1357 20
1358 101
1359 102
1360 103
1361 104
1362 105
1363 106
1364 107
1365 108
1366 109
1367 110
1368 111
1369 112
1370 113
1371 114
1372 115
1373 116
1374 117
1375 118
1376 119
1377 120
1378 SELECT c1 FROM t2_1;
1379 c1
1380 5
1381 6
1382 7
1383 8
1384 9
1385 10
1386 11
1387 12
1388 13
1389 14
1390 15
1391 16
1392 17
1393 18
1394 19
1395 20
1396 101
1397 102
1398 103
1399 104
1400 105
1401 106
1402 107
1403 108
1404 109
1405 110
1406 111
1407 112
1408 113
1409 114
1410 115
1411 116
1412 117
1413 118
1414 119
1415 120
1416 SELECT c1 FROM t3_1;
1417 c1
1418 5
1419 6
1420 7
1421 8
1422 9
1423 10
1424 13
1425 14
1426 15
1427 16
1428 17
1429 18
1430 19
1431 20
1432 101
1433 102
1434 103
1435 104
1436 105
1437 106
1438 107
1439 108
1440 109
1441 110
1442 111
1443 112
1444 113
1445 114
1446 115
1447 116
1448 117
1449 118
1450 119
1451 120
1452 SELECT c1 FROM t4_1;
1453 c1
1454 5
1455 6
1456 7
1457 8
1458 9
1459 10
1460 15
1461 16
1462 17
1463 18
1464 19
1465 20
1466 101
1467 102
1468 103
1469 104
1470 105
1471 106
1472 107
1473 108
1474 109
1475 110
1476 111
1477 112
1478 113
1479 114
1480 115
1481 116
1482 117
1483 118
1484 119
1485 120
1486 SELECT count(*) FROM t1_temp;
1487 count(*)
1488 26
1489 SELECT count(*) FROM t2_temp;
1490 count(*)
1491 26
1492 SELECT count(*) FROM t4_temp;
1493 count(*)
1494 22
1495 SELECT c1 FROM t1_temp;
1496 c1
1497 5
1498 6
1499 7
1500 8
1501 9
1502 10
1503 11
1504 12
1505 13
1506 14
1507 15
1508 16
1509 17
1510 18
1511 19
1512 20
1513 122
1514 124
1515 126
1516 128
1517 130
1518 132
1519 134
1520 136
1521 138
1522 140
1523 SELECT c1 FROM t2_temp;
1524 c1
1525 5
1526 6
1527 7
1528 8
1529 9
1530 10
1531 11
1532 12
1533 13
1534 14
1535 15
1536 16
1537 17
1538 18
1539 19
1540 20
1541 122
1542 124
1543 126
1544 128
1545 130
1546 132
1547 134
1548 136
1549 138
1550 140
1551 SELECT c1 FROM t4_temp;
1552 c1
1553 5
1554 6
1555 7
1556 8
1557 9
1558 10
1559 15
1560 16
1561 17
1562 18
1563 19
1564 20
1565 122
1566 124
1567 126
1568 128
1569 130
1570 132
1571 134
1572 136
1573 138
1574 140
1575 DROP TABLE t1_1 ,t2_1 ,t3_1,t4_1;
1576 "#connection 2 - verify tables"
1577 SELECT count(*) FROM t1_2;
1578 count(*)
1579 36
1580 SELECT count(*) FROM t2_2;
1581 count(*)
1582 36
1583 SELECT count(*) FROM t3_2;
1584 count(*)
1585 34
1586 SELECT count(*) FROM t4_2;
1587 count(*)
1588 32
1589 SELECT c1 FROM t1_2;
1590 c1
1591 5
1592 6
1593 7
1594 8
1595 9
1596 10
1597 11
1598 12
1599 13
1600 14
1601 15
1602 16
1603 17
1604 18
1605 19
1606 20
1607 101
1608 102
1609 103
1610 104
1611 105
1612 106
1613 107
1614 108
1615 109
1616 110
1617 111
1618 112
1619 113
1620 114
1621 115
1622 116
1623 117
1624 118
1625 119
1626 120
1627 SELECT c1 FROM t2_2;
1628 c1
1629 5
1630 6
1631 7
1632 8
1633 9
1634 10
1635 11
1636 12
1637 13
1638 14
1639 15
1640 16
1641 17
1642 18
1643 19
1644 20
1645 101
1646 102
1647 103
1648 104
1649 105
1650 106
1651 107
1652 108
1653 109
1654 110
1655 111
1656 112
1657 113
1658 114
1659 115
1660 116
1661 117
1662 118
1663 119
1664 120
1665 SELECT c1 FROM t3_2;
1666 c1
1667 5
1668 6
1669 7
1670 8
1671 9
1672 10
1673 13
1674 14
1675 15
1676 16
1677 17
1678 18
1679 19
1680 20
1681 101
1682 102
1683 103
1684 104
1685 105
1686 106
1687 107
1688 108
1689 109
1690 110
1691 111
1692 112
1693 113
1694 114
1695 115
1696 116
1697 117
1698 118
1699 119
1700 120
1701 SELECT c1 FROM t4_2;
1702 c1
1703 5
1704 6
1705 7
1706 8
1707 9
1708 10
1709 15
1710 16
1711 17
1712 18
1713 19
1714 20
1715 101
1716 102
1717 103
1718 104
1719 105
1720 106
1721 107
1722 108
1723 109
1724 110
1725 111
1726 112
1727 113
1728 114
1729 115
1730 116
1731 117
1732 118
1733 119
1734 120
1735 SELECT count(*) FROM t1_temp;
1736 count(*)
1737 26
1738 SELECT count(*) FROM t2_temp;
1739 count(*)
1740 26
1741 SELECT count(*) FROM t4_temp;
1742 count(*)
1743 22
1744 SELECT c1 FROM t1_temp;
1745 c1
1746 5
1747 6
1748 7
1749 8
1750 9
1751 10
1752 11
1753 12
1754 13
1755 14
1756 15
1757 16
1758 17
1759 18
1760 19
1761 20
1762 122
1763 124
1764 126
1765 128
1766 130
1767 132
1768 134
1769 136
1770 138
1771 140
1772 SELECT c1 FROM t2_temp;
1773 c1
1774 5
1775 6
1776 7
1777 8
1778 9
1779 10
1780 11
1781 12
1782 13
1783 14
1784 15
1785 16
1786 17
1787 18
1788 19
1789 20
1790 122
1791 124
1792 126
1793 128
1794 130
1795 132
1796 134
1797 136
1798 138
1799 140
1800 SELECT c1 FROM t4_temp;
1801 c1
1802 5
1803 6
1804 7
1805 8
1806 9
1807 10
1808 15
1809 16
1810 17
1811 18
1812 19
1813 20
1814 122
1815 124
1816 126
1817 128
1818 130
1819 132
1820 134
1821 136
1822 138
1823 140
1824 DROP TABLE t1_2 ,t2_2 ,t3_2,t4_2;
1825 DROP PROCEDURE populate_tables;
1826 SHOW TABLES;
1827 Tables_in_test