Debian Codegraph @

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

raw
1 # ------------------------------------------------------------
2 # Start server with old datadir with
3 # Table t1 and t2
4 # having c1, c2, c3 normal columns
5 # +------+------+------+
6 # | c1 | c2 | c3 |
7 # +------+------+------+
8 # | r1c1 | r1c2 | r1c3 |
9 # +------+------+------+
10 #
11 # Table t3
12 # having c1, c2 normal columns
13 # having c3 INSTANT ADD column with NULL default value
14 # having c4 INSTANT ADD column with default value
15 # +------+------+------+--------+
16 # | c1 | c2 | c3 | c4 |
17 # +------+------+------+--------+
18 # | r1c1 | r1c2 | NULL | c4_def |
19 # +------+------+------+--------+
20 # ------------------------------------------------------------
21 # Stop the running the server
22 # Copy and unzip the datadir.
23 # Restart the server against the unzipped datadir
24 # restart: --datadir=DATADIR --innodb-page-size=16k
25 # ------------------------------------------------------------
26 # TEST 1 : Old version tables have no INSTANT ADD Columns
27 # ------------------------------------------------------------
28 # ------------------------------------------------------------
29 # Check metadat of upgraded table t1 [c1, c2, c3]
30 # Expectation : New INSTANT METADATA is not present.
31 # ------------------------------------------------------------
32 # Metadata from INFORMATION_SCHEMA.TABLES
33 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
34 test/t1 6 0 0
35 # Metadata from INFORMATION_SCHEMA.COLUMNS
36 NAME POS MTYPE PRTYPE HAS_DEFAULT
37 c1 0 13 16711934 0
38 c2 1 13 16711934 0
39 c3 2 13 16711934 0
40 # ------------------------------------------------------------
41 # INSTANT DROP a column and check metadata t1 [c1, c3, -c2]
42 # Expectation : New INSTANT METADATA is populated.
43 # ------------------------------------------------------------
44 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
45 # Metadata from INFORMATION_SCHEMA.TABLES
46 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
47 test/t1 5 0 1
48 # Metadata from INFORMATION_SCHEMA.COLUMNS
49 NAME POS MTYPE PRTYPE HAS_DEFAULT
50 c1 0 13 16711934 0
51 c3 1 13 16711934 0
52 # ------------------------------------------------------------
53 # Check metadat of upgraded table t2 [c1, c2, c3]
54 # Expectation : New INSTANT METADATA is not present.
55 # ------------------------------------------------------------
56 # Metadata from INFORMATION_SCHEMA.TABLES
57 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
58 test/t2 6 0 0
59 # Metadata from INFORMATION_SCHEMA.COLUMNS
60 NAME POS MTYPE PRTYPE HAS_DEFAULT
61 c1 0 13 16711934 0
62 c2 1 13 16711934 0
63 c3 2 13 16711934 0
64 # ------------------------------------------------------------
65 # INSTANT ADD a column and check metadata t2 [c1, c2, c3, +c4]
66 # Expectation : New INSTANT METADATA is populated.
67 # ------------------------------------------------------------
68 ALTER TABLE t2 ADD COLUMN c4 CHAR(10) DEFAULT "c4_def", ALGORITHM=INSTANT;
69 # Metadata from INFORMATION_SCHEMA.TABLES
70 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
71 test/t2 7 0 1
72 # Metadata from INFORMATION_SCHEMA.COLUMNS
73 NAME POS MTYPE PRTYPE HAS_DEFAULT
74 c1 0 13 16711934 0
75 c2 1 13 16711934 0
76 c3 2 13 16711934 0
77 c4 3 13 16711934 1
78 # ------------------------------------------------------------
79 # INSTANT DROP a column and check metadata t2 [c1, c2, -c3, c4]
80 # Expectation : New INSTANT METADATA is updated.
81 # ------------------------------------------------------------
82 ALTER TABLE t2 DROP COLUMN c3, ALGORITHM=INSTANT;
83 # Metadata from INFORMATION_SCHEMA.TABLES
84 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
85 test/t2 6 0 2
86 # Metadata from INFORMATION_SCHEMA.COLUMNS
87 NAME POS MTYPE PRTYPE HAS_DEFAULT
88 c1 0 13 16711934 0
89 c2 1 13 16711934 0
90 c4 2 13 16711934 1
91 DROP TABLE t1;
92 DROP TABLE t2;
93 # ------------------------------------------------------------
94 # TEST 2 : Old version tables have INSTANT ADD Columns
95 # ------------------------------------------------------------
96 # ------------------------------------------------------------
97 # Check metadat of upgraded table t3 [c1, c2, c3, c4]
98 # Expectation : Old INSTANT METADATA is present
99 # New INSTANT METADATA is not present.
100 # ------------------------------------------------------------
101 # Metadata from INFORMATION_SCHEMA.TABLES
102 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
103 test/t3 7 2 0
104 # Metadata from INFORMATION_SCHEMA.COLUMNS
105 NAME POS MTYPE PRTYPE HAS_DEFAULT
106 c1 0 13 16711934 0
107 c2 1 13 16711934 0
108 c3 2 13 16711934 1
109 c4 3 13 16711934 1
110 # --------------------------------------------------------------------
111 # INSTANT DROP a normal column and check metadata t3 [c1, -c2, c3, c4]
112 # Expectation : Old INSTANT METADATA stays same.
113 # New INSTANT METADATA is populated.
114 # ---------------------------------------------------------------------
115 ALTER TABLE t3 DROP COLUMN c2, ALGORITHM=INSTANT;
116 # Metadata from INFORMATION_SCHEMA.TABLES
117 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
118 test/t3 6 2 1
119 # Metadata from INFORMATION_SCHEMA.COLUMNS
120 NAME POS MTYPE PRTYPE HAS_DEFAULT
121 c1 0 13 16711934 0
122 c3 1 13 16711934 1
123 c4 2 13 16711934 1
124 # -----------------------------------------------------------------------
125 # INSTANT DROP an INSTANT column and check metadata t3 [c1, ~c2, -c3, c4]
126 # Expectation : Old INSTANT METADATA stays same.
127 # New INSTANT METADATA is updated.
128 # -----------------------------------------------------------------------
129 ALTER TABLE t3 DROP COLUMN c3, ALGORITHM=INSTANT;
130 # Metadata from INFORMATION_SCHEMA.TABLES
131 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
132 test/t3 5 2 2
133 # Metadata from INFORMATION_SCHEMA.COLUMNS
134 NAME POS MTYPE PRTYPE HAS_DEFAULT
135 c1 0 13 16711934 0
136 c4 1 13 16711934 1
137 # -----------------------------------------------------------------------
138 # INSTANT ADD a new column and check metadata t3 [c1, ~c2, ~c3, c4, +c5]
139 # Expectation : Old INSTANT METADATA stays same.
140 # New INSTANT METADATA is updated.
141 # -----------------------------------------------------------------------
142 ALTER TABLE t3 ADD COLUMN c5 CHAR(10) DEFAULT "c5_def", ALGORITHM=INSTANT;
143 # Metadata from INFORMATION_SCHEMA.TABLES
144 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
145 test/t3 6 2 3
146 # Metadata from INFORMATION_SCHEMA.COLUMNS
147 NAME POS MTYPE PRTYPE HAS_DEFAULT
148 c1 0 13 16711934 0
149 c4 1 13 16711934 1
150 c5 2 13 16711934 1
151 # -----------------------------------------------------------------------------
152 # INSTANT DROP newly added column and check metadata t3 [c1, ~c2, ~c3, c4, -c5]
153 # Expectation : Old INSTANT METADATA stays same.
154 # New INSTANT METADATA is updated.
155 # -----------------------------------------------------------------------------
156 ALTER TABLE t3 DROP COLUMN c5, ALGORITHM=INSTANT;
157 # Metadata from INFORMATION_SCHEMA.TABLES
158 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
159 test/t3 5 2 4
160 # Metadata from INFORMATION_SCHEMA.COLUMNS
161 NAME POS MTYPE PRTYPE HAS_DEFAULT
162 c1 0 13 16711934 0
163 c4 1 13 16711934 1
164 DROP TABLE t3;
165 # -------------------------------------------------
166 # TEST 3 : New tables with INSTANT ADD/DROP Columns
167 # -------------------------------------------------
168 CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10));
169 # ------------------------------------------------------------
170 # Check metadat of table t1 [c1, c2, c3]
171 # Expectation : Old INSTANT METADATA is not present
172 # New INSTANT METADATA is not present.
173 # ------------------------------------------------------------
174 # Metadata from INFORMATION_SCHEMA.TABLES
175 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
176 test/t1 6 0 0
177 # Metadata from INFORMATION_SCHEMA.COLUMNS
178 NAME POS MTYPE PRTYPE HAS_DEFAULT
179 c1 0 13 16711934 0
180 c2 1 13 16711934 0
181 c3 2 13 16711934 0
182 # -----------------------------------------------------------------------
183 # INSTANT DROP a column and check metadata t1 [c1, -c2, c3]
184 # Expectation : Old INSTANT METADATA is not present.
185 # New INSTANT METADATA is populated.
186 # -----------------------------------------------------------------------
187 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
188 # Metadata from INFORMATION_SCHEMA.TABLES
189 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
190 test/t1 5 0 1
191 # Metadata from INFORMATION_SCHEMA.COLUMNS
192 NAME POS MTYPE PRTYPE HAS_DEFAULT
193 c1 0 13 16711934 0
194 c3 1 13 16711934 0
195 # -----------------------------------------------------------------------
196 # INSTANT ADD a column and check metadata t1 [+c0, c1, ~c2, c3]
197 # Expectation : Old INSTANT METADATA not present.
198 # New INSTANT METADATA is updated.
199 # -----------------------------------------------------------------------
200 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "c0_def" FIRST, ALGORITHM=INSTANT;
201 # Metadata from INFORMATION_SCHEMA.TABLES
202 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
203 test/t1 6 0 2
204 # Metadata from INFORMATION_SCHEMA.COLUMNS
205 NAME POS MTYPE PRTYPE HAS_DEFAULT
206 c0 0 13 16711934 1
207 c1 1 13 16711934 0
208 c3 2 13 16711934 0
209 # ----------------------------------------------------------------------------
210 # INSTANT DROP the INSTANT ADD column and check metadata t1 [-c0, c1, ~c2, c3]
211 # Expectation : Old INSTANT METADATA is not present.
212 # New INSTANT METADATA is updated.
213 # ----------------------------------------------------------------------------
214 ALTER TABLE t1 DROP COLUMN c0, ALGORITHM=INSTANT;
215 # Metadata from INFORMATION_SCHEMA.TABLES
216 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
217 test/t1 5 0 3
218 # Metadata from INFORMATION_SCHEMA.COLUMNS
219 NAME POS MTYPE PRTYPE HAS_DEFAULT
220 c1 0 13 16711934 0
221 c3 1 13 16711934 0
222 DROP TABLE t1;
223 ###########
224 # CLEANUP #
225 ###########
226 # Shutdown server
227 # Remove copied files
228 # Restarting server to restore server state
229 # restart