Debian Codegraph @

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

raw
1 #############################################################################
2 # Test script to test EXPORT/IMPORT for a partitioned table in older version
3 # having INSTANT ADD columns.
4 #
5 # CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=COMPACT
6 # PARTITION BY RANGE(c1 * 2)
7 # (PARTITION p0 VALUES LESS THAN (128),
8 # PARTITION p1 VALUES LESS THAN (256),
9 # PARTITION p2 VALUES LESS THAN (384),
10 # PARTITION p3 VALUES LESS THAN MAXVALUE);
11 # INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
12 # ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT;
13 # INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ;
14 #############################################################################
15
16 --source include/have_innodb_16k.inc
17
18 --write_file $MYSQLTEST_VARDIR/tmp/import.inc EOF
19 ALTER TABLE t1 discard tablespace;
20 --copy_files_wildcard $MYSQL_TMP_DIR/$src_dir/ $MYSQLD_DATADIR/test/ t1*.cfg
21 --copy_files_wildcard $MYSQL_TMP_DIR/$src_dir/ $MYSQLD_DATADIR/test/ t1*.ibd
22 --error $error_num
23 ALTER TABLE t1 IMPORT TABLESPACE;
24 EOF
25
26 let MYSQLD_DATADIR = `SELECT @@datadir`;
27 let $DATADIR = `SELECT @@datadir`;
28
29 --echo # ------------------------------------------------------------
30 --echo # Exported table t1 (in older version) having
31 --echo # c1 INT, c2 INT, c3 INT, c4 TEXT normal columns
32 --echo # c5 char(20) INSTANT ADD column with default def_c5.
33 --echo # +------+------+------+---------+--------+
34 --echo # | c1 | c2 | c3 | c4 | c5 |
35 --echo # +------+------+------+---------+--------+
36 --echo # | 1 | 2 | 3 | abcdefg | c5_def |
37 --echo # | 100 | 200 | 300 | qwerty | c5_def |
38 --echo # | 200 | 300 | 400 | asdfg | c5_def |
39 --echo # | 300 | 400 | 500 | xxxxxx | r4c5 |
40 --echo # +------+------+------+---------+--------+
41 --echo # ------------------------------------------------------------
42
43 --echo # Copy and unzip the ibd/cfg files.
44 --copy_file $MYSQLTEST_VARDIR/std_data/instant_ddl/old_instant_part_comp.zip $MYSQL_TMP_DIR/old_instant_part_comp.zip
45 --file_exists $MYSQL_TMP_DIR/old_instant_part_comp.zip
46 --exec unzip -qo $MYSQL_TMP_DIR/old_instant_part_comp.zip -d $MYSQL_TMP_DIR
47 --echo # listing MYSQL_TMP_DIR/old_instant_part_comp
48 --list_files $MYSQL_TMP_DIR/old_instant_part_comp
49 --let $src_dir = old_instant_part_comp
50
51 --echo # ------------------------------------------------------------
52 --echo # Scenario 1 : No INSTANT ADD/DROP Columns in target table
53 --echo # ------------------------------------------------------------
54 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, c5 char(20)) row_format=compact
55 PARTITION BY RANGE(c1 * 2)
56 (PARTITION p0 VALUES LESS THAN (128),
57 PARTITION p1 VALUES LESS THAN (256),
58 PARTITION p2 VALUES LESS THAN (384),
59 PARTITION p3 VALUES LESS THAN MAXVALUE);
60
61 --let $table_name=t1
62 --source suite/innodb/include/print_instant_part_metadata.inc
63
64 --echo # IMPORT
65 --let $error_num=0
66 --source $MYSQLTEST_VARDIR/tmp/import.inc
67
68 SELECT * FROM t1 ORDER BY c1;
69
70 --let $table_name=t1
71 --source suite/innodb/include/print_instant_part_metadata.inc
72
73 --echo # Cleanup
74 DROP TABLE t1;
75
76 --echo # ------------------------------------------------------------
77 --echo # Scenario 2 : No INSTANT ADD/DROP. Column mismatch
78 --echo # ------------------------------------------------------------
79 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, c5 char(10)) row_format=compact
80 PARTITION BY RANGE(c1 * 2)
81 (PARTITION p0 VALUES LESS THAN (128),
82 PARTITION p1 VALUES LESS THAN (256),
83 PARTITION p2 VALUES LESS THAN (384),
84 PARTITION p3 VALUES LESS THAN MAXVALUE);
85
86 --let $table_name=t1
87 --source suite/innodb/include/print_instant_part_metadata.inc
88
89 --echo # IMPORT
90 --let $error_num=1808
91 --source $MYSQLTEST_VARDIR/tmp/import.inc
92
93 --echo # Cleanup
94 --remove_files_wildcard $DATADIR/test/ t1*
95 DROP TABLE t1;
96
97 --echo # ------------------------------------------------------------
98 --echo # Scenario 3 : INSTANT ADD/DROP Columns in target table
99 --echo # ------------------------------------------------------------
100 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=compact
101 PARTITION BY RANGE(c1 * 2)
102 (PARTITION p0 VALUES LESS THAN (128),
103 PARTITION p1 VALUES LESS THAN (256),
104 PARTITION p2 VALUES LESS THAN (384),
105 PARTITION p3 VALUES LESS THAN MAXVALUE);
106 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT "c5_def", ALGORITHM=INSTANT;
107
108 --let $table_name=t1
109 --source suite/innodb/include/print_instant_part_metadata.inc
110
111 --echo # IMPORT
112 --let $error_num=1808
113 --source $MYSQLTEST_VARDIR/tmp/import.inc
114
115 --echo # Cleanup
116 --remove_files_wildcard $DATADIR/test/ t1*
117 DROP TABLE t1;
118
119 --echo # ------------------------------------------------------------
120 --echo # Scenario 4 : INSTANT ADD/DROP. Column mismatch
121 --echo # ------------------------------------------------------------
122 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=compact
123 PARTITION BY RANGE(c1 * 2)
124 (PARTITION p0 VALUES LESS THAN (128),
125 PARTITION p1 VALUES LESS THAN (256),
126 PARTITION p2 VALUES LESS THAN (384),
127 PARTITION p3 VALUES LESS THAN MAXVALUE);
128 ALTER TABLE t1 ADD COLUMN c5 char(10) DEFAULT "c5_def", ALGORITHM=INSTANT;
129
130 --let $table_name=t1
131 --source suite/innodb/include/print_instant_part_metadata.inc
132
133 --echo # IMPORT
134 --let $error_num=1808
135 --source $MYSQLTEST_VARDIR/tmp/import.inc
136
137 --echo # Cleanup
138 --remove_files_wildcard $DATADIR/test/ t1*
139 DROP TABLE t1;
140
141 --echo # ------------------------------------------------------------
142 --echo # Scenario 5 : INSTANT ADD/DROP. Default value mismatch
143 --echo # ------------------------------------------------------------
144 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=compact
145 PARTITION BY RANGE(c1 * 2)
146 (PARTITION p0 VALUES LESS THAN (128),
147 PARTITION p1 VALUES LESS THAN (256),
148 PARTITION p2 VALUES LESS THAN (384),
149 PARTITION p3 VALUES LESS THAN MAXVALUE);
150 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT "def_c5", ALGORITHM=INSTANT;
151
152 --let $table_name=t1
153 --source suite/innodb/include/print_instant_part_metadata.inc
154
155 --echo # IMPORT
156 --let $error_num=1808
157 --source $MYSQLTEST_VARDIR/tmp/import.inc
158
159 --echo # Cleanup
160 DROP TABLE t1;
161 --remove_files_wildcard $DATADIR/test/ t1*
162 --force-rmdir $MYSQL_TMP_DIR/old_instant_part_comp
163 --remove_file $MYSQL_TMP_DIR/old_instant_part_comp.zip
164
165 --echo # --------------------------------------------------------------
166 --echo # Scenario 6 : Table with INSTANT ADD and reorganized partitions
167 --echo # --------------------------------------------------------------
168 # Following is the table definition EXPORTED from old INSTANT ADD
169 #
170 # CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT KEY, c1 char(10), c2 char(10))
171 # PARTITION BY RANGE(id) (
172 # PARTITION p0 VALUES LESS THAN (100),
173 # PARTITION p1 VALUES LESS THAN (200),
174 # PARTITION p2 VALUES LESS THAN MAXVALUE);
175 #
176 # Insert into t1(c1, c2) values ("r1c1", "r1c2");
177 # Alter table t1 add column c3 char (10) default "c3_def", algorithm=instant;
178 # Insert into t1 (c1,c2,c3) values ("r2c1", "r2c2", "r2c3");
179 # ALTER TABLE t1 reorganize partition p1 into (partition p1_1 values less than (150), partition p1_2 values less than(200));
180
181 --echo # Copy and unzip the ibd/cfg files.
182 --copy_file $MYSQLTEST_VARDIR/std_data/instant_ddl/old_instant_reorg_part_comp.zip $MYSQL_TMP_DIR/old_instant_reorg_part_comp.zip
183 --file_exists $MYSQL_TMP_DIR/old_instant_reorg_part_comp.zip
184 --exec unzip -qo $MYSQL_TMP_DIR/old_instant_reorg_part_comp.zip -d $MYSQL_TMP_DIR
185 --echo # listing MYSQL_TMP_DIR/old_instant_reorg_part_comp
186 --list_files $MYSQL_TMP_DIR/old_instant_reorg_part_comp
187 --let $src_dir = old_instant_reorg_part_comp
188
189 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT KEY, c1 char(10), c2 char(10), c3 char(10))
190 PARTITION BY RANGE(id) (
191 PARTITION p0 VALUES LESS THAN (100),
192 PARTITION p1_1 VALUES LESS THAN (150),
193 PARTITION p1_2 VALUES LESS THAN (200),
194 PARTITION p2 VALUES LESS THAN MAXVALUE);
195
196 --let $table_name=t1
197 --source suite/innodb/include/print_instant_part_metadata.inc
198
199 --echo # IMPORT
200 --let $error_num=0
201 --source $MYSQLTEST_VARDIR/tmp/import.inc
202
203 SELECT * FROM t1 ORDER BY c1;
204
205 --let $table_name=t1
206 --source suite/innodb/include/print_instant_part_metadata.inc
207
208 --echo # Cleanup
209 DROP TABLE t1;
210 --remove_files_wildcard $DATADIR/test/ t1*
211 --force-rmdir $MYSQL_TMP_DIR/old_instant_reorg_part_comp
212 --remove_file $MYSQL_TMP_DIR/old_instant_reorg_part_comp.zip
213
214 --echo ###########
215 --echo # CLEANUP #
216 --echo ###########
217 --remove_file $MYSQLTEST_VARDIR/tmp/import.inc