path: root/mysql-test/suite/innodb/t/innodb-wl5522-1.test

# Not supported in embedded
--source include/not_embedded.inc

-- source include/have_innodb.inc

call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists.  Please DISCARD the tablespace before IMPORT.");

let MYSQLD_DATADIR =`SELECT @@datadir`;

# Following testcases are created from JET cases (where import
# export instance are differnt server )
# Here test will be run on same import and export instance.

CREATE DATABASE testdb_wl5522;

# case 1
CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb;
INSERT INTO testdb_wl5522.t1 VALUES (1),(123),(331);
SELECT c1 FROM testdb_wl5522.t1;
FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
SELECT * FROM testdb_wl5522.t1 ORDER BY c1;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

UNLOCK TABLES;

DROP TABLE testdb_wl5522.t1;
CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb;
ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;


perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
SELECT * FROM testdb_wl5522.t1 ORDER BY c1;
DROP TABLE testdb_wl5522.t1;

# case 2
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);
--error 1048
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 1062
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);

FLUSH TABLES testdb_wl5522.t1 WITH READ LOCK;
SELECT COUNT(*) FROM testdb_wl5522.t1;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF
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;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
CHECK TABLE testdb_wl5522.t1;

SELECT COUNT(*) FROM testdb_wl5522.t1;

DROP TABLE testdb_wl5522.t1;


# case 3 - with blob objects

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;
FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

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;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

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;

DROP TABLE testdb_wl5522.t1;

# case 4 - trasportable tablesace with autoincrement
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;
FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

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;


perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int;

# error on inserting duplicate value
--error 1062
INSERT INTO testdb_wl5522.t1 VALUES (1,'a1');
# insert new values
INSERT INTO testdb_wl5522.t1(col_2_varchar) VALUES ('a101'),('a102'),('a103');
SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int;
# check table can be altered
ALTER TABLE testdb_wl5522.t1 MODIFY col_1_int BIGINT;
SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int;

DROP TABLE testdb_wl5522.t1;

# case 5 - check with primary and foreign key
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;
SELECT * FROM testdb_wl5522.t1_fk;
FLUSH TABLES testdb_wl5522.t1,testdb_wl5522.t1_fk FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
ib_backup_tablespaces("testdb_wl5522", "t1_fk");
EOF

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;

# Alter table discrad table is not allowed with foreign_key_checks = 1
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;


perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_discard_tablespaces("testdb_wl5522", "t1_fk");
ib_restore_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1_fk");
EOF

ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
ALTER TABLE testdb_wl5522.t1_fk IMPORT TABLESPACE;
SELECT * FROM testdb_wl5522.t1;
SELECT * FROM testdb_wl5522.t1_fk;

# Enter Invalid value: PK-FK relationship violation
--error 1452
INSERT INTO testdb_wl5522.t1_fk VALUES (100,'a100');

SET AUTOCOMMIT = 0;
INSERT INTO testdb_wl5522.t1_fk VALUES (4,'a4'),(5,'a5');
ROLLBACK;
SELECT * FROM testdb_wl5522.t1_fk;

DROP TABLE testdb_wl5522.t1_fk,testdb_wl5522.t1;
SET AUTOCOMMIT = 1;


# case 6 - transporatbale tablespace with transactions
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;
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;
FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

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;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

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;
SET AUTOCOMMIT = 1;

DROP TABLE testdb_wl5522.t1;

#case 7 - transpotable tablespace with transaction(earlier failed with jet)

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;
FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

UNLOCK TABLES;

DROP TABLE testdb_wl5522.t1;
CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb;
ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;


perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
SELECT * FROM testdb_wl5522.t1 ORDER BY i;
SET AUTOCOMMIT = 0;
INSERT INTO testdb_wl5522.t1 VALUES (101),(102),(103);
COMMIT;
SELECT * FROM testdb_wl5522.t1 ORDER BY i;
SET AUTOCOMMIT = 1;
DROP TABLE testdb_wl5522.t1;


# case 8 - negative cases
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;
FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

UNLOCK TABLES;

# try if we can flush again
FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
UNLOCK TABLES;

DROP TABLE testdb_wl5522.t1;

# create table with incorrect schema
CREATE TABLE testdb_wl5522.t1 ( i bigint) ENGINE = Innodb;
ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

# error as mismatch in column data type
--error ER_TABLE_SCHEMA_MISMATCH
ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;

