Debian Codegraph @

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

raw
1 #
2 # This test tests various DML operations and their effect on the tablespaces
3 #
4 # Test tables with file_per_table tablespace
5 #
6 # Scenario-1: Create a table without autoextend_size and validate that the allocation
7 # happens as per the old logic
8 #
9 CREATE TABLE t1 (c1 INT, c2 TEXT);
10 # Verify the initial size of the file
11 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
12 WHERE NAME LIKE '%t1%';
13 NAME FILE_SIZE AUTOEXTEND_SIZE
14 test/t1 114688 0
15 # This is a smaller tablespace. The tablespace should extend by adding smaller number
16 # of pages initially
17 # Add few rows to the table and check the size of the file
18 select count(*) from t1;
19 count(*)
20 1
21 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
22 WHERE NAME LIKE '%t1%';
23 NAME FILE_SIZE AUTOEXTEND_SIZE
24 test/t1 163840 0
25 select count(*) from t1;
26 count(*)
27 2
28 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
29 WHERE NAME LIKE '%t1%';
30 NAME FILE_SIZE AUTOEXTEND_SIZE
31 test/t1 229376 0
32 select count(*) from t1;
33 count(*)
34 3
35 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
36 WHERE NAME LIKE '%t1%';
37 NAME FILE_SIZE AUTOEXTEND_SIZE
38 test/t1 294912 0
39 # Once the tablespace size is more than half of the extent size, tablespace is extended with
40 # 4 extents at a time
41 select count(*) from t1;
42 count(*)
43 10
44 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
45 WHERE NAME LIKE '%t1%';
46 NAME FILE_SIZE AUTOEXTEND_SIZE
47 test/t1 4194304 0
48 drop table t1;
49 #
50 # Scenario-2: Create table with autoextend_size clause and validate that the allocation happens as per the
51 # new logic and the initial size of the tablespace file is the value of AUTOEXTEND_SIZE
52 #
53 CREATE TABLE t2(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
54 select count(*) from t2;
55 count(*)
56 10
57 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
58 WHERE NAME LIKE '%t2%';
59 NAME FILE_SIZE AUTOEXTEND_SIZE
60 test/t2 4194304 4194304
61 select count(*) from t2;
62 count(*)
63 20
64 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
65 WHERE NAME LIKE '%t2%';
66 NAME FILE_SIZE AUTOEXTEND_SIZE
67 test/t2 4194304 4194304
68 select count(*) from t2;
69 count(*)
70 1020
71 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
72 WHERE NAME LIKE '%t2%';
73 NAME FILE_SIZE AUTOEXTEND_SIZE
74 test/t2 71303168 4194304
75 select count(*) from t2;
76 count(*)
77 2020
78 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
79 WHERE NAME LIKE '%t2%';
80 NAME FILE_SIZE AUTOEXTEND_SIZE
81 test/t2 138412032 4194304
82 DROP TABLE t2;
83 #
84 # Scenario-3: Create a table without autoextend_size and alter it later on to add autoextend_size property
85 # Validate that the allocation is done with the old logic until autoextend_size is introduced to the table
86 #
87 CREATE TABLE t3(c1 INT, c2 TEXT);
88 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
89 WHERE NAME LIKE '%t3%';
90 NAME FILE_SIZE AUTOEXTEND_SIZE
91 test/t3 114688 0
92 SELECT COUNT(*) FROM t3;
93 COUNT(*)
94 1
95 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
96 WHERE NAME LIKE '%t3%';
97 NAME FILE_SIZE AUTOEXTEND_SIZE
98 test/t3 163840 0
99 ALTER TABLE t3 AUTOEXTEND_SIZE 4M;
100 SELECT COUNT(*) FROM t3;
101 COUNT(*)
102 2
103 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
104 WHERE NAME LIKE '%t3%';
105 NAME FILE_SIZE AUTOEXTEND_SIZE
106 test/t3 4194304 4194304
107 DROP TABLE t3;
108 #
109 # Scenario-4: Test crash recovery and verify that the autoextend_size values are reinstated
110 # after recovery
111 #
112 CREATE TABLE t4(c1 INT, c2 longblob) AUTOEXTEND_SIZE 8m;
113 # The initialize size of the table should be same as the autoextend_size
114 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
115 WHERE NAME LIKE '%t4%';
116 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
117 test/t4 1.0000 8388608 8388608
118 # Crash the server while attempting to allocate more space
119 INSERT INTO t4 VALUES (1, repeat(1, 4 * 1024 * 1024));
120 SET DEBUG="+d, fsp_crash_before_space_extend";
121 INSERT INTO t4 VALUES (1, repeat(1, 4 * 1024 * 1024));
122 ERROR HY000: Lost connection to MySQL server during query
123 # Restart mysqld after the crash and reconnect
124 # restart
125 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
126 WHERE NAME LIKE '%t4%';
127 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
128 test/t4 1.0000 8388608 8388608
129 INSERT INTO t4 VALUES (1, repeat(1, 4 * 1024 * 1024));
130 SELECT COUNT(*) FROM t4;
131 COUNT(*)
132 2
133 # Verify new file size is a multiple of autoextend_size
134 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
135 WHERE NAME LIKE '%t4%';
136 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
137 test/t4 2.0000 16777216 8388608
138 DROP TABLE t4;
139 #
140 # Scenario-5: Test crash recovery when server is started with --skip-innodb-validate-tablespace-paths
141 # option and verify that the server respects the autoextend_size value
142 #
143 CREATE TABLE t5(c1 INT, c2 longblob) AUTOEXTEND_SIZE 8m;
144 # The initialize size of the table should be same as the autoextend_size
145 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
146 WHERE NAME LIKE '%t5%';
147 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
148 test/t5 1.0000 8388608 8388608
149 # Crash the server while attempting to allocate more space
150 INSERT INTO t5 VALUES (1, repeat(1, 4 * 1024 * 1024));
151 SET DEBUG="+d, fsp_crash_before_space_extend";
152 INSERT INTO t5 VALUES (1, repeat(1, 4 * 1024 * 1024));
153 ERROR HY000: Lost connection to MySQL server during query
154 # Restart mysqld with --skip-innodb-validate-tablespace-paths option after
155 # the crash and reconnect
156 # restart: --skip-innodb-validate-tablespace-paths
157 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
158 WHERE NAME LIKE '%t5%';
159 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
160 test/t5 1.0000 8388608 8388608
161 INSERT INTO t5 VALUES (1, repeat(1, 4 * 1024 * 1024));
162 SELECT COUNT(*) FROM t5;
163 COUNT(*)
164 2
165 # Verify new file size is a multiple of autoextend_size
166 SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
167 WHERE NAME LIKE '%t5%';
168 NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
169 test/t5 2.0000 16777216 8388608
170 DROP TABLE t5;
171 # restart
172 #
173 # Scenario-6: Test concurrent updates to autoextend_size while inserts are going on
174 # on another connection
175 #
176 CREATE TABLE t6(id int, l longblob, v varchar(100)) AUTOEXTEND_SIZE 4m;
177 CREATE PROCEDURE update_aes()
178 BEGIN
179 DECLARE i INT DEFAULT 0;
180 WHILE i < 1000 DO
181 ALTER TABLE t6 AUTOEXTEND_SIZE=4m;
182 ALTER TABLE t6 AUTOEXTEND_SIZE=64m;
183 ALTER TABLE t6 AUTOEXTEND_SIZE=16m;
184 ALTER TABLE t6 AUTOEXTEND_SIZE=8m;
185 ALTER TABLE t6 AUTOEXTEND_SIZE=32m;
186 SET i = i + 1;
187 END WHILE;
188 END |
189 CREATE PROCEDURE insert_data()
190 BEGIN
191 DECLARE i INT DEFAULT 0;
192 WHILE i < 10000 DO
193 INSERT INTO t6 (l) VALUES (repeat(2, 1024 * 16));
194 SET i = i + 1;
195 END WHILE;
196 END |
197 CALL insert_data();;
198 CALL update_aes();
199 SELECT COUNT(*) FROM t6;
200 COUNT(*)
201 10000
202 DROP TABLE t6;
203 DROP PROCEDURE insert_data;
204 DROP PROCEDURE update_aes;