Debian Codegraph @

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

raw
1 ##########################################################################
2 # Test script to test DD Metadata after UPGRADE from older version table
3 # (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 (c1 char(10), c2 char(10), c3 char(10));
13 # Insert into t1 values ("r1c1", "r1c2", "r1c3");
14 # SELECT * from t1;
15 #
16 # Create table t2 (c1 char(10), c2 char(10), c3 char(10));
17 # Insert into t2 values ("r1c1", "r1c2", "r1c3");
18 # SELECT * from t2;
19 #
20 # Create table t3 (c1 char(10), c2 char(10));
21 # Insert into t3 values ("r1c1", "r1c2");
22 # SELECT * from t3;
23 # ALTER table t3 add column c3 char(10) DEFAULT NULL, algorithm=instant;
24 # SELECT * from t3;
25 # ALTER table t3 add column c4 char(10) DEFAULT "c4_def", algorithm=instant;
26 # SELECT * from t3;
27 # ------------------------------------------------------------
28
29 --echo # ------------------------------------------------------------
30 --echo # Start server with old datadir with
31 --echo # Table t1 and t2
32 --echo # having c1, c2, c3 normal columns
33 --echo # +------+------+------+
34 --echo # | c1 | c2 | c3 |
35 --echo # +------+------+------+
36 --echo # | r1c1 | r1c2 | r1c3 |
37 --echo # +------+------+------+
38 --echo #
39 --echo # Table t3
40 --echo # having c1, c2 normal columns
41 --echo # having c3 INSTANT ADD column with NULL default value
42 --echo # having c4 INSTANT ADD column with default value
43 --echo # +------+------+------+--------+
44 --echo # | c1 | c2 | c3 | c4 |
45 --echo # +------+------+------+--------+
46 --echo # | r1c1 | r1c2 | NULL | c4_def |
47 --echo # +------+------+------+--------+
48 --echo # ------------------------------------------------------------
49
50 --echo # Stop the running the server
51 --source include/shutdown_mysqld.inc
52
53 --echo # Copy and unzip the datadir.
54 --copy_file $MYSQLTEST_VARDIR/std_data/instant_ddl/data_instant_ddl_upgrade.zip $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip
55 --file_exists $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip
56 --exec unzip -qo $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip -d $MYSQL_TMP_DIR
57 let $DATADIR = $MYSQL_TMP_DIR/data;
58
59 --echo # Restart the server against the unzipped datadir
60 --replace_result $DATADIR DATADIR
61 --let $restart_parameters = restart: --datadir=$DATADIR --innodb-page-size=16k
62 --let $wait_counter=3000
63 --source include/start_mysqld.inc
64
65 --echo # ------------------------------------------------------------
66 --echo # TEST 1 : Old version tables have no INSTANT ADD Columns
67 --echo # ------------------------------------------------------------
68
69 --echo # ------------------------------------------------------------
70 --echo # Check metadat of upgraded table t1 [c1, c2, c3]
71 --echo # Expectation : New INSTANT METADATA is not present.
72 --echo # ------------------------------------------------------------
73 --let $table_name=t1
74 --source suite/innodb/include/print_instant_metadata.inc
75
76 --echo # ------------------------------------------------------------
77 --echo # INSTANT DROP a column and check metadata t1 [c1, c3, -c2]
78 --echo # Expectation : New INSTANT METADATA is populated.
79 --echo # ------------------------------------------------------------
80 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
81 --let $table_name=t1
82 --source suite/innodb/include/print_instant_metadata.inc
83
84 --echo # ------------------------------------------------------------
85 --echo # Check metadat of upgraded table t2 [c1, c2, c3]
86 --echo # Expectation : New INSTANT METADATA is not present.
87 --echo # ------------------------------------------------------------
88 --let $table_name=t2
89 --source suite/innodb/include/print_instant_metadata.inc
90
91 --echo # ------------------------------------------------------------
92 --echo # INSTANT ADD a column and check metadata t2 [c1, c2, c3, +c4]
93 --echo # Expectation : New INSTANT METADATA is populated.
94 --echo # ------------------------------------------------------------
95 ALTER TABLE t2 ADD COLUMN c4 CHAR(10) DEFAULT "c4_def", ALGORITHM=INSTANT;
96 --let $table_name=t2
97 --source suite/innodb/include/print_instant_metadata.inc
98
99 --echo # ------------------------------------------------------------
100 --echo # INSTANT DROP a column and check metadata t2 [c1, c2, -c3, c4]
101 --echo # Expectation : New INSTANT METADATA is updated.
102 --echo # ------------------------------------------------------------
103 ALTER TABLE t2 DROP COLUMN c3, ALGORITHM=INSTANT;
104 --let $table_name=t2
105 --source suite/innodb/include/print_instant_metadata.inc
106
107 DROP TABLE t1;
108 DROP TABLE t2;
109
110 --echo # ------------------------------------------------------------
111 --echo # TEST 2 : Old version tables have INSTANT ADD Columns
112 --echo # ------------------------------------------------------------
113
114 --echo # ------------------------------------------------------------
115 --echo # Check metadat of upgraded table t3 [c1, c2, c3, c4]
116 --echo # Expectation : Old INSTANT METADATA is present
117 --echo # New INSTANT METADATA is not present.
118 --echo # ------------------------------------------------------------
119 --let $table_name=t3
120 --source suite/innodb/include/print_instant_metadata.inc
121
122 --echo # --------------------------------------------------------------------
123 --echo # INSTANT DROP a normal column and check metadata t3 [c1, -c2, c3, c4]
124 --echo # Expectation : Old INSTANT METADATA stays same.
125 --echo # New INSTANT METADATA is populated.
126 --echo # ---------------------------------------------------------------------
127 ALTER TABLE t3 DROP COLUMN c2, ALGORITHM=INSTANT;
128 --let $table_name=t3
129 --source suite/innodb/include/print_instant_metadata.inc
130
131 --echo # -----------------------------------------------------------------------
132 --echo # INSTANT DROP an INSTANT column and check metadata t3 [c1, ~c2, -c3, c4]
133 --echo # Expectation : Old INSTANT METADATA stays same.
134 --echo # New INSTANT METADATA is updated.
135 --echo # -----------------------------------------------------------------------
136 ALTER TABLE t3 DROP COLUMN c3, ALGORITHM=INSTANT;
137 --let $table_name=t3
138 --source suite/innodb/include/print_instant_metadata.inc
139
140 --echo # -----------------------------------------------------------------------
141 --echo # INSTANT ADD a new column and check metadata t3 [c1, ~c2, ~c3, c4, +c5]
142 --echo # Expectation : Old INSTANT METADATA stays same.
143 --echo # New INSTANT METADATA is updated.
144 --echo # -----------------------------------------------------------------------
145 ALTER TABLE t3 ADD COLUMN c5 CHAR(10) DEFAULT "c5_def", ALGORITHM=INSTANT;
146 --let $table_name=t3
147 --source suite/innodb/include/print_instant_metadata.inc
148
149 --echo # -----------------------------------------------------------------------------
150 --echo # INSTANT DROP newly added column and check metadata t3 [c1, ~c2, ~c3, c4, -c5]
151 --echo # Expectation : Old INSTANT METADATA stays same.
152 --echo # New INSTANT METADATA is updated.
153 --echo # -----------------------------------------------------------------------------
154 ALTER TABLE t3 DROP COLUMN c5, ALGORITHM=INSTANT;
155 --let $table_name=t3
156 --source suite/innodb/include/print_instant_metadata.inc
157
158 DROP TABLE t3;
159
160 --echo # -------------------------------------------------
161 --echo # TEST 3 : New tables with INSTANT ADD/DROP Columns
162 --echo # -------------------------------------------------
163 CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10));
164
165 --echo # ------------------------------------------------------------
166 --echo # Check metadat of table t1 [c1, c2, c3]
167 --echo # Expectation : Old INSTANT METADATA is not present
168 --echo # New INSTANT METADATA is not present.
169 --echo # ------------------------------------------------------------
170 --let $table_name=t1
171 --source suite/innodb/include/print_instant_metadata.inc
172
173 --echo # -----------------------------------------------------------------------
174 --echo # INSTANT DROP a column and check metadata t1 [c1, -c2, c3]
175 --echo # Expectation : Old INSTANT METADATA is not present.
176 --echo # New INSTANT METADATA is populated.
177 --echo # -----------------------------------------------------------------------
178 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
179 --let $table_name=t1
180 --source suite/innodb/include/print_instant_metadata.inc
181
182 --echo # -----------------------------------------------------------------------
183 --echo # INSTANT ADD a column and check metadata t1 [+c0, c1, ~c2, c3]
184 --echo # Expectation : Old INSTANT METADATA not present.
185 --echo # New INSTANT METADATA is updated.
186 --echo # -----------------------------------------------------------------------
187 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "c0_def" FIRST, ALGORITHM=INSTANT;
188 --let $table_name=t1
189 --source suite/innodb/include/print_instant_metadata.inc
190
191 --echo # ----------------------------------------------------------------------------
192 --echo # INSTANT DROP the INSTANT ADD column and check metadata t1 [-c0, c1, ~c2, c3]
193 --echo # Expectation : Old INSTANT METADATA is not present.
194 --echo # New INSTANT METADATA is updated.
195 --echo # ----------------------------------------------------------------------------
196 ALTER TABLE t1 DROP COLUMN c0, ALGORITHM=INSTANT;
197 --let $table_name=t1
198 --source suite/innodb/include/print_instant_metadata.inc
199
200 DROP TABLE t1;
201
202 --echo ###########
203 --echo # CLEANUP #
204 --echo ###########
205
206 --echo # Shutdown server
207 --source include/shutdown_mysqld.inc
208
209 --echo # Remove copied files
210 --file_exists $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip
211 --force-rmdir $MYSQL_TMP_DIR/data
212 --remove_file $MYSQL_TMP_DIR/data_instant_ddl_upgrade.zip
213
214 --echo # Restarting server to restore server state
215 let $restart_parameters =;
216 --source include/start_mysqld.inc