Debian Codegraph @

mysql-test/suite/innodb/r/instant_ddl_rollback_debug.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 INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
10 test/t1 7 0 0 4 4 4
11 # Metadata from INFORMATION_SCHEMA.COLUMNS
12 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
13 id 0 6 1283 0 0 0
14 c1 1 12 16715791 0 0 0
15 c2 2 12 16715791 0 0 0
16 c3 3 12 16715791 0 0 0
17 # DD Metadata of table
18 NAME SE_PRIVATE_DATA
19 t1 NULL
20 # DD Metadata of columns in table
21 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
22 id 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID;
23 c1 2 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID;
24 c2 3 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID;
25 c3 4 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID;
26 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID;
27 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID;
28 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
29 SELECT id, length(c1), length(c2), length(c3) FROM t1;
30 id length(c1) length(c2) length(c3)
31 1 4000 4000 1
32 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1;
33 id LEFT(c1 , 10) LEFT(c2, 10) c3
34 1 aaaaaaaaaa bbbbbbbbbb c
35 # INSTANT ADD a column
36 ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT;
37 # Metadata from INFORMATION_SCHEMA.TABLES
38 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
39 test/t1 8 0 1 4 5 5
40 # Metadata from INFORMATION_SCHEMA.COLUMNS
41 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
42 id 0 6 1283 0 0 0
43 c1 1 12 16715791 0 0 0
44 c2 2 12 16715791 0 0 0
45 c3 3 12 16715791 0 0 0
46 c4 4 12 16716047 1 1 0
47 # DD Metadata of table
48 NAME SE_PRIVATE_DATA
49 t1 NULL
50 # DD Metadata of columns in table
51 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
52 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
53 c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
54 c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
55 c3 4 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=5;table_id=TABLE_ID;
56 c4 5 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1;
57 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
58 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
59 INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100));
60 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
61 id length(c1) length(c2) length(c3) length(c4)
62 1 4000 4000 1 500
63 2 4000 4000 1 100
64 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
65 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
66 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
67 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
68 START TRANSACTION;
69 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
70 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
71 id length(c1) length(c2) length(c3) length(c4)
72 1 200 4000 1 500
73 2 4000 4000 1 100
74 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
75 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
76 1 xxxxxxxxxx bbbbbbbbbb c dddddddddd
77 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
78 ROLLBACK;
79 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
80 id length(c1) length(c2) length(c3) length(c4)
81 1 4000 4000 1 500
82 2 4000 4000 1 100
83 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
84 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
85 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
86 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
87 # INSTANT DROP a column
88 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
89 # Metadata from INFORMATION_SCHEMA.TABLES
90 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
91 test/t1 7 0 2 4 4 5
92 # Metadata from INFORMATION_SCHEMA.COLUMNS
93 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
94 id 0 6 1283 0 0 0
95 c1 1 12 16715791 0 0 0
96 c2 2 12 16715791 0 0 0
97 c4 3 12 16716047 1 1 0
98 !hidden!_dropped_v2_p5_c3 7 12 16715791 0 0 2
99 # DD Metadata of table
100 NAME SE_PRIVATE_DATA
101 t1 NULL
102 # DD Metadata of columns in table
103 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
104 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
105 c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
106 c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
107 c4 4 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1;
108 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
109 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
110 !hidden!_dropped_v2_p5_c3 7 MYSQL_TYPE_VARCHAR 0 SE physical_pos=5;version_dropped=2;
111 SELECT id, length(c1), length(c2), length(c4) FROM t1;
112 id length(c1) length(c2) length(c4)
113 1 4000 4000 500
114 2 4000 4000 100
115 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
116 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
117 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
118 2 eeeeeeeeee ffffffffff hhhhhhhhhh
119 START TRANSACTION;
120 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
121 SELECT id, length(c1), length(c2), length(c4) FROM t1;
122 id length(c1) length(c2) length(c4)
123 1 200 4000 500
124 2 4000 4000 100
125 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
126 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
127 1 xxxxxxxxxx bbbbbbbbbb dddddddddd
128 2 eeeeeeeeee ffffffffff hhhhhhhhhh
129 ROLLBACK;
130 SELECT id, length(c1), length(c2), length(c4) FROM t1;
131 id length(c1) length(c2) length(c4)
132 1 4000 4000 500
133 2 4000 4000 100
134 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
135 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
136 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
137 2 eeeeeeeeee ffffffffff hhhhhhhhhh
138 ###########
139 # CLEANUP #
140 ###########
141 DROP TABLE t1;
142 ############################################
143 # Test instant ADD/DROP COLUMN for DYNAMIC format
144 ############################################
145 # ------------------------------------------------------------
146 # Create a table with 3 columns. [id, c1, c2, c3]
147 # ------------------------------------------------------------
148 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=DYNAMIC;;
149 # Metadata from INFORMATION_SCHEMA.TABLES
150 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
151 test/t1 7 0 0 4 4 4
152 # Metadata from INFORMATION_SCHEMA.COLUMNS
153 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
154 id 0 6 1283 0 0 0
155 c1 1 12 16715791 0 0 0
156 c2 2 12 16715791 0 0 0
157 c3 3 12 16715791 0 0 0
158 # DD Metadata of table
159 NAME SE_PRIVATE_DATA
160 t1 NULL
161 # DD Metadata of columns in table
162 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
163 id 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID;
164 c1 2 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID;
165 c2 3 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID;
166 c3 4 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID;
167 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID;
168 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID;
169 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
170 SELECT id, length(c1), length(c2), length(c3) FROM t1;
171 id length(c1) length(c2) length(c3)
172 1 4000 4000 1
173 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1;
174 id LEFT(c1 , 10) LEFT(c2, 10) c3
175 1 aaaaaaaaaa bbbbbbbbbb c
176 # INSTANT ADD a column
177 ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT;
178 # Metadata from INFORMATION_SCHEMA.TABLES
179 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
180 test/t1 8 0 1 4 5 5
181 # Metadata from INFORMATION_SCHEMA.COLUMNS
182 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
183 id 0 6 1283 0 0 0
184 c1 1 12 16715791 0 0 0
185 c2 2 12 16715791 0 0 0
186 c3 3 12 16715791 0 0 0
187 c4 4 12 16716047 1 1 0
188 # DD Metadata of table
189 NAME SE_PRIVATE_DATA
190 t1 NULL
191 # DD Metadata of columns in table
192 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
193 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
194 c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
195 c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
196 c3 4 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=5;table_id=TABLE_ID;
197 c4 5 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1;
198 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
199 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
200 INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100));
201 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
202 id length(c1) length(c2) length(c3) length(c4)
203 1 4000 4000 1 500
204 2 4000 4000 1 100
205 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
206 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
207 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
208 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
209 START TRANSACTION;
210 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
211 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
212 id length(c1) length(c2) length(c3) length(c4)
213 1 200 4000 1 500
214 2 4000 4000 1 100
215 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
216 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
217 1 xxxxxxxxxx bbbbbbbbbb c dddddddddd
218 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
219 ROLLBACK;
220 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
221 id length(c1) length(c2) length(c3) length(c4)
222 1 4000 4000 1 500
223 2 4000 4000 1 100
224 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
225 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
226 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
227 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
228 # INSTANT DROP a column
229 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
230 # Metadata from INFORMATION_SCHEMA.TABLES
231 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
232 test/t1 7 0 2 4 4 5
233 # Metadata from INFORMATION_SCHEMA.COLUMNS
234 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
235 id 0 6 1283 0 0 0
236 c1 1 12 16715791 0 0 0
237 c2 2 12 16715791 0 0 0
238 c4 3 12 16716047 1 1 0
239 !hidden!_dropped_v2_p5_c3 7 12 16715791 0 0 2
240 # DD Metadata of table
241 NAME SE_PRIVATE_DATA
242 t1 NULL
243 # DD Metadata of columns in table
244 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
245 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
246 c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
247 c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
248 c4 4 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1;
249 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
250 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
251 !hidden!_dropped_v2_p5_c3 7 MYSQL_TYPE_VARCHAR 0 SE physical_pos=5;version_dropped=2;
252 SELECT id, length(c1), length(c2), length(c4) FROM t1;
253 id length(c1) length(c2) length(c4)
254 1 4000 4000 500
255 2 4000 4000 100
256 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
257 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
258 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
259 2 eeeeeeeeee ffffffffff hhhhhhhhhh
260 START TRANSACTION;
261 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
262 SELECT id, length(c1), length(c2), length(c4) FROM t1;
263 id length(c1) length(c2) length(c4)
264 1 200 4000 500
265 2 4000 4000 100
266 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
267 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
268 1 xxxxxxxxxx bbbbbbbbbb dddddddddd
269 2 eeeeeeeeee ffffffffff hhhhhhhhhh
270 ROLLBACK;
271 SELECT id, length(c1), length(c2), length(c4) FROM t1;
272 id length(c1) length(c2) length(c4)
273 1 4000 4000 500
274 2 4000 4000 100
275 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
276 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
277 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
278 2 eeeeeeeeee ffffffffff hhhhhhhhhh
279 ###########
280 # CLEANUP #
281 ###########
282 DROP TABLE t1;
283 ############################################
284 # Test instant ADD/DROP COLUMN for COMPACT format
285 ############################################
286 # ------------------------------------------------------------
287 # Create a table with 3 columns. [id, c1, c2, c3]
288 # ------------------------------------------------------------
289 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=COMPACT;;
290 # Metadata from INFORMATION_SCHEMA.TABLES
291 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
292 test/t1 7 0 0 4 4 4
293 # Metadata from INFORMATION_SCHEMA.COLUMNS
294 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
295 id 0 6 1283 0 0 0
296 c1 1 12 16715791 0 0 0
297 c2 2 12 16715791 0 0 0
298 c3 3 12 16715791 0 0 0
299 # DD Metadata of table
300 NAME SE_PRIVATE_DATA
301 t1 NULL
302 # DD Metadata of columns in table
303 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
304 id 1 MYSQL_TYPE_LONG 1 Visible table_id=TABLE_ID;
305 c1 2 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID;
306 c2 3 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID;
307 c3 4 MYSQL_TYPE_VARCHAR 0 Visible table_id=TABLE_ID;
308 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID;
309 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID;
310 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
311 SELECT id, length(c1), length(c2), length(c3) FROM t1;
312 id length(c1) length(c2) length(c3)
313 1 4000 4000 1
314 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3 FROM t1;
315 id LEFT(c1 , 10) LEFT(c2, 10) c3
316 1 aaaaaaaaaa bbbbbbbbbb c
317 # INSTANT ADD a column
318 ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ALGORITHM=INSTANT;
319 # Metadata from INFORMATION_SCHEMA.TABLES
320 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
321 test/t1 8 0 1 4 5 5
322 # Metadata from INFORMATION_SCHEMA.COLUMNS
323 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
324 id 0 6 1283 0 0 0
325 c1 1 12 16715791 0 0 0
326 c2 2 12 16715791 0 0 0
327 c3 3 12 16715791 0 0 0
328 c4 4 12 16716047 1 1 0
329 # DD Metadata of table
330 NAME SE_PRIVATE_DATA
331 t1 NULL
332 # DD Metadata of columns in table
333 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
334 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
335 c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
336 c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
337 c3 4 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=5;table_id=TABLE_ID;
338 c4 5 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1;
339 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
340 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
341 INSERT INTO t1 VALUES(2, repeat('e', 4000), repeat('f', 4000), repeat('g', 1), repeat('h', 100));
342 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
343 id length(c1) length(c2) length(c3) length(c4)
344 1 4000 4000 1 500
345 2 4000 4000 1 100
346 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
347 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
348 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
349 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
350 START TRANSACTION;
351 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
352 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
353 id length(c1) length(c2) length(c3) length(c4)
354 1 200 4000 1 500
355 2 4000 4000 1 100
356 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
357 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
358 1 xxxxxxxxxx bbbbbbbbbb c dddddddddd
359 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
360 ROLLBACK;
361 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
362 id length(c1) length(c2) length(c3) length(c4)
363 1 4000 4000 1 500
364 2 4000 4000 1 100
365 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), c3, LEFT(c4, 10) FROM t1;
366 id LEFT(c1 , 10) LEFT(c2, 10) c3 LEFT(c4, 10)
367 1 aaaaaaaaaa bbbbbbbbbb c dddddddddd
368 2 eeeeeeeeee ffffffffff g hhhhhhhhhh
369 # INSTANT DROP a column
370 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
371 # Metadata from INFORMATION_SCHEMA.TABLES
372 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
373 test/t1 7 0 2 4 4 5
374 # Metadata from INFORMATION_SCHEMA.COLUMNS
375 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
376 id 0 6 1283 0 0 0
377 c1 1 12 16715791 0 0 0
378 c2 2 12 16715791 0 0 0
379 c4 3 12 16716047 1 1 0
380 !hidden!_dropped_v2_p5_c3 7 12 16715791 0 0 2
381 # DD Metadata of table
382 NAME SE_PRIVATE_DATA
383 t1 NULL
384 # DD Metadata of columns in table
385 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
386 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
387 c1 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
388 c2 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
389 c4 4 MYSQL_TYPE_VARCHAR 0 Visible default=6464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464;physical_pos=6;table_id=TABLE_ID;version_added=1;
390 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
391 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
392 !hidden!_dropped_v2_p5_c3 7 MYSQL_TYPE_VARCHAR 0 SE physical_pos=5;version_dropped=2;
393 SELECT id, length(c1), length(c2), length(c4) FROM t1;
394 id length(c1) length(c2) length(c4)
395 1 4000 4000 500
396 2 4000 4000 100
397 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
398 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
399 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
400 2 eeeeeeeeee ffffffffff hhhhhhhhhh
401 START TRANSACTION;
402 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
403 SELECT id, length(c1), length(c2), length(c4) FROM t1;
404 id length(c1) length(c2) length(c4)
405 1 200 4000 500
406 2 4000 4000 100
407 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
408 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
409 1 xxxxxxxxxx bbbbbbbbbb dddddddddd
410 2 eeeeeeeeee ffffffffff hhhhhhhhhh
411 ROLLBACK;
412 SELECT id, length(c1), length(c2), length(c4) FROM t1;
413 id length(c1) length(c2) length(c4)
414 1 4000 4000 500
415 2 4000 4000 100
416 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c4, 10) FROM t1;
417 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c4, 10)
418 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
419 2 eeeeeeeeee ffffffffff hhhhhhhhhh
420 ###########
421 # CLEANUP #
422 ###########
423 DROP TABLE t1;