1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
# A rhino can weigh over 2000 kg, run about 12 meters per second,
# but see accurately only 10 meters...
# A group of rhinos is called a 'crash'!
#
# InnoDB should make the way wide and smooth for undo truncation
# to occur while dml is happening.
CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
CREATE UNDO TABLESPACE undo_005 ADD DATAFILE 'undo_005.ibu';
CREATE UNDO TABLESPACE undo_006 ADD DATAFILE 'undo_006.ibu';
CREATE DATABASE africa;
USE africa;
CREATE TABLE serengeti (a INT PRIMARY KEY, b TEXT);
DELIMITER |;
# Offset the timing of each thread's DDL.
CREATE PROCEDURE crash (IN loops INT, IN undo_number INT)
BEGIN
SET @undo_num = undo_number;
SET @before = 5 * (@undo_num - 2);
SET @during = 25;
SET @after = 25 - @before;
SET @all = @before + @during + @after;
PREPARE stmt_ins FROM "INSERT INTO serengeti VALUES (?, ?)";
SET @a = ((@undo_num - 2) * 10000) + 1;
SET @n = 0;
WHILE (@n < @all) DO
SET @b = concat("Insert #", @a);
EXECUTE stmt_ins USING @a, @b;
SET @a = @a + 1;
SET @n = @n + 1;
END WHILE;
PREPARE stmt FROM "UPDATE serengeti SET b = ? WHERE a = ?";
SET @loop = 0;
WHILE (@loop < loops) DO
SET @a = ((@undo_num - 2) * 10000) + 1;
SET @n = 0;
WHILE (@n < @before) DO
SET @b = concat("Update #", @a, ", loop ", @loop, " BEFORE undo_00", @undo_num, " is SET INACTIVE");
EXECUTE stmt USING @b, @a;
SET @a = @a + 1;
SET @n = @n + 1;
END WHILE;
IF (@undo_num = 3) THEN
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
END IF;
IF (@undo_num = 4) THEN
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
END IF;
IF (@undo_num = 5) THEN
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
END IF;
IF (@undo_num = 6) THEN
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
END IF;
SET @n = 0;
WHILE (@n < @during) DO
SET @b = concat("Update #", @a, ", loop ", @loop, " WHILE undo_00", @undo_num, " is SET INACTIVE");
EXECUTE stmt USING @b, @a;
SET @a = @a + 1;
SET @n = @n + 1;
END WHILE;
IF (@undo_num = 3) THEN
ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
END IF;
IF (@undo_num = 4) THEN
ALTER UNDO TABLESPACE undo_004 SET ACTIVE;
END IF;
IF (@undo_num = 5) THEN
ALTER UNDO TABLESPACE undo_005 SET ACTIVE;
END IF;
IF (@undo_num = 6) THEN
ALTER UNDO TABLESPACE undo_006 SET ACTIVE;
END IF;
SET @n = 0;
WHILE (@n < @after) DO
SET @b = concat("Update #", @a, ", loop ", @loop, " AFTER undo_00", @undo_num, " is SET ACTIVE");
EXECUTE stmt USING @b, @a;
SET @a = @a + 1;
SET @n = @n + 1;
END WHILE;
SET @loop = @loop + 1;
END WHILE;
END|
DELIMITER ;|
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connect (con3,localhost,root,,);
connect (con4,localhost,root,,);
connection con1;
--send CALL africa.crash(10, 3);
connection con2;
--send CALL africa.crash(10, 4);
connection con3;
--send CALL africa.crash(10, 5);
connection con4;
--send CALL africa.crash(10, 6);
connection con1;
--reap;
connection con2;
--reap;
connection con3;
--reap;
connection con4;
--reap;
connection default;
disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;
SELECT count(*) from serengeti;
USE test;
DROP DATABASE africa;
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
let $inactive_undo_space = undo_003;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_003;
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
let $inactive_undo_space = undo_004;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_004;
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
let $inactive_undo_space = undo_005;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_005;
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
let $inactive_undo_space = undo_006;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_006;
|
