| 1 |
# ------------------------------------------------------------ |
| 2 |
# Exported table t1 (in older version) having |
| 3 |
# c1, c2 normal columns |
| 4 |
# c3 INSTANT ADD column with default def_c3. |
| 5 |
# c4 INSTANT ADD column with default def_c4. |
| 6 |
# +------+------+--------+--------+ |
| 7 |
# | c1 | c2 | c3 | c4 | |
| 8 |
# +------+------+--------+--------+ |
| 9 |
# | r1c1 | r1c2 | c3_def | c4_def | |
| 10 |
# | r2c1 | r2c2 | r2c3 | c4_def | |
| 11 |
# | r3c1 | r3c2 | r3c3 | r3c4 | |
| 12 |
# +------+------+--------+--------+ |
| 13 |
# ------------------------------------------------------------ |
| 14 |
# Copy and unzip the ibd/cfg files. |
| 15 |
# listing MYSQL_TMP_DIR/old_instant_comp |
| 16 |
t1.cfg |
| 17 |
t1.ibd |
| 18 |
# ------------------------------------------------------------ |
| 19 |
# Scenario 1 : No INSTANT ADD/DROP Columns in target table |
| 20 |
# ------------------------------------------------------------ |
| 21 |
create table t1 (c1 char(10), c2 char(10), c3 char(10), c4 char(10)) row_format=compact; |
| 22 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 23 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS |
| 24 |
test/t1 7 0 0 |
| 25 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 26 |
NAME POS MTYPE PRTYPE HAS_DEFAULT |
| 27 |
c1 0 13 16711934 0 |
| 28 |
c2 1 13 16711934 0 |
| 29 |
c3 2 13 16711934 0 |
| 30 |
c4 3 13 16711934 0 |
| 31 |
# IMPORT |
| 32 |
ALTER TABLE t1 DISCARD TABLESPACE; |
| 33 |
# Copy cfg/ibd files from temp location |
| 34 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 35 |
SELECT * FROM t1 ORDER BY c1; |
| 36 |
c1 c2 c3 c4 |
| 37 |
r1c1 r1c2 c3_def c4_def |
| 38 |
r2c1 r2c2 r2c3 c4_def |
| 39 |
r3c1 r3c2 r3c3 r3c4 |
| 40 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 41 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS |
| 42 |
test/t1 7 2 0 |
| 43 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 44 |
NAME POS MTYPE PRTYPE HAS_DEFAULT |
| 45 |
c1 0 13 16711934 0 |
| 46 |
c2 1 13 16711934 0 |
| 47 |
c3 2 13 16711934 1 |
| 48 |
c4 3 13 16711934 1 |
| 49 |
# Cleanup |
| 50 |
DROP TABLE t1; |
| 51 |
# ------------------------------------------------------------ |
| 52 |
# Scenario 2 : No INSTANT ADD/DROP. Column mismatch |
| 53 |
# ------------------------------------------------------------ |
| 54 |
create table t1 (c1 char(10), c2 char(20), c3 char(10), c4 char(10)) row_format=compact; |
| 55 |
# IMPORT |
| 56 |
ALTER TABLE t1 DISCARD TABLESPACE; |
| 57 |
# Copy cfg/ibd files from temp location |
| 58 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 59 |
ERROR HY000: Schema mismatch (Column c2 length mismatch.) |
| 60 |
# Cleanup |
| 61 |
DROP TABLE t1; |
| 62 |
# ------------------------------------------------------------ |
| 63 |
# Scenario 3 : INSTANT ADD/DROP Columns in target table |
| 64 |
# ------------------------------------------------------------ |
| 65 |
create table t1 (c1 char(10), c2 char(10)) row_format=compact; |
| 66 |
ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ADD COLUMN c4 char(10) DEFAULT "c4_def", ALGORITHM=INSTANT; |
| 67 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 68 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS |
| 69 |
test/t1 7 0 1 |
| 70 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 71 |
NAME POS MTYPE PRTYPE HAS_DEFAULT |
| 72 |
c1 0 13 16711934 0 |
| 73 |
c2 1 13 16711934 0 |
| 74 |
c3 2 13 16711934 1 |
| 75 |
c4 3 13 16711934 1 |
| 76 |
# IMPORT |
| 77 |
ALTER TABLE t1 DISCARD TABLESPACE; |
| 78 |
# Copy cfg/ibd files from temp location |
| 79 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 80 |
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.) |
| 81 |
# Cleanup |
| 82 |
DROP TABLE t1; |
| 83 |
# ------------------------------------------------------------ |
| 84 |
# Scenario 4 : INSTANT ADD/DROP. Column mismatch |
| 85 |
# ------------------------------------------------------------ |
| 86 |
create table t1 (c1 char(10), c2 char(10)) row_format=compact; |
| 87 |
ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "c3_def", ADD COLUMN c4 char(10) DEFAULT "c4_def", ALGORITHM=INSTANT; |
| 88 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 89 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS |
| 90 |
test/t1 7 0 1 |
| 91 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 92 |
NAME POS MTYPE PRTYPE HAS_DEFAULT |
| 93 |
c1 0 13 16711934 0 |
| 94 |
c2 1 13 16711934 0 |
| 95 |
c3 2 13 16711934 1 |
| 96 |
c4 3 13 16711934 1 |
| 97 |
# IMPORT |
| 98 |
ALTER TABLE t1 DISCARD TABLESPACE; |
| 99 |
# Copy cfg/ibd files from temp location |
| 100 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 101 |
ERROR HY000: Schema mismatch (Column c3 length mismatch.) |
| 102 |
# Cleanup |
| 103 |
DROP TABLE t1; |
| 104 |
# ------------------------------------------------------------ |
| 105 |
# Scenario 5 : INSTANT ADD/DROP. Default value mismatch |
| 106 |
# ------------------------------------------------------------ |
| 107 |
create table t1 (c1 char(10), c2 char(10)) row_format=compact; |
| 108 |
ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "def_c3", ADD COLUMN c4 char(10) DEFAULT "c4_def", ALGORITHM=INSTANT; |
| 109 |
# Metadata from INFORMATION_SCHEMA.TABLES |
| 110 |
NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS |
| 111 |
test/t1 7 0 1 |
| 112 |
# Metadata from INFORMATION_SCHEMA.COLUMNS |
| 113 |
NAME POS MTYPE PRTYPE HAS_DEFAULT |
| 114 |
c1 0 13 16711934 0 |
| 115 |
c2 1 13 16711934 0 |
| 116 |
c3 2 13 16711934 1 |
| 117 |
c4 3 13 16711934 1 |
| 118 |
# IMPORT |
| 119 |
ALTER TABLE t1 DISCARD TABLESPACE; |
| 120 |
# Copy cfg/ibd files from temp location |
| 121 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 122 |
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.) |
| 123 |
# Cleanup |
| 124 |
DROP TABLE t1; |
| 125 |
########### |
| 126 |
# CLEANUP # |
| 127 |
########### |