4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/innodb/r/transportable_tbsp-1.result

991 lines
33 KiB
Plaintext
Raw Permalink Blame History

This file contains invisible Unicode characters!

This file contains invisible Unicode characters that may be processed differently from what appears below. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to reveal hidden characters.

SET @old_innodb_file_per_table = @@innodb_file_per_table;
SET GLOBAL innodb_file_per_table = 1;
SELECT @@innodb_file_per_table;
@@innodb_file_per_table
1
# Following testcases are created from JET cases (where import
# export instance are different servers)
# Here test will be run on same import and export instance.
#
# case 1 Simple export, discard and import.
#
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
;
INSERT INTO t1 VALUES (1),(123),(331);
SELECT c1 FROM t1;
c1
1
123
331
FLUSH TABLES t1 FOR EXPORT;
SELECT * FROM t1 ORDER BY c1;
c1
1
123
331
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
c1
1
123
331
DROP TABLE t1;
#
# case 2 With indexes and more datatypes.
#
CREATE TABLE t1 (
col1 BIT(1),
col2 BOOLEAN,
col3 TINYINT,
col4 SMALLINT,
col5 MEDIUMINT,
col6 INT,
col7 BIGINT,
col8 FLOAT (14,3),
col9 DOUBLE (14,3),
col10 VARCHAR(20),
col11 TEXT,
col12 ENUM('a','b','c'),
col13 TEXT,
col14 CHAR(20),
col15 VARBINARY (400),
col16 BINARY(40),
col17 BLOB (400),
col18 INT NOT NULL PRIMARY KEY,
col19 DATE,
col20 DATETIME,
col21 TIMESTAMP NULL,
col22 TIME,
col23 YEAR) ENGINE = InnoDB
;
Warnings:
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
CREATE INDEX idx1 ON t1(col18);
CREATE INDEX prefix_idx ON t1(col14 (10));
CREATE UNIQUE INDEX idx2 ON t1(col12);
CREATE UNIQUE INDEX idx3 ON t1(col8);
INSERT INTO t1 VALUES
(1,1,-128,32767,-8388608,2147483647,-9223372036854775808, 92233720368.222,
-92233720368.222,'aaa', + 'aaaaaaaaaa','b','bbbbb','ccccc',
REPEAT('d',40),REPEAT('d',40),REPEAT('d',40),1,'1000-01-01',
'3000-12-31 23:59:59.99','1990-01-01 00:00:01.00',
'01:59:59.00','1901');
INSERT INTO t1 VALUES
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
INSERT INTO t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
ERROR 23000: Column 'col18' cannot be null
INSERT INTO t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
FLUSH TABLES t1 WITH READ LOCK;
SELECT * FROM t1;
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
 1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (
col1 BIT(1),
col2 BOOLEAN,
col3 TINYINT,
col4 SMALLINT,
col5 MEDIUMINT,
col6 INT,
col7 BIGINT,
col8 FLOAT (14,3),
col9 DOUBLE (14,3),
col10 VARCHAR(20),
col11 TEXT,
col12 ENUM('a','b','c'),
col13 TEXT,
col14 CHAR(20),
col15 VARBINARY (400),
col16 BINARY(40),
col17 BLOB (400),
col18 INT NOT NULL PRIMARY KEY,
col19 DATE,
col20 DATETIME,
col21 TIMESTAMP NULL,
col22 TIME,
col23 YEAR) ENGINE = InnoDB
;
Warnings:
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
CREATE INDEX idx1 ON t1(col18);
CREATE INDEX prefix_idx ON t1(col14 (10));
CREATE UNIQUE INDEX idx2 ON t1(col12);
CREATE UNIQUE INDEX idx3 ON t1(col8);
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
SELECT * FROM t1;
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
 1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
