Debian Codegraph @

mysql-test/suite/innodb/include/instant_ddl_basic.inc

raw
1 ##########################################################################
2 # Test script to test ALTER TABLE ADD/DROP COLUMN ... ALGORITHM=INSTANT
3 # at different places for table with
4 # - No index defined
5 # - Cluster index defined
6 # - Secondary index defined
7 #
8 # This test is to be run in debug mode only and with debug flag
9 # $row_format : indicates the row_format on which test is to be run.
10 # $clust_index : indicates Primary key clause (if any)
11 # $sec_index : indicates secondary key clause (if any)
12 ##########################################################################
13
14 --echo # ------------------------------------------------------------
15 --echo # Create a table with 3 columns. [c1, c2, c3]
16 --echo # ------------------------------------------------------------
17 --eval CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) $clust_index $sec_index) ROW_FORMAT=$row_format
18
19 --let $table_name=t1
20 --source suite/innodb/include/print_instant_metadata.inc
21
22 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
23 SELECT * FROM t1 ORDER BY c1;
24
25 --echo
26 --echo # -----------------------ADD COLUMN TEST----------------------
27 --echo
28
29 --echo # ------------------------------------------------------------
30 --echo # Add a new column at the end. [c1, c2, c3, +c4]
31 --echo # ------------------------------------------------------------
32 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
33
34 --let $table_name=t1
35 --source suite/innodb/include/print_instant_metadata.inc
36
37 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
38 SELECT * FROM t1 ORDER BY c1;
39
40 --echo # ------------------------------------------------------------
41 --echo # Add a new column in between. [c1, +c5, c2, c3, c4]
42 --echo # ------------------------------------------------------------
43 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
44
45 --let $table_name=t1
46 --source suite/innodb/include/print_instant_metadata.inc
47
48 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
49 SELECT * FROM t1 ORDER BY c1;
50
51 --echo # ------------------------------------------------------------
52 --echo # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
53 --echo # ------------------------------------------------------------
54 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
55
56 --let $table_name=t1
57 --source suite/innodb/include/print_instant_metadata.inc
58
59 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
60 SELECT * FROM t1 ORDER BY c1;
61
62
63 --echo # ------------------------------------------------------------
64 --echo # Rebuild table. [c6, c1, c5, c2, c3, c4]
65 --echo # ------------------------------------------------------------
66 ALTER TABLE t1 force;
67
68 --let $table_name=t1
69 --source suite/innodb/include/print_instant_metadata.inc
70
71 SELECT * FROM t1 ORDER BY c1;
72
73 --echo
74 --echo # -----------------------DROP COLUMN TEST---------------------
75 --echo
76
77 --echo # ------------------------------------------------------------
78 --echo # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
79 --echo # ------------------------------------------------------------
80 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
81
82 --let $table_name=t1
83 --source suite/innodb/include/print_instant_metadata.inc
84
85 SELECT * FROM t1 ORDER BY c1;
86 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
87 SELECT * FROM t1 ORDER BY c1;
88
89 --echo # ------------------------------------------------------------
90 --echo # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
91 --echo # ------------------------------------------------------------
92 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
93
94 --let $table_name=t1
95 --source suite/innodb/include/print_instant_metadata.inc
96
97 SELECT * FROM t1 ORDER BY c1;
98 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
99 SELECT * FROM t1 ORDER BY c1;
100
101 --echo # ------------------------------------------------------------
102 --echo # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
103 --echo # ------------------------------------------------------------
104 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
105
106 --let $table_name=t1
107 --source suite/innodb/include/print_instant_metadata.inc
108
109 SELECT * FROM t1 ORDER BY c1;
110 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
111 SELECT * FROM t1 ORDER BY c1;
112
113 --echo # ------------------------------------------------------------
114 --echo # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
115 --echo # ------------------------------------------------------------
116 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
117
118 --let $table_name=t1
119 --source suite/innodb/include/print_instant_metadata.inc
120
121 SELECT * FROM t1 ORDER BY c1;
122 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
123 SELECT * FROM t1 ORDER BY c1;
124
125 --echo # --------------------------------------------------------------------------
126 --echo # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
127 --echo # --------------------------------------------------------------------------
128 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
129
130 --let $table_name=t1
131 --source suite/innodb/include/print_instant_metadata.inc
132
133 SELECT * FROM t1 ORDER BY c1;
134 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
135 SELECT * FROM t1 ORDER BY c1;
136
137 --echo # ------------------------------------------------------------
138 --echo # Rebuild table. [c1, c2, c7]
139 --echo # ------------------------------------------------------------
140 ALTER TABLE t1 force;
141
142 --let $table_name=t1
143 --source suite/innodb/include/print_instant_metadata.inc
144
145 SELECT * FROM t1 ORDER BY c1;
146 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
147 SELECT * FROM t1 ORDER BY c1;
148
149 --echo ###########
150 --echo # CLEANUP #
151 --echo ###########
152 DROP TABLE t1;
153
154 #
155 # Bug #33937504: INSTANT ADD COLUMNS are visible in older read view created before INSTANT DDL
156 #
157
158 --connect(con2,localhost,root,,)
159 --connect(con3,localhost,root,,)
160
161
162 --let $drop=0
163
164 while ($drop <= 1) {
165
166 --let $use_instant=0
167 while ($use_instant <= 1) {
168
169 connection con2;
170 --let $table_name=t2
171 --eval CREATE TABLE $table_name (c1 char(10), c2 char(10)) ROW_FORMAT=$row_format
172 INSERT INTO t2 VALUES ("r1c1", "r1c2");
173 SELECT * FROM t2;
174
175 --let $table_name=t1
176 --eval CREATE TABLE $table_name (c1 char(10), c2 char(10)) ROW_FORMAT=$row_format
177 INSERT INTO t1 VALUES ("r1c1", "r1c2");
178 SELECT * FROM t1;
179
180 BEGIN;
181 SELECT * FROM t2;
182
183 connection con3;
184 if ($use_instant == 0) {
185 if ($drop == 0) {
186 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
187 ALGORITHM=COPY;
188 }
189 if ($drop == 1) {
190 ALTER TABLE t1 DROP COLUMN c2,
191 ALGORITHM=COPY;
192 }
193 }
194
195 if ($use_instant == 1) {
196 if ($drop == 0) {
197 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
198 ALGORITHM=INSTANT;
199 }
200 if ($drop == 1) {
201 ALTER TABLE t1 DROP COLUMN c2,
202 ALGORITHM=INSTANT;
203 }
204 }
205
206 UPDATE t1 SET c1 = "r1c11";
207 SELECT * FROM t1;
208
209 connection con2;
210 --error ER_TABLE_DEF_CHANGED
211 SELECT * FROM t1;
212
213 DROP TABLE t1;
214 DROP TABLE t2;
215
216 --inc $use_instant
217 }
218 --inc $drop
219 }
220
221 connection default;
222
223 disconnect con2;
224 disconnect con3;