# # Tests for CREATE/ALTER TABLE with AUTOEXTEND_SIZE clause # # Test error situations for CREATE TABLE CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 48k; ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 1M; ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 5G; ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 5M; ERROR HY000: AUTOEXTEND_SIZE should be a multiple of 4M CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE=1.5M; ERROR 42000: Only integers allowed as number here near '1.5M' at line 1 # Create table with autoextend_size > 0 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 4M; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 4194304 4194304 SHOW CREATE TABLE mytable; Table Create Table mytable CREATE TABLE `mytable` ( `c1` int DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ALTER TABLE mytable AUTOEXTEND_SIZE 0; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 4194304 0 SHOW CREATE TABLE mytable; Table Create Table mytable CREATE TABLE `mytable` ( `c1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ALTER TABLE mytable AUTOEXTEND_SIZE 48k; ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M ALTER TABLE mytable AUTOEXTEND_SIZE 1M; ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M ALTER TABLE mytable AUTOEXTEND_SIZE 5G; ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M ALTER TABLE mytable AUTOEXTEND_SIZE 5M; ERROR HY000: AUTOEXTEND_SIZE should be a multiple of 4M DROP TABLE mytable; # Create table with autoextend_size = 0 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE=0; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 114688 0 SHOW CREATE TABLE mytable; Table Create Table mytable CREATE TABLE `mytable` ( `c1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE mytable; # Test CREATE/ALTER TABLE with AUTOEXTEND_SIZE value specified as # absolute number of bytes without K, M or G qualifiers CREATE TABLE mytable (c1 INT) AUTOEXTEND_SIZE 4194304; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 4194304 4194304 ALTER TABLE mytable AUTOEXTEND_SIZE 8388608; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 4194304 8388608 DROP TABLE mytable; # Test CREATE TABLE LIKE statement for non-partitioned tables CREATE TABLE mytable (c1 INT) AUTOEXTEND_SIZE 4M; SHOW CREATE TABLE mytable; Table Create Table mytable CREATE TABLE `mytable` ( `c1` int DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 4194304 4194304 CREATE TABLE mytable_copy LIKE mytable; SHOW CREATE TABLE mytable_copy; Table Create Table mytable_copy CREATE TABLE `mytable_copy` ( `c1` int DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable_copy%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable_copy 4194304 4194304 DROP TABLE mytable_copy; ALTER TABLE mytable AUTOEXTEND_SIZE 0; SHOW CREATE TABLE mytable; Table Create Table mytable CREATE TABLE `mytable` ( `c1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 4194304 0 CREATE TABLE mytable_copy LIKE mytable; SHOW CREATE TABLE mytable_copy; Table Create Table mytable_copy CREATE TABLE `mytable_copy` ( `c1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable_copy%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable_copy 114688 0 DROP TABLE mytable_copy; ALTER TABLE mytable AUTOEXTEND_SIZE 64M; SHOW CREATE TABLE mytable; Table Create Table mytable CREATE TABLE `mytable` ( `c1` int DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=67108864 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 4194304 67108864 CREATE TABLE mytable_copy LIKE mytable; SHOW CREATE TABLE mytable_copy; Table Create Table mytable_copy CREATE TABLE `mytable_copy` ( `c1` int DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=67108864 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable_copy%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable_copy 67108864 67108864 DROP TABLE mytable_copy; DROP TABLE mytable; # Validate the initial size of the table when created without the AUTOEXTEND_SIZE clause CREATE TABLE mytable (c1 INT); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 114688 0 SHOW CREATE TABLE mytable; Table Create Table mytable CREATE TABLE `mytable` ( `c1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # Verify the autoextend_size attribute is updated in the data dictionary ALTER TABLE mytable AUTOEXTEND_SIZE=4M; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 114688 4194304 # Validate SHOW CREATE TABLE displays autoextend_size clause SHOW CREATE TABLE mytable; Table Create Table mytable CREATE TABLE `mytable` ( `c1` int DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # Validate that the AUTOEXTEND_SIZE attribute is persisted properly and seen beyond server restart # restart SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 114688 4194304 DROP TABLE mytable; # Test that the initial size of the table is same as AUTOEXTEND_SIZE, if the clause if mentioned CREATE TABLE mytable(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M; INSERT INTO mytable VALUES (1, 'aaaaaa'); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 8388608 8388608 # Test ALTER TABLE statement ALTER TABLE mytable ADD COLUMN c3 INT, AUTOEXTEND_SIZE 4M; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 4194304 4194304 SELECT * FROM mytable; c1 c2 c3 1 aaaaaa NULL ALTER TABLE mytable DROP COLUMN c3, AUTOEXTEND_SIZE=0; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%mytable%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/mytable 114688 0 SHOW CREATE TABLE mytable; Table Create Table mytable CREATE TABLE `mytable` ( `c1` int DEFAULT NULL, `c2` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT * FROM mytable; c1 c2 1 aaaaaa DROP TABLE mytable; # Test that AUTOEXTEND_SIZE does not change after ALTER TABLE ... ALGORITHM COPY CREATE TABLE t1 (c1 INT , c2 varchar(100)) AUTOEXTEND_SIZE =4m; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%t1%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/t1 4194304 4194304 ALTER TABLE t1 ADD INDEX idx (c2), ALGORITHM=copy; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%t1%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/t1 4194304 4194304 DROP TABLE t1; # Tests for partitioned tables. The autoextend_size should be updated # for each partitioned tablespace # Create a non-partitioned table and add partitions to it and verify that the # autoextend_size attribute is applied to all the partitions CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart 4194304 4194304 ALTER TABLE tpart PARTITION BY RANGE (c1) (PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100)); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart#p#p0 4194304 4194304 test/tpart#p#p1 4194304 4194304 SHOW CREATE TABLE tpart; Table Create Table tpart CREATE TABLE `tpart` ( `c1` int DEFAULT NULL, `c2` text ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`c1`) (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */ ALTER TABLE tpart ADD PARTITION (PARTITION p2 VALUES LESS THAN (200)); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart#p#p0 4194304 4194304 test/tpart#p#p1 4194304 4194304 test/tpart#p#p2 4194304 4194304 ALTER TABLE tpart DROP PARTITION p1; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart#p#p0 4194304 4194304 test/tpart#p#p2 4194304 4194304 DROP TABLE tpart; # Create a non-partitioned table and alter it to add partitions while also modifying # the autoextend_size CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart 4194304 4194304 ALTER TABLE tpart AUTOEXTEND_SIZE 1M PARTITION BY RANGE (c1) (PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100)); ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M ALTER TABLE tpart AUTOEXTEND_SIZE 8M PARTITION BY RANGE (c1) (PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100)); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart#p#p0 8388608 8388608 test/tpart#p#p1 8388608 8388608 SHOW CREATE TABLE tpart; Table Create Table tpart CREATE TABLE `tpart` ( `c1` int DEFAULT NULL, `c2` text ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`c1`) (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */ ALTER TABLE tpart ADD PARTITION (PARTITION p2 VALUES LESS THAN (200)); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart#p#p0 8388608 8388608 test/tpart#p#p1 8388608 8388608 test/tpart#p#p2 8388608 8388608 DROP TABLE tpart; # Create a partitioned table and verify autoextend_size value for the partitions CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 1M PARTITION BY RANGE (c1) ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100)); ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100)); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart#p#p0 4194304 4194304 test/tpart#p#p1 4194304 4194304 SHOW CREATE TABLE tpart; Table Create Table tpart CREATE TABLE `tpart` ( `c1` int DEFAULT NULL, `c2` text ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`c1`) (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */ DROP TABLE tpart; CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100)); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart#p#p0 4194304 4194304 test/tpart#p#p1 4194304 4194304 SHOW CREATE TABLE tpart; Table Create Table tpart CREATE TABLE `tpart` ( `c1` int DEFAULT NULL, `c2` text ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`c1`) (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */ CREATE TABLE tpart_copy LIKE tpart; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart_copy%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart_copy#p#p0 4194304 4194304 test/tpart_copy#p#p1 4194304 4194304 SHOW CREATE TABLE tpart_copy; Table Create Table tpart_copy CREATE TABLE `tpart_copy` ( `c1` int DEFAULT NULL, `c2` text ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`c1`) (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */ DROP TABLE tpart_copy; # Test ALTER TABLE ... AUTOEXTEND_SIZE changes on partitioned tableѕ ALTER TABLE tpart AUTOEXTEND_SIZE 48k; ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M ALTER TABLE tpart AUTOEXTEND_SIZE 1M; ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M ALTER TABLE tpart AUTOEXTEND_SIZE 5G; ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M ALTER TABLE tpart AUTOEXTEND_SIZE 5M; ERROR HY000: AUTOEXTEND_SIZE should be a multiple of 4M ALTER TABLE tpart AUTOEXTEND_SIZE 8M; SHOW CREATE TABLE tpart; Table Create Table tpart CREATE TABLE `tpart` ( `c1` int DEFAULT NULL, `c2` text ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`c1`) (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */ DROP TABLE tpart; # Test ALTER TABLE ... REMOVE PARTITIONING and ALTER TABLE ... EXCHANGE PARTITION # for partitioned table CREATE TABLE tpart ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) ) AUTOEXTEND_SIZE = 8M PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150), PARTITION p3 VALUES LESS THAN (MAXVALUE)); SHOW CREATE TABLE tpart; Table Create Table tpart CREATE TABLE `tpart` ( `id` int NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`id`) (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ INSERT INTO tpart VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black"); SELECT COUNT(*) FROM tpart; COUNT(*) 4 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart#p#p0 8388608 8388608 test/tpart#p#p1 8388608 8388608 test/tpart#p#p2 8388608 8388608 test/tpart#p#p3 8388608 8388608 CREATE TABLE tcopy_part LIKE tpart; SHOW CREATE TABLE tcopy_part; Table Create Table tcopy_part CREATE TABLE `tcopy_part` ( `id` int NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`id`) (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ # Remove partitioning and validate the value of AUTOEXTEND_SIZE ALTER TABLE tcopy_part REMOVE PARTITIONING; SHOW CREATE TABLE tcopy_part; Table Create Table tcopy_part CREATE TABLE `tcopy_part` ( `id` int NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tcopy_part%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tcopy_part 8388608 8388608 # Exchange a partition SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tpart'; PARTITION_NAME TABLE_ROWS p0 1 p1 0 p2 0 p3 3 ALTER TABLE tpart EXCHANGE PARTITION p3 WITH TABLE tcopy_part; SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tpart'; PARTITION_NAME TABLE_ROWS p0 0 p1 0 p2 0 p3 0 SELECT COUNT(*) FROM tpart; COUNT(*) 1 SELECT COUNT(*) FROM tcopy_part; COUNT(*) 3 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tpart#p#p0 8388608 8388608 test/tpart#p#p1 8388608 8388608 test/tpart#p#p2 8388608 8388608 test/tpart#p#p3 8388608 8388608 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tcopy_part%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tcopy_part 8388608 8388608 DROP TABLE tpart; DROP TABLE tcopy_part; # Tests for sub-partitioned tables. The autoextend_size should be updated # for each sub-partition # Create a non-partitioned table and add sub-partitions to it CREATE TABLE tsubpart(a INT NOT NULL PRIMARY KEY, b INT) AUTOEXTEND_SIZE 4M; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart 4194304 4194304 ALTER TABLE tsubpart PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 3 ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN maxvalue); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart#p#p1#sp#p1sp0 4194304 4194304 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304 test/tsubpart#p#p3#sp#p3sp0 4194304 4194304 test/tsubpart#p#p3#sp#p3sp1 4194304 4194304 test/tsubpart#p#p3#sp#p3sp2 4194304 4194304 SHOW CREATE TABLE tsubpart; Table Create Table tsubpart CREATE TABLE `tsubpart` ( `a` int NOT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`a`) ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`a`) SUBPARTITION BY HASH (`a`) SUBPARTITIONS 3 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ ALTER TABLE tsubpart DROP PARTITION p3; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart#p#p1#sp#p1sp0 4194304 4194304 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304 DROP TABLE tsubpart; # Create a non-partitioned table and add sub-partitions to it whle also modifying # autoextend_size CREATE TABLE tsubpart(a INT NOT NULL PRIMARY KEY, b INT) AUTOEXTEND_SIZE 4M; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart 4194304 4194304 ALTER TABLE tsubpart AUTOEXTEND_SIZE 8M PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 3 ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN maxvalue); SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart#p#p1#sp#p1sp0 8388608 8388608 test/tsubpart#p#p1#sp#p1sp1 8388608 8388608 test/tsubpart#p#p1#sp#p1sp2 8388608 8388608 test/tsubpart#p#p2#sp#p2sp0 8388608 8388608 test/tsubpart#p#p2#sp#p2sp1 8388608 8388608 test/tsubpart#p#p2#sp#p2sp2 8388608 8388608 test/tsubpart#p#p3#sp#p3sp0 8388608 8388608 test/tsubpart#p#p3#sp#p3sp1 8388608 8388608 test/tsubpart#p#p3#sp#p3sp2 8388608 8388608 SHOW CREATE TABLE tsubpart; Table Create Table tsubpart CREATE TABLE `tsubpart` ( `a` int NOT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`a`) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`a`) SUBPARTITION BY HASH (`a`) SUBPARTITIONS 3 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ ALTER TABLE tsubpart DROP PARTITION p3; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart#p#p1#sp#p1sp0 8388608 8388608 test/tsubpart#p#p1#sp#p1sp1 8388608 8388608 test/tsubpart#p#p1#sp#p1sp2 8388608 8388608 test/tsubpart#p#p2#sp#p2sp0 8388608 8388608 test/tsubpart#p#p2#sp#p2sp1 8388608 8388608 test/tsubpart#p#p2#sp#p2sp2 8388608 8388608 DROP TABLE tsubpart; # Create a sub-partitioned table and verify that changes to AUTOEXTEND_SIZE # are applied to all the subpartitions CREATE TABLE tsubpart (a INT NOT NULL PRIMARY KEY, b INT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 3 ( PARTITION p1 values less than (10), PARTITION p2 values less than (20), PARTITION p3 values less than maxvalue); SHOW CREATE TABLE tsubpart; Table Create Table tsubpart CREATE TABLE `tsubpart` ( `a` int NOT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`a`) ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`a`) SUBPARTITION BY HASH (`a`) SUBPARTITIONS 3 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart#p#p1#sp#p1sp0 4194304 4194304 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304 test/tsubpart#p#p3#sp#p3sp0 4194304 4194304 test/tsubpart#p#p3#sp#p3sp1 4194304 4194304 test/tsubpart#p#p3#sp#p3sp2 4194304 4194304 CREATE TABLE tsubpart_copy LIKE tsubpart; SHOW CREATE TABLE tsubpart_copy; Table Create Table tsubpart_copy CREATE TABLE `tsubpart_copy` ( `a` int NOT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`a`) ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`a`) SUBPARTITION BY HASH (`a`) SUBPARTITIONS 3 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart_copy%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart_copy#p#p1#sp#p1sp0 4194304 4194304 test/tsubpart_copy#p#p1#sp#p1sp1 4194304 4194304 test/tsubpart_copy#p#p1#sp#p1sp2 4194304 4194304 test/tsubpart_copy#p#p2#sp#p2sp0 4194304 4194304 test/tsubpart_copy#p#p2#sp#p2sp1 4194304 4194304 test/tsubpart_copy#p#p2#sp#p2sp2 4194304 4194304 test/tsubpart_copy#p#p3#sp#p3sp0 4194304 4194304 test/tsubpart_copy#p#p3#sp#p3sp1 4194304 4194304 test/tsubpart_copy#p#p3#sp#p3sp2 4194304 4194304 DROP TABLE tsubpart_copy; ALTER TABLE tsubpart AUTOEXTEND_SIZE 8M; SHOW CREATE TABLE tsubpart; Table Create Table tsubpart CREATE TABLE `tsubpart` ( `a` int NOT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`a`) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`a`) SUBPARTITION BY HASH (`a`) SUBPARTITIONS 3 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart#p#p1#sp#p1sp0 4194304 8388608 test/tsubpart#p#p1#sp#p1sp1 4194304 8388608 test/tsubpart#p#p1#sp#p1sp2 4194304 8388608 test/tsubpart#p#p2#sp#p2sp0 4194304 8388608 test/tsubpart#p#p2#sp#p2sp1 4194304 8388608 test/tsubpart#p#p2#sp#p2sp2 4194304 8388608 test/tsubpart#p#p3#sp#p3sp0 4194304 8388608 test/tsubpart#p#p3#sp#p3sp1 4194304 8388608 test/tsubpart#p#p3#sp#p3sp2 4194304 8388608 ALTER TABLE tsubpart AUTOEXTEND_SIZE 0; SHOW CREATE TABLE tsubpart; Table Create Table tsubpart CREATE TABLE `tsubpart` ( `a` int NOT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`a`) SUBPARTITION BY HASH (`a`) SUBPARTITIONS 3 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart#p#p1#sp#p1sp0 4194304 0 test/tsubpart#p#p1#sp#p1sp1 4194304 0 test/tsubpart#p#p1#sp#p1sp2 4194304 0 test/tsubpart#p#p2#sp#p2sp0 4194304 0 test/tsubpart#p#p2#sp#p2sp1 4194304 0 test/tsubpart#p#p2#sp#p2sp2 4194304 0 test/tsubpart#p#p3#sp#p3sp0 4194304 0 test/tsubpart#p#p3#sp#p3sp1 4194304 0 test/tsubpart#p#p3#sp#p3sp2 4194304 0 DROP TABLE tsubpart; # Test ALTER TABLE ... REMOVE PARTITIONING and ALTER TABLE ... EXCHANGE PARTITION # for sub-partitioned table CREATE TABLE tsubpart ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) ) AUTOEXTEND_SIZE = 8M PARTITION BY RANGE (id) SUBPARTITION BY KEY (lname) SUBPARTITIONS 2 ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150), PARTITION p3 VALUES LESS THAN (MAXVALUE)); SHOW CREATE TABLE tsubpart; Table Create Table tsubpart CREATE TABLE `tsubpart` ( `id` int NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`id`) SUBPARTITION BY KEY (lname) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ INSERT INTO tsubpart VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black"); SELECT COUNT(*) FROM tsubpart; COUNT(*) 4 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart#p#p0#sp#p0sp0 8388608 8388608 test/tsubpart#p#p0#sp#p0sp1 8388608 8388608 test/tsubpart#p#p1#sp#p1sp0 8388608 8388608 test/tsubpart#p#p1#sp#p1sp1 8388608 8388608 test/tsubpart#p#p2#sp#p2sp0 8388608 8388608 test/tsubpart#p#p2#sp#p2sp1 8388608 8388608 test/tsubpart#p#p3#sp#p3sp0 8388608 8388608 test/tsubpart#p#p3#sp#p3sp1 8388608 8388608 CREATE TABLE tcopy_subpart LIKE tsubpart; SHOW CREATE TABLE tcopy_subpart; Table Create Table tcopy_subpart CREATE TABLE `tcopy_subpart` ( `id` int NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`id`) SUBPARTITION BY KEY (lname) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ # Remove partitioning and validate the value of AUTOEXTEND_SIZE ALTER TABLE tcopy_subpart REMOVE PARTITIONING; SHOW CREATE TABLE tcopy_subpart; Table Create Table tcopy_subpart CREATE TABLE `tcopy_subpart` ( `id` int NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tcopy_subpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tcopy_subpart 8388608 8388608 # Exchange a partition SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tsubpart'; PARTITION_NAME TABLE_ROWS p0 1 p0 0 p1 0 p1 0 p2 0 p2 0 p3 1 p3 2 ALTER TABLE tsubpart EXCHANGE PARTITION p3sp0 WITH TABLE tcopy_subpart; SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tsubpart'; PARTITION_NAME TABLE_ROWS p0 0 p0 0 p1 0 p1 0 p2 0 p2 0 p3 0 p3 2 SELECT COUNT(*) FROM tsubpart; COUNT(*) 3 SELECT COUNT(*) FROM tcopy_subpart; COUNT(*) 1 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsubpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tsubpart#p#p0#sp#p0sp0 8388608 8388608 test/tsubpart#p#p0#sp#p0sp1 8388608 8388608 test/tsubpart#p#p1#sp#p1sp0 8388608 8388608 test/tsubpart#p#p1#sp#p1sp1 8388608 8388608 test/tsubpart#p#p2#sp#p2sp0 8388608 8388608 test/tsubpart#p#p2#sp#p2sp1 8388608 8388608 test/tsubpart#p#p3#sp#p3sp1 8388608 8388608 test/tsubpart#p#p3#sp#p3sp0 8388608 8388608 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tcopy_subpart%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/tcopy_subpart 8388608 8388608 DROP TABLE tsubpart; DROP TABLE tcopy_subpart; # Validate that AUTOEXTEND_SIZE clause is not supported with temporary tables CREATE TEMPORARY TABLE temp1 (c1 int) AUTOEXTEND_SIZE 4M; ERROR HY000: AUTOEXTEND_SIZE clause is not valid for temporary tablespace. CREATE TEMPORARY TABLE temp1(c1 INT); ALTER TABLE temp1 AUTOEXTEND_SIZE 4M; ERROR HY000: AUTOEXTEND_SIZE clause is not valid for temporary tablespace. DROP TEMPORARY TABLE temp1; # Validate that AUTOEXTEND_SIZE value is effective when the server is started # with --skip-innodb-validate-tablespace-paths command like option CREATE TABLE t1(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%t1%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/t1 4194304 4194304 CREATE PROCEDURE bulk_insert() BEGIN DECLARE i INT DEFAULT 1; WHILE i < 10000 DO INSERT INTO t1 VALUES (i, repeat('aaaaaa', 10000)); COMMIT; SET i = i + 1; END WHILE; END | # restart: --skip-innodb-validate-tablespace-paths call bulk_insert(); SELECT COUNT(*) FROM t1; COUNT(*) 9999 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%t1%'; NAME FILE_SIZE AUTOEXTEND_SIZE test/t1 662700032 4194304 DROP PROCEDURE bulk_insert; DROP TABLE t1; # restart # Bug#32235621 - Concurrent execution of ALTER TABLE and SHOW CREATE TABLE crashes # the server CREATE TABLE t1( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`, `c0001`), KEY `i0002` (`c0000`(112)) ) AUTOEXTEND_SIZE=8M ENGINE=InnoDB; CREATE PROCEDURE alter_table() BEGIN DECLARE i INT DEFAULT 0; WHILE i < 20 DO ALTER TABLE t1 FORCE; SET i = i + 1; END WHILE; END | CREATE PROCEDURE show_create_table() BEGIN DECLARE i INT DEFAULT 0; WHILE i < 20 DO SHOW CREATE TABLE t1; SET i = i + 1; END WHILE; END | CALL show_create_table(); CALL alter_table(); Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Table Create Table t1 CREATE TABLE `t1` ( `c0001` int NOT NULL /*!80023 INVISIBLE */, `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `c0002` decimal(65,14) DEFAULT NULL, `c0004` tinyblob NOT NULL, KEY `i0001` (`c0002`,`c0001`), KEY `i0002` (`c0000`(112)) ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE t1; DROP PROCEDURE show_create_table; DROP PROCEDURE alter_table;