diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb-wl5522-1.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-wl5522-1.test | 952 |
1 files changed, 952 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/innodb-wl5522-1.test b/mysql-test/suite/innodb/t/innodb-wl5522-1.test new file mode 100644 index 00000000000..b1db34976a9 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-wl5522-1.test @@ -0,0 +1,952 @@ +# Not supported in embedded +--source include/not_embedded.inc + +-- source include/have_innodb.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +let MYSQLD_DATADIR =`SELECT @@datadir`; +let $innodb_file_per_table = `SELECT @@innodb_file_per_table`; +let $innodb_file_format = `SELECT @@innodb_file_format`; + +SET GLOBAL innodb_file_per_table = 1; +SELECT @@innodb_file_per_table; + +SET GLOBAL innodb_file_format = `Barracuda`; +SELECT @@innodb_file_format; + +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. + +DROP DATABASE IF EXISTS testdb_wl5522; +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 + +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; +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 + +eval SET GLOBAL INNODB_FILE_FORMAT=$innodb_file_format; +eval SET GLOBAL INNODB_FILE_PER_TABLE=$innodb_file_per_table; |