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