Debian Codegraph @

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

raw
1 # Disable in valgrind because of timeout
2 --source include/big_test.inc
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 # Test tables with file_per_table tablespace
13
14 --echo #
15 --echo # Scenario-1: Create a table without autoextend_size and validate that the allocation
16 --echo # happens as per the old logic
17 --echo #
18
19 CREATE TABLE t1 (c1 INT, c2 TEXT);
20
21 --echo # Verify the initial size of the file
22 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
23 WHERE NAME LIKE '%t1%';
24
25 --echo # This is a smaller tablespace. The tablespace should extend by adding smaller number
26 --echo # of pages initially
27
28 --echo # Add few rows to the table and check the size of the file
29 let $row_count = 1;
30 let $table_name = t1;
31 --source suite/innodb/include/innodb_autoextend_dml.inc
32
33 select count(*) from t1;
34
35 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
36 WHERE NAME LIKE '%t1%';
37
38 let $row_count = 1;
39 let $table_name = t1;
40 --source suite/innodb/include/innodb_autoextend_dml.inc
41
42 select count(*) from t1;
43 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
44 WHERE NAME LIKE '%t1%';
45
46 let $row_count = 1;
47 let $table_name = t1;
48 --source suite/innodb/include/innodb_autoextend_dml.inc
49
50 select count(*) from t1;
51 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
52 WHERE NAME LIKE '%t1%';
53
54 --echo # Once the tablespace size is more than half of the extent size, tablespace is extended with
55 --echo # 4 extents at a time
56 let $row_count = 7;
57 let $table_name = t1;
58 --source suite/innodb/include/innodb_autoextend_dml.inc
59
60 select count(*) from t1;
61 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
62 WHERE NAME LIKE '%t1%';
63
64 drop table t1;
65
66 --echo #
67 --echo # Scenario-2: Create table with autoextend_size clause and validate that the allocation happens as per the
68 --echo # new logic and the initial size of the tablespace file is the value of AUTOEXTEND_SIZE
69 --echo #
70 CREATE TABLE t2(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
71 let $row_count = 10;
72 let $table_name = t2;
73 --source suite/innodb/include/innodb_autoextend_dml.inc
74 select count(*) from t2;
75 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
76 WHERE NAME LIKE '%t2%';
77
78 let $row_count = 10;
79 let $table_name = t2;
80 --source suite/innodb/include/innodb_autoextend_dml.inc
81 select count(*) from t2;
82 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
83 WHERE NAME LIKE '%t2%';
84
85 let $row_count = 1000;
86 let $table_name = t2;
87 --source suite/innodb/include/innodb_autoextend_dml.inc
88 select count(*) from t2;
89 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
90 WHERE NAME LIKE '%t2%';
91
92 let $row_count = 1000;
93 let $table_name = t2;
94 --source suite/innodb/include/innodb_autoextend_dml.inc
95 select count(*) from t2;
96 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
97 WHERE NAME LIKE '%t2%';
98
99 DROP TABLE t2;
100
101 --echo #
102 --echo # Scenario-3: Create a table without autoextend_size and alter it later on to add autoextend_size property
103 --echo # Validate that the allocation is done with the old logic until autoextend_size is introduced to the table
104 --echo #
105 CREATE TABLE t3(c1 INT, c2 TEXT);
106 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
107 WHERE NAME LIKE '%t3%';
108
109 let $row_count = 1;
110 let $table_name = t3;
111 --source suite/innodb/include/innodb_autoextend_dml.inc
112 SELECT COUNT(*) FROM t3;
113 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
114 WHERE NAME LIKE '%t3%';
115
116 ALTER TABLE t3 AUTOEXTEND_SIZE 4M;
117 let $row_count = 1;
118 let $table_name = t3;
119 --source suite/innodb/include/innodb_autoextend_dml.inc
120 SELECT COUNT(*) FROM t3;
121 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
122 WHERE NAME LIKE '%t3%';
123
124 DROP TABLE t3;
125
126 --echo #
127 --echo # Scenario-4: Test crash recovery and verify that the autoextend_size values are reinstated
128 --echo # after recovery
129 --echo #
130 CREATE TABLE t4(c1 INT, c2 longblob) AUTOEXTEND_SIZE 8m;
131
132 --echo # The initialize size of the table should be same as the autoextend_size
133 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
134 WHERE NAME LIKE '%t4%';
135
136 --echo # Crash the server while attempting to allocate more space
137 INSERT INTO t4 VALUES (1, repeat(1, 4 * 1024 * 1024));
138
139 --source include/expect_crash.inc
140 SET DEBUG="+d, fsp_crash_before_space_extend";
141 --error 2013
142 INSERT INTO t4 VALUES (1, repeat(1, 4 * 1024 * 1024));
143
144 --echo # Restart mysqld after the crash and reconnect
145 --source include/start_mysqld.inc
146
147 # Verify the current size of the tablespace
148 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
149 WHERE NAME LIKE '%t4%';
150
151 # Insert another row and verify that the space has been extended by the autoextend_size value
152 INSERT INTO t4 VALUES (1, repeat(1, 4 * 1024 * 1024));
153 SELECT COUNT(*) FROM t4;
154
155 --echo # Verify new file size is a multiple of autoextend_size
156 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
157 WHERE NAME LIKE '%t4%';
158
159 DROP TABLE t4;
160
161 --echo #
162 --echo # Scenario-5: Test crash recovery when server is started with --skip-innodb-validate-tablespace-paths
163 --echo # option and verify that the server respects the autoextend_size value
164 --echo #
165 CREATE TABLE t5(c1 INT, c2 longblob) AUTOEXTEND_SIZE 8m;
166
167 --echo # The initialize size of the table should be same as the autoextend_size
168 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
169 WHERE NAME LIKE '%t5%';
170
171 --echo # Crash the server while attempting to allocate more space
172 INSERT INTO t5 VALUES (1, repeat(1, 4 * 1024 * 1024));
173
174 SET DEBUG="+d, fsp_crash_before_space_extend";
175
176 --source include/expect_crash.inc
177
178 --error 2013
179 INSERT INTO t5 VALUES (1, repeat(1, 4 * 1024 * 1024));
180
181 --echo # Restart mysqld with --skip-innodb-validate-tablespace-paths option after
182 --echo # the crash and reconnect
183 --let $restart_parameters = restart: --skip-innodb-validate-tablespace-paths
184 --source include/start_mysqld.inc
185
186 # Verify the current size of the tablespace
187 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
188 WHERE NAME LIKE '%t5%';
189
190 # Insert another row and verify that the space has been extended by the autoextend_size value
191 INSERT INTO t5 VALUES (1, repeat(1, 4 * 1024 * 1024));
192 SELECT COUNT(*) FROM t5;
193
194 --echo # Verify new file size is a multiple of autoextend_size
195 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
196 WHERE NAME LIKE '%t5%';
197
198 DROP TABLE t5;
199
200 # Restart the server without --skip-innodb-validate-tablespace-paths option
201 --let $restart_parameters =
202 --source include/restart_mysqld.inc
203
204 --echo #
205 --echo # Scenario-6: Test concurrent updates to autoextend_size while inserts are going on
206 --echo # on another connection
207 --echo #
208
209 CREATE TABLE t6(id int, l longblob, v varchar(100)) AUTOEXTEND_SIZE 4m;
210
211 delimiter |;
212 CREATE PROCEDURE update_aes()
213 BEGIN
214 DECLARE i INT DEFAULT 0;
215 WHILE i < 1000 DO
216 ALTER TABLE t6 AUTOEXTEND_SIZE=4m;
217 ALTER TABLE t6 AUTOEXTEND_SIZE=64m;
218 ALTER TABLE t6 AUTOEXTEND_SIZE=16m;
219 ALTER TABLE t6 AUTOEXTEND_SIZE=8m;
220 ALTER TABLE t6 AUTOEXTEND_SIZE=32m;
221 SET i = i + 1;
222 END WHILE;
223 END |
224
225 CREATE PROCEDURE insert_data()
226 BEGIN
227 DECLARE i INT DEFAULT 0;
228 WHILE i < 10000 DO
229 INSERT INTO t6 (l) VALUES (repeat(2, 1024 * 16));
230 SET i = i + 1;
231 END WHILE;
232 END |
233 delimiter ;|
234
235 # Create concurrent connections
236 connect (conn1, localhost, root,,);
237 connect (conn2, localhost, root,,);
238
239 # Connect to connection 1
240 --connection conn1
241
242 --send CALL insert_data();
243
244 # Connect to connection 2
245 --connection conn2
246 CALL update_aes();
247
248 --connection conn1
249 --reap
250
251 --connection default
252 --disconnect conn1
253 --disconnect conn2
254
255 SELECT COUNT(*) FROM t6;
256
257 DROP TABLE t6;
258
259 DROP PROCEDURE insert_data;
260 DROP PROCEDURE update_aes;