# Test "ALTER TABLE ... IMPORT TABLESPACE" in InnoDB --echo # --echo # Bug#20977779 CANNOT IMPORT TABLES CONTAINING PREFIX INDEXES --echo # CREATE TABLE t1 (c1 VARCHAR(32), c2 VARCHAR(32), c3 VARCHAR(32), PRIMARY KEY (c1, c2, c3)) ENGINE=InnoDB; ALTER TABLE t1 ADD INDEX ind1(c1(5), c2, c3); ALTER TABLE t1 ADD INDEX ind2(c3, c1(10), c2); ALTER TABLE t1 ADD INDEX ind3(c2, c3, c1(20)); INSERT INTO t1 VALUES ('Test Data -1', 'Test Data -2', 'Test Data -3'); --echo # Test with 2ndary index having prefix --source suite/innodb/include/import.inc --echo # Test with PK & 2ndary index with prefix ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c1(5), c2(10), c3(20)); --source suite/innodb/include/import.inc DROP TABLE t1; --echo # --echo # Bug#30561144 IMPORT TABLESPACE FAILS: ERROR 1808 (HY000): SCHEMA MISMATCH --echo # CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY); ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT; --echo # Insert some data into t1 --disable_query_log let $i=1000; while ($i) { INSERT INTO t1(v) VALUES ("aaaaaaaaaaaaa"); dec $i; } --enable_query_log SELECT * from t1 limit 10; SELECT COUNT(*) from t1; --echo # Flush the table and store CFG/IBD files to temp FLUSH TABLE t1 FOR EXPORT; --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/t1.cfg_back --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/t1.ibd_back UNLOCK TABLES; --echo # --echo # 1) Both tables contain INSTANT column, with matching default values. --echo # CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY); ALTER TABLE t2 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT; ALTER TABLE t2 DISCARD TABLESPACE; --echo # Copy CFG/IBD file from temp --copy_file $MYSQLD_DATADIR/t1.cfg_back $MYSQLD_DATADIR/test/t2.cfg --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t2.ibd --echo # IMPORT should succeed now ALTER TABLE t2 IMPORT TABLESPACE; SELECT * from t2 limit 10; DROP TABLE t2; --echo # --echo # 2) Both tables contain INSTANT column, with conflicting default values. --echo # CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY); ALTER TABLE t2 ADD COLUMN v VARCHAR(255) DEFAULT "different_value", ALGORITHM=INSTANT; ALTER TABLE t2 DISCARD TABLESPACE; --echo # Copy CFG/IBD file from temp --copy_file $MYSQLD_DATADIR/t1.cfg_back $MYSQLD_DATADIR/test/t2.cfg --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t2.ibd --echo # IMPORT should fail because of the conflicting values --error 1808 ALTER TABLE t2 IMPORT TABLESPACE; --echo # Manually remove tablespace files, since import failed drop won't delete them. --remove_file $MYSQLD_DATADIR/test/t2.cfg --remove_file $MYSQLD_DATADIR/test/t2.ibd --echo # Cleanup DROP TABLE t2; DROP TABLE t1; --remove_file $MYSQLD_DATADIR/t1.cfg_back --remove_file $MYSQLD_DATADIR/t1.ibd_back --echo # --echo # Bug #30830441 ALTER TABLE IMPORT TABLESPACE FAILS AFTER A SERVER RESTART --echo # CREATE TABLE t1 ( id int ); INSERT INTO t1 values (1),(2),(3); FLUSH TABLES t1 FOR EXPORT; UNLOCK TABLES; --source include/shutdown_mysqld.inc --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/t1.ibd_back --source include/start_mysqld.inc ALTER TABLE t1 DISCARD TABLESPACE; --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t1.ibd --disable_warnings ALTER TABLE t1 IMPORT TABLESPACE; --enable_warnings DROP TABLE t1; --remove_file $MYSQLD_DATADIR/t1.ibd_back