DROP TABLE t1;
#
# case 3 - with blob objects
#
CREATE TABLE t1 (
col_1_varbinary VARBINARY (4000),
col_2_varchar VARCHAR (4000),
col_3_text TEXT (4000),
col_4_blob BLOB (4000),
col_5_text TEXT (4000),
col_6_varchar VARCHAR (4000),
col_7_binary BINARY (255)
) ROW_FORMAT=DYNAMIC ENGINE = InnoDB
;
INSERT INTO t1 VALUES(
REPEAT('a', 4000),REPEAT('o', 4000),REPEAT('a', 4000), REPEAT('o', 4000),
REPEAT('a', 4000),REPEAT('a', 4000),REPEAT('a', 255));
SELECT col_1_varbinary = REPEAT("a", 4000),
col_2_varchar = REPEAT("o", 4000),
col_3_text = REPEAT("a", 4000),
col_4_blob = REPEAT("o", 4000),
col_5_text = REPEAT("a", 4000),
col_6_varchar = REPEAT("a", 4000),
col_7_binary = REPEAT("a", 255)
FROM t1;
col_1_varbinary = REPEAT("a", 4000) col_2_varchar = REPEAT("o", 4000) col_3_text = REPEAT("a", 4000) col_4_blob = REPEAT("o", 4000) col_5_text = REPEAT("a", 4000) col_6_varchar = REPEAT("a", 4000) col_7_binary = REPEAT("a", 255)
1 1 1 1 1 1 1
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (
col_1_varbinary VARBINARY (4000),
col_2_varchar VARCHAR (4000),
col_3_text TEXT (4000),
col_4_blob BLOB (4000),
col_5_text TEXT (4000),
col_6_varchar VARCHAR (4000),
col_7_binary BINARY (255)
) ROW_FORMAT=DYNAMIC ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT col_1_varbinary = REPEAT("a", 4000),
col_2_varchar = REPEAT("o", 4000),
col_3_text = REPEAT("a", 4000),
col_4_blob = REPEAT("o", 4000),
col_5_text = REPEAT("a", 4000),
col_6_varchar = REPEAT("a", 4000),
col_7_binary = REPEAT("a", 255)
FROM t1;
col_1_varbinary = REPEAT("a", 4000) col_2_varchar = REPEAT("o", 4000) col_3_text = REPEAT("a", 4000) col_4_blob = REPEAT("o", 4000) col_5_text = REPEAT("a", 4000) col_6_varchar = REPEAT("a", 4000) col_7_binary = REPEAT("a", 255)
1 1 1 1 1 1 1
DROP TABLE t1;
#
# case 4 - transportable tablespace with auto_increment
#
CREATE TABLE t1 (
col_1_int INT AUTO_INCREMENT,
col_2_varchar VARCHAR (20),
PRIMARY KEY (col_1_int)) ENGINE = InnoDB
;
INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3');
INSERT INTO t1 (col_2_varchar) VALUES ('a4'),('a5'),('a6');
SELECT * FROM t1 ORDER BY col_1_int;
col_1_int col_2_varchar
1 a1
2 a2
3 a3
4 a4
5 a5
6 a6
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (
col_1_int INT AUTO_INCREMENT,
col_2_varchar VARCHAR (20),
PRIMARY KEY (col_1_int)) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT * FROM t1 ORDER BY col_1_int;
col_1_int col_2_varchar
1 a1
2 a2
3 a3
4 a4
5 a5
6 a6
INSERT INTO t1 VALUES (1,'a1');
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
INSERT INTO t1(col_2_varchar) VALUES ('a101'),('a102'),('a103');
SELECT * FROM t1 ORDER BY col_1_int;
col_1_int col_2_varchar
1 a1
2 a2
3 a3
4 a4
5 a5
6 a6
7 a101
8 a102
9 a103
ALTER TABLE t1 MODIFY col_1_int BIGINT;
SELECT * FROM t1 ORDER BY col_1_int;
col_1_int col_2_varchar
1 a1
2 a2
3 a3
4 a4
5 a5
6 a6
7 a101
8 a102
9 a103
DROP TABLE t1;
#
# case 5 - check with primary and foreign key
#
CREATE TABLE t1 (
col_1_int INT,col_2_varchar VARCHAR (20),
PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB;
CREATE TABLE t1_fk (
col_1_int INT,col_2_varchar VARCHAR (20),
PRIMARY KEY (col_1_int),
FOREIGN KEY (col_2_varchar) REFERENCES t1(col_2_varchar)
) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5');
INSERT INTO t1_fk VALUES (1,'a1'),(2,'a2'),(3,'a3');
SELECT * FROM t1;
col_1_int col_2_varchar
1 a1
2 a2
3 a3
4 a4
5 a5
SELECT * FROM t1_fk;
col_1_int col_2_varchar
1 a1
2 a2
3 a3
FLUSH TABLES t1,t1_fk FOR EXPORT;
backup: t1
backup: t1_fk
UNLOCK TABLES;
DROP TABLE t1_fk,t1;
CREATE TABLE t1 (
col_1_int INT,col_2_varchar VARCHAR (20),
PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB;
CREATE TABLE t1_fk (
col_1_int INT,col_2_varchar VARCHAR (20),
PRIMARY KEY (col_1_int),
FOREIGN KEY (col_2_varchar) REFERENCES t1(col_2_varchar)
) ENGINE = InnoDB;
# Alter table discard table is not allowed with foreign_key_checks = 1
SET foreign_key_checks = 0;
ALTER TABLE t1_fk DISCARD TABLESPACE;
ALTER TABLE t1 DISCARD TABLESPACE;
SET foreign_key_checks = 1;
restore: t1 .ibd and .cfg files
restore: t1_fk .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
ALTER TABLE t1_fk IMPORT TABLESPACE;
SELECT * FROM t1;
col_1_int col_2_varchar
1 a1
2 a2
3 a3
4 a4
5 a5
SELECT * FROM t1_fk;
col_1_int col_2_varchar
1 a1
2 a2
3 a3
# Enter Invalid value: PK-FK relationship violation
INSERT INTO t1_fk VALUES (100,'a100');
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1_fk`, CONSTRAINT `t1_fk_ibfk_1` FOREIGN KEY (`col_2_varchar`) REFERENCES `t1` (`col_2_varchar`))
SET AUTOCOMMIT = 0;
INSERT INTO t1_fk VALUES (4,'a4'),(5,'a5');
ROLLBACK;
SELECT * FROM t1_fk;
col_1_int col_2_varchar
1 a1
2 a2
3 a3
DROP TABLE t1_fk,t1;
SET AUTOCOMMIT = 1;
#
# case 6 - transportable tablespace with transactions
#
CREATE TABLE t1 (
col_1_int int,col_2_varchar VARCHAR (20),
PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB
;
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (1,'a1'),(2,'a2');
SELECT * FROM t1;
col_1_int col_2_varchar
1 a1
2 a2
COMMIT;
INSERT INTO t1 VALUES (3,'a3'),(4,'a4');
ROLLBACK;
INSERT INTO t1 VALUES (5,'a5'),(6,'a6');
COMMIT;
SELECT * FROM t1 ORDER BY col_1_int;
col_1_int col_2_varchar
1 a1
2 a2
5 a5
6 a6
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (
col_1_int int,col_2_varchar VARCHAR (20),
PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
SET AUTOCOMMIT = 0;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
INSERT INTO t1 VALUES (7,'a7'),(8,'a8');
COMMIT;
INSERT INTO t1 VALUES (9,'a9'),(10,'a10');
ROLLBACK;
INSERT INTO t1 VALUES (11,'a11'),(12,'a12');
COMMIT;
SELECT * FROM t1 ORDER BY col_1_int;
col_1_int col_2_varchar
1 a1
2 a2
5 a5
6 a6
7 a7
8 a8
11 a11
12 a12
SET AUTOCOMMIT = 1;
DROP TABLE t1;
#
# case 7 - Transportable tablespace with transaction
# (earlier failed with jet)
#
CREATE TABLE t1 (i int) ENGINE = InnoDB
;
INSERT INTO t1 VALUES (100),(200),(300);
SELECT * FROM t1 ORDER BY i;
i
100
200
300
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (i int) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT * FROM t1 ORDER BY i;
i
100
200
300
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (101),(102),(103);
COMMIT;
SELECT * FROM t1 ORDER BY i;
i
100
101
102
103
200
300
SET AUTOCOMMIT = 1;
DROP TABLE t1;
#
# case 8 - negative cases
#
CREATE TABLE t1 (i int) ENGINE = InnoDB
;
INSERT INTO t1 VALUES (100),(200),(300);
SELECT * FROM t1 ORDER BY i;
i
100
200
300
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
# try if we can flush again
FLUSH TABLES t1 FOR EXPORT;
UNLOCK TABLES;
DROP TABLE t1;
# create table with incorrect schema
CREATE TABLE t1 (i bigint) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
# error as mismatch in column data type
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Schema mismatch (Column i precise type mismatch.)
# explicitly delete ibd file before creating table with correct schema
unlink: t1.ibd
unlink: t1.cfg
DROP TABLE t1;
CREATE TABLE t1 (i int) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
# Import should succeed
ALTER TABLE t1 IMPORT TABLESPACE;
# Try to import twice
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Tablespace 'test/t1' exists.
SELECT * FROM t1 ORDER BY i;
i
100
200
300
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (101),(102),(103);
COMMIT;
SELECT * FROM t1 ORDER BY i;
i
100
101
102
103
200
300
DROP TABLE t1;
CREATE TABLE t1 (i int) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
# do not delete ibd file and try to import
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
DROP TABLE t1;
SET AUTOCOMMIT = 1;
#
# case 9 - empty table import
#
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
;
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (1),(123),(331);
SELECT c1 FROM t1;
c1
1
123
331
ROLLBACK;
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
SET AUTOCOMMIT = 1;
#
# case 10 - Transportable tablespaces with prefix index
#
CREATE TABLE t1 (col_1 CHAR (255),
col_2 VARCHAR (255), col_3 VARCHAR (255),
col_4 VARCHAR (255),col_5 VARCHAR (255),
col_6 text (255), col_7 text (255),
col_8 text (255),col_9 text (255),
col_10 BLOB (255),col_11 BLOB (255),
col_12 BLOB (255), col_13 BLOB (255),
col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
;
CREATE INDEX prefix_idx ON t1(
col_1 (50),col_2 (50),col_3 (50),
col_4 (50),col_5 (50),col_6 (50),
col_7 (50),col_8 (50),col_9 (50),
col_10 (50),col_11 (50),col_12 (50),
col_13(50));
INSERT INTO t1 VALUES (
REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
SELECT
col_1 = REPEAT("col1_00001",10),
col_2 = REPEAT("col2_00001",10),
col_3 = REPEAT("col3_00001",10),
col_4 = REPEAT("col4_00001",10),
col_5 = REPEAT("col5_00001",10),
col_6 = REPEAT("col6_00001",10),
col_7 = REPEAT("col7_00001",10),
col_8 = REPEAT("col8_00001",10),
col_9 = REPEAT("col9_00001",10),
col_10 = REPEAT("col10_00001",10),
col_11 = REPEAT("col11_00001",10),
col_12 = REPEAT("col12_00001",10),
col_13 = REPEAT("col13_00001",10),
col_14 = REPEAT("col14_00001",10),
col_15
FROM t1;
col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (col_1 CHAR (255),
col_2 VARCHAR (255), col_3 VARCHAR (255),
col_4 VARCHAR (255),col_5 VARCHAR (255),
col_6 text (255), col_7 text (255),
col_8 text (255),col_9 text (255),
col_10 BLOB (255),col_11 BLOB (255),
col_12 BLOB (255), col_13 BLOB (255),
col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
;
CREATE INDEX prefix_idx ON t1(
col_1 (50),col_2 (50),col_3 (50),
col_4 (50),col_5 (50),col_6 (50),
col_7 (50),col_8 (50),col_9 (50),
col_10 (50),col_11 (50),col_12 (50),
col_13(50));
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT
col_1 = REPEAT("col1_00001",10),
col_2 = REPEAT("col2_00001",10),
col_3 = REPEAT("col3_00001",10),
col_4 = REPEAT("col4_00001",10),
col_5 = REPEAT("col5_00001",10),
col_6 = REPEAT("col6_00001",10),
col_7 = REPEAT("col7_00001",10),
col_8 = REPEAT("col8_00001",10),
col_9 = REPEAT("col9_00001",10),
col_10 = REPEAT("col10_00001",10),
col_11 = REPEAT("col11_00001",10),
col_12 = REPEAT("col12_00001",10),
col_13 = REPEAT("col13_00001",10),
col_14 = REPEAT("col14_00001",10),
col_15
FROM t1;
col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
DROP TABLE t1;
#
# case 11 - Transportable tablespaces with secondary index
#
CREATE TABLE t1 (col_1 CHAR (255),
col_2 VARCHAR (255), col_3 VARCHAR (255),
col_4 VARCHAR (255),col_5 VARCHAR (255),
col_6 text (255), col_7 text (255),
col_8 text (255),col_9 text (255),
col_10 BLOB (255),col_11 BLOB (255),
col_12 BLOB (255), col_13 BLOB (255),
col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
;
CREATE INDEX idx1 ON t1(col_1);
CREATE INDEX idx2 ON t1(col_2);
CREATE INDEX idx3 ON t1(col_3);
CREATE INDEX idx4 ON t1(col_4);
CREATE INDEX idx5 ON t1(col_5);
CREATE INDEX idx6 ON t1(col_6(255));
CREATE INDEX idx7 ON t1(col_7(255));
CREATE INDEX idx8 ON t1(col_8(255));
CREATE INDEX idx9 ON t1(col_9(255));
CREATE INDEX idx10 ON t1(col_10(255));
CREATE INDEX idx11 ON t1(col_11(255));
CREATE INDEX idx12 ON t1(col_12(255));
CREATE INDEX idx13 ON t1(col_13(255));
CREATE INDEX idx14 ON t1(col_14(255));
INSERT INTO t1 VALUES (
REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
SELECT
col_1 = REPEAT("col1_00001",10),
col_2 = REPEAT("col2_00001",10),
col_3 = REPEAT("col3_00001",10),
col_4 = REPEAT("col4_00001",10),
col_5 = REPEAT("col5_00001",10),
col_6 = REPEAT("col6_00001",10),
col_7 = REPEAT("col7_00001",10),
col_8 = REPEAT("col8_00001",10),
col_9 = REPEAT("col9_00001",10),
col_10 = REPEAT("col10_00001",10),
col_11 = REPEAT("col11_00001",10),
col_12 = REPEAT("col12_00001",10),
col_13 = REPEAT("col13_00001",10),
col_14 = REPEAT("col14_00001",10),
col_15
FROM t1;
col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (col_1 CHAR (255),
col_2 VARCHAR (255), col_3 VARCHAR (255),
col_4 VARCHAR (255),col_5 VARCHAR (255),
col_6 text (255), col_7 text (255),
col_8 text (255),col_9 text (255),
col_10 BLOB (255),col_11 BLOB (255),
col_12 BLOB (255), col_13 BLOB (255),
col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
;
CREATE INDEX idx1 ON t1(col_1);
CREATE INDEX idx2 ON t1(col_2);
CREATE INDEX idx3 ON t1(col_3);
CREATE INDEX idx4 ON t1(col_4);
CREATE INDEX idx5 ON t1(col_5);
CREATE INDEX idx6 ON t1(col_6(255));
CREATE INDEX idx7 ON t1(col_7(255));
CREATE INDEX idx8 ON t1(col_8(255));
CREATE INDEX idx9 ON t1(col_9(255));
CREATE INDEX idx10 ON t1(col_10(255));
CREATE INDEX idx11 ON t1(col_11(255));
CREATE INDEX idx12 ON t1(col_12(255));
CREATE INDEX idx13 ON t1(col_13(255));
CREATE INDEX idx14 ON t1(col_14(255));
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT
col_1 = REPEAT("col1_00001",10),
col_2 = REPEAT("col2_00001",10),
col_3 = REPEAT("col3_00001",10),
col_4 = REPEAT("col4_00001",10),
col_5 = REPEAT("col5_00001",10),
col_6 = REPEAT("col6_00001",10),
col_7 = REPEAT("col7_00001",10),
col_8 = REPEAT("col8_00001",10),
col_9 = REPEAT("col9_00001",10),
col_10 = REPEAT("col10_00001",10),
col_11 = REPEAT("col11_00001",10),
col_12 = REPEAT("col12_00001",10),
col_13 = REPEAT("col13_00001",10),
col_14 = REPEAT("col14_00001",10),
col_15
FROM t1;
col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
# perform transaction on imported table
SET AUTOCOMMIT = 0;
INSERT INTO t1(col_15) VALUES (15000),(16000);
SELECT col_15 FROM t1 WHERE col_15 > 11000;
col_15
15000
16000
ROLLBACK;
SELECT col_15 FROM t1 WHERE col_15 > 11000;
col_15
INSERT INTO t1(col_15) VALUES (15000),(16000);
COMMIT;
SELECT col_15 FROM t1 WHERE col_15 > 11000;
col_15
15000
16000
# Perform DDL operations on imported table.
ALTER TABLE t1 DROP INDEX idx1;
ALTER TABLE t1 DROP INDEX idx6;
ALTER TABLE t1 DROP INDEX idx10;
SELECT
col_1 = REPEAT("col1_00001",10),
col_2 = REPEAT("col2_00001",10),
col_3 = REPEAT("col3_00001",10),
col_4 = REPEAT("col4_00001",10),
col_5 = REPEAT("col5_00001",10),
col_6 = REPEAT("col6_00001",10),
col_7 = REPEAT("col7_00001",10),
col_8 = REPEAT("col8_00001",10),
col_9 = REPEAT("col9_00001",10),
col_10 = REPEAT("col10_00001",10),
col_11 = REPEAT("col11_00001",10),
col_12 = REPEAT("col12_00001",10),
col_13 = REPEAT("col13_00001",10),
col_14 = REPEAT("col14_00001",10),
col_15
FROM t1;
col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 15000
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 16000
ALTER TABLE t1 ADD INDEX idx1 (col_1);
ALTER TABLE t1 ADD INDEX idx6 (col_1(255));
Warnings:
Warning 1831 Duplicate index 'idx6' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
ALTER TABLE t1 ADD INDEX idx10 (col_10(255));
SELECT
col_1 = REPEAT("col1_00001",10),
col_2 = REPEAT("col2_00001",10),
col_3 = REPEAT("col3_00001",10),
col_4 = REPEAT("col4_00001",10),
col_5 = REPEAT("col5_00001",10),
col_6 = REPEAT("col6_00001",10),
col_7 = REPEAT("col7_00001",10),
col_8 = REPEAT("col8_00001",10),
col_9 = REPEAT("col9_00001",10),
col_10 = REPEAT("col10_00001",10),
col_11 = REPEAT("col11_00001",10),
col_12 = REPEAT("col12_00001",10),
col_13 = REPEAT("col13_00001",10),
col_14 = REPEAT("col14_00001",10),
col_15
FROM t1;
col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 15000
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 16000
DROP TABLE t1;
SET AUTOCOMMIT = 1;
#
# case 12 - Transportable tablespaces with trigger / view
#
CREATE TABLE t1(col1 bit(1),
col2 boolean,col3 tinyint, col4 smallint,
col5 mediumint,col6 int, col7 bigint,
col8 float (14,3),col9 double (14,3),
col10 VARCHAR(20) CHARACTER SET utf8,
col11 TEXT CHARACTER SET binary,
col12 ENUM('a','b','c') CHARACTER SET binary,
col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
col14 CHAR(20), col15 VARBINARY (400),
col16 BINARY(40), col17 BLOB (400),
col18 int not null primary key,
col19 DATE,col20 DATETIME, col21 TIMESTAMP NULL,
col22 TIME, col23 YEAR) ENGINE = InnoDB
;
Warnings:
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
# table for trigger action
CREATE TABLE trigger_table (i int) ENGINE = InnoDB;
CREATE TRIGGER tri AFTER INSERT ON t1
FOR EACH ROW INSERT INTO trigger_table VALUES(NEW.col18);
CREATE OR REPLACE VIEW VW1 AS SELECT * FROM t1;
CREATE INDEX idx1 ON t1(col18);
CREATE INDEX prefix_idx ON t1(col14 (10));
CREATE UNIQUE INDEX idx2 ON t1(col12);
CREATE UNIQUE INDEX idx3 ON t1(col8);
INSERT INTO t1 VALUES (
1,1,-128,32767,-8388608,2147483647,-9223372036854775808,92233720368.222,
-92233720368.222,'aaa','aaaaaaaaaa','b','bbbbb','ccccc',REPEAT('d',40),
REPEAT('d',40),REPEAT('d',40),1,'1000-01-01','3000-12-31 23:59:59.99',
'1990-01-01 00:00:01.00','01:59:59.00','1901');
INSERT INTO t1 VALUES (
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
INSERT INTO t1 VALUES (
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
ERROR 23000: Column 'col18' cannot be null
INSERT INTO t1 VALUES (
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * FROM t1;
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
 1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
SELECT * FROM trigger_table;
i
1
3
SELECT * FROM VW1;
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
 1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
# trigger is also dropped when table is dropped
DROP TABLE t1;
CREATE TABLE t1(col1 bit(1),
col2 boolean,col3 tinyint, col4 smallint,
col5 mediumint,col6 int, col7 bigint,
col8 float (14,3),col9 double (14,3),
col10 VARCHAR(20) CHARACTER SET utf8,
col11 TEXT CHARACTER SET binary,
col12 ENUM('a','b','c') CHARACTER SET binary,
col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
col14 CHAR(20), col15 VARBINARY (400),
col16 BINARY(40), col17 BLOB (400),
col18 int not null primary key,
col19 DATE,col20 DATETIME, col21 TIMESTAMP NULL,
col22 TIME, col23 YEAR) ENGINE = InnoDB
;
Warnings:
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
CREATE INDEX idx1 ON t1(col18);
CREATE INDEX prefix_idx ON t1(col14 (10));
CREATE UNIQUE INDEX idx2 ON t1(col12);
CREATE UNIQUE INDEX idx3 ON t1(col8);
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT * FROM t1;
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
 1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
SELECT * FROM trigger_table;
i
1
3
SELECT * FROM VW1;
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
 1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
INSERT INTO t1(col18) VALUES (5);
SELECT * FROM trigger_table;
i
1
3
UPDATE t1 SET col18=10 WHERE col18=1;
SELECT * FROM VW1;
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 5 NULL NULL NULL NULL NULL
 1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 10 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
SELECT * FROM t1 WHERE col18=10;
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
 1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 10 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
ALTER TABLE t1 ADD COLUMN col24 varbinary(40) default null;
INSERT INTO t1(col18,col24) VALUES (6,REPEAT('a',10));
SELECT col24,col18 FROM t1 WHERE col18 in (6,1,10) ORDER BY col18;
col24 col18
aaaaaaaaaa 6
NULL 10
ALTER TABLE t1 DROP INDEX prefix_idx;
SELECT col18,col14 FROM t1 WHERE col14 like '_ccc%';
col18 col14
10 ccccc
ALTER TABLE t1 ADD INDEX prefix_idx (col24(10));
SELECT col18,col24 FROM t1 WHERE col24 like '_a_a%';
col18 col24
6 aaaaaaaaaa
DROP VIEW VW1;
DROP TABLE t1;
DROP TABLE trigger_table;
#
# case 13 Simple export, discard and import with instant ADD COLUMN.
#
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
;
INSERT INTO t1 VALUES (1), (123), (212), (331), (332), (333), (334), (335) ;
ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 500;
SELECT c1 FROM t1;
c1
1
123
212
331
332
333
334
335
FLUSH TABLES t1 FOR EXPORT;
SELECT * FROM t1 ORDER BY c1;
c1 c2
1 500
123 500
212 500
331 500
332 500
333 500
334 500
335 500
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 INT) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
SELECT * FROM t1 ORDER BY c1;
c1 c2
1 500
123 500
212 500
331 500
332 500
333 500
334 500
335 500
DROP TABLE t1;
#
# case 14 Simple export, discard and import with instant ADD COLUMN.
# However, this time the .cfg file would be missing
#
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
;
INSERT INTO t1 VALUES (1), (123), (212), (331), (332), (333), (334), (335) ;
ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 500;
SELECT c1 FROM t1;
c1
1
123
212
331
332
333
334
335
FLUSH TABLES t1 FOR EXPORT;
SELECT * FROM t1 ORDER BY c1;
c1 c2
1 500
123 500
212 500
331 500
332 500
333 500
334 500
335 500
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 INT) ENGINE = InnoDB
;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
# remove the .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.)
DROP TABLE t1;
SET GLOBAL innodb_file_per_table = @old_innodb_file_per_table;
Reference in New Issue View Git Blame Copy Permalink