List:Commits« Previous MessageNext Message »
From:Vinay Fisrekar Date:January 7 2013 7:04am
Subject:bzr push into mysql-trunk branch (vinay.fisrekar:4865 to 4866)
View as plain text  
 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 Fisrekar28 Jan