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
146
147
148
149
150
|
--- alter_algorithm.result
+++ alter_algorithm.reject
@@ -7,40 +7,40 @@
INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1);
SELECT @@alter_algorithm;
@@alter_algorithm
-COPY
+INPLACE
# All the following cases needs table rebuild
# Add and Drop primary key
ALTER TABLE t1 ADD COLUMN col1 INT NOT NULL,DROP PRIMARY KEY,ADD PRIMARY KEY(col1);
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Make existing column NULLABLE
ALTER TABLE t1 MODIFY f2 INT;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Drop Stored Column
ALTER TABLE t1 DROP COLUMN f5;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Add base non-generated column as a last column in the compressed table
ALTER TABLE t1 ADD COLUMN f6 INT NOT NULL;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Add base non-generated column but not in the last position
ALTER TABLE t1 ADD COLUMN f7 INT NOT NULL after f3;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Force the table to rebuild
ALTER TABLE t1 FORCE;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Row format changes
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Engine table
ALTER TABLE t1 ENGINE=INNODB;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT;
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 0
@@ -56,22 +56,22 @@
FOREIGN KEY fidx(f1) REFERENCES t1(f1))ENGINE=INNODB;
INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4);
ALTER TABLE t1 ADD INDEX idx1(f4), page_compressed=1;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE t1 DROP INDEX idx, page_compression_level=5;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE t1 ADD UNIQUE INDEX u1(f2);
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE t1 DROP INDEX f4, page_compression_level=9;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
SET foreign_key_checks = 0;
affected rows: 0
ALTER TABLE t1 ADD FOREIGN KEY(f5) REFERENCES t2(f1);
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
DROP TABLE t2, t1;
affected rows: 0
CREATE TABLE t1(f1 INT NOT NULL,
@@ -84,28 +84,27 @@
INSERT INTO t1(f1, f2) VALUES(1, 1);
# Add column at the end of the table
ALTER TABLE t1 ADD COLUMN f4 char(100) default 'BIG WALL';
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Change virtual column expression
ALTER TABLE t1 CHANGE f3 f3 INT AS (f2 * f2) VIRTUAL;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Add virtual column
ALTER TABLE t1 ADD COLUMN f5 INT AS (f2) VIRTUAL;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Rename Column
ALTER TABLE t1 CHANGE f3 vcol INT AS (f2) VIRTUAL;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Rename table
ALTER TABLE t1 RENAME t3;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
# Drop Virtual Column
ALTER TABLE t3 DROP COLUMN vcol;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
# Column length varies
ALTER TABLE t2 CHANGE f3 f3 VARCHAR(20);
affected rows: 0
@@ -113,12 +112,12 @@
SET foreign_key_checks = 0;
affected rows: 0
ALTER TABLE t3 ADD FOREIGN KEY fidx(f2) REFERENCES t2(f1);
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
SET foreign_key_checks = 1;
affected rows: 0
ALTER TABLE t3 DROP FOREIGN KEY fidx;
-affected rows: 1
-info: Records: 1 Duplicates: 0 Warnings: 0
+affected rows: 0
+info: Records: 0 Duplicates: 0 Warnings: 0
DROP TABLE t3, t2;
affected rows: 0
|
