4866 Vinay Fisrekar 2013-01-07
Adding tests from WL QA notes. Updating debug test with more cases.
added:
mysql-test/suite/innodb/r/innodb_wl6560_1.result
mysql-test/suite/innodb/t/innodb_wl6560_1.test
modified:
mysql-test/suite/innodb/r/innodb_wl6560_debug.result
mysql-test/suite/innodb/t/innodb_wl6560_debug.test
4865 Krunal Bauskar 2013-01-07
- WL#6560: improved error/warning messages to notify appropriate file-type
modified:
storage/innobase/os/os0file.cc
storage/innobase/srv/srv0space.cc
=== added file 'mysql-test/suite/innodb/r/innodb_wl6560_1.result'
--- a/mysql-test/suite/innodb/r/innodb_wl6560_1.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/r/innodb_wl6560_1.result 2013-01-07 07:01:27 +0000
@@ -0,0 +1,206 @@
+call mtr.add_suppression("Please add another temp-data file or use 'autoextend' for the last data file");
+call mtr.add_suppression("The table 't1' is full");
+set global innodb_file_per_table = off;
+DROP TABLE IF EXISTS t1;
+SELECT @@global.innodb_file_per_table;
+@@global.innodb_file_per_table
+0
+SELECT @mycount_SYS_DATAFILES:=COUNT(*) FROM information_schema.INNODB_SYS_DATAFILES;
+@mycount_SYS_DATAFILES:=COUNT(*)
+NUM
+CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+# creation of shared temp-tablespace in MYSQL_DATA_DIR
+ibtmp1
+# check no entries made INNODB_SYS_DATAFILES
+SELECT @mycount_SYS_DATAFILES=COUNT(*) FROM information_schema.INNODB_SYS_DATAFILES;
+@mycount_SYS_DATAFILES=COUNT(*)
+1
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+START TRANSACTION;
+INSERT INTO t1(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
+REPEAT('a2',50));
+INSERT INTO t1(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
+REPEAT('a3',50));
+UPDATE t1 SET e = REPEAT('ta',50);
+DELETE FROM t1 WHERE a = 3;
+SAVEPOINT A;
+INSERT INTO t1(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
+REPEAT('a4',50));
+UPDATE t1 SET e = REPEAT('yb',50);
+DELETE FROM t1 WHERE a = 1;
+SAVEPOINT B;
+SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+a RIGHT(b,10) RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+2 12.111 a2a2a2a2a2 a2a2a2a2a2 ybybybybyb
+4 14.111 a4a4a4a4a4 a4a4a4a4a4 ybybybybyb
+ROLLBACK TO SAVEPOINT A;
+COMMIT;
+SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+a RIGHT(b,10) RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+1 11.111 a1a1a1a1a1 a1a1a1a1a1 tatatatata
+2 12.111 a2a2a2a2a2 a2a2a2a2a2 tatatatata
+ALTER TABLE t1 ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
+SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10),RIGHT(f,10) FROM t1;
+a RIGHT(b,10) RIGHT(c,10) RIGHT(d,10) RIGHT(e,10) RIGHT(f,10)
+1 11.111 a1a1a1a1a1 a1a1a1a1a1 tatatatata NULL
+2 12.111 a2a2a2a2a2 a2a2a2a2a2 tatatatata NULL
+ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=INPLACE;
+ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
+ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
+SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+a RIGHT(b,10) RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+1 11.111 a1a1a1a1a1 a1a1a1a1a1 tatatatata
+2 12.111 a2a2a2a2a2 a2a2a2a2a2 tatatatata
+DROP TABLE t1;
+DROP TABLE IF EXISTS t1;
+DROP DATABASE IF EXISTS test_db;
+set global innodb_file_per_table = on;
+SELECT @@global.innodb_file_per_table;
+@@global.innodb_file_per_table
+1
+CREATE DATABASE test_db;
+"Create con1 client connection"
+# creation of shared temp-tablespace in MYSQL_DATA_DIR
+ibtmp1
+DROP TABLE IF EXISTS t1,t2,t3,t4;
+SELECT @@global.innodb_file_per_table;
+@@global.innodb_file_per_table
+1
+USE test;
+CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT, c BLOB(100),
+d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE t3 (val INT) ENGINE = InnoDB PARTITION BY LIST(val)(
+PARTITION mypart1 VALUES IN (1,3,5),
+PARTITION MyPart2 VALUES IN (2,4,6)
+);
+ERROR HY000: Cannot create temporary table with partitions
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('t1',50),REPEAT('t1',50),
+REPEAT('a2',50));
+INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('t2',50),REPEAT('t2',50),
+REPEAT('a2',50));
+SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c LIKE t2.c;
+a a RIGHT(t1.c,10) RIGHT(t2.c,10)
+SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
+a a RIGHT(t1.c,10) RIGHT(t2.c,10)
+1 1 a1a1a1a1a1 t1t1t1t1t1
+CREATE OR REPLACE VIEW vw1 AS SELECT * FROM t1;
+ERROR HY000: View's SELECT refers to a temporary table 't1'
+CREATE TEMPORARY TABLE t3 AS
+SELECT t1.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2
+WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
+SELECT * FROM t3;
+a RIGHT(t1.c,10) RIGHT(t2.c,10)
+1 a1a1a1a1a1 t1t1t1t1t1
+CREATE TABLE t4 AS
+SELECT t1.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2
+WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
+SELECT * FROM t4;
+a RIGHT(t1.c,10) RIGHT(t2.c,10)
+1 a1a1a1a1a1 t1t1t1t1t1
+SELECT * FROM t3,t4 WHERE t3.a=t4.a;
+a RIGHT(t1.c,10) RIGHT(t2.c,10) a RIGHT(t1.c,10) RIGHT(t2.c,10)
+1 a1a1a1a1a1 t1t1t1t1t1 1 a1a1a1a1a1 t1t1t1t1t1
+SELECT * FROM t3,t4 WHERE t3.a!=t4.a;
+a RIGHT(t1.c,10) RIGHT(t2.c,10) a RIGHT(t1.c,10) RIGHT(t2.c,10)
+LOCK TABLES t1 READ;
+SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c LIKE t2.c;
+a a RIGHT(t1.c,10) RIGHT(t2.c,10)
+SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
+a a RIGHT(t1.c,10) RIGHT(t2.c,10)
+1 1 a1a1a1a1a1 t1t1t1t1t1
+DELETE FROM t1;
+SELECT * FROM t1;
+a b c d e
+UNLOCK TABLES;
+LOCK TABLES t2 WRITE,t3 WRITE,t4 WRITE;
+DELETE FROM t2 WHERE t2.c LIKE REPEAT('t2',50);
+SELECT t2.a,RIGHT(t2.c,10) FROM t2;
+a RIGHT(t2.c,10)
+1 t1t1t1t1t1
+DELETE FROM t3;
+DELETE FROM t4;
+SELECT * FROM t3,t4;
+a RIGHT(t1.c,10) RIGHT(t2.c,10) a RIGHT(t1.c,10) RIGHT(t2.c,10)
+UNLOCK TABLES;
+ibtmp1
+t4.ibd
+DROP TABLE t1,t2,t3,t4;
+DROP TABLE IF EXISTS t1,t2;
+SELECT @@global.innodb_file_per_table;
+@@global.innodb_file_per_table
+1
+USE test;
+CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+1 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+2 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+UPDATE t1 SET a = a + 5;
+DELETE FROM t1 WHERE a > 6;
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+6 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
+a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+1 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+UPDATE t2 SET a = a + 5;
+DELETE FROM t2 WHERE a > 6;
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
+a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+6 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+# restart server
+# list_file ,no files expected - temp tablespace cleared on stopping server
+# list_file ,temp tablespace goes into 1st tmpdir if mutiple tmpdir listed
+ibtmp1
+# list_file ,temp tablespace does not go into 2nd tmp dir
+USE test;
+SHOW TABLES;
+Tables_in_test
+t2
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
+a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+6 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+UPDATE t2 SET a = a + 5;
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
+a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+11 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+1 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+2 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+UPDATE t1 SET a = a + 5;
+DELETE FROM t1 WHERE a > 6;
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
+6 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
+DROP TABLE t1;
+# restart server
+USE test;
+SHOW TABLES;
+Tables_in_test
+t2
+set global innodb_file_format = Antelope;
+set global innodb_file_per_table = 1;
+set global innodb_file_format = Antelope;
+set global innodb_file_per_table = 1;
=== modified file 'mysql-test/suite/innodb/r/innodb_wl6560_debug.result'
--- a/mysql-test/suite/innodb/r/innodb_wl6560_debug.result 2012-12-28 07:12:47 +0000
+++ b/mysql-test/suite/innodb/r/innodb_wl6560_debug.result 2013-01-07 07:01:27 +0000
@@ -20,6 +20,7 @@ drop table t1;
# files in MYSQL_TMP_DIR
ibtmp1
set session debug="+d,ib_ddl_crash_during_create";
+set session debug="+d,ib_ddl_crash_during_create";
create temporary table t1
(a int, b int, primary key(a), index(b)) engine = innodb;
ERROR HY000: Lost connection to MySQL server during query
@@ -33,5 +34,144 @@ select * from t1;
a b
1 2
drop table t1;
+set session debug="-d,ib_ddl_crash_during_create";
+SET DEBUG_SYNC='RESET';
+use test;
+create temporary table t1
+(a int, b char(100), c char(100)) engine = innodb;
+create table t2
+(a int, b char(100), c char(100)) engine = innodb;
+CREATE PROCEDURE populate_t1_t2()
+BEGIN
+DECLARE i INT DEFAULT 1;
+while (i <= 5000) DO
+insert into t1 values (i, 'a', 'b');
+insert into t2 values (i, 'a', 'b');
+SET i = i + 1;
+END WHILE;
+END|
+set autocommit=0;
+# set debug point ib_crash_during_tablespace_extension
+set session debug="+d,ib_crash_during_tablespace_extension";
+select count(*) from t1;
+count(*)
+5000
+select count(*) from t2;
+count(*)
+5000
+commit;
+show tables;
+Tables_in_test
+t2
+select count(*) from t1;
+count(*)
+5000
+select count(*) from t2;
+count(*)
+5000
+call populate_t1_t2();
+ERROR HY000: Lost connection to MySQL server during query
+use test;
+show tables;
+Tables_in_test
+t2
+select count(*) from t2;
+count(*)
+5000
+select * from t2 limit 10;
+a b c
+1 a b
+2 a b
+3 a b
+4 a b
+5 a b
+6 a b
+7 a b
+8 a b
+9 a b
+10 a b
+set autocommit=1;
+truncate table t2;
+select count(*) from t2;
+count(*)
+0
+drop procedure populate_t1_t2;
+drop table t2;
+SET DEBUG_SYNC='RESET';
+use test;
+create temporary table t1
+(a int, b char(100), c char(100)) engine = innodb;
+create table t2
+(a int, b char(100), c char(100)) engine = innodb;
+insert into t1 values (1,'a','b');
+insert into t2 values (1,'a','b');
+SELECT * from t1;
+a b c
+1 a b
+SELECT * from t2;
+a b c
+1 a b
+set session debug="+d,crash_commit_after";
+insert into t2 values (2,'a','b');
+ERROR HY000: Lost connection to MySQL server during query
+use test;
+show tables;
+Tables_in_test
+t2
+SELECT * from t2;
+a b c
+1 a b
+2 a b
+insert into t2 values (3,'a','b');
+SELECT * from t2;
+a b c
+1 a b
+2 a b
+3 a b
+SET DEBUG_SYNC='RESET';
+create temporary table t1
+(a int, b char(100), c char(100)) engine = innodb;
+insert into t1 values (1,'a','b');
+set session debug="+d,crash_commit_before";
+insert into t2 values (4,'a','b');
+ERROR HY000: Lost connection to MySQL server during query
+use test;
+show tables;
+Tables_in_test
+t2
+SELECT * from t2;
+a b c
+1 a b
+2 a b
+3 a b
+update t2 set a = a * -1;
+SELECT * from t2 order by a;
+a b c
+-3 a b
+-2 a b
+-1 a b
+SET DEBUG_SYNC='RESET';
+create temporary table t1
+(a int, b char(100), c char(100)) engine = innodb;
+insert into t1 values (1,'a','b');
+set session debug="+d,crash_commit_before";
+insert into t1 values (4,'a','b');
+ERROR HY000: Lost connection to MySQL server during query
+use test;
+show tables;
+Tables_in_test
+t2
+SELECT * from t2;
+a b c
+-1 a b
+-2 a b
+-3 a b
+update t2 set a = a * -1;
+SELECT * from t2 order by a;
+a b c
+1 a b
+2 a b
+3 a b
+drop table t2;
set global innodb_file_format = Antelope;
set global innodb_file_per_table = 1;
=== added file 'mysql-test/suite/innodb/t/innodb_wl6560_1.test'
--- a/mysql-test/suite/innodb/t/innodb_wl6560_1.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb_wl6560_1.test 2013-01-07 07:01:27 +0000
@@ -0,0 +1,399 @@
+#
+# WL#6560: InnoDB: separate tablespace for innodb-temp-tables.
+#
+
+--source include/have_innodb.inc
+#--source include/have_debug.inc
+# Embedded server does not support crashing
+--source include/not_embedded.inc
+
+call mtr.add_suppression("Please add another temp-data file or use 'autoextend' for the last data file");
+call mtr.add_suppression("The table 't1' is full");
+
+################################################################################
+#
+# Will test following scenarios:
+
+#
+# 1. ddl + dml operation involving temp-tablespace.
+# a) insert/delete/update/transacion/select
+# b) create index/drop index/add column/drop column
+# c) alter inplace/copy on temp table
+
+# 2. ddl + dml operation on temp table from 2 sessions.
+# a) Both session create 20 tables each of same name.
+# b) No error expected across session as temp tables are not be
+# visible across session.
+# c) create drop same table multiple time
+
+# 3. Temp table operation -
+# a) can not create temp table with partition
+# b) can not create/drop view on temp tables.
+# c) select/join on temp tables.
+# d) Lock/flush on tables
+# e) create temp table as select
+
+
+# 4. Temp table operation -
+# a) restart on existing datadir and see ibtmp1 removed on clean shutdown
+# b) check temp tablesace created in 1st tmpdir if multiple specified
+#
+
+################################################################################
+
+#-----------------------------------------------------------------------------
+#
+let $per_table = `select @@innodb_file_per_table`;
+let $format = `select @@innodb_file_format`;
+
+set global innodb_file_per_table = off;
+let $MYSQL_DATA_DIR = `select @@datadir`;
+let $MYSQL_TMP_DIR = `select @@tmpdir`;
+
+#-----------------------------------------------------------------------------
+#
+# 1. ddl + dml operation involving temp-tablespace.
+# a) insert/delete/update/transacion/select
+# b) create index/drop index/add column/drop column
+# c) alter inplace/copy on temp table
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+SELECT @@global.innodb_file_per_table;
+--replace_column 1 NUM
+SELECT @mycount_SYS_DATAFILES:=COUNT(*) FROM information_schema.INNODB_SYS_DATAFILES;
+CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+
+--echo # creation of shared temp-tablespace in MYSQL_DATA_DIR
+--list_files $MYSQL_TMP_DIR/ ib*tmp1*
+
+--echo # check no entries made INNODB_SYS_DATAFILES
+SELECT @mycount_SYS_DATAFILES=COUNT(*) FROM information_schema.INNODB_SYS_DATAFILES;
+
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+
+# DML+TRANSACTION
+START TRANSACTION;
+INSERT INTO t1(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
+REPEAT('a2',50));
+INSERT INTO t1(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
+REPEAT('a3',50));
+UPDATE t1 SET e = REPEAT('ta',50);
+DELETE FROM t1 WHERE a = 3;
+SAVEPOINT A;
+INSERT INTO t1(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
+REPEAT('a4',50));
+UPDATE t1 SET e = REPEAT('yb',50);
+DELETE FROM t1 WHERE a = 1;
+SAVEPOINT B;
+SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+ROLLBACK TO SAVEPOINT A;
+COMMIT;
+SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+
+# DDL
+ALTER TABLE t1 ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
+SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10),RIGHT(f,10) FROM t1;
+--error 1845
+ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=INPLACE;
+ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
+SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+# drop table
+DROP TABLE t1;
+
+
+#-----------------------------------------------------------------------------
+#
+# 2. ddl + dml operation on temp table from 2 sessions.
+# a) Both session create 20 tables each of same name.
+# b) No error expected across session as temp tables are not be
+# visible across session.
+# c) create drop same table multiple time
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP DATABASE IF EXISTS test_db;
+--enable_warnings
+# check by allowing file per table on
+set global innodb_file_per_table = on;
+SELECT @@global.innodb_file_per_table;
+let $counter= 2;
+
+CREATE DATABASE test_db;
+
+--echo "Create con1 client connection"
+connect (con1,localhost,root,,);
+
+--disable_query_log
+while ($counter<23) {
+# create table from con1 connection
+connection con1;
+USE test_db;
+EVAL CREATE TEMPORARY TABLE t$counter (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+EVAL INSERT INTO t$counter(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+START TRANSACTION;
+EVAL INSERT INTO t$counter(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
+REPEAT('a2',50));
+EVAL INSERT INTO t$counter(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
+REPEAT('a3',50));
+EVAL UPDATE t$counter SET e = REPEAT('ta',50);
+EVAL DELETE FROM t$counter WHERE a = 3;
+SAVEPOINT A;
+EVAL INSERT INTO t$counter(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
+REPEAT('a4',50));
+EVAL UPDATE t$counter SET e = REPEAT('yb',50);
+EVAL DELETE FROM t$counter WHERE a = 1;
+SAVEPOINT B;
+ROLLBACK TO SAVEPOINT A;
+COMMIT;
+EVAL ALTER TABLE t$counter ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
+EVAL ALTER TABLE t$counter DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
+
+# create same table from default connection
+connection default;
+USE test_db;
+EVAL CREATE TEMPORARY TABLE t$counter (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+EVAL INSERT INTO t$counter(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+START TRANSACTION;
+EVAL INSERT INTO t$counter(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
+REPEAT('a2',50));
+EVAL INSERT INTO t$counter(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
+REPEAT('a3',50));
+EVAL UPDATE t$counter SET e = REPEAT('ta',50);
+EVAL DELETE FROM t$counter WHERE a = 3;
+SAVEPOINT A;
+EVAL INSERT INTO t$counter(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
+REPEAT('a4',50));
+EVAL UPDATE t$counter SET e = REPEAT('yb',50);
+EVAL DELETE FROM t$counter WHERE a = 1;
+SAVEPOINT B;
+ROLLBACK TO SAVEPOINT A;
+COMMIT;
+EVAL ALTER TABLE t$counter ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
+EVAL ALTER TABLE t$counter DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
+
+# create and drop same table in loop
+CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+START TRANSACTION;
+INSERT INTO t1(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
+REPEAT('a2',50));
+INSERT INTO t1(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
+REPEAT('a3',50));
+UPDATE t1 SET e = REPEAT('ta',50);
+DELETE FROM t1 WHERE a = 3;
+SAVEPOINT A;
+INSERT INTO t1(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
+REPEAT('a4',50));
+UPDATE t1 SET e = REPEAT('yb',50);
+DELETE FROM t1 WHERE a = 1;
+SAVEPOINT B;
+ROLLBACK TO SAVEPOINT A;
+COMMIT;
+ALTER TABLE t1 ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
+ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
+DROP TABLE t1;
+
+# check separate ibd file is not created
+--list_files $MYSQL_DATA_DIR/test_db/ *ibd
+--inc $counter
+}
+
+
+DROP DATABASE test_db;
+USE test;
+--enable_query_log
+--echo # creation of shared temp-tablespace in MYSQL_DATA_DIR
+--list_files $MYSQL_TMP_DIR/ ib*tmp1*
+
+#-----------------------------------------------------------------------------
+#
+# 3. Temp table operation -
+# a) can not create temp table with partition
+# b) can not create/drop view on temp tables.
+# c) select/join on temp tables.
+# d) Lock/flush on tables
+# e) create temp table as select
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2,t3,t4;
+--enable_warnings
+# check by allowing file per table on
+SELECT @@global.innodb_file_per_table;
+USE test;
+
+CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT, c BLOB(100),
+d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+
+# Can not create temp table with partition
+--ERROR 1562
+CREATE TEMPORARY TABLE t3 (val INT) ENGINE = InnoDB PARTITION BY LIST(val)(
+ PARTITION mypart1 VALUES IN (1,3,5),
+ PARTITION MyPart2 VALUES IN (2,4,6)
+ );
+
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('t1',50),REPEAT('t1',50),
+REPEAT('a2',50));
+INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('t2',50),REPEAT('t2',50),
+REPEAT('a2',50));
+
+# Check with join of
+SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c LIKE t2.c;
+SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
+
+# you cannot create a TEMPORARY view
+--ERROR 1352
+CREATE OR REPLACE VIEW vw1 AS SELECT * FROM t1;
+
+# Check with create temp as <select>
+CREATE TEMPORARY TABLE t3 AS
+SELECT t1.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2
+WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
+SELECT * FROM t3;
+CREATE TABLE t4 AS
+SELECT t1.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2
+WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
+SELECT * FROM t4;
+# join on temp and non temp table
+SELECT * FROM t3,t4 WHERE t3.a=t4.a;
+SELECT * FROM t3,t4 WHERE t3.a!=t4.a;
+
+#Acquire lock and flush
+LOCK TABLES t1 READ;
+SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c LIKE t2.c;
+SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
+# delete records even type is read as lock table is ignored for temp tables
+DELETE FROM t1;
+SELECT * FROM t1;
+UNLOCK TABLES;
+LOCK TABLES t2 WRITE,t3 WRITE,t4 WRITE;
+DELETE FROM t2 WHERE t2.c LIKE REPEAT('t2',50);
+SELECT t2.a,RIGHT(t2.c,10) FROM t2;
+DELETE FROM t3;
+DELETE FROM t4;
+SELECT * FROM t3,t4;
+UNLOCK TABLES;
+
+--list_files $MYSQL_TMP_DIR/ ib*tmp1*
+--list_files $MYSQL_DATA_DIR/test/ t4*ibd*
+DROP TABLE t1,t2,t3,t4;
+
+
+#-----------------------------------------------------------------------------
+#
+# 4. Temp table operation -
+# a) restart on existing datadir and see ibtmp1 removed on clean shutdown
+# b) check temp tablesace created in 1st tmpdir if multiple specified
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+# check by allowing file per table on
+SELECT @@global.innodb_file_per_table;
+USE test;
+
+CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+
+
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+UPDATE t1 SET a = a + 5;
+DELETE FROM t1 WHERE a > 6;
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+
+
+INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
+UPDATE t2 SET a = a + 5;
+DELETE FROM t2 WHERE a > 6;
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
+
+--echo # restart server
+--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--shutdown_server 10
+--source include/wait_until_disconnected.inc
+# Do something while server is down
+--echo # list_file ,no files expected - temp tablespace cleared on stopping server
+--list_files $MYSQL_TMP_DIR/ ib*tmp1*
+
+--enable_reconnect
+--mkdir $MYSQLTEST_VARDIR/tmp/testdir1
+--mkdir $MYSQLTEST_VARDIR/tmp/testdir2
+# # Write file to make mysql-test-run.pl start up the server again
+--exec echo "restart: --tmpdir=$MYSQLTEST_VARDIR/tmp/testdir1/:$MYSQLTEST_VARDIR/tmp/testdir2 " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--source include/wait_until_connected_again.inc
+--echo # list_file ,temp tablespace goes into 1st tmpdir if mutiple tmpdir listed
+--list_files $MYSQLTEST_VARDIR/tmp/testdir1/ ib*tmp1*
+--echo # list_file ,temp tablespace does not go into 2nd tmp dir
+--list_files $MYSQLTEST_VARDIR/tmp/testdir2/ ib*tmp1*
+#
+USE test;
+# check not temp table
+SHOW TABLES;
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
+UPDATE t2 SET a = a + 5;
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
+
+
+# check temp table after restart
+CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
+c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
+
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
+REPEAT('a1',50));
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+UPDATE t1 SET a = a + 5;
+DELETE FROM t1 WHERE a > 6;
+SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
+DROP TABLE t1;
+
+--echo # restart server
+--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--shutdown_server 10
+--source include/wait_until_disconnected.inc
+# Do something while server is down
+ --enable_reconnect
+# # # Write file to make mysql-test-run.pl start up the server again
+ --exec echo "restart: " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+ --source include/wait_until_connected_again.inc
+
+ #
+ USE test;
+ # check not temp table
+ SHOW TABLES;
+
+#-----------------------------------------------------------------------------
+#
+# remove test-bed/cleanup
+#
+eval set global innodb_file_format = $format;
+eval set global innodb_file_per_table = $per_table;
+
+
+#-----------------------------------------------------------------------------
+#
+# remove test-bed/cleanup
+#
+eval set global innodb_file_format = $format;
+eval set global innodb_file_per_table = $per_table;
=== modified file 'mysql-test/suite/innodb/t/innodb_wl6560_debug.test'
--- a/mysql-test/suite/innodb/t/innodb_wl6560_debug.test 2012-12-28 07:12:47 +0000
+++ b/mysql-test/suite/innodb/t/innodb_wl6560_debug.test 2013-01-07 07:01:27 +0000
@@ -74,6 +74,7 @@ drop table t1;
set session debug="+d,ib_ddl_crash_during_create";
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+set session debug="+d,ib_ddl_crash_during_create";
--error 2013
create temporary table t1
(a int, b int, primary key(a), index(b)) engine = innodb;
@@ -91,6 +92,140 @@ drop table t1;
#-----------------------------------------------------------------------------
#
+# 3. hit a crash point during tablespace expansion.
+#
+set session debug="-d,ib_ddl_crash_during_create";
+SET DEBUG_SYNC='RESET';
+use test;
+create temporary table t1
+ (a int, b char(100), c char(100)) engine = innodb;
+create table t2
+ (a int, b char(100), c char(100)) engine = innodb;
+
+delimiter |;
+CREATE PROCEDURE populate_t1_t2()
+BEGIN
+ DECLARE i INT DEFAULT 1;
+ while (i <= 5000) DO
+ insert into t1 values (i, 'a', 'b');
+ insert into t2 values (i, 'a', 'b');
+ SET i = i + 1;
+ END WHILE;
+END|
+delimiter ;|
+set autocommit=0;
+--disable_query_log
+call populate_t1_t2();
+--echo # set debug point ib_crash_during_tablespace_extension
+--enable_query_log
+set session debug="+d,ib_crash_during_tablespace_extension";
+select count(*) from t1;
+select count(*) from t2;
+commit;
+show tables;
+select count(*) from t1;
+select count(*) from t2;
+--error 2013
+call populate_t1_t2();
+
+#
+--enable_reconnect
+# Write file to make mysql-test-run.pl start up the server again
+--exec echo "restart " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--source include/wait_until_connected_again.inc
+#
+use test;
+show tables;
+
+select count(*) from t2;
+select * from t2 limit 10;
+set autocommit=1;
+truncate table t2;
+select count(*) from t2;
+#
+drop procedure populate_t1_t2;
+drop table t2;
+#
+
+#-----------------------------------------------------------------------------
+#
+# 3. hit a crash point while appying log
+#
+SET DEBUG_SYNC='RESET';
+use test;
+create temporary table t1
+ (a int, b char(100), c char(100)) engine = innodb;
+create table t2
+ (a int, b char(100), c char(100)) engine = innodb;
+
+insert into t1 values (1,'a','b');
+insert into t2 values (1,'a','b');
+SELECT * from t1;
+SELECT * from t2;
+set session debug="+d,crash_commit_after";
+--error 2013
+insert into t2 values (2,'a','b');
+
+#
+--enable_reconnect
+# Write file to make mysql-test-run.pl start up the server again
+--exec echo "restart " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--source include/wait_until_connected_again.inc
+#
+use test;
+show tables;
+SELECT * from t2;
+insert into t2 values (3,'a','b');
+SELECT * from t2;
+
+SET DEBUG_SYNC='RESET';
+create temporary table t1
+ (a int, b char(100), c char(100)) engine = innodb;
+insert into t1 values (1,'a','b');
+set session debug="+d,crash_commit_before";
+--error 2013
+insert into t2 values (4,'a','b');
+
+#
+--enable_reconnect
+# Write file to make mysql-test-run.pl start up the server again
+--exec echo "restart " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--source include/wait_until_connected_again.inc
+##
+use test;
+show tables;
+SELECT * from t2;
+update t2 set a = a * -1;
+SELECT * from t2 order by a;
+#
+
+SET DEBUG_SYNC='RESET';
+create temporary table t1
+ (a int, b char(100), c char(100)) engine = innodb;
+insert into t1 values (1,'a','b');
+set session debug="+d,crash_commit_before";
+# crash on temp table
+--error 2013
+insert into t1 values (4,'a','b');
+
+#
+--enable_reconnect
+# Write file to make mysql-test-run.pl start up the server again
+--exec echo "restart " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--source include/wait_until_connected_again.inc
+##
+use test;
+show tables;
+SELECT * from t2;
+update t2 set a = a * -1;
+SELECT * from t2 order by a;
+#
+drop table t2;
+#
+
+
+#-----------------------------------------------------------------------------
+#
# remove test-bed
#
eval set global innodb_file_format = $format;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (vinay.fisrekar:4865 to 4866) | Vinay Fisrekar | 28 Jan 2013 |
