Debian Codegraph @

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

raw
1 #############################################################################
2 # Bug#32788564: TOO MANY COLUMNS ON PARTITIONED TABLE MAKES DATABASE BROKEN #
3 #############################################################################
4
5 --source include/have_innodb_16k.inc
6
7 # This test verifies the bug#32788564 where the server crashed while
8 # executing TRUNCATE TABLE on a partitioned table, if an
9 # ALTER TABLE ADD COLUMN failed to add columns because of hitting the
10 # upper limit on the number of columns in a table
11
12 DELIMITER |;
13
14 # Procedure to add enough columns to a table such that it reaches the maximum
15 # number of columns allowed on a table
16 CREATE PROCEDURE add_columns(table_name varchar(50), algorithm varchar(10))
17 BEGIN
18 DECLARE i INT DEFAULT 1;
19 SET @sql_text = CONCAT('ALTER TABLE ', table_name, ' ');
20 while (i < 1012) do
21 SET @sql_text = CONCAT(@sql_text, ' ADD COLUMN ', CONCAT('col_', i), ' INTEGER NULL DEFAULT NULL,');
22 set i = i + 1;
23 end while;
24 SET @sql_text = CONCAT(@sql_text, 'ALGORITHM=', algorithm);
25 PREPARE stmt FROM @sql_text;
26 EXECUTE stmt;
27 DEALLOCATE PREPARE stmt;
28 END|
29
30 CREATE PROCEDURE create_tables()
31 BEGIN
32 # Create a non-partitioned table
33 CREATE TABLE `test`.`TEST_TABLE` (
34 `TIMESTAMP` datetime NOT NULL,
35 `INTERVALL` int NOT NULL,
36 `IDENT_KEY` int NOT NULL,
37 `FIELD_1` double DEFAULT NULL,
38 `FIELD_2` double DEFAULT NULL,
39 `FIELD_3` double DEFAULT NULL
40 );
41
42 # Create a partitioned table
43 CREATE TABLE `test`.`TEST_TABLE_PARTITIONED` (
44 `TIMESTAMP` datetime NOT NULL,
45 `INTERVALL` int NOT NULL,
46 `IDENT_KEY` int NOT NULL,
47 `FIELD_1` double DEFAULT NULL,
48 `FIELD_2` double DEFAULT NULL,
49 `FIELD_3` double DEFAULT NULL
50 )
51 PARTITION BY RANGE COLUMNS(`TIMESTAMP`)
52 (
53 PARTITION p20210321 VALUES LESS THAN ('2021-03-22'),
54 PARTITION p20210322 VALUES LESS THAN ('2021-03-23'),
55 PARTITION p20210323 VALUES LESS THAN MAXVALUE
56 );
57 END |
58 DELIMITER ;|
59
60 # Test ALTER TABLE ... ADD COLUMN both on partitioned and unpartitioned
61 # tables using all the possible algorithms viz. INPLACE, INSTANT, COPY
62 # and verify that the table can be truncate even after the
63 # ALTER TABLE ... ADD COLUMN fails
64
65 # Test for ALGORITHM=INPLACE
66 call create_tables();
67 call add_columns('TEST_TABLE', 'INPLACE');
68 call add_columns('TEST_TABLE_PARTITIONED', 'INPLACE');
69
70 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
71 ALTER TABLE TEST_TABLE ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=INPLACE;
72
73 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
74 ALTER TABLE TEST_TABLE_PARTITIONED ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=INPLACE;
75
76 TRUNCATE TABLE TEST_TABLE;
77 TRUNCATE TABLE TEST_TABLE_PARTITIONED;
78 DROP TABLE TEST_TABLE;
79 DROP TABLE TEST_TABLE_PARTITIONED;
80
81 # Test for ALGORITHM=INSTANT
82 call create_tables();
83 call add_columns('TEST_TABLE', 'INSTANT');
84 call add_columns('TEST_TABLE_PARTITIONED', 'INSTANT');
85
86 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
87 ALTER TABLE TEST_TABLE ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=INSTANT;
88
89 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
90 ALTER TABLE TEST_TABLE_PARTITIONED ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=INSTANT;
91
92 TRUNCATE TABLE TEST_TABLE;
93 TRUNCATE TABLE TEST_TABLE_PARTITIONED;
94 DROP TABLE TEST_TABLE;
95 DROP TABLE TEST_TABLE_PARTITIONED;
96
97 # Test for ALGORITHM=COPY
98 call create_tables();
99 call add_columns('TEST_TABLE', 'COPY');
100 call add_columns('TEST_TABLE_PARTITIONED', 'COPY');
101
102 --error ER_TOO_MANY_FIELDS
103 ALTER TABLE TEST_TABLE ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=COPY;
104
105 --error ER_TOO_MANY_FIELDS
106 ALTER TABLE TEST_TABLE_PARTITIONED ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=COPY;
107
108 TRUNCATE TABLE TEST_TABLE;
109 TRUNCATE TABLE TEST_TABLE_PARTITIONED;
110 DROP TABLE TEST_TABLE;
111 DROP TABLE TEST_TABLE_PARTITIONED;
112
113 # Test for ALGORITHM=DEFAULT
114 call create_tables();
115 call add_columns('TEST_TABLE', 'DEFAULT');
116 call add_columns('TEST_TABLE_PARTITIONED', 'DEFAULT');
117
118 --error ER_TOO_MANY_FIELDS
119 ALTER TABLE TEST_TABLE ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=DEFAULT;
120
121 --error ER_TOO_MANY_FIELDS
122 ALTER TABLE TEST_TABLE_PARTITIONED ADD I_VJ278 INTEGER NULL DEFAULT NULL, ALGORITHM=DEFAULT;
123
124 TRUNCATE TABLE TEST_TABLE;
125 TRUNCATE TABLE TEST_TABLE_PARTITIONED;
126 DROP TABLE TEST_TABLE;
127 DROP TABLE TEST_TABLE_PARTITIONED;
128
129 # Cleanup
130 DROP PROCEDURE create_tables;
131 DROP PROCEDURE add_columns;