summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/innodb/r/innodb-wl5522-1.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb-wl5522-1.result')
-rw-r--r--mysql-test/suite/innodb/r/innodb-wl5522-1.result813
1 files changed, 813 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-wl5522-1.result b/mysql-test/suite/innodb/r/innodb-wl5522-1.result
new file mode 100644
index 00000000..204d6bc8
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-wl5522-1.result
@@ -0,0 +1,813 @@
+call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT.");
+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;
+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, it's 0X408 in the table and 0X403 in the tablespace meta file)
+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 'testdb_wl5522/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");
+#
+# MDEV-27882 Innodb - recognise MySQL-8.0 innodb flags and give a specific error message
+#
+#
+CREATE TABLE `t1` (`i` int(11) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB;
+FLUSH TABLES t1 FOR EXPORT;
+backup: t1
+UNLOCK TABLES;
+ALTER TABLE t1 DISCARD TABLESPACE;
+call mtr.add_suppression("InnoDB: unsupported MySQL tablespace");
+ALTER TABLE t1 IMPORT TABLESPACE;
+ERROR 42000: Table 't1' uses an extension that doesn't exist in this MariaDB version
+DROP TABLE t1;
+#
+# End of 10.3 tests
+#
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-30 02:01:34 +0000