Debian Codegraph @

mysql-test/suite/innodb/r/instant_ddl_rollback.result

raw
1 ##############################################
2 # Test instant ADD/DROP COLUMN for REDUNDANT format
3 ##############################################
4 # ------------------------------------------------------------
5 # Create a table with 3 columns. [id, c1, c2, c3]
6 # ------------------------------------------------------------
7 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=REDUNDANT;;
8 # Metadata from INFORMATION_SCHEMA.TABLES
9 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
10 test/t1 7 0 0
11 # Metadata from INFORMATION_SCHEMA.COLUMNS
12 NAME POS MTYPE PRTYPE HAS_DEFAULT
13 id 0 6 1283 0
14 c1 1 12 16715791 0
15 c2 2 12 16715791 0
16 c3 3 12 16715791 0
17 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
18 SELECT id, length(c1), length(c2), length(c3) FROM t1;
19 id length(c1) length(c2) length(c3)
20 1 4000 4000 1
21 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1;
22 id LEFT(c1 , 10) LEFT(c2, 10) c3
23 1 aaaaaaaaaa bbbbbbbbbb c
24 # INSTANT ADD a column
25 ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT;
26 # Metadata from INFORMATION_SCHEMA.TABLES
27 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
28 test/t1 8 0 1
29 # Metadata from INFORMATION_SCHEMA.COLUMNS
30 NAME POS MTYPE PRTYPE HAS_DEFAULT
31 id 0 6 1283 0
32 c1 1 12 16715791 0
33 c2 2 12 16715791 0
34 c3 3 12 16715791 0
35 c4 4 12 16716047 1
36 INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100));
37 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
38 id length(c1) length(c2) length(c3) length(c4)
39 1 4000 4000 1 500
40 2 4000 4000 1 100
41 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
42 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
43 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
44 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
45 START TRANSACTION;
46 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
47 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
48 id length(c1) length(c2) length(c3) length(c4)
49 1 200 4000 1 500
50 2 4000 4000 1 100
51 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
52 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
53 1 xxxxxxxxxx bbbbbbbbbb c dddddddddd
54 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
55 ROLLBACK;
56 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
57 id length(c1) length(c2) length(c3) length(c4)
58 1 4000 4000 1 500
59 2 4000 4000 1 100
60 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
61 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
62 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
63 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
64 # INSTANT DROP a column
65 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
66 # Metadata from INFORMATION_SCHEMA.TABLES
67 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
68 test/t1 7 0 2
69 # Metadata from INFORMATION_SCHEMA.COLUMNS
70 NAME POS MTYPE PRTYPE HAS_DEFAULT
71 id 0 6 1283 0
72 c1 1 12 16715791 0
73 c2 2 12 16715791 0
74 c4 3 12 16716047 1
75 SELECT id, length(c1), length(c2), length(c4) FROM t1;
76 id length(c1) length(c2) length(c4)
77 1 4000 4000 500
78 2 4000 4000 100
79 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
80 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
81 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
82 2 eeeeeeeeee ffffffffff hhhhhhhhhh
83 START TRANSACTION;
84 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
85 SELECT id, length(c1), length(c2), length(c4) FROM t1;
86 id length(c1) length(c2) length(c4)
87 1 200 4000 500
88 2 4000 4000 100
89 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
90 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
91 1 xxxxxxxxxx bbbbbbbbbb dddddddddd
92 2 eeeeeeeeee ffffffffff hhhhhhhhhh
93 ROLLBACK;
94 SELECT id, length(c1), length(c2), length(c4) FROM t1;
95 id length(c1) length(c2) length(c4)
96 1 4000 4000 500
97 2 4000 4000 100
98 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
99 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
100 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
101 2 eeeeeeeeee ffffffffff hhhhhhhhhh
102 ###########
103 # CLEANUP #
104 ###########
105 DROP TABLE t1;
106 ############################################
107 # Test instant ADD/DROP COLUMN for DYNAMIC format
108 ############################################
109 # ------------------------------------------------------------
110 # Create a table with 3 columns. [id, c1, c2, c3]
111 # ------------------------------------------------------------
112 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=DYNAMIC;;
113 # Metadata from INFORMATION_SCHEMA.TABLES
114 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
115 test/t1 7 0 0
116 # Metadata from INFORMATION_SCHEMA.COLUMNS
117 NAME POS MTYPE PRTYPE HAS_DEFAULT
118 id 0 6 1283 0
119 c1 1 12 16715791 0
120 c2 2 12 16715791 0
121 c3 3 12 16715791 0
122 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
123 SELECT id, length(c1), length(c2), length(c3) FROM t1;
124 id length(c1) length(c2) length(c3)
125 1 4000 4000 1
126 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1;
127 id LEFT(c1 , 10) LEFT(c2, 10) c3
128 1 aaaaaaaaaa bbbbbbbbbb c
129 # INSTANT ADD a column
130 ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT;
131 # Metadata from INFORMATION_SCHEMA.TABLES
132 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
133 test/t1 8 0 1
134 # Metadata from INFORMATION_SCHEMA.COLUMNS
135 NAME POS MTYPE PRTYPE HAS_DEFAULT
136 id 0 6 1283 0
137 c1 1 12 16715791 0
138 c2 2 12 16715791 0
139 c3 3 12 16715791 0
140 c4 4 12 16716047 1
141 INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100));
142 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
143 id length(c1) length(c2) length(c3) length(c4)
144 1 4000 4000 1 500
145 2 4000 4000 1 100
146 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
147 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
148 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
149 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
150 START TRANSACTION;
151 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
152 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
153 id length(c1) length(c2) length(c3) length(c4)
154 1 200 4000 1 500
155 2 4000 4000 1 100
156 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
157 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
158 1 xxxxxxxxxx bbbbbbbbbb c dddddddddd
159 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
160 ROLLBACK;
161 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
162 id length(c1) length(c2) length(c3) length(c4)
163 1 4000 4000 1 500
164 2 4000 4000 1 100
165 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
166 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
167 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
168 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
169 # INSTANT DROP a column
170 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
171 # Metadata from INFORMATION_SCHEMA.TABLES
172 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
173 test/t1 7 0 2
174 # Metadata from INFORMATION_SCHEMA.COLUMNS
175 NAME POS MTYPE PRTYPE HAS_DEFAULT
176 id 0 6 1283 0
177 c1 1 12 16715791 0
178 c2 2 12 16715791 0
179 c4 3 12 16716047 1
180 SELECT id, length(c1), length(c2), length(c4) FROM t1;
181 id length(c1) length(c2) length(c4)
182 1 4000 4000 500
183 2 4000 4000 100
184 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
185 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
186 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
187 2 eeeeeeeeee ffffffffff hhhhhhhhhh
188 START TRANSACTION;
189 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
190 SELECT id, length(c1), length(c2), length(c4) FROM t1;
191 id length(c1) length(c2) length(c4)
192 1 200 4000 500
193 2 4000 4000 100
194 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
195 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
196 1 xxxxxxxxxx bbbbbbbbbb dddddddddd
197 2 eeeeeeeeee ffffffffff hhhhhhhhhh
198 ROLLBACK;
199 SELECT id, length(c1), length(c2), length(c4) FROM t1;
200 id length(c1) length(c2) length(c4)
201 1 4000 4000 500
202 2 4000 4000 100
203 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
204 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
205 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
206 2 eeeeeeeeee ffffffffff hhhhhhhhhh
207 ###########
208 # CLEANUP #
209 ###########
210 DROP TABLE t1;
211 ############################################
212 # Test instant ADD/DROP COLUMN for COMPACT format
213 ############################################
214 # ------------------------------------------------------------
215 # Create a table with 3 columns. [id, c1, c2, c3]
216 # ------------------------------------------------------------
217 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=COMPACT;;
218 # Metadata from INFORMATION_SCHEMA.TABLES
219 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
220 test/t1 7 0 0
221 # Metadata from INFORMATION_SCHEMA.COLUMNS
222 NAME POS MTYPE PRTYPE HAS_DEFAULT
223 id 0 6 1283 0
224 c1 1 12 16715791 0
225 c2 2 12 16715791 0
226 c3 3 12 16715791 0
227 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
228 SELECT id, length(c1), length(c2), length(c3) FROM t1;
229 id length(c1) length(c2) length(c3)
230 1 4000 4000 1
231 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1;
232 id LEFT(c1 , 10) LEFT(c2, 10) c3
233 1 aaaaaaaaaa bbbbbbbbbb c
234 # INSTANT ADD a column
235 ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT;
236 # Metadata from INFORMATION_SCHEMA.TABLES
237 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
238 test/t1 8 0 1
239 # Metadata from INFORMATION_SCHEMA.COLUMNS
240 NAME POS MTYPE PRTYPE HAS_DEFAULT
241 id 0 6 1283 0
242 c1 1 12 16715791 0
243 c2 2 12 16715791 0
244 c3 3 12 16715791 0
245 c4 4 12 16716047 1
246 INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100));
247 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
248 id length(c1) length(c2) length(c3) length(c4)
249 1 4000 4000 1 500
250 2 4000 4000 1 100
251 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
252 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
253 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
254 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
255 START TRANSACTION;
256 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
257 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
258 id length(c1) length(c2) length(c3) length(c4)
259 1 200 4000 1 500
260 2 4000 4000 1 100
261 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
262 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
263 1 xxxxxxxxxx bbbbbbbbbb c dddddddddd
264 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
265 ROLLBACK;
266 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
267 id length(c1) length(c2) length(c3) length(c4)
268 1 4000 4000 1 500
269 2 4000 4000 1 100
270 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
271 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
272 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
273 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
274 # INSTANT DROP a column
275 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
276 # Metadata from INFORMATION_SCHEMA.TABLES
277 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
278 test/t1 7 0 2
279 # Metadata from INFORMATION_SCHEMA.COLUMNS
280 NAME POS MTYPE PRTYPE HAS_DEFAULT
281 id 0 6 1283 0
282 c1 1 12 16715791 0
283 c2 2 12 16715791 0
284 c4 3 12 16716047 1
285 SELECT id, length(c1), length(c2), length(c4) FROM t1;
286 id length(c1) length(c2) length(c4)
287 1 4000 4000 500
288 2 4000 4000 100
289 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
290 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
291 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
292 2 eeeeeeeeee ffffffffff hhhhhhhhhh
293 START TRANSACTION;
294 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
295 SELECT id, length(c1), length(c2), length(c4) FROM t1;
296 id length(c1) length(c2) length(c4)
297 1 200 4000 500
298 2 4000 4000 100
299 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
300 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
301 1 xxxxxxxxxx bbbbbbbbbb dddddddddd
302 2 eeeeeeeeee ffffffffff hhhhhhhhhh
303 ROLLBACK;
304 SELECT id, length(c1), length(c2), length(c4) FROM t1;
305 id length(c1) length(c2) length(c4)
306 1 4000 4000 500
307 2 4000 4000 100
308 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
309 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
310 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
311 2 eeeeeeeeee ffffffffff hhhhhhhhhh
312 ###########
313 # CLEANUP #
314 ###########
315 DROP TABLE t1;