# explicilty delet idb file before creating table with correct schema
perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_unlink_tablespace("testdb_wl5522", "t1");
EOF

DROP TABLE testdb_wl5522.t1;

CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb;
ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

# Import should succeed
ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
# Try to import twice
--error 1813
ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
SELECT * FROM testdb_wl5522.t1 ORDER BY i;

SET AUTOCOMMIT = 0;
INSERT INTO testdb_wl5522.t1 VALUES (101),(102),(103);
COMMIT;
SELECT * FROM testdb_wl5522.t1 ORDER BY i;
DROP TABLE testdb_wl5522.t1;
CREATE TABLE testdb_wl5522.t1 (i int) ENGINE = Innodb;
ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
# do not delete ibt file and try to import
perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF
#--error 1000
ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
DROP TABLE testdb_wl5522.t1;
SET AUTOCOMMIT = 1;


# case 9 - empty table import
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;
ROLLBACK;
FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

UNLOCK TABLES;

DROP TABLE testdb_wl5522.t1;
CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb;
ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;


perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
SELECT * FROM testdb_wl5522.t1 ORDER BY c1;
DROP TABLE testdb_wl5522.t1;
SET AUTOCOMMIT = 1;

# case 10 - tt with prefix index

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;

FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

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;


perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

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;

DROP TABLE testdb_wl5522.t1;


# case 11 - tt with secondary index

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;

FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

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;


perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

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;

# perform transaction on impoted table
SET AUTOCOMMIT = 0;
INSERT INTO testdb_wl5522.t1(col_15) VALUES (15000),(16000);
SELECT col_15 FROM testdb_wl5522.t1 WHERE col_15 > 11000;
ROLLBACK;
SELECT col_15 FROM testdb_wl5522.t1 WHERE col_15 > 11000;
INSERT INTO testdb_wl5522.t1(col_15) VALUES (15000),(16000);
COMMIT;
SELECT col_15 FROM testdb_wl5522.t1 WHERE col_15 > 11000;
# dml
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;

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;

DROP TABLE testdb_wl5522.t1;
SET AUTOCOMMIT = 1;

# case 12 - tt with trigger / view

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;

# table for trigger action
CREATE TABLE testdb_wl5522.trigger_table ( i int ) ENGINE = Innodb;
# define trigger
CREATE  TRIGGER testdb_wl5522.tri AFTER INSERT ON  testdb_wl5522.t1
FOR EACH ROW INSERT INTO testdb_wl5522.trigger_table VALUES(NEW.col18);
# define view
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);


--error 1048
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 1062
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);

SELECT COUNT(*) FROM testdb_wl5522.t1;
SELECT * FROM testdb_wl5522.trigger_table;
SELECT COUNT(*) FROM testdb_wl5522.VW1;
FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;

perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("testdb_wl5522", "t1");
EOF

UNLOCK TABLES;

# trigger is also dropped when table is dropped
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;


perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("testdb_wl5522", "t1");
ib_restore_tablespaces("testdb_wl5522", "t1");
EOF

ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;

SELECT COUNT(*) FROM testdb_wl5522.t1;
SELECT * FROM testdb_wl5522.trigger_table;
SELECT COUNT(*) FROM testdb_wl5522.VW1;

# trigger table is not updated as trigger got dropped
INSERT INTO testdb_wl5522.t1(col18) VALUES (5);
# validate data in table not updated
SELECT * FROM testdb_wl5522.trigger_table;

UPDATE testdb_wl5522.t1 SET col18=10 WHERE col18=1;

# view shows updated data
SELECT COUNT(*) FROM testdb_wl5522.VW1;

SELECT COUNT(*) FROM testdb_wl5522.t1 WHERE col18=10;
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;
ALTER TABLE testdb_wl5522.t1 DROP INDEX prefix_idx;
SELECT col18,col14 FROM testdb_wl5522.t1 WHERE col14 like '_ccc%';
ALTER TABLE testdb_wl5522.t1 ADD INDEX prefix_idx (col24(10));
SELECT col18,col24 FROM testdb_wl5522.t1 WHERE col24 like '_a_a%';

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");

# cleanup
--remove_file $MYSQLTEST_VARDIR/tmp/t1.cfg
--remove_file $MYSQLTEST_VARDIR/tmp/t1.ibd
--remove_file $MYSQLTEST_VARDIR/tmp/t1_fk.cfg
--remove_file $MYSQLTEST_VARDIR/tmp/t1_fk.ibd