Debian Codegraph @

mysql-test/suite/innodb/include/instant_ddl_import_partition.inc

raw
1 ##########################################################################
2 # Test script to test EXPORT/IMPORT for partitioned table having INSTANT
3 # ADD/DROP columns.
4 #
5 # $row_format is to be set to the row_format on which test is to be run.
6 ##########################################################################
7
8 --write_file $MYSQLTEST_VARDIR/tmp/export.inc EXECUTE_STEP
9 FLUSH TABLE t1 FOR EXPORT;
10 perl;
11 require 'include/innodb-util.inc';
12 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2", "t1#p#p3");
13 EOF
14 UNLOCK TABLES;
15 EXECUTE_STEP
16
17 --write_file $MYSQLTEST_VARDIR/tmp/import.inc EXECUTE_STEP
18 ALTER TABLE t1 discard tablespace;
19 perl;
20 require 'include/innodb-util.inc';
21 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2", "t1#p#p3");
22 EOF
23 ALTER TABLE t1 IMPORT TABLESPACE;
24 EXECUTE_STEP
25
26 --write_file $MYSQLTEST_VARDIR/tmp/cleanup.inc EXECUTE_STEP
27 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp/ t1*
28 EXECUTE_STEP
29
30 --echo # Create a table with 4 columns. [c1, c2, c3, c4]
31 --eval CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=$row_format PARTITION BY RANGE(c1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE)
32
33 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
34 SELECT * from t1;
35
36 --echo # Add a new column at the end. [c1, c2, c3, c4, +c5]
37 ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT;
38 INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ;
39 SELECT * FROM t1 ORDER BY c1;
40
41 --echo # Add a new column in between. [c1, +c6, c2, c3, c4, c5]
42 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
43 INSERT INTO t1 VALUES(400, "r5c6", 500, 600, "xxxxxx", "r5c5") ;
44 SELECT * FROM t1 ORDER BY c1;
45
46 --let $table_name=t1
47 --source suite/innodb/include/print_instant_part_metadata.inc
48
49 let MYSQLD_DATADIR = `SELECT @@datadir`;
50
51 --echo # ------------------------------------------------------------
52 --echo # Scenario 1.1 : Same table with tablespace discarded
53 --echo # ------------------------------------------------------------
54 --echo # EXPORT
55 --source $MYSQLTEST_VARDIR/tmp/export.inc
56
57 --echo # IMPORT
58 --source $MYSQLTEST_VARDIR/tmp/import.inc
59
60 SELECT * FROM t1 ORDER BY c1;
61
62 --let $table_name=t1
63 --source suite/innodb/include/print_instant_part_metadata.inc
64
65 --source $MYSQLTEST_VARDIR/tmp/cleanup.inc
66
67 --echo # ------------------------------------------------------------
68 --echo # Scenario 1.2 : New table created with like
69 --echo # ------------------------------------------------------------
70 --echo # EXPORT
71 --source $MYSQLTEST_VARDIR/tmp/export.inc
72
73 CREATE TABLE t2 like t1;
74 SHOW CREATE TABLE t2;
75 DROP TABLE t1;
76 ALTER TABLE t2 RENAME as t1;
77
78 --echo # IMPORT
79 --source $MYSQLTEST_VARDIR/tmp/import.inc
80
81 SELECT * FROM t1 ORDER BY c1;
82
83 --let $table_name=t1
84 --source suite/innodb/include/print_instant_part_metadata.inc
85
86 --source $MYSQLTEST_VARDIR/tmp/cleanup.inc
87
88 --echo # ------------------------------------------------------------
89 --echo # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5]
90 --echo # ------------------------------------------------------------
91 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
92
93 --let $table_name=t1
94 --source suite/innodb/include/print_instant_part_metadata.inc
95
96 SELECT * FROM t1 ORDER BY c1;
97 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ;
98 SELECT * FROM t1 ORDER BY c1;
99
100 --echo # ------------------------------------------------------------
101 --echo # Scenario 2.1 : Same table with tablespace discarded
102 --echo # ------------------------------------------------------------
103 --echo # EXPORT
104 --source $MYSQLTEST_VARDIR/tmp/export.inc
105
106 --echo # IMPORT
107 --source $MYSQLTEST_VARDIR/tmp/import.inc
108
109 SELECT * FROM t1 ORDER BY c1;
110
111 --let $table_name=t1
112 --source suite/innodb/include/print_instant_part_metadata.inc
113
114 --source $MYSQLTEST_VARDIR/tmp/cleanup.inc
115
116 --echo # ------------------------------------------------------------
117 --echo # Scenario 2.2 : New table created with like
118 --echo # ------------------------------------------------------------
119 --echo # EXPORT
120 --source $MYSQLTEST_VARDIR/tmp/export.inc
121
122 CREATE TABLE t2 like t1;
123 SHOW CREATE TABLE t2;
124 DROP TABLE t1;
125 ALTER TABLE t2 RENAME as t1;
126
127 --echo # IMPORT
128 --source $MYSQLTEST_VARDIR/tmp/import.inc
129 SELECT * FROM t1 ORDER BY c1;
130
131 --let $table_name=t1
132 --source suite/innodb/include/print_instant_part_metadata.inc
133
134 --source $MYSQLTEST_VARDIR/tmp/cleanup.inc
135 DROP TABLE t1;
136
137 --echo # ------------------------------------------------------------
138 --echo # A Partitioned table having multiple INSTANT ADD/DROP columns
139 --echo # and REORGNIZED partitions.
140 --echo # ------------------------------------------------------------
141 --eval CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50), job VARCHAR(50), dept INT, phoneno INT) ROW_FORMAT=$row_format PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (10), PARTITION P1 VALUES LESS THAN (20), PARTITION P2 VALUES LESS THAN (30), PARTITION P3 VALUES LESS THAN (40), PARTITION P4 VALUES LESS THAN (50))
142
143 INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111);
144 INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222);
145 INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333);
146 INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444);
147
148 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
149 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
150 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
151 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
152
153 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
154 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
155 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
156 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
157
158 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
159 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
160 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
161 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
162
163 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
164 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
165 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
166 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
167
168 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
169 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
170 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
171 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
172
173 ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50));
174
175 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
176 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
177
178 SELECT * FROM t1 ORDER BY id;
179
180 --echo # ------------------------------------------------------------
181 --echo # Scenario 3.1 : Same table with tablespace discarded
182 --echo # ------------------------------------------------------------
183 --echo # EXPORT
184 --source $MYSQLTEST_VARDIR/tmp/export.inc
185
186 --echo # IMPORT
187 --source $MYSQLTEST_VARDIR/tmp/import.inc
188
189 SELECT * FROM t1 ORDER BY id;
190
191 --let $table_name=t1
192 --source suite/innodb/include/print_instant_part_metadata.inc
193
194 --source $MYSQLTEST_VARDIR/tmp/cleanup.inc
195
196 --echo # ------------------------------------------------------------
197 --echo # Scenario 3.2 : New table created with like
198 --echo # ------------------------------------------------------------
199 --echo # EXPORT
200 --source $MYSQLTEST_VARDIR/tmp/export.inc
201
202 CREATE TABLE t2 like t1;
203 SHOW CREATE TABLE t2;
204 DROP TABLE t1;
205 ALTER TABLE t2 RENAME as t1;
206
207 --echo # IMPORT
208 --source $MYSQLTEST_VARDIR/tmp/import.inc
209 SELECT * FROM t1 ORDER BY id;
210
211 --let $table_name=t1
212 --source suite/innodb/include/print_instant_part_metadata.inc
213
214 --source $MYSQLTEST_VARDIR/tmp/cleanup.inc
215
216 DROP TABLE t1;
217
218 --echo ###########
219 --echo # CLEANUP #
220 --echo ###########
221 --remove_file $MYSQLTEST_VARDIR/tmp/import.inc
222 --remove_file $MYSQLTEST_VARDIR/tmp/export.inc
223 --remove_file $MYSQLTEST_VARDIR/tmp/cleanup.inc