mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 01:45:33 +01:00
9b431d714f
Allow ALTER TABLE ... IMPORT TABLESPACE without creating the table followed by discarding the tablespace. That is, assuming we want to import table t1 to t2, instead of CREATE TABLE t2 LIKE t1; ALTER TABLE t2 DISCARD TABLESPACE; FLUSH TABLES t1 FOR EXPORT; --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd UNLOCK TABLES; ALTER TABLE t2 IMPORT TABLESPACE; We can simply do FLUSH TABLES t1 FOR EXPORT; --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg --copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/t2.frm --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd UNLOCK TABLES; ALTER TABLE t2 IMPORT TABLESPACE; We achieve this by creating a "stub" table in the second scenario while opening the table, where t2 does not exist but needs to import from t1. The "stub" table is similar to a table that is created but then instructed to discard its tablespace. We include tests with various row formats, encryption, with indexes and auto-increment.
104 lines
3.3 KiB
Text
104 lines
3.3 KiB
Text
#
|
|
# MDEV-26137 ALTER TABLE IMPORT enhancement
|
|
#
|
|
# drop t1 before importing t2
|
|
CREATE TABLE t1 (a int) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(42);
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
ALTER TABLE t2 IMPORT TABLESPACE;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT * FROM t2;
|
|
a
|
|
42
|
|
DROP TABLE t2;
|
|
# created t2 but did not discard tablespace
|
|
CREATE TABLE t1 (a int) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(42);
|
|
CREATE TABLE t2 LIKE t1;
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
call mtr.add_suppression("InnoDB: Unable to import tablespace");
|
|
ALTER TABLE t2 IMPORT TABLESPACE;
|
|
ERROR HY000: Tablespace for table 'test/t2' exists. Please DISCARD the tablespace before IMPORT
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT * FROM t2;
|
|
a
|
|
DROP TABLE t2;
|
|
# attempt to import when there's no tablespace
|
|
ALTER TABLE t2 IMPORT TABLESPACE;
|
|
ERROR 42S02: Table 'test.t2' doesn't exist
|
|
# with index
|
|
CREATE TABLE t1 (a int, b varchar(50)) ENGINE=InnoDB;
|
|
CREATE UNIQUE INDEX ai ON t1 (a);
|
|
INSERT INTO t1 VALUES(42, "hello");
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
UNLOCK TABLES;
|
|
ALTER TABLE t2 IMPORT TABLESPACE;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` varchar(50) DEFAULT NULL,
|
|
UNIQUE KEY `ai` (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT * FROM t2;
|
|
a b
|
|
42 hello
|
|
SHOW INDEX FROM t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t1 0 ai 1 a A 1 NULL NULL YES BTREE NO
|
|
SHOW INDEX FROM t2;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t2 0 ai 1 a A 1 NULL NULL YES BTREE NO
|
|
DROP TABLE t1, t2;
|
|
# with virtual column index
|
|
CREATE TABLE t1 (a int, b int as (a * a)) ENGINE=InnoDB;
|
|
CREATE UNIQUE INDEX ai ON t1 (b);
|
|
INSERT INTO t1 VALUES(42, default);
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
UNLOCK TABLES;
|
|
ALTER TABLE t2 IMPORT TABLESPACE;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS (`a` * `a`) VIRTUAL,
|
|
UNIQUE KEY `ai` (`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT * FROM t2;
|
|
a b
|
|
42 1764
|
|
SELECT b FROM t2 USE INDEX (ai);
|
|
b
|
|
1764
|
|
SHOW INDEX FROM t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t1 0 ai 1 b A 1 NULL NULL YES BTREE NO
|
|
SHOW INDEX FROM t2;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t2 0 ai 1 b A 1 NULL NULL YES BTREE NO
|
|
CHECK TABLE t2 EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
DROP TABLE t1, t2;
|
|
# with auto_increment
|
|
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, i2 INT, i1 INT)ENGINE=INNODB;
|
|
INSERT INTO t1 (i2) SELECT 4 FROM seq_1_to_1024;
|
|
FLUSH TABLE t1 FOR EXPORT;
|
|
UNLOCK TABLES;
|
|
ALTER TABLE t2 IMPORT TABLESPACE;
|
|
CHECK TABLE t2 EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
DROP TABLE t2, t1;
|