Debian Codegraph @

mysql-test/suite/innodb/r/innodb_autoextend_dml_2.result

raw
1 #
2 # This test tests various DML operations and their effect on the tablespaces
3 #
4 #
5 # Test with tables using general tablespaces
6 #
7 #
8 # Scenario-1: Create a general tablespace without autoextend_size attribute and validate that the allocation
9 # happens using the old allocation logic
10 #
11 CREATE TABLESPACE myspace;
12 CREATE TABLE tsp(c1 INT, c2 TEXT) TABLESPACE myspace;
13 SELECT COUNT(*) FROM tsp;
14 COUNT(*)
15 1
16 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
17 WHERE NAME LIKE '%myspace%';
18 NAME FILE_SIZE AUTOEXTEND_SIZE
19 myspace 163840 0
20 SELECT COUNT(*) FROM tsp;
21 COUNT(*)
22 2
23 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
24 WHERE NAME LIKE '%myspace%';
25 NAME FILE_SIZE AUTOEXTEND_SIZE
26 myspace 229376 0
27 DROP TABLE tsp;
28 DROP TABLESPACE myspace;
29 #
30 # Scenario-2: Create a general tablespace without autoextend_size attribute and validate
31 # that the allocation happens as per the old logic until autoextend_size attribute is
32 # changed for the tablespace
33 #
34 CREATE TABLESPACE myspace;
35 # Without the autoextend_size attribute, the tablespace should extend by adding only
36 # a few pages
37 CREATE TABLE tsp(c1 INT, c2 TEXT) TABLESPACE myspace;
38 SELECT COUNT(*) FROM tsp;
39 COUNT(*)
40 1
41 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
42 WHERE NAME LIKE '%myspace%';
43 NAME FILE_SIZE AUTOEXTEND_SIZE
44 myspace 163840 0
45 ALTER TABLESPACE myspace AUTOEXTEND_SIZE 4M;
46 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
47 WHERE NAME LIKE '%myspace%';
48 NAME FILE_SIZE AUTOEXTEND_SIZE
49 myspace 163840 4194304
50 # Tablespace should be extended to make it's size same as the autoextend_size
51 SELECT COUNT(*) FROM tsp;
52 COUNT(*)
53 2
54 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
55 WHERE NAME LIKE '%myspace%';
56 NAME FILE_SIZE AUTOEXTEND_SIZE
57 myspace 4194304 4194304
58 # Tablespace should extend by a multiple of autoextend_size
59 SELECT COUNT(*) FROM tsp;
60 COUNT(*)
61 12
62 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
63 WHERE NAME LIKE '%myspace%';
64 NAME FILE_SIZE AUTOEXTEND_SIZE
65 myspace 8388608 4194304
66 DROP TABLE tsp;
67 DROP TABLESPACE myspace;
68 #
69 # Scenario-3: Test allocation with partitioned and sub-partitioned tables
70 #
71 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) (
72 PARTITION p0 VALUES LESS THAN (20),
73 PARTITION p1 VALUES LESS THAN (50),
74 PARTITION p2 VALUES LESS THAN maxvalue);
75 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
76 WHERE NAME LIKE '%tpart%';
77 NAME FILE_SIZE AUTOEXTEND_SIZE
78 test/tpart#p#p0 4194304 4194304
79 test/tpart#p#p1 4194304 4194304
80 test/tpart#p#p2 4194304 4194304
81 CREATE PROCEDURE bulk_insert()
82 BEGIN
83 DECLARE i INT DEFAULT 1;
84 WHILE i < 10000 DO
85 INSERT INTO tpart VALUES(i / 100, 'abcdefgh');
86 SET i = i + 1;
87 END WHILE;
88 END
89 |
90 call bulk_insert();
91 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
92 WHERE NAME LIKE '%tpart%';
93 NAME FILE_SIZE AUTOEXTEND_SIZE
94 test/tpart#p#p0 4194304 4194304
95 test/tpart#p#p1 4194304 4194304
96 test/tpart#p#p2 4194304 4194304
97 SELECT COUNT(*) FROM tpart;
98 COUNT(*)
99 9999
100 DROP TABLE tpart;
101 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
102 PARTITION BY RANGE (c1)
103 SUBPARTITION BY HASH (c1)
104 SUBPARTITIONS 3 (
105 PARTITION p1 values less than (10),
106 PARTITION p2 values less than (20),
107 PARTITION p3 values less than maxvalue);
108 call bulk_insert();
109 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
110 WHERE NAME LIKE '%tpart%';
111 NAME FILE_SIZE AUTOEXTEND_SIZE
112 test/tpart#p#p1#sp#p1sp0 4194304 4194304
113 test/tpart#p#p1#sp#p1sp1 4194304 4194304
114 test/tpart#p#p1#sp#p1sp2 4194304 4194304
115 test/tpart#p#p2#sp#p2sp0 4194304 4194304
116 test/tpart#p#p2#sp#p2sp1 4194304 4194304
117 test/tpart#p#p2#sp#p2sp2 4194304 4194304
118 test/tpart#p#p3#sp#p3sp0 4194304 4194304
119 test/tpart#p#p3#sp#p3sp1 4194304 4194304
120 test/tpart#p#p3#sp#p3sp2 4194304 4194304
121 SELECT COUNT(*) FROM tpart;
122 COUNT(*)
123 9999
124 DROP TABLE tpart;
125 DROP PROCEDURE bulk_insert;
126 #
127 # Scenario-4: Test truncate table with non-partitioned table
128 #
129 CREATE TABLE mytable (c1 INT) AUTOEXTEND_SIZE 4M;
130 INSERT INTO mytable VALUES(1);
131 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
132 WHERE NAME LIKE '%mytable%';
133 NAME FILE_SIZE AUTOEXTEND_SIZE
134 test/mytable 4194304 4194304
135 TRUNCATE TABLE mytable;
136 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
137 WHERE NAME LIKE '%mytable%';
138 NAME FILE_SIZE AUTOEXTEND_SIZE
139 test/mytable 4194304 4194304
140 DROP TABLE mytable;
141 #
142 # Scenario-5: Test truncate table with partitioned and sub-partitioned table
143 #
144 CREATE TABLE tpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
145 PARTITION BY RANGE (c1) (
146 PARTITION p0 VALUES LESS THAN (100),
147 PARTITION p1 VALUES LESS THAN (200),
148 PARTITION p2 VALUES LESS THAN maxvalue);
149 INSERT INTO tpart VALUES (99, repeat('a', 10000)), (199, repeat('b', 10000)), (299, repeat('c', 10000));
150 SELECT COUNT(*) FROM tpart;
151 COUNT(*)
152 3
153 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
154 WHERE NAME LIKE '%tpart%';
155 NAME FILE_SIZE AUTOEXTEND_SIZE
156 test/tpart#p#p0 4194304 4194304
157 test/tpart#p#p1 4194304 4194304
158 test/tpart#p#p2 4194304 4194304
159 TRUNCATE TABLE tpart;
160 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
161 WHERE NAME LIKE '%tpart%';
162 NAME FILE_SIZE AUTOEXTEND_SIZE
163 test/tpart#p#p0 4194304 4194304
164 test/tpart#p#p1 4194304 4194304
165 test/tpart#p#p2 4194304 4194304
166 DROP TABLE tpart;
167 # Test truncate table with sub-partitioned table
168 CREATE TABLE tsubpart (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
169 PARTITION BY RANGE (c1)
170 SUBPARTITION BY HASH (c1)
171 SUBPARTITIONS 3 (
172 PARTITION p0 VALUES LESS THAN (100),
173 PARTITION p1 VALUES LESS THAN (200),
174 PARTITION p2 VALUES LESS THAN maxvalue);
175 INSERT INTO tsubpart VALUES (99, repeat('a', 10000)), (199, repeat('b', 10000)), (299, repeat('c', 10000));
176 SELECT COUNT(*) FROM tsubpart;
177 COUNT(*)
178 3
179 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
180 WHERE NAME LIKE '%tsubpart%';
181 NAME FILE_SIZE AUTOEXTEND_SIZE
182 test/tsubpart#p#p0#sp#p0sp0 4194304 4194304
183 test/tsubpart#p#p0#sp#p0sp1 4194304 4194304
184 test/tsubpart#p#p0#sp#p0sp2 4194304 4194304
185 test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
186 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
187 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
188 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
189 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
190 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
191 TRUNCATE TABLE tsubpart;
192 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
193 WHERE NAME LIKE '%tsubpart%';
194 NAME FILE_SIZE AUTOEXTEND_SIZE
195 test/tsubpart#p#p0#sp#p0sp0 4194304 4194304
196 test/tsubpart#p#p0#sp#p0sp1 4194304 4194304
197 test/tsubpart#p#p0#sp#p0sp2 4194304 4194304
198 test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
199 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
200 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
201 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
202 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
203 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
204 DROP TABLE tsubpart;
205 #
206 # Scenario-6: Test truncate table with explicit tablespace
207 #
208 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 4M;
209 CREATE TABLE mytable (c1 INT) TABLESPACE myspace;
210 INSERT INTO mytable VALUES (1);
211 SELECT COUNT(*) FROM mytable;
212 COUNT(*)
213 1
214 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
215 WHERE NAME LIKE '%myspace%';
216 NAME FILE_SIZE AUTOEXTEND_SIZE
217 myspace 4194304 4194304
218 TRUNCATE TABLE mytable;
219 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
220 WHERE NAME LIKE '%myspace%';
221 NAME FILE_SIZE AUTOEXTEND_SIZE
222 myspace 4194304 4194304
223 DROP TABLE mytable;
224 DROP TABLESPACE myspace;
225 #
226 # Scenario-7: Test crash recovery and verify that the autoextend_size value
227 # is reinstated after recovery
228 #
229 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 8m;
230 # The initial size of the tablespace should be same as the autoextend_size
231 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
232 WHERE NAME LIKE '%myspace%';
233 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
234 myspace 1.0000 8388608 8388608
235 CREATE TABLE tsp(c1 int, c2 longblob) TABLESPACE myspace;
236 # Crash the server while attempting to allocate more space
237 INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024));
238 SET DEBUG="+d, fsp_crash_before_space_extend";
239 INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024));
240 ERROR HY000: Lost connection to MySQL server during query
241 # Restart mysqld after the crash and reconnect
242 # restart
243 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
244 WHERE NAME LIKE '%myspace%';
245 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
246 myspace 1.0000 8388608 8388608
247 INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024));
248 SELECT COUNT(*) FROM tsp;
249 COUNT(*)
250 2
251 # Verify new file size is a multiple of autoextend_size
252 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
253 WHERE NAME LIKE '%myspace%';
254 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
255 myspace 2.0000 16777216 8388608
256 DROP TABLE tsp;
257 DROP TABLESPACE myspace;
258 #
259 # Scenario-8: Test crash recovery when the server is started with --skip-innodb-validate-tablespace-paths
260 # option and verify that the server respects the autoextend_size value
261 #
262 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 8m;
263 # The initialize size of the tablespace should be same as the autoextend_size
264 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
265 WHERE NAME LIKE '%myspace%';
266 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
267 myspace 1.0000 8388608 8388608
268 CREATE TABLE tsp(c1 int, c2 longblob) TABLESPACE myspace;
269 # Crash the server while attempting to allocate more space
270 INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024));
271 SET DEBUG="+d, fsp_crash_before_space_extend";
272 INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024));
273 ERROR HY000: Lost connection to MySQL server during query
274 # Restart mysqld with --skip-innodb-validate-tablespace-paths option
275 # after the crash and reconnect
276 # restart: --skip-innodb-validate-tablespace-paths
277 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
278 WHERE NAME LIKE '%myspace%';
279 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
280 myspace 1.0000 8388608 8388608
281 INSERT INTO tsp VALUES(1, repeat(1, 4 * 1024 * 1024));
282 SELECT COUNT(*) FROM tsp;
283 COUNT(*)
284 2
285 # Verify new file size is a multiple of autoextend_size
286 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
287 WHERE NAME LIKE '%myspace%';
288 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
289 myspace 2.0000 16777216 8388608
290 DROP TABLE tsp;
291 DROP TABLESPACE myspace;
292 # restart
293 #
294 # Scenario-9: Test concurrent inserts and ALTER TABLESPACE ... AUTOEXTEND_SIZE
295 #
296 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 4m;
297 CREATE TABLE tsp(id int, l longblob, v varchar(100)) TABLESPACE myspace;
298 CREATE PROCEDURE update_aes()
299 BEGIN
300 DECLARE i INT DEFAULT 0;
301 WHILE i < 1000 DO
302 ALTER TABLESPACE myspace AUTOEXTEND_SIZE=4m;
303 ALTER TABLESPACE myspace AUTOEXTEND_SIZE=64m;
304 ALTER TABLESPACE myspace AUTOEXTEND_SIZE=16m;
305 ALTER TABLESPACE myspace AUTOEXTEND_SIZE=8m;
306 ALTER TABLESPACE myspace AUTOEXTEND_SIZE=32m;
307 SET i = i + 1;
308 END WHILE;
309 END |
310 CREATE PROCEDURE insert_data()
311 BEGIN
312 DECLARE i INT DEFAULT 0;
313 WHILE i < 10000 DO
314 INSERT INTO tsp (l) VALUES (repeat(2, 1024*16));
315 SET i = i + 1;
316 END WHILE;
317 END |
318 CALL insert_data();;
319 CALL update_aes();
320 SELECT COUNT(*) FROM tsp;
321 COUNT(*)
322 10000
323 DROP TABLE tsp;
324 DROP TABLESPACE myspace;
325 DROP PROCEDURE update_aes;
326 DROP PROCEDURE insert_data;