Edit File: innodb-wl5522-1.result
DROP TABLE IF EXISTS t1; SET GLOBAL innodb_file_per_table = 1; SELECT @@innodb_file_per_table; @@innodb_file_per_table 1 SET GLOBAL innodb_file_format = `Barracuda`; SELECT @@innodb_file_format; @@innodb_file_format Barracuda DROP DATABASE IF EXISTS testdb_wl5522; Warnings: Note 1008 Can't drop database 'testdb_wl5522'; database doesn't exist CREATE DATABASE testdb_wl5522; CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb; INSERT INTO testdb_wl5522.t1 VALUES (1),(123),(331); SELECT c1 FROM testdb_wl5522.t1; c1 1 123 331 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT; SELECT * FROM testdb_wl5522.t1 ORDER BY c1; c1 1 123 331 backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb; ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; SELECT * FROM testdb_wl5522.t1 ORDER BY c1; c1 1 123 331 DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.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 , col22 TIME , col23 YEAR ) ENGINE = Innodb; CREATE INDEX idx1 ON testdb_wl5522.t1(col18); CREATE INDEX prefix_idx ON testdb_wl5522.t1(col14 (10)); CREATE UNIQUE INDEX idx2 ON testdb_wl5522.t1(col12); CREATE UNIQUE INDEX idx3 ON testdb_wl5522.t1(col8); INSERT INTO testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.t1 WITH READ LOCK; SELECT COUNT(*) FROM testdb_wl5522.t1; COUNT(*) 2 backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.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 , col22 TIME , col23 YEAR ) ENGINE = Innodb; CREATE INDEX idx1 ON testdb_wl5522.t1(col18); CREATE INDEX prefix_idx ON testdb_wl5522.t1(col14 (10)); CREATE UNIQUE INDEX idx2 ON testdb_wl5522.t1(col12); CREATE UNIQUE INDEX idx3 ON testdb_wl5522.t1(col8); ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; CHECK TABLE testdb_wl5522.t1; Table Op Msg_type Msg_text testdb_wl5522.t1 check status OK SELECT COUNT(*) FROM testdb_wl5522.t1; COUNT(*) 2 DROP TABLE testdb_wl5522.t1; SET GLOBAL innodb_file_format='Barracuda'; CREATE TABLE testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.t1 FOR EXPORT; backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.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 testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( col_1_int INT AUTO_INCREMENT, col_2_varchar VARCHAR (20), PRIMARY KEY (col_1_int)) ENGINE = Innodb; INSERT INTO testdb_wl5522.t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'); INSERT INTO testdb_wl5522.t1 (col_2_varchar) VALUES ('a4'),('a5'),('a6'); SELECT * FROM testdb_wl5522.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 testdb_wl5522.t1 FOR EXPORT; backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( col_1_int INT AUTO_INCREMENT, col_2_varchar VARCHAR (20), PRIMARY KEY (col_1_int)) ENGINE = Innodb; ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; SELECT * FROM testdb_wl5522.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 testdb_wl5522.t1 VALUES (1,'a1'); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' INSERT INTO testdb_wl5522.t1(col_2_varchar) VALUES ('a101'),('a102'),('a103'); SELECT * FROM testdb_wl5522.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 testdb_wl5522.t1 MODIFY col_1_int BIGINT; SELECT * FROM testdb_wl5522.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 testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( col_1_int INT,col_2_varchar VARCHAR (20), PRIMARY KEY (col_2_varchar)) ENGINE = Innodb; CREATE TABLE testdb_wl5522.t1_fk ( col_1_int INT,col_2_varchar VARCHAR (20), PRIMARY KEY (col_1_int), FOREIGN KEY (col_2_varchar) REFERENCES testdb_wl5522.t1(col_2_varchar) ) ENGINE = Innodb; INSERT INTO testdb_wl5522.t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5'); INSERT INTO testdb_wl5522.t1_fk VALUES (1,'a1'),(2,'a2'),(3,'a3'); SELECT * FROM testdb_wl5522.t1; col_1_int col_2_varchar 1 a1 2 a2 3 a3 4 a4 5 a5 SELECT * FROM testdb_wl5522.t1_fk; col_1_int col_2_varchar 1 a1 2 a2 3 a3 FLUSH TABLES testdb_wl5522.t1,testdb_wl5522.t1_fk FOR EXPORT; backup: t1 backup: t1_fk UNLOCK TABLES; DROP TABLE testdb_wl5522.t1_fk,testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( col_1_int INT,col_2_varchar VARCHAR (20), PRIMARY KEY (col_2_varchar)) ENGINE = Innodb; CREATE TABLE testdb_wl5522.t1_fk ( col_1_int INT,col_2_varchar VARCHAR (20), PRIMARY KEY (col_1_int), FOREIGN KEY (col_2_varchar) REFERENCES testdb_wl5522.t1(col_2_varchar) ) ENGINE = Innodb; SET foreign_key_checks = 0; ALTER TABLE testdb_wl5522.t1_fk DISCARD TABLESPACE; ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; SET foreign_key_checks = 1; restore: t1 .ibd and .cfg files restore: t1_fk .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; ALTER TABLE testdb_wl5522.t1_fk IMPORT TABLESPACE; SELECT * FROM testdb_wl5522.t1; col_1_int col_2_varchar 1 a1 2 a2 3 a3 4 a4 5 a5 SELECT * FROM testdb_wl5522.t1_fk; col_1_int col_2_varchar 1 a1 2 a2 3 a3 INSERT INTO testdb_wl5522.t1_fk VALUES (100,'a100'); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`testdb_wl5522`.`t1_fk`, CONSTRAINT `t1_fk_ibfk_1` FOREIGN KEY (`col_2_varchar`) REFERENCES `t1` (`col_2_varchar`)) SET AUTOCOMMIT = 0; INSERT INTO testdb_wl5522.t1_fk VALUES (4,'a4'),(5,'a5'); ROLLBACK; SELECT * FROM testdb_wl5522.t1_fk; col_1_int col_2_varchar 1 a1 2 a2 3 a3 DROP TABLE testdb_wl5522.t1_fk,testdb_wl5522.t1; SET AUTOCOMMIT = 1; CREATE TABLE testdb_wl5522.t1 ( col_1_int int,col_2_varchar VARCHAR (20), PRIMARY KEY (col_2_varchar)) ENGINE = Innodb; SET AUTOCOMMIT = 0; INSERT INTO testdb_wl5522.t1 VALUES (1,'a1'),(2,'a2'); SELECT * FROM testdb_wl5522.t1; col_1_int col_2_varchar 1 a1 2 a2 COMMIT; INSERT INTO testdb_wl5522.t1 VALUES (3,'a3'),(4,'a4'); ROLLBACK; INSERT INTO testdb_wl5522.t1 VALUES (5,'a5'),(6,'a6'); COMMIT; SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int; col_1_int col_2_varchar 1 a1 2 a2 5 a5 6 a6 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT; backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( col_1_int int,col_2_varchar VARCHAR (20), PRIMARY KEY (col_2_varchar)) ENGINE = Innodb; ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; SET AUTOCOMMIT = 0; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; INSERT INTO testdb_wl5522.t1 VALUES (7,'a7'),(8,'a8'); COMMIT; INSERT INTO testdb_wl5522.t1 VALUES (9,'a9'),(10,'a10'); ROLLBACK; INSERT INTO testdb_wl5522.t1 VALUES (11,'a11'),(12,'a12'); COMMIT; SELECT * FROM testdb_wl5522.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 testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb; INSERT INTO testdb_wl5522.t1 VALUES (100),(200),(300); SELECT * FROM testdb_wl5522.t1 ORDER BY i; i 100 200 300 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT; backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb; ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; SELECT * FROM testdb_wl5522.t1 ORDER BY i; i 100 200 300 SET AUTOCOMMIT = 0; INSERT INTO testdb_wl5522.t1 VALUES (101),(102),(103); COMMIT; SELECT * FROM testdb_wl5522.t1 ORDER BY i; i 100 101 102 103 200 300 SET AUTOCOMMIT = 1; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb; INSERT INTO testdb_wl5522.t1 VALUES (100),(200),(300); SELECT * FROM testdb_wl5522.t1 ORDER BY i; i 100 200 300 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT; backup: t1 UNLOCK TABLES; FLUSH TABLES testdb_wl5522.t1 FOR EXPORT; UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( i bigint) ENGINE = Innodb; ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Column i precise type mismatch.) unlink: t1.ibd unlink: t1.cfg DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb; ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; ERROR HY000: Tablespace for table 't1' exists. Please DISCARD the tablespace before IMPORT. SELECT * FROM testdb_wl5522.t1 ORDER BY i; i 100 200 300 SET AUTOCOMMIT = 0; INSERT INTO testdb_wl5522.t1 VALUES (101),(102),(103); COMMIT; SELECT * FROM testdb_wl5522.t1 ORDER BY i; i 100 101 102 103 200 300 DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 (i int) ENGINE = Innodb; ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; DROP TABLE testdb_wl5522.t1; SET AUTOCOMMIT = 1; CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb; SET AUTOCOMMIT = 0; INSERT INTO testdb_wl5522.t1 VALUES (1),(123),(331); SELECT c1 FROM testdb_wl5522.t1; c1 1 123 331 ROLLBACK; FLUSH TABLES testdb_wl5522.t1 FOR EXPORT; backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb; ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; SELECT * FROM testdb_wl5522.t1 ORDER BY c1; c1 DROP TABLE testdb_wl5522.t1; SET AUTOCOMMIT = 1; CREATE TABLE testdb_wl5522.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; CREATE INDEX prefix_idx ON testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.t1 FOR EXPORT; backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.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; CREATE INDEX prefix_idx ON testdb_wl5522.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 testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.t1; CREATE TABLE testdb_wl5522.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; CREATE INDEX idx1 ON testdb_wl5522.t1(col_1); CREATE INDEX idx2 ON testdb_wl5522.t1(col_2); CREATE INDEX idx3 ON testdb_wl5522.t1(col_3); CREATE INDEX idx4 ON testdb_wl5522.t1(col_4); CREATE INDEX idx5 ON testdb_wl5522.t1(col_5); CREATE INDEX idx6 ON testdb_wl5522.t1(col_6(255)); CREATE INDEX idx7 ON testdb_wl5522.t1(col_7(255)); CREATE INDEX idx8 ON testdb_wl5522.t1(col_8(255)); CREATE INDEX idx9 ON testdb_wl5522.t1(col_9(255)); CREATE INDEX idx10 ON testdb_wl5522.t1(col_10(255)); CREATE INDEX idx11 ON testdb_wl5522.t1(col_11(255)); CREATE INDEX idx12 ON testdb_wl5522.t1(col_12(255)); CREATE INDEX idx13 ON testdb_wl5522.t1(col_13(255)); CREATE INDEX idx14 ON testdb_wl5522.t1(col_14(255)); INSERT INTO testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.t1 FOR EXPORT; backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.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; CREATE INDEX idx1 ON testdb_wl5522.t1(col_1); CREATE INDEX idx2 ON testdb_wl5522.t1(col_2); CREATE INDEX idx3 ON testdb_wl5522.t1(col_3); CREATE INDEX idx4 ON testdb_wl5522.t1(col_4); CREATE INDEX idx5 ON testdb_wl5522.t1(col_5); CREATE INDEX idx6 ON testdb_wl5522.t1(col_6(255)); CREATE INDEX idx7 ON testdb_wl5522.t1(col_7(255)); CREATE INDEX idx8 ON testdb_wl5522.t1(col_8(255)); CREATE INDEX idx9 ON testdb_wl5522.t1(col_9(255)); CREATE INDEX idx10 ON testdb_wl5522.t1(col_10(255)); CREATE INDEX idx11 ON testdb_wl5522.t1(col_11(255)); CREATE INDEX idx12 ON testdb_wl5522.t1(col_12(255)); CREATE INDEX idx13 ON testdb_wl5522.t1(col_13(255)); CREATE INDEX idx14 ON testdb_wl5522.t1(col_14(255)); ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.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 testdb_wl5522.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 SET AUTOCOMMIT = 0; INSERT INTO testdb_wl5522.t1(col_15) VALUES (15000),(16000); SELECT col_15 FROM testdb_wl5522.t1 WHERE col_15 > 11000; col_15 15000 16000 ROLLBACK; SELECT col_15 FROM testdb_wl5522.t1 WHERE col_15 > 11000; col_15 INSERT INTO testdb_wl5522.t1(col_15) VALUES (15000),(16000); COMMIT; SELECT col_15 FROM testdb_wl5522.t1 WHERE col_15 > 11000; col_15 15000 16000 ALTER TABLE testdb_wl5522.t1 DROP INDEX idx1; ALTER TABLE testdb_wl5522.t1 DROP INDEX idx6; ALTER TABLE testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.t1 ADD INDEX idx1 (col_1); ALTER TABLE testdb_wl5522.t1 ADD INDEX idx6 (col_1(255)); ALTER TABLE testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.t1; SET AUTOCOMMIT = 1; CREATE TABLE testdb_wl5522.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 , col22 TIME , col23 YEAR ) ENGINE = Innodb; CREATE TABLE testdb_wl5522.trigger_table ( i int ) ENGINE = Innodb; CREATE TRIGGER testdb_wl5522.tri AFTER INSERT ON testdb_wl5522.t1 FOR EACH ROW INSERT INTO testdb_wl5522.trigger_table VALUES(NEW.col18); CREATE OR REPLACE VIEW testdb_wl5522.VW1 AS SELECT * FROM testdb_wl5522.t1; CREATE INDEX idx1 ON testdb_wl5522.t1(col18); CREATE INDEX prefix_idx ON testdb_wl5522.t1(col14 (10)); CREATE UNIQUE INDEX idx2 ON testdb_wl5522.t1(col12); CREATE UNIQUE INDEX idx3 ON testdb_wl5522.t1(col8); INSERT INTO testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.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 testdb_wl5522.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 COUNT(*) FROM testdb_wl5522.t1; COUNT(*) 2 SELECT * FROM testdb_wl5522.trigger_table; i 1 3 SELECT COUNT(*) FROM testdb_wl5522.VW1; COUNT(*) 2 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT; backup: t1 UNLOCK TABLES; DROP TABLE testdb_wl5522.t1; CREATE TABLE testdb_wl5522.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 , col22 TIME , col23 YEAR ) ENGINE = Innodb; CREATE INDEX idx1 ON testdb_wl5522.t1(col18); CREATE INDEX prefix_idx ON testdb_wl5522.t1(col14 (10)); CREATE UNIQUE INDEX idx2 ON testdb_wl5522.t1(col12); CREATE UNIQUE INDEX idx3 ON testdb_wl5522.t1(col8); ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE; SELECT COUNT(*) FROM testdb_wl5522.t1; COUNT(*) 2 SELECT * FROM testdb_wl5522.trigger_table; i 1 3 SELECT COUNT(*) FROM testdb_wl5522.VW1; COUNT(*) 2 INSERT INTO testdb_wl5522.t1(col18) VALUES (5); SELECT * FROM testdb_wl5522.trigger_table; i 1 3 UPDATE testdb_wl5522.t1 SET col18=10 WHERE col18=1; SELECT COUNT(*) FROM testdb_wl5522.VW1; COUNT(*) 3 SELECT COUNT(*) FROM testdb_wl5522.t1 WHERE col18=10; COUNT(*) 1 ALTER TABLE testdb_wl5522.t1 ADD COLUMN col24 varbinary(40) default null; INSERT INTO testdb_wl5522.t1(col18,col24) VALUES (6,REPEAT('a',10)); SELECT col24,col18 FROM testdb_wl5522.t1 WHERE col18 in (6,1,10) ORDER BY col18; col24 col18 aaaaaaaaaa 6 NULL 10 ALTER TABLE testdb_wl5522.t1 DROP INDEX prefix_idx; SELECT col18,col14 FROM testdb_wl5522.t1 WHERE col14 like '_ccc%'; col18 col14 10 ccccc ALTER TABLE testdb_wl5522.t1 ADD INDEX prefix_idx (col24(10)); SELECT col18,col24 FROM testdb_wl5522.t1 WHERE col24 like '_a_a%'; col18 col24 6 aaaaaaaaaa DROP TABLE testdb_wl5522.t1; DROP DATABASE testdb_wl5522; call mtr.add_suppression("Got error -1 when reading table '.*'"); call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'.*"); call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded"); SET GLOBAL INNODB_FILE_FORMAT=Antelope; SET GLOBAL INNODB_FILE_PER_TABLE=1;