Debian Codegraph @

mysql-test/suite/innodb/t/innodb_autoextend_dml_2.test

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