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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
--source include/have_innodb.inc
let $algorithm = `SELECT @@ALTER_ALGORITHM`;
let $error_code = 0;
if ($algorithm == "NOCOPY") {
let $error_code = ER_ALTER_OPERATION_NOT_SUPPORTED, ER_ALTER_OPERATION_NOT_SUPPORTED_REASON;
}
if ($algorithm == "INSTANT") {
let $error_code = ER_ALTER_OPERATION_NOT_SUPPORTED, ER_ALTER_OPERATION_NOT_SUPPORTED_REASON;
}
CREATE TABLE t1(f1 INT NOT NULL,
f2 INT NOT NULL,
f3 INT NULL,
f4 INT as (f2) STORED,
f5 INT as (f3) STORED,
PRIMARY KEY(f1))ROW_FORMAT=COMPRESSED, ENGINE=INNODB;
INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1);
SELECT @@alter_algorithm;
--enable_info
--echo # All the following cases needs table rebuild
--echo # Add and Drop primary key
--error $error_code
ALTER TABLE t1 ADD COLUMN col1 INT NOT NULL,DROP PRIMARY KEY,ADD PRIMARY KEY(col1);
--echo # Make existing column NULLABLE
--error $error_code
ALTER TABLE t1 MODIFY f2 INT;
--echo # Drop Stored Column
--error $error_code
ALTER TABLE t1 DROP COLUMN f5;
--echo # Add base non-generated column as a last column in the compressed table
--error $error_code
ALTER TABLE t1 ADD COLUMN f6 INT NOT NULL;
--echo # Add base non-generated column but not in the last position
--error $error_code
ALTER TABLE t1 ADD COLUMN f7 INT NOT NULL after f3;
--echo # Force the table to rebuild
--error $error_code
ALTER TABLE t1 FORCE;
--echo # Row format changes
--error $error_code
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
--echo # Engine table
--error $error_code
ALTER TABLE t1 ENGINE=INNODB;
# Irrespective of alter_algorithm value, the following command
# should succeed because of explicitly mentioning ALGORTHM=DEFAULT
ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT;
DROP TABLE t1;
--disable_info
if ($algorithm == "NOCOPY") {
let $error_code = 0;
}
if ($algorithm == "INSTANT") {
let $error_code = ER_ALTER_OPERATION_NOT_SUPPORTED_REASON;
}
CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL,
f3 INT AS (f2 * f2) VIRTUAL,
f4 INT NOT NULL UNIQUE,
f5 INT NOT NULL,
INDEX idx(f2))ENGINE=INNODB;
CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL,
INDEX(f1),
FOREIGN KEY fidx(f1) REFERENCES t1(f1))ENGINE=INNODB;
INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4);
--enable_info
--error $error_code
ALTER TABLE t1 ADD INDEX idx1(f4), page_compressed=1;
--error $error_code
ALTER TABLE t1 DROP INDEX idx, page_compression_level=5;
--error $error_code
ALTER TABLE t1 ADD UNIQUE INDEX u1(f2);
--error $error_code
ALTER TABLE t1 DROP INDEX f4, page_compression_level=9;
SET foreign_key_checks = 0;
--error $error_code
ALTER TABLE t1 ADD FOREIGN KEY(f5) REFERENCES t2(f1);
DROP TABLE t2, t1;
--disable_info
CREATE TABLE t1(f1 INT NOT NULL,
f2 INT NOT NULL,
f3 INT AS (f2 * f2) VIRTUAL,
INDEX idx (f2))engine=innodb;
CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL,
f3 VARCHAR(10),
INDEX(f1))ENGINE=INNODB;
INSERT INTO t1(f1, f2) VALUES(1, 1);
--enable_info
--echo # Add column at the end of the table
ALTER TABLE t1 ADD COLUMN f4 char(100) default 'BIG WALL';
--echo # Change virtual column expression
ALTER TABLE t1 CHANGE f3 f3 INT AS (f2 * f2) VIRTUAL;
--echo # Add virtual column
ALTER TABLE t1 ADD COLUMN f5 INT AS (f2) VIRTUAL;
--echo # Rename Column
ALTER TABLE t1 CHANGE f3 vcol INT AS (f2) VIRTUAL;
--echo # Rename table
ALTER TABLE t1 RENAME t3;
--echo # Drop Virtual Column
ALTER TABLE t3 DROP COLUMN vcol;
--echo # Column length varies
ALTER TABLE t2 CHANGE f3 f3 VARCHAR(20);
SET foreign_key_checks = 0;
ALTER TABLE t3 ADD FOREIGN KEY fidx(f2) REFERENCES t2(f1);
SET foreign_key_checks = 1;
ALTER TABLE t3 DROP FOREIGN KEY fidx;
DROP TABLE t3, t2;
--disable_info
|
