| 1 |
# ------------------------------------------------------------ |
| 2 |
# Exported table t1 (in older version) having |
| 3 |
# c1 INT, c2 INT, c3 INT, c4 TEXT normal columns |
| 4 |
# c5 char(20) INSTANT ADD column with default def_c5. |
| 5 |
# +------+------+------+---------+--------+ |
| 6 |
# | c1 | c2 | c3 | c4 | c5 | |
| 7 |
# +------+------+------+---------+--------+ |
| 8 |
# | 1 | 2 | 3 | abcdefg | c5_def | |
| 9 |
# | 100 | 200 | 300 | qwerty | c5_def | |
| 10 |
# | 200 | 300 | 400 | asdfg | c5_def | |
| 11 |
# | 300 | 400 | 500 | xxxxxx | r4c5 | |
| 12 |
# +------+------+------+---------+--------+ |
| 13 |
# ------------------------------------------------------------ |
| 14 |
# Copy and unzip the ibd/cfg files. |
| 15 |
# listing MYSQL_TMP_DIR/old_instant_part_comp |
| 16 |
t1#p#p0.cfg |
| 17 |
t1#p#p0.ibd |
| 18 |
t1#p#p1.cfg |
| 19 |
t1#p#p1.ibd |
| 20 |
t1#p#p2.cfg |
| 21 |
t1#p#p2.ibd |
| 22 |
t1#p#p3.cfg |
| 23 |
t1#p#p3.ibd |
| 24 |
# ------------------------------------------------------------ |
| 25 |
# Scenario 1 : No INSTANT ADD/DROP Columns in target table |
| 26 |
# ------------------------------------------------------------ |
| 27 |
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, c5 char(20)) row_format=compact |
| 28 |
PARTITION BY RANGE(c1 * 2) |
| 29 |
(PARTITION p0 VALUES LESS THAN (128), |
| 30 |
PARTITION p1 VALUES LESS THAN (256), |
| 31 |
PARTITION p2 VALUES LESS THAN (384), |
| 32 |
PARTITION p3 VALUES LESS THAN MAXVALUE); |
| 33 |
# DD Metadata of columns in table |
| 34 |
NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA |
| 35 |
c1 1 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 36 |
c2 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 37 |
c3 3 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 38 |
c4 4 MYSQL_TYPE_BLOB 0 Visible table_id=TABLE_ID; |
| 39 |
c5 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; |
| 40 |
DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; |
| 41 |
DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; |
| 42 |
DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; |
| 43 |
# DD Metadata of partitions in table |
| 44 |
NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA |
| 45 |
p0 128 NULL |
| 46 |
p1 256 NULL |
| 47 |
p2 384 NULL |
| 48 |
p3 MAXVALUE NULL |
| 49 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 50 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 51 |
test/t1#p#p0 8 0 0 5 5 5 |
| 52 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 53 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 54 |
c1 0 6 1027 0 0 0 |
| 55 |
c2 1 6 1027 0 0 0 |
| 56 |
c3 2 6 1027 0 0 0 |
| 57 |
c4 3 5 16711932 0 0 0 |
| 58 |
c5 4 13 16711934 0 0 0 |
| 59 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 60 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 61 |
test/t1#p#p1 8 0 0 5 5 5 |
| 62 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 63 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 64 |
c1 0 6 1027 0 0 0 |
| 65 |
c2 1 6 1027 0 0 0 |
| 66 |
c3 2 6 1027 0 0 0 |
| 67 |
c4 3 5 16711932 0 0 0 |
| 68 |
c5 4 13 16711934 0 0 0 |
| 69 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 70 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 71 |
test/t1#p#p2 8 0 0 5 5 5 |
| 72 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 73 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 74 |
c1 0 6 1027 0 0 0 |
| 75 |
c2 1 6 1027 0 0 0 |
| 76 |
c3 2 6 1027 0 0 0 |
| 77 |
c4 3 5 16711932 0 0 0 |
| 78 |
c5 4 13 16711934 0 0 0 |
| 79 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 80 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 81 |
test/t1#p#p3 8 0 0 5 5 5 |
| 82 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 83 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 84 |
c1 0 6 1027 0 0 0 |
| 85 |
c2 1 6 1027 0 0 0 |
| 86 |
c3 2 6 1027 0 0 0 |
| 87 |
c4 3 5 16711932 0 0 0 |
| 88 |
c5 4 13 16711934 0 0 0 |
| 89 |
# IMPORT |
| 90 |
ALTER TABLE t1 discard tablespace; |
| 91 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 92 |
SELECT * FROM t1 ORDER BY c1; |
| 93 |
c1 c2 c3 c4 c5 |
| 94 |
1 2 3 abcdefg c5_def |
| 95 |
100 200 300 qwerty c5_def |
| 96 |
200 300 400 asdfg c5_def |
| 97 |
300 400 500 xxxxxx r4c5 |
| 98 |
# DD Metadata of columns in table |
| 99 |
NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA |
| 100 |
c1 1 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 101 |
c2 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 102 |
c3 3 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 103 |
c4 4 MYSQL_TYPE_BLOB 0 Visible table_id=TABLE_ID; |
| 104 |
c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;table_id=TABLE_ID; |
| 105 |
DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; |
| 106 |
DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; |
| 107 |
DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; |
| 108 |
# DD Metadata of partitions in table |
| 109 |
NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA |
| 110 |
p0 128 discard=0;instant_col=4; |
| 111 |
p1 256 discard=0;instant_col=4; |
| 112 |
p2 384 discard=0;instant_col=4; |
| 113 |
p3 MAXVALUE discard=0;instant_col=4; |
| 114 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 115 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 116 |
test/t1#p#p0 8 4 0 5 5 5 |
| 117 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 118 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 119 |
c1 0 6 1027 0 0 0 |
| 120 |
c2 1 6 1027 0 0 0 |
| 121 |
c3 2 6 1027 0 0 0 |
| 122 |
c4 3 5 16711932 0 0 0 |
| 123 |
c5 4 13 16711934 1 0 0 |
| 124 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 125 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 126 |
test/t1#p#p1 8 4 0 5 5 5 |
| 127 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 128 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 129 |
c1 0 6 1027 0 0 0 |
| 130 |
c2 1 6 1027 0 0 0 |
| 131 |
c3 2 6 1027 0 0 0 |
| 132 |
c4 3 5 16711932 0 0 0 |
| 133 |
c5 4 13 16711934 1 0 0 |
| 134 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 135 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 136 |
test/t1#p#p2 8 4 0 5 5 5 |
| 137 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 138 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 139 |
c1 0 6 1027 0 0 0 |
| 140 |
c2 1 6 1027 0 0 0 |
| 141 |
c3 2 6 1027 0 0 0 |
| 142 |
c4 3 5 16711932 0 0 0 |
| 143 |
c5 4 13 16711934 1 0 0 |
| 144 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 145 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 146 |
test/t1#p#p3 8 4 0 5 5 5 |
| 147 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 148 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 149 |
c1 0 6 1027 0 0 0 |
| 150 |
c2 1 6 1027 0 0 0 |
| 151 |
c3 2 6 1027 0 0 0 |
| 152 |
c4 3 5 16711932 0 0 0 |
| 153 |
c5 4 13 16711934 1 0 0 |
| 154 |
# Cleanup |
| 155 |
DROP TABLE t1; |
| 156 |
# ------------------------------------------------------------ |
| 157 |
# Scenario 2 : No INSTANT ADD/DROP. Column mismatch |
| 158 |
# ------------------------------------------------------------ |
| 159 |
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, c5 char(10)) row_format=compact |
| 160 |
PARTITION BY RANGE(c1 * 2) |
| 161 |
(PARTITION p0 VALUES LESS THAN (128), |
| 162 |
PARTITION p1 VALUES LESS THAN (256), |
| 163 |
PARTITION p2 VALUES LESS THAN (384), |
| 164 |
PARTITION p3 VALUES LESS THAN MAXVALUE); |
| 165 |
# DD Metadata of columns in table |
| 166 |
NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA |
| 167 |
c1 1 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 168 |
c2 2 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 169 |
c3 3 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 170 |
c4 4 MYSQL_TYPE_BLOB 0 Visible table_id=TABLE_ID; |
| 171 |
c5 5 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; |
| 172 |
DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; |
| 173 |
DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; |
| 174 |
DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; |
| 175 |
# DD Metadata of partitions in table |
| 176 |
NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA |
| 177 |
p0 128 NULL |
| 178 |
p1 256 NULL |
| 179 |
p2 384 NULL |
| 180 |
p3 MAXVALUE NULL |
| 181 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 182 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 183 |
test/t1#p#p0 8 0 0 5 5 5 |
| 184 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 185 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 186 |
c1 0 6 1027 0 0 0 |
| 187 |
c2 1 6 1027 0 0 0 |
| 188 |
c3 2 6 1027 0 0 0 |
| 189 |
c4 3 5 16711932 0 0 0 |
| 190 |
c5 4 13 16711934 0 0 0 |
| 191 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 192 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 193 |
test/t1#p#p1 8 0 0 5 5 5 |
| 194 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 195 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 196 |
c1 0 6 1027 0 0 0 |
| 197 |
c2 1 6 1027 0 0 0 |
| 198 |
c3 2 6 1027 0 0 0 |
| 199 |
c4 3 5 16711932 0 0 0 |
| 200 |
c5 4 13 16711934 0 0 0 |
| 201 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 202 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 203 |
test/t1#p#p2 8 0 0 5 5 5 |
| 204 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 205 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 206 |
c1 0 6 1027 0 0 0 |
| 207 |
c2 1 6 1027 0 0 0 |
| 208 |
c3 2 6 1027 0 0 0 |
| 209 |
c4 3 5 16711932 0 0 0 |
| 210 |
c5 4 13 16711934 0 0 0 |
| 211 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 212 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 213 |
test/t1#p#p3 8 0 0 5 5 5 |
| 214 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 215 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 216 |
c1 0 6 1027 0 0 0 |
| 217 |
c2 1 6 1027 0 0 0 |
| 218 |
c3 2 6 1027 0 0 0 |
| 219 |
c4 3 5 16711932 0 0 0 |
| 220 |
c5 4 13 16711934 0 0 0 |
| 221 |
# IMPORT |
| 222 |
ALTER TABLE t1 discard tablespace; |
| 223 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 224 |
ERROR HY000: Schema mismatch (Column c5 length mismatch.) |
| 225 |
# Cleanup |
| 226 |
DROP TABLE t1; |
| 227 |
# ------------------------------------------------------------ |
| 228 |
# Scenario 3 : INSTANT ADD/DROP Columns in target table |
| 229 |
# ------------------------------------------------------------ |
| 230 |
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=compact |
| 231 |
PARTITION BY RANGE(c1 * 2) |
| 232 |
(PARTITION p0 VALUES LESS THAN (128), |
| 233 |
PARTITION p1 VALUES LESS THAN (256), |
| 234 |
PARTITION p2 VALUES LESS THAN (384), |
| 235 |
PARTITION p3 VALUES LESS THAN MAXVALUE); |
| 236 |
ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT "c5_def", ALGORITHM=INSTANT; |
| 237 |
# DD Metadata of columns in table |
| 238 |
NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA |
| 239 |
c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID; |
| 240 |
c2 2 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID; |
| 241 |
c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID; |
| 242 |
c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID; |
| 243 |
c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1; |
| 244 |
DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; |
| 245 |
DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; |
| 246 |
DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; |
| 247 |
# DD Metadata of partitions in table |
| 248 |
NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA |
| 249 |
p0 128 NULL |
| 250 |
p1 256 NULL |
| 251 |
p2 384 NULL |
| 252 |
p3 MAXVALUE NULL |
| 253 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 254 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 255 |
test/t1#p#p0 8 0 1 4 5 5 |
| 256 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 257 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 258 |
c1 0 6 1027 0 0 0 |
| 259 |
c2 1 6 1027 0 0 0 |
| 260 |
c3 2 6 1027 0 0 0 |
| 261 |
c4 3 5 16711932 0 0 0 |
| 262 |
c5 4 13 16711934 1 1 0 |
| 263 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 264 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 265 |
test/t1#p#p1 8 0 1 4 5 5 |
| 266 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 267 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 268 |
c1 0 6 1027 0 0 0 |
| 269 |
c2 1 6 1027 0 0 0 |
| 270 |
c3 2 6 1027 0 0 0 |
| 271 |
c4 3 5 16711932 0 0 0 |
| 272 |
c5 4 13 16711934 1 1 0 |
| 273 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 274 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 275 |
test/t1#p#p2 8 0 1 4 5 5 |
| 276 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 277 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 278 |
c1 0 6 1027 0 0 0 |
| 279 |
c2 1 6 1027 0 0 0 |
| 280 |
c3 2 6 1027 0 0 0 |
| 281 |
c4 3 5 16711932 0 0 0 |
| 282 |
c5 4 13 16711934 1 1 0 |
| 283 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 284 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 285 |
test/t1#p#p3 8 0 1 4 5 5 |
| 286 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 287 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 288 |
c1 0 6 1027 0 0 0 |
| 289 |
c2 1 6 1027 0 0 0 |
| 290 |
c3 2 6 1027 0 0 0 |
| 291 |
c4 3 5 16711932 0 0 0 |
| 292 |
c5 4 13 16711934 1 1 0 |
| 293 |
# IMPORT |
| 294 |
ALTER TABLE t1 discard tablespace; |
| 295 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 296 |
ERROR HY000: Schema mismatch (Target table has INSTANT columns but the .cfg file is from earlier release with INSTANT column in the source table. Instant metadata can't match. Please create target table with no INSTANT column and try IMPORT.) |
| 297 |
# Cleanup |
| 298 |
DROP TABLE t1; |
| 299 |
# ------------------------------------------------------------ |
| 300 |
# Scenario 4 : INSTANT ADD/DROP. Column mismatch |
| 301 |
# ------------------------------------------------------------ |
| 302 |
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=compact |
| 303 |
PARTITION BY RANGE(c1 * 2) |
| 304 |
(PARTITION p0 VALUES LESS THAN (128), |
| 305 |
PARTITION p1 VALUES LESS THAN (256), |
| 306 |
PARTITION p2 VALUES LESS THAN (384), |
| 307 |
PARTITION p3 VALUES LESS THAN MAXVALUE); |
| 308 |
ALTER TABLE t1 ADD COLUMN c5 char(10) DEFAULT "c5_def", ALGORITHM=INSTANT; |
| 309 |
# DD Metadata of columns in table |
| 310 |
NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA |
| 311 |
c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID; |
| 312 |
c2 2 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID; |
| 313 |
c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID; |
| 314 |
c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID; |
| 315 |
c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020;physical_pos=7;table_id=TABLE_ID;version_added=1; |
| 316 |
DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; |
| 317 |
DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; |
| 318 |
DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; |
| 319 |
# DD Metadata of partitions in table |
| 320 |
NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA |
| 321 |
p0 128 NULL |
| 322 |
p1 256 NULL |
| 323 |
p2 384 NULL |
| 324 |
p3 MAXVALUE NULL |
| 325 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 326 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 327 |
test/t1#p#p0 8 0 1 4 5 5 |
| 328 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 329 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 330 |
c1 0 6 1027 0 0 0 |
| 331 |
c2 1 6 1027 0 0 0 |
| 332 |
c3 2 6 1027 0 0 0 |
| 333 |
c4 3 5 16711932 0 0 0 |
| 334 |
c5 4 13 16711934 1 1 0 |
| 335 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 336 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 337 |
test/t1#p#p1 8 0 1 4 5 5 |
| 338 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 339 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 340 |
c1 0 6 1027 0 0 0 |
| 341 |
c2 1 6 1027 0 0 0 |
| 342 |
c3 2 6 1027 0 0 0 |
| 343 |
c4 3 5 16711932 0 0 0 |
| 344 |
c5 4 13 16711934 1 1 0 |
| 345 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 346 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 347 |
test/t1#p#p2 8 0 1 4 5 5 |
| 348 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 349 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 350 |
c1 0 6 1027 0 0 0 |
| 351 |
c2 1 6 1027 0 0 0 |
| 352 |
c3 2 6 1027 0 0 0 |
| 353 |
c4 3 5 16711932 0 0 0 |
| 354 |
c5 4 13 16711934 1 1 0 |
| 355 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 356 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 357 |
test/t1#p#p3 8 0 1 4 5 5 |
| 358 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 359 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 360 |
c1 0 6 1027 0 0 0 |
| 361 |
c2 1 6 1027 0 0 0 |
| 362 |
c3 2 6 1027 0 0 0 |
| 363 |
c4 3 5 16711932 0 0 0 |
| 364 |
c5 4 13 16711934 1 1 0 |
| 365 |
# IMPORT |
| 366 |
ALTER TABLE t1 discard tablespace; |
| 367 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 368 |
ERROR HY000: Schema mismatch (Column c5 length mismatch.) |
| 369 |
# Cleanup |
| 370 |
DROP TABLE t1; |
| 371 |
# ------------------------------------------------------------ |
| 372 |
# Scenario 5 : INSTANT ADD/DROP. Default value mismatch |
| 373 |
# ------------------------------------------------------------ |
| 374 |
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=compact |
| 375 |
PARTITION BY RANGE(c1 * 2) |
| 376 |
(PARTITION p0 VALUES LESS THAN (128), |
| 377 |
PARTITION p1 VALUES LESS THAN (256), |
| 378 |
PARTITION p2 VALUES LESS THAN (384), |
| 379 |
PARTITION p3 VALUES LESS THAN MAXVALUE); |
| 380 |
ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT "def_c5", ALGORITHM=INSTANT; |
| 381 |
# DD Metadata of columns in table |
| 382 |
NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA |
| 383 |
c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID; |
| 384 |
c2 2 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID; |
| 385 |
c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID; |
| 386 |
c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID; |
| 387 |
c5 5 MYSQL_TYPE_STRING 0 Visible default=6465665f63352020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1; |
| 388 |
DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID; |
| 389 |
DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID; |
| 390 |
DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID; |
| 391 |
# DD Metadata of partitions in table |
| 392 |
NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA |
| 393 |
p0 128 NULL |
| 394 |
p1 256 NULL |
| 395 |
p2 384 NULL |
| 396 |
p3 MAXVALUE NULL |
| 397 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 398 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 399 |
test/t1#p#p0 8 0 1 4 5 5 |
| 400 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 401 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 402 |
c1 0 6 1027 0 0 0 |
| 403 |
c2 1 6 1027 0 0 0 |
| 404 |
c3 2 6 1027 0 0 0 |
| 405 |
c4 3 5 16711932 0 0 0 |
| 406 |
c5 4 13 16711934 1 1 0 |
| 407 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 408 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 409 |
test/t1#p#p1 8 0 1 4 5 5 |
| 410 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 411 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 412 |
c1 0 6 1027 0 0 0 |
| 413 |
c2 1 6 1027 0 0 0 |
| 414 |
c3 2 6 1027 0 0 0 |
| 415 |
c4 3 5 16711932 0 0 0 |
| 416 |
c5 4 13 16711934 1 1 0 |
| 417 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 418 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 419 |
test/t1#p#p2 8 0 1 4 5 5 |
| 420 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 421 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 422 |
c1 0 6 1027 0 0 0 |
| 423 |
c2 1 6 1027 0 0 0 |
| 424 |
c3 2 6 1027 0 0 0 |
| 425 |
c4 3 5 16711932 0 0 0 |
| 426 |
c5 4 13 16711934 1 1 0 |
| 427 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 428 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 429 |
test/t1#p#p3 8 0 1 4 5 5 |
| 430 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 431 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 432 |
c1 0 6 1027 0 0 0 |
| 433 |
c2 1 6 1027 0 0 0 |
| 434 |
c3 2 6 1027 0 0 0 |
| 435 |
c4 3 5 16711932 0 0 0 |
| 436 |
c5 4 13 16711934 1 1 0 |
| 437 |
# IMPORT |
| 438 |
ALTER TABLE t1 discard tablespace; |
| 439 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 440 |
ERROR HY000: Schema mismatch (Target table has INSTANT columns but the .cfg file is from earlier release with INSTANT column in the source table. Instant metadata can't match. Please create target table with no INSTANT column and try IMPORT.) |
| 441 |
# Cleanup |
| 442 |
DROP TABLE t1; |
| 443 |
# -------------------------------------------------------------- |
| 444 |
# Scenario 6 : Table with INSTANT ADD and reorganized partitions |
| 445 |
# -------------------------------------------------------------- |
| 446 |
# Copy and unzip the ibd/cfg files. |
| 447 |
# listing MYSQL_TMP_DIR/old_instant_reorg_part_comp |
| 448 |
t1#p#p0.cfg |
| 449 |
t1#p#p0.ibd |
| 450 |
t1#p#p1_1.cfg |
| 451 |
t1#p#p1_1.ibd |
| 452 |
t1#p#p1_2.cfg |
| 453 |
t1#p#p1_2.ibd |
| 454 |
t1#p#p2.cfg |
| 455 |
t1#p#p2.ibd |
| 456 |
CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT KEY, c1 char(10), c2 char(10), c3 char(10)) |
| 457 |
PARTITION BY RANGE(id) ( |
| 458 |
PARTITION p0 VALUES LESS THAN (100), |
| 459 |
PARTITION p1_1 VALUES LESS THAN (150), |
| 460 |
PARTITION p1_2 VALUES LESS THAN (200), |
| 461 |
PARTITION p2 VALUES LESS THAN MAXVALUE); |
| 462 |
# DD Metadata of columns in table |
| 463 |
NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA |
| 464 |
id 1 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 465 |
c1 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; |
| 466 |
c2 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; |
| 467 |
c3 4 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; |
| 468 |
DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; |
| 469 |
DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; |
| 470 |
# DD Metadata of partitions in table |
| 471 |
NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA |
| 472 |
p0 100 autoinc=0;version=0; |
| 473 |
p1_1 150 autoinc=0;version=0; |
| 474 |
p1_2 200 autoinc=0;version=0; |
| 475 |
p2 MAXVALUE autoinc=0;version=0; |
| 476 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 477 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 478 |
test/t1#p#p0 7 0 0 4 4 4 |
| 479 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 480 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 481 |
id 0 6 1283 0 0 0 |
| 482 |
c1 1 13 16711934 0 0 0 |
| 483 |
c2 2 13 16711934 0 0 0 |
| 484 |
c3 3 13 16711934 0 0 0 |
| 485 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 486 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 487 |
test/t1#p#p1_1 7 0 0 4 4 4 |
| 488 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 489 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 490 |
id 0 6 1283 0 0 0 |
| 491 |
c1 1 13 16711934 0 0 0 |
| 492 |
c2 2 13 16711934 0 0 0 |
| 493 |
c3 3 13 16711934 0 0 0 |
| 494 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 495 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 496 |
test/t1#p#p1_2 7 0 0 4 4 4 |
| 497 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 498 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 499 |
id 0 6 1283 0 0 0 |
| 500 |
c1 1 13 16711934 0 0 0 |
| 501 |
c2 2 13 16711934 0 0 0 |
| 502 |
c3 3 13 16711934 0 0 0 |
| 503 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 504 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 505 |
test/t1#p#p2 7 0 0 4 4 4 |
| 506 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 507 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 508 |
id 0 6 1283 0 0 0 |
| 509 |
c1 1 13 16711934 0 0 0 |
| 510 |
c2 2 13 16711934 0 0 0 |
| 511 |
c3 3 13 16711934 0 0 0 |
| 512 |
# IMPORT |
| 513 |
ALTER TABLE t1 discard tablespace; |
| 514 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 515 |
SELECT * FROM t1 ORDER BY c1; |
| 516 |
id c1 c2 c3 |
| 517 |
1 r1c1 r1c2 c3_def |
| 518 |
2 r2c1 r2c2 r2c3 |
| 519 |
# DD Metadata of columns in table |
| 520 |
NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA |
| 521 |
id 1 MYSQL_TYPE_LONG 0 Visible table_id=TABLE_ID; |
| 522 |
c1 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; |
| 523 |
c2 3 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID; |
| 524 |
c3 4 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;table_id=TABLE_ID; |
| 525 |
DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID; |
| 526 |
DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID; |
| 527 |
# DD Metadata of partitions in table |
| 528 |
NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA |
| 529 |
p0 100 autoinc=0;discard=0;instant_col=3;version=0; |
| 530 |
p1_1 150 autoinc=0;discard=0;version=0; |
| 531 |
p1_2 200 autoinc=0;discard=0;version=0; |
| 532 |
p2 MAXVALUE autoinc=0;discard=0;instant_col=3;version=0; |
| 533 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 534 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 535 |
test/t1#p#p0 7 3 0 4 4 4 |
| 536 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 537 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 538 |
id 0 6 1283 0 0 0 |
| 539 |
c1 1 13 16711934 0 0 0 |
| 540 |
c2 2 13 16711934 0 0 0 |
| 541 |
c3 3 13 16711934 1 0 0 |
| 542 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 543 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 544 |
test/t1#p#p1_1 7 0 0 4 4 4 |
| 545 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 546 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 547 |
id 0 6 1283 0 0 0 |
| 548 |
c1 1 13 16711934 0 0 0 |
| 549 |
c2 2 13 16711934 0 0 0 |
| 550 |
c3 3 13 16711934 0 0 0 |
| 551 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 552 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 553 |
test/t1#p#p1_2 7 0 0 4 4 4 |
| 554 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 555 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 556 |
id 0 6 1283 0 0 0 |
| 557 |
c1 1 13 16711934 0 0 0 |
| 558 |
c2 2 13 16711934 0 0 0 |
| 559 |
c3 3 13 16711934 0 0 0 |
| 560 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 561 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS |
| 562 |
test/t1#p#p2 7 3 0 4 4 4 |
| 563 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 564 |
NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED |
| 565 |
id 0 6 1283 0 0 0 |
| 566 |
c1 1 13 16711934 0 0 0 |
| 567 |
c2 2 13 16711934 0 0 0 |
| 568 |
c3 3 13 16711934 1 0 0 |
| 569 |
# Cleanup |
| 570 |
DROP TABLE t1; |
| 571 |
########### |
| 572 |
# CLEANUP # |
| 573 |
########### |