Debian Codegraph @

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

raw
1 --source include/big_test.inc
2 --source include/have_innodb_16k.inc
3
4 --echo #
5 --echo # Tests for CREATE/ALTER TABLE with AUTOEXTEND_SIZE clause
6 --echo #
7
8 --echo # Test error situations for CREATE TABLE
9 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
10 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 48k;
11
12 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
13 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 1M;
14
15 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
16 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 5G;
17
18 --error ER_INNODB_INVALID_AUTOEXTEND_SIZE_VALUE
19 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 5M;
20
21 --error ER_PARSE_ERROR
22 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE=1.5M;
23
24 --echo # Create table with autoextend_size > 0
25 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 4M;
26 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
27 WHERE NAME LIKE '%mytable%';
28 SHOW CREATE TABLE mytable;
29
30 ALTER TABLE mytable AUTOEXTEND_SIZE 0;
31 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
32 WHERE NAME LIKE '%mytable%';
33 SHOW CREATE TABLE mytable;
34
35 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
36 ALTER TABLE mytable AUTOEXTEND_SIZE 48k;
37
38 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
39 ALTER TABLE mytable AUTOEXTEND_SIZE 1M;
40
41 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
42 ALTER TABLE mytable AUTOEXTEND_SIZE 5G;
43
44 --error ER_INNODB_INVALID_AUTOEXTEND_SIZE_VALUE
45 ALTER TABLE mytable AUTOEXTEND_SIZE 5M;
46
47 DROP TABLE mytable;
48
49 --echo # Create table with autoextend_size = 0
50 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE=0;
51 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
52 WHERE NAME LIKE '%mytable%';
53 SHOW CREATE TABLE mytable;
54
55 DROP TABLE mytable;
56
57 --echo # Test CREATE/ALTER TABLE with AUTOEXTEND_SIZE value specified as
58 --echo # absolute number of bytes without K, M or G qualifiers
59 CREATE TABLE mytable (c1 INT) AUTOEXTEND_SIZE 4194304;
60 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
61 WHERE NAME LIKE '%mytable%';
62
63 ALTER TABLE mytable AUTOEXTEND_SIZE 8388608;
64 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
65 WHERE NAME LIKE '%mytable%';
66
67 DROP TABLE mytable;
68
69 --echo # Test CREATE TABLE LIKE statement for non-partitioned tables
70 CREATE TABLE mytable (c1 INT) AUTOEXTEND_SIZE 4M;
71 SHOW CREATE TABLE mytable;
72 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
73 WHERE NAME LIKE '%mytable%';
74
75 CREATE TABLE mytable_copy LIKE mytable;
76 SHOW CREATE TABLE mytable_copy;
77 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
78 WHERE NAME LIKE '%mytable_copy%';
79
80 DROP TABLE mytable_copy;
81
82 ALTER TABLE mytable AUTOEXTEND_SIZE 0;
83 SHOW CREATE TABLE mytable;
84 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
85 WHERE NAME LIKE '%mytable%';
86
87 CREATE TABLE mytable_copy LIKE mytable;
88 SHOW CREATE TABLE mytable_copy;
89 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
90 WHERE NAME LIKE '%mytable_copy%';
91
92 DROP TABLE mytable_copy;
93
94 ALTER TABLE mytable AUTOEXTEND_SIZE 64M;
95 SHOW CREATE TABLE mytable;
96 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
97 WHERE NAME LIKE '%mytable%';
98
99 CREATE TABLE mytable_copy LIKE mytable;
100 SHOW CREATE TABLE mytable_copy;
101 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
102 WHERE NAME LIKE '%mytable_copy%';
103
104 DROP TABLE mytable_copy;
105
106 DROP TABLE mytable;
107
108 --echo # Validate the initial size of the table when created without the AUTOEXTEND_SIZE clause
109 CREATE TABLE mytable (c1 INT);
110 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
111 WHERE NAME LIKE '%mytable%';
112
113 SHOW CREATE TABLE mytable;
114
115 --echo # Verify the autoextend_size attribute is updated in the data dictionary
116 ALTER TABLE mytable AUTOEXTEND_SIZE=4M;
117 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
118 WHERE NAME LIKE '%mytable%';
119
120 --echo # Validate SHOW CREATE TABLE displays autoextend_size clause
121 SHOW CREATE TABLE mytable;
122
123 --echo # Validate that the AUTOEXTEND_SIZE attribute is persisted properly and seen beyond server restart
124 --source include/restart_mysqld.inc
125
126 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
127 WHERE NAME LIKE '%mytable%';
128
129 DROP TABLE mytable;
130
131 --echo # Test that the initial size of the table is same as AUTOEXTEND_SIZE, if the clause if mentioned
132 CREATE TABLE mytable(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M;
133 INSERT INTO mytable VALUES (1, 'aaaaaa');
134 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
135 WHERE NAME LIKE '%mytable%';
136
137 --echo # Test ALTER TABLE statement
138 ALTER TABLE mytable ADD COLUMN c3 INT, AUTOEXTEND_SIZE 4M;
139 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
140 WHERE NAME LIKE '%mytable%';
141 SELECT * FROM mytable;
142
143 ALTER TABLE mytable DROP COLUMN c3, AUTOEXTEND_SIZE=0;
144 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
145 WHERE NAME LIKE '%mytable%';
146 SHOW CREATE TABLE mytable;
147 SELECT * FROM mytable;
148
149 DROP TABLE mytable;
150
151 --echo # Test that AUTOEXTEND_SIZE does not change after ALTER TABLE ... ALGORITHM COPY
152 CREATE TABLE t1 (c1 INT , c2 varchar(100)) AUTOEXTEND_SIZE =4m;
153 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
154 WHERE NAME LIKE '%t1%';
155
156 ALTER TABLE t1 ADD INDEX idx (c2), ALGORITHM=copy;
157
158 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
159 WHERE NAME LIKE '%t1%';
160
161 DROP TABLE t1;
162
163 --echo # Tests for partitioned tables. The autoextend_size should be updated
164 --echo # for each partitioned tablespace
165
166 --echo # Create a non-partitioned table and add partitions to it and verify that the
167 --echo # autoextend_size attribute is applied to all the partitions
168 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
169 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
170 WHERE NAME LIKE '%tpart%';
171
172 ALTER TABLE tpart PARTITION BY RANGE (c1) (PARTITION p0 VALUES LESS THAN (50),
173 PARTITION p1 VALUES LESS THAN (100));
174
175 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
176 WHERE NAME LIKE '%tpart%';
177
178 SHOW CREATE TABLE tpart;
179
180 ALTER TABLE tpart ADD PARTITION (PARTITION p2 VALUES LESS THAN (200));
181 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
182 WHERE NAME LIKE '%tpart%';
183
184 ALTER TABLE tpart DROP PARTITION p1;
185 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
186 WHERE NAME LIKE '%tpart%';
187
188 DROP TABLE tpart;
189
190 --echo # Create a non-partitioned table and alter it to add partitions while also modifying
191 --echo # the autoextend_size
192 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
193 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
194 WHERE NAME LIKE '%tpart%';
195
196 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
197 ALTER TABLE tpart AUTOEXTEND_SIZE 1M PARTITION BY RANGE (c1) (PARTITION p0 VALUES LESS THAN (50),
198 PARTITION p1 VALUES LESS THAN (100));
199
200 ALTER TABLE tpart AUTOEXTEND_SIZE 8M PARTITION BY RANGE (c1) (PARTITION p0 VALUES LESS THAN (50),
201 PARTITION p1 VALUES LESS THAN (100));
202 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
203 WHERE NAME LIKE '%tpart%';
204
205 SHOW CREATE TABLE tpart;
206
207 ALTER TABLE tpart ADD PARTITION (PARTITION p2 VALUES LESS THAN (200));
208 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
209 WHERE NAME LIKE '%tpart%';
210
211 DROP TABLE tpart;
212
213 --echo # Create a partitioned table and verify autoextend_size value for the partitions
214 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
215 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 1M PARTITION BY RANGE (c1) (
216 PARTITION p0 VALUES LESS THAN (50),
217 PARTITION p1 VALUES LESS THAN (100));
218
219 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) (
220 PARTITION p0 VALUES LESS THAN (50),
221 PARTITION p1 VALUES LESS THAN (100));
222 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
223 WHERE NAME LIKE '%tpart%';
224
225 SHOW CREATE TABLE tpart;
226
227 DROP TABLE tpart;
228
229 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) (
230 PARTITION p0 VALUES LESS THAN (50),
231 PARTITION p1 VALUES LESS THAN (100));
232 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
233 WHERE NAME LIKE '%tpart%';
234
235 SHOW CREATE TABLE tpart;
236
237 CREATE TABLE tpart_copy LIKE tpart;
238 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
239 WHERE NAME LIKE '%tpart_copy%';
240
241 SHOW CREATE TABLE tpart_copy;
242
243 DROP TABLE tpart_copy;
244
245 --echo # Test ALTER TABLE ... AUTOEXTEND_SIZE changes on partitioned tableѕ
246
247 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
248 ALTER TABLE tpart AUTOEXTEND_SIZE 48k;
249
250 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
251 ALTER TABLE tpart AUTOEXTEND_SIZE 1M;
252
253 --error ER_INNODB_AUTOEXTEND_SIZE_OUT_OF_RANGE
254 ALTER TABLE tpart AUTOEXTEND_SIZE 5G;
255
256 --error ER_INNODB_INVALID_AUTOEXTEND_SIZE_VALUE
257 ALTER TABLE tpart AUTOEXTEND_SIZE 5M;
258
259 ALTER TABLE tpart AUTOEXTEND_SIZE 8M;
260
261 SHOW CREATE TABLE tpart;
262
263 DROP TABLE tpart;
264
265 --echo # Test ALTER TABLE ... REMOVE PARTITIONING and ALTER TABLE ... EXCHANGE PARTITION
266 --echo # for partitioned table
267 CREATE TABLE tpart (
268 id INT NOT NULL,
269 fname VARCHAR(30),
270 lname VARCHAR(30)
271 ) AUTOEXTEND_SIZE = 8M
272 PARTITION BY RANGE (id) (
273 PARTITION p0 VALUES LESS THAN (50),
274 PARTITION p1 VALUES LESS THAN (100),
275 PARTITION p2 VALUES LESS THAN (150),
276 PARTITION p3 VALUES LESS THAN (MAXVALUE));
277
278 SHOW CREATE TABLE tpart;
279 INSERT INTO tpart VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black");
280 SELECT COUNT(*) FROM tpart;
281 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
282 WHERE NAME LIKE '%tpart%';
283
284 CREATE TABLE tcopy_part LIKE tpart;
285 SHOW CREATE TABLE tcopy_part;
286
287 --echo # Remove partitioning and validate the value of AUTOEXTEND_SIZE
288 ALTER TABLE tcopy_part REMOVE PARTITIONING;
289 SHOW CREATE TABLE tcopy_part;
290 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
291 WHERE NAME LIKE '%tcopy_part%';
292
293 --echo # Exchange a partition
294 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tpart';
295 ALTER TABLE tpart EXCHANGE PARTITION p3 WITH TABLE tcopy_part;
296 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tpart';
297
298 SELECT COUNT(*) FROM tpart;
299 SELECT COUNT(*) FROM tcopy_part;
300 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
301 WHERE NAME LIKE '%tpart%';
302 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
303 WHERE NAME LIKE '%tcopy_part%';
304
305 DROP TABLE tpart;
306 DROP TABLE tcopy_part;
307
308 --echo # Tests for sub-partitioned tables. The autoextend_size should be updated
309 --echo # for each sub-partition
310
311 --echo # Create a non-partitioned table and add sub-partitions to it
312 CREATE TABLE tsubpart(a INT NOT NULL PRIMARY KEY, b INT) AUTOEXTEND_SIZE 4M;
313 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
314 WHERE NAME LIKE '%tsubpart%';
315
316 ALTER TABLE tsubpart PARTITION BY RANGE (a) SUBPARTITION BY HASH (a)
317 SUBPARTITIONS 3 (
318 PARTITION p1 VALUES LESS THAN (10),
319 PARTITION p2 VALUES LESS THAN (20),
320 PARTITION p3 VALUES LESS THAN maxvalue);
321 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
322 WHERE NAME LIKE '%tsubpart%';
323
324 SHOW CREATE TABLE tsubpart;
325
326 ALTER TABLE tsubpart DROP PARTITION p3;
327 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
328 WHERE NAME LIKE '%tsubpart%';
329
330 DROP TABLE tsubpart;
331
332 --echo # Create a non-partitioned table and add sub-partitions to it whle also modifying
333 --echo # autoextend_size
334 CREATE TABLE tsubpart(a INT NOT NULL PRIMARY KEY, b INT) AUTOEXTEND_SIZE 4M;
335 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
336 WHERE NAME LIKE '%tsubpart%';
337
338 ALTER TABLE tsubpart AUTOEXTEND_SIZE 8M PARTITION BY RANGE (a) SUBPARTITION BY HASH (a)
339 SUBPARTITIONS 3 (
340 PARTITION p1 VALUES LESS THAN (10),
341 PARTITION p2 VALUES LESS THAN (20),
342 PARTITION p3 VALUES LESS THAN maxvalue);
343
344 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
345 WHERE NAME LIKE '%tsubpart%';
346
347 SHOW CREATE TABLE tsubpart;
348
349 ALTER TABLE tsubpart DROP PARTITION p3;
350 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
351 WHERE NAME LIKE '%tsubpart%';
352
353 DROP TABLE tsubpart;
354
355 --echo # Create a sub-partitioned table and verify that changes to AUTOEXTEND_SIZE
356 --echo # are applied to all the subpartitions
357 CREATE TABLE tsubpart (a INT NOT NULL PRIMARY KEY, b INT)
358 AUTOEXTEND_SIZE 4M
359 PARTITION BY RANGE (a)
360 SUBPARTITION BY HASH (a)
361 SUBPARTITIONS 3 (
362 PARTITION p1 values less than (10),
363 PARTITION p2 values less than (20),
364 PARTITION p3 values less than maxvalue);
365
366 SHOW CREATE TABLE tsubpart;
367
368 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
369 WHERE NAME LIKE '%tsubpart%';
370
371 CREATE TABLE tsubpart_copy LIKE tsubpart;
372 SHOW CREATE TABLE tsubpart_copy;
373
374 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
375 WHERE NAME LIKE '%tsubpart_copy%';
376
377 DROP TABLE tsubpart_copy;
378
379 ALTER TABLE tsubpart AUTOEXTEND_SIZE 8M;
380 SHOW CREATE TABLE tsubpart;
381 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
382 WHERE NAME LIKE '%tsubpart%';
383
384 ALTER TABLE tsubpart AUTOEXTEND_SIZE 0;
385 SHOW CREATE TABLE tsubpart;
386 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
387 WHERE NAME LIKE '%tsubpart%';
388
389 DROP TABLE tsubpart;
390
391 --echo # Test ALTER TABLE ... REMOVE PARTITIONING and ALTER TABLE ... EXCHANGE PARTITION
392 --echo # for sub-partitioned table
393 CREATE TABLE tsubpart (
394 id INT NOT NULL,
395 fname VARCHAR(30),
396 lname VARCHAR(30)
397 ) AUTOEXTEND_SIZE = 8M
398 PARTITION BY RANGE (id)
399 SUBPARTITION BY KEY (lname)
400 SUBPARTITIONS 2 (
401 PARTITION p0 VALUES LESS THAN (50),
402 PARTITION p1 VALUES LESS THAN (100),
403 PARTITION p2 VALUES LESS THAN (150),
404 PARTITION p3 VALUES LESS THAN (MAXVALUE));
405
406 SHOW CREATE TABLE tsubpart;
407 INSERT INTO tsubpart VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black");
408 SELECT COUNT(*) FROM tsubpart;
409 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
410 WHERE NAME LIKE '%tsubpart%';
411
412 CREATE TABLE tcopy_subpart LIKE tsubpart;
413 SHOW CREATE TABLE tcopy_subpart;
414
415 --echo # Remove partitioning and validate the value of AUTOEXTEND_SIZE
416 ALTER TABLE tcopy_subpart REMOVE PARTITIONING;
417 SHOW CREATE TABLE tcopy_subpart;
418 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
419 WHERE NAME LIKE '%tcopy_subpart%';
420
421 --echo # Exchange a partition
422 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tsubpart';
423 ALTER TABLE tsubpart EXCHANGE PARTITION p3sp0 WITH TABLE tcopy_subpart;
424 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tsubpart';
425
426 SELECT COUNT(*) FROM tsubpart;
427 SELECT COUNT(*) FROM tcopy_subpart;
428 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
429 WHERE NAME LIKE '%tsubpart%';
430 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
431 WHERE NAME LIKE '%tcopy_subpart%';
432
433 DROP TABLE tsubpart;
434 DROP TABLE tcopy_subpart;
435
436 --echo # Validate that AUTOEXTEND_SIZE clause is not supported with temporary tables
437 --error ER_CANNOT_USE_AUTOEXTEND_SIZE_CLAUSE
438 CREATE TEMPORARY TABLE temp1 (c1 int) AUTOEXTEND_SIZE 4M;
439
440 CREATE TEMPORARY TABLE temp1(c1 INT);
441
442 --error ER_CANNOT_USE_AUTOEXTEND_SIZE_CLAUSE
443 ALTER TABLE temp1 AUTOEXTEND_SIZE 4M;
444
445 DROP TEMPORARY TABLE temp1;
446
447 --echo # Validate that AUTOEXTEND_SIZE value is effective when the server is started
448 --echo # with --skip-innodb-validate-tablespace-paths command like option
449 CREATE TABLE t1(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
450 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
451 WHERE NAME LIKE '%t1%';
452
453 DELIMITER |;
454 CREATE PROCEDURE bulk_insert()
455 BEGIN
456 DECLARE i INT DEFAULT 1;
457 WHILE i < 10000 DO
458 INSERT INTO t1 VALUES (i, repeat('aaaaaa', 10000));
459 COMMIT;
460 SET i = i + 1;
461 END WHILE;
462 END
463 |
464 DELIMITER ;|
465
466 --let $restart_parameters = restart: --skip-innodb-validate-tablespace-paths
467 --source include/restart_mysqld.inc
468
469 call bulk_insert();
470
471 SELECT COUNT(*) FROM t1;
472 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
473 WHERE NAME LIKE '%t1%';
474
475 DROP PROCEDURE bulk_insert;
476 DROP TABLE t1;
477
478 --let $restart_parameters =
479 --source include/restart_mysqld.inc
480
481 --echo # Bug#32235621 - Concurrent execution of ALTER TABLE and SHOW CREATE TABLE crashes
482 --echo # the server
483
484 CREATE TABLE t1(
485 `c0001` int NOT NULL /*!80023 INVISIBLE */,
486 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
487 `c0002` decimal(65,14) DEFAULT NULL,
488 `c0004` tinyblob NOT NULL,
489 KEY `i0001` (`c0002`, `c0001`),
490 KEY `i0002` (`c0000`(112))
491 ) AUTOEXTEND_SIZE=8M ENGINE=InnoDB;
492
493 delimiter |;
494 CREATE PROCEDURE alter_table()
495 BEGIN
496 DECLARE i INT DEFAULT 0;
497 WHILE i < 20 DO
498 ALTER TABLE t1 FORCE;
499 SET i = i + 1;
500 END WHILE;
501 END |
502
503 CREATE PROCEDURE show_create_table()
504 BEGIN
505 DECLARE i INT DEFAULT 0;
506 WHILE i < 20 DO
507 SHOW CREATE TABLE t1;
508 SET i = i + 1;
509 END WHILE;
510 END |
511 delimiter ;|
512
513 # Create connections to execute the stored procedures concurrently
514 connect (conn1, localhost, root,,);
515 connect (conn2, localhost, root,,);
516
517 # Execute SHOW CREATE TABLE in the background
518 --connection conn1
519 --send CALL show_create_table()
520
521 # Execute ALTER TABLE on another thread
522 --connection conn2
523 CALL alter_table();
524
525 --connection conn1
526 --reap
527
528 --connection default
529 --disconnect conn1
530 --disconnect conn2
531
532 # Cleanup
533 DROP TABLE t1;
534 DROP PROCEDURE show_create_table;
535 DROP PROCEDURE alter_table;