Debian Codegraph @

mysql-test/suite/innodb/t/instant_ddl_upgrade_part_metadata.test

raw
1 ##########################################################################
2 # Test script to test DD Metadata after UPGRADE from older version
3 # partition table (not) having INSTANT ADD columns.
4 ##########################################################################
5
6 --source include/not_valgrind.inc
7 --source include/have_lowercase0.inc
8
9 # ------------------------------------------------------------
10 # Tables created on earlier version with following
11 # ------------------------------------------------------------
12 # CREATE TABLE t1_part (c1 INT, c2 CHAR(10))
13 # PARTITION BY RANGE(c1) (
14 # PARTITION p0 VALUES LESS THAN (100),
15 # PARTITION p1 VALUES LESS THAN (200));
16 # INSERT INTO t1_part VALUES(10, "r1c2");
17 #
18 # CREATE TABLE t2_part (c1 INT, c2 CHAR(10))
19 # PARTITION BY RANGE(c1) (
20 # PARTITION p0 VALUES LESS THAN (100),
21 # PARTITION p1 VALUES LESS THAN (200));
22 # INSERT INTO t2_part VALUES(10, "r1c2");
23 #
24 # CREATE TABLE t3_part (c1 INT, c2 CHAR(10))
25 # PARTITION BY RANGE(c1) (
26 # PARTITION p0 VALUES LESS THAN (100),
27 # PARTITION p1 VALUES LESS THAN (200));
28 # INSERT INTO t3_part VALUES(10, "r1c2");
29 # ALTER TABLE t3_part ADD COLUMN c3 CHAR(10) DEFAULT "def_c3", ALGORITHM=INSTANT;
30 # INSERT INTO t3_part VALUES(110, "r1c2", "c3");
31 # ALTER TABLE t3_part ADD PARTITION (PARTITION p2 VALUES LESS THAN (300));
32 # ALTER TABLE t3_part ADD COLUMN c4 CHAR(10) DEFAULT "def_c4", ALGORITHM=INSTANT;
33 # ------------------------------------------------------------
34
35 --echo # ------------------------------------------------------------
36 --echo # Start server with old datadir with
37 --echo # Table t1_part and t2_part
38 --echo # having 2 partitions p0 and p1
39 --echo # having c1, c2 normal columns
40 --echo # +------+------+
41 --echo # | c1 | c2 |
42 --echo # +------+------+
43 --echo # | 10 | r1c2 |
44 --echo # +------+------+
45 --echo #
46 --echo # Table t3_part (partition p0, p1)
47 --echo # having c1, c2 normal columns
48 --echo # having c3 INSTANT ADD column with default value
49 --echo # partition p2 added.
50 --echo # having c4 INSTANT ADD column with default value
51 --echo # +------+------+--------+--------+
52 --echo # | c1 | c2 | c3 | c4 |
53 --echo # +------+------+--------+--------+
54 --echo # | 10 | r1c2 | def_c3 | def_c4 |
55 --echo # +------+------+--------+--------+
56 --echo # ------------------------------------------------------------
57
58 --echo # Stop the running the server
59 --source include/shutdown_mysqld.inc
60
61 --echo # Copy and unzip the datadir.
62 --copy_file $MYSQLTEST_VARDIR/std_data/instant_ddl/data_instant_ddl_upgrade.zip $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip
63 --file_exists $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip
64 --exec unzip -qo $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip -d $MYSQL_TMP_DIR
65 let $DATADIR = $MYSQL_TMP_DIR/data;
66
67 --echo # Restart the server against the unzipped datadir
68 --replace_result $DATADIR DATADIR
69 --let $restart_parameters = restart: --datadir=$DATADIR --innodb-page-size=16k
70 --let $wait_counter=3000
71 --source include/start_mysqld.inc
72
73 --echo # ------------------------------------------------------------
74 --echo # TEST 1 : Old version tables have no INSTANT ADD Columns
75 --echo # ------------------------------------------------------------
76
77 --echo # ------------------------------------------------------------
78 --echo # Check metadata of upgraded table t1_part [c1, c2]
79 --echo # Expectation : New INSTANT METADATA is not present.
80 --echo # ------------------------------------------------------------
81 --let $table_name=t1_part
82 --source suite/innodb/include/print_instant_part_metadata.inc
83
84 --echo # ------------------------------------------------------------
85 --echo # INSTANT DROP a column and check metadata t1_part [c1, -c2]
86 --echo # Expectation : New INSTANT METADATA is populated.
87 --echo # ------------------------------------------------------------
88 ALTER TABLE t1_part DROP COLUMN c2, ALGORITHM=INSTANT;
89 --let $table_name=t1_part
90 --source suite/innodb/include/print_instant_part_metadata.inc
91
92 --echo # ------------------------------------------------------------
93 --echo # Check metadata of upgraded table t2_part [c1, c2]
94 --echo # Expectation : New INSTANT METADATA is not present.
95 --echo # ------------------------------------------------------------
96 --let $table_name=t2_part
97 --source suite/innodb/include/print_instant_part_metadata.inc
98
99 --echo # ------------------------------------------------------------
100 --echo # INSTANT ADD a column and check metadata t2_part [c1, c2, +c3]
101 --echo # Expectation : New INSTANT METADATA is populated.
102 --echo # ------------------------------------------------------------
103 ALTER TABLE t2_part ADD COLUMN c3 CHAR(10) DEFAULT "c3_def", ALGORITHM=INSTANT;
104 --let $table_name=t2_part
105 --source suite/innodb/include/print_instant_part_metadata.inc
106
107 --echo # ------------------------------------------------------------
108 --echo # INSTANT DROP a column and check metadata t2_part [c1, -c2, c3]
109 --echo # Expectation : New INSTANT METADATA is updated.
110 --echo # ------------------------------------------------------------
111 ALTER TABLE t2_part DROP COLUMN c2, ALGORITHM=INSTANT;
112 --let $table_name=t2_part
113 --source suite/innodb/include/print_instant_part_metadata.inc
114
115 DROP TABLE t1_part;
116 DROP TABLE t2_part;
117
118 --echo # ------------------------------------------------------------
119 --echo # TEST 2 : Old version tables have INSTANT ADD Columns
120 --echo # ------------------------------------------------------------
121
122 --echo # ------------------------------------------------------------
123 --echo # Check metadata of upgraded table t3_part [c1, c2, c3, c4]
124 --echo # Expectation : Old INSTANT METADATA is present and has
125 --echo # different INSTANT_COLS for p2.
126 --echo # No default value for c3 for p2.
127 --echo # New INSTANT METADATA is not present.
128 --echo # ------------------------------------------------------------
129 --let $table_name=t3_part
130 --source suite/innodb/include/print_instant_part_metadata.inc
131
132 --echo # -----------------------------------------------------------------------
133 --echo # INSTANT ADD a new column and check metadata t3_part [c1, c2, c3, c4, +c5]
134 --echo # Expectation : Old INSTANT METADATA stays same.
135 --echo # New INSTANT METADATA is populated.
136 --echo # Default value for c5 in each partition.
137 --echo # -----------------------------------------------------------------------
138 ALTER TABLE t3_part ADD COLUMN c5 CHAR(10) DEFAULT "c5_def", ALGORITHM=INSTANT;
139 --let $table_name=t3_part
140 --source suite/innodb/include/print_instant_part_metadata.inc
141
142 --echo # --------------------------------------------------------------------
143 --echo # Add a new partition p3.
144 --echo # Expectation : Old INSTANT METADATA stays same.
145 --echo # Default value for c5 in each partition.
146 --echo # -----------------------------------------------------------------------
147 ALTER TABLE t3_part ADD PARTITION (PARTITION p3 VALUES LESS THAN (400));
148 --let $table_name=t3_part
149 --source suite/innodb/include/print_instant_part_metadata.inc
150
151 --echo # --------------------------------------------------------------------
152 --echo # INSTANT DROP a normal column and check metadata t3_part [c1, -c2, c3, c4, c5]
153 --echo # Expectation : Old INSTANT METADATA stays same.
154 --echo # New INSTANT METADATA is updated.
155 --echo # ---------------------------------------------------------------------
156 ALTER TABLE t3_part DROP COLUMN c2, ALGORITHM=INSTANT;
157 --let $table_name=t3_part
158 --source suite/innodb/include/print_instant_part_metadata.inc
159
160 --echo # -----------------------------------------------------------------------
161 --echo # INSTANT DROP an INSTANT column and check metadata t3_part [c1, ~c2, ~c3, c4, c5]
162 --echo # Expectation : Old INSTANT METADATA stays same.
163 --echo # New INSTANT METADATA is updated.
164 --echo # -----------------------------------------------------------------------
165 ALTER TABLE t3_part DROP COLUMN c3, ALGORITHM=INSTANT;
166 --let $table_name=t3_part
167 --source suite/innodb/include/print_instant_part_metadata.inc
168
169 --echo # -----------------------------------------------------------------------------
170 --echo # INSTANT DROP newly added column and check metadata t3_part [c1, ~c2, ~c3, c4, -c5]
171 --echo # Expectation : Old INSTANT METADATA stays same.
172 --echo # New INSTANT METADATA is updated.
173 --echo # -----------------------------------------------------------------------------
174 ALTER TABLE t3_part DROP COLUMN c5, ALGORITHM=INSTANT;
175 --let $table_name=t3_part
176 --source suite/innodb/include/print_instant_part_metadata.inc
177
178 DROP TABLE t3_part;
179
180 --echo # -------------------------------------------------
181 --echo # TEST 3 : New tables with INSTANT ADD/DROP Columns
182 --echo # -------------------------------------------------
183 CREATE TABLE t1_part (c1 INT, c2 CHAR(10)) PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200));
184 INSERT INTO t1_part VALUES(10, "r1c2");
185
186 --echo # ------------------------------------------------------------
187 --echo # Check metadata of table t1_part [c1, c2]
188 --echo # Expectation : Old INSTANT METADATA is not present
189 --echo # New INSTANT METADATA is not present.
190 --echo # ------------------------------------------------------------
191 --let $table_name=t1_part
192 --source suite/innodb/include/print_instant_part_metadata.inc
193
194 --echo # -----------------------------------------------------------------------
195 --echo # INSTANT DROP a column and check metadata t1_part [c1, -c2]
196 --echo # Expectation : Old INSTANT METADATA is not present.
197 --echo # New INSTANT METADATA is populated.
198 --echo # -----------------------------------------------------------------------
199 ALTER TABLE t1_part DROP COLUMN c2, ALGORITHM=INSTANT;
200 --let $table_name=t1_part
201 --source suite/innodb/include/print_instant_part_metadata.inc
202
203 --echo # -----------------------------------------------------------------------
204 --echo # INSTANT ADD a column and check metadata t1_part [c1, ~c2, +c3]
205 --echo # Expectation : Old INSTANT METADATA not present.
206 --echo # New INSTANT METADATA is updated.
207 --echo # -----------------------------------------------------------------------
208 ALTER TABLE t1_part ADD COLUMN c3 CHAR(10) DEFAULT "c3_def", ALGORITHM=INSTANT;
209 --let $table_name=t1_part
210 --source suite/innodb/include/print_instant_part_metadata.inc
211
212 --echo # --------------------------------------------------------------------
213 --echo # Add a new partition p2.
214 --echo # Expectation : Old INSTANT METADATA not present.
215 --echo # New INSTANT METADATA is updated.
216 --echo # Default value for c3 in each partition.
217 --echo # -----------------------------------------------------------------------
218 ALTER TABLE t1_part ADD PARTITION (PARTITION p2 VALUES LESS THAN (300));
219 --let $table_name=t1_part
220 --source suite/innodb/include/print_instant_part_metadata.inc
221
222 --echo # ----------------------------------------------------------------------------
223 --echo # INSTANT DROP the INSTANT ADD column and check metadata t1_part [c1, ~c2, -c3]
224 --echo # Expectation : Old INSTANT METADATA is not present.
225 --echo # New INSTANT METADATA is updated.
226 --echo # ----------------------------------------------------------------------------
227 ALTER TABLE t1_part DROP COLUMN c3, ALGORITHM=INSTANT;
228 --let $table_name=t1_part
229 --source suite/innodb/include/print_instant_part_metadata.inc
230
231 DROP TABLE t1_part;
232
233 --echo ###########
234 --echo # CLEANUP #
235 --echo ###########
236
237 --echo # Shutdown server
238 --source include/shutdown_mysqld.inc
239
240 --echo # Remove copied files
241 --file_exists $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip
242 --force-rmdir $MYSQL_TMP_DIR/data
243 --remove_file $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip
244
245 --echo # Restarting server to restore server state
246 let $restart_parameters =;
247 --source include/start_mysqld.inc