BUG#6883: Added tests for create/drop temporary table, UNLOCK TABLES

If a create table can not do implicit commit, the stmt now fails
CREATE/DROP TEMPORARY TABLE is now flushed to binlog


mysql-test/include/rpl_stmt_seq.inc:
  Documentation, cleared up code
mysql-test/r/rpl_ddl.result:
  New results
mysql-test/t/rpl_ddl.test:
  Added tests for create/drop temporary table
  Added tests for different types of locks in UNLOCK TABLES
  Cleared up code/documentation
sql/sql_parse.cc:
  If A CREATE TABLE fails to do implicit commit, then the stmt now fails (DROP works in same way)
  CREATE/DROP TEMOPORARY TABLE is now flushed to binlog

1 files changed, 187 insertions, 111 deletions

diff --git a/mysql-test/t/rpl_ddl.test b/mysql-test/t/rpl_ddl.test
index a99a71a841b..d9b09d397b5 100644
--- a/mysql-test/t/rpl_ddl.test
+++ b/mysql-test/t/rpl_ddl.test
@@ -1,4 +1,4 @@
-######################## rpl-ddl.test ########################
+######################## rpl_ddl.test ########################
 #                                                            #
 # DDL statements (sometimes with implicit COMMIT) executed   #
 # by the master and it's propagation into the slave          #
@@ -28,89 +28,65 @@
 #         eval SELECT CONCAT('######## ','$my_stmt','  ########') as "";
 #
 
-######## tested DDL commands
-#
-# let $my_stmt= COMMIT;
-# let $my_stmt= ROLLBACK;
-# let $my_stmt= SET AUTOCOMMIT=1;
-# let $my_stmt= START TRANSACTION;
-# let $my_stmt= BEGIN;
-# let $my_stmt= DROP TABLE d1.t2;
-# let $my_stmt= RENAME TABLE d1.t3 to d1.t20; <- wrong syntax !!
-# let $my_stmt= ALTER TABLE d1.t4 ADD column f2 BIGINT;
-# let $my_stmt= CREATE TABLE d1.t21 (f1 BIGINT) ENGINE= "InnoDB";
-# let $my_stmt= TRUNCATE TABLE d1.t7;
-# let $my_stmt= LOCK TABLES d1.t1 WRITE, d1.t8 READ;
-# let $my_stmt= UNLOCK TABLES;
-# let $my_stmt= DROP INDEX my_idx6 ON d1.t6;
-# let $my_stmt= CREATE INDEX my_idx5 ON d1.t5(f1);
-# let $my_stmt= DROP DATABASE d2;
-# let $my_stmt= CREATE DATABASE d3;
-#
-# FIXME: @code{LOAD MASTER DATA} is not tested
-#
-#############################################
-
 --source include/have_innodb.inc
-source include/master-slave.inc;
+--source include/master-slave.inc
 
-
-######## some preparations
-#
+###############################################################
+# Some preparations
+###############################################################
 SET AUTOCOMMIT = 1;
 #
 # 1. DROP all objects, which probably already exist, but must be created here
 #
 --disable_warnings
-DROP DATABASE IF EXISTS d1;
-DROP DATABASE IF EXISTS d2;
-DROP DATABASE IF EXISTS d3;
+DROP DATABASE IF EXISTS mysqltest1;
+DROP DATABASE IF EXISTS mysqltest2;
+DROP DATABASE IF EXISTS mysqltest3;
 --enable_warnings
 #
 # 2. CREATE all objects needed
-#    working database is d1
-#    working (transactional!) is d1.t1 
+#    working database is mysqltest1
+#    working (transactional!) is mysqltest1.t1 
 #
-CREATE DATABASE d1;
-CREATE DATABASE d2;
-CREATE TABLE d1.t1 (f1 BIGINT) ENGINE= "InnoDB";
-INSERT INTO d1.t1 SET f1= 0;
-CREATE TABLE d1.t2 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t3 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t4 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t5 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t6 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE INDEX my_idx6 ON d1.t6(f1);
-CREATE TABLE d1.t7 (f1 BIGINT) ENGINE= "InnoDB";
-INSERT INTO d1.t7 SET f1= 0;
-CREATE TABLE d1.t8 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t9 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t10 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t11 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t12 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t13 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t14 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t15 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t16 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t17 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t18 (f1 BIGINT) ENGINE= "InnoDB";
-CREATE TABLE d1.t19 (f1 BIGINT) ENGINE= "InnoDB";
-#
-COMMIT; # Just to be sure
+CREATE DATABASE mysqltest1;
+CREATE DATABASE mysqltest2;
+CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE= "InnoDB";
+INSERT INTO mysqltest1.t1 SET f1= 0;
+CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE INDEX my_idx6 ON mysqltest1.t6(f1);
+CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE= "InnoDB";
+INSERT INTO mysqltest1.t7 SET f1= 0;
+CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE= "InnoDB";
+CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT);
+
 #
 # 3. master sessions: never do AUTOCOMMIT
-#    slave  sessions: do AUTOCOMMIT, because we want to see fresh values 
-#                      every time
-#              default database is d1
+#    slave  sessions: never do AUTOCOMMIT
+#
 SET AUTOCOMMIT = 0;
-use d1;
+use mysqltest1;
 sync_slave_with_master;
 connection slave;
 --disable_query_log
 SELECT '-------- switch to slave --------' as "";
 --enable_query_log
-SET AUTOCOMMIT = 1;
-use d1;
+SET AUTOCOMMIT = 0;
+use mysqltest1;
 connection master;
 --disable_query_log
 SELECT '-------- switch to master -------' as "";
@@ -122,27 +98,48 @@ SELECT '-------- switch to master -------' as "";
 # sequences are nearly independend of the previous statements. 
 --disable_abort_on_error
 
-###### banal case: (explicit) COMMIT and ROLLBACK
-# just for checking if the test sequence is usable
+###############################################################
+# Banal case: (explicit) COMMIT and ROLLBACK
+# Just for checking if the test sequence is usable
+###############################################################
+
 let $my_stmt= COMMIT;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
+
 let $my_stmt= ROLLBACK;
+let $my_master_commit= false;
+let $my_slave_commit= false;
 --source include/rpl_stmt_seq.inc
 
-###### cases with commands very similar to COMMIT
+###############################################################
+# Cases with commands very similar to COMMIT
+###############################################################
+
 let $my_stmt= SET AUTOCOMMIT=1;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
 SET AUTOCOMMIT=0;
-#
+
 let $my_stmt= START TRANSACTION;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
-#
+
 let $my_stmt= BEGIN;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
 
-###### cases with commands, which create or drop objects
-#### (BASE) TABLES and (UPDATABLE) VIEWs
-let $my_stmt= DROP TABLE d1.t2;
+###############################################################
+# Cases with (BASE) TABLES and (UPDATABLE) VIEWs
+###############################################################
+
+let $my_stmt= DROP TABLE mysqltest1.t2;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
 SHOW TABLES LIKE 't2';
 connection slave;
@@ -154,8 +151,31 @@ connection master;
 --disable_query_log
 SELECT '-------- switch to master -------' as "";
 --enable_query_log
-#
-let $my_stmt= RENAME TABLE d1.t3 to d1.t20;
+
+# Note: Since this test is executed with a skip-innodb slave, the
+# slave incorrectly commits the insert.  One can *not* have InnoDB on
+# master and MyISAM on slave and expect that a transactional rollback
+# after a CREATE TEMPORARY TABLE should work correctly on the slave.
+# For this to work properly the handler on the slave must be able to 
+# handle transactions (e.g. InnoDB or NDB).
+let $my_stmt= DROP TEMPORARY TABLE mysqltest1.t23;
+let $my_master_commit= false;
+let $my_slave_commit= true;
+--source include/rpl_stmt_seq.inc
+SHOW TABLES LIKE 't23';
+connection slave;
+--disable_query_log
+SELECT '-------- switch to slave --------' as "";
+--enable_query_log
+SHOW TABLES LIKE 't23';
+connection master;
+--disable_query_log
+SELECT '-------- switch to master -------' as "";
+--enable_query_log
+
+let $my_stmt= RENAME TABLE mysqltest1.t3 to mysqltest1.t20;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
 SHOW TABLES LIKE 't20';
 connection slave;
@@ -167,102 +187,158 @@ connection master;
 --disable_query_log
 SELECT '-------- switch to master -------' as "";
 --enable_query_log
-#
-let $my_stmt= ALTER TABLE d1.t4 ADD column f2 BIGINT;
+
+let $my_stmt= ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
-describe d1.t4;
+describe mysqltest1.t4;
 connection slave;
 --disable_query_log
 SELECT '-------- switch to slave --------' as "";
 --enable_query_log
-describe d1.t4;
+describe mysqltest1.t4;
 connection master;
 --disable_query_log
 SELECT '-------- switch to master -------' as "";
 --enable_query_log
-#
-let $my_stmt= CREATE TABLE d1.t21 (f1 BIGINT) ENGINE= "InnoDB";
+
+let $my_stmt= CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB";
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
-#
-let $my_stmt= TRUNCATE TABLE d1.t7;
+
+# Note: Since this test is executed with a skip-innodb slave, the
+# slave incorrectly commits the insert.  One can *not* have InnoDB on
+# master and MyISAM on slave and expect that a transactional rollback
+# after a CREATE TEMPORARY TABLE should work correctly on the slave.
+# For this to work properly the handler on the slave must be able to 
+# handle transactions (e.g. InnoDB or NDB).
+let $my_stmt= CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT);
+let $my_master_commit= false;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
-SELECT * FROM d1.t7;
+
+let $my_stmt= TRUNCATE TABLE mysqltest1.t7;
+let $my_master_commit= true;
+let $my_slave_commit= true;
+--source include/rpl_stmt_seq.inc
+SELECT * FROM mysqltest1.t7;
 connection slave;
 --disable_query_log
 SELECT '-------- switch to slave --------' as "";
 --enable_query_log
-SELECT * FROM d1.t7;
+SELECT * FROM mysqltest1.t7;
 connection master;
 --disable_query_log
 SELECT '-------- switch to master -------' as "";
 --enable_query_log
-#
-# MySQL insists in locking d1.t1, because rpl_stmt_seq performs an INSERT into
-# this table.
-let $my_stmt= LOCK TABLES d1.t1 WRITE, d1.t8 READ;
+
+###############################################################
+# Cases with LOCK/UNLOCK
+###############################################################
+
+# MySQL insists in locking mysqltest1.t1, because rpl_stmt_seq performs an
+# INSERT into this table.
+let $my_stmt= LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
 UNLOCK TABLES;
-#
-LOCK TABLES d1.t1 WRITE, d1.t8 READ;
+
+# No prior locking
 let $my_stmt= UNLOCK TABLES;
+let $my_master_commit= false;
+let $my_slave_commit= false;
 --source include/rpl_stmt_seq.inc
-#
-#### INDEXES
-let $my_stmt= DROP INDEX my_idx6 ON d1.t6;
+
+# With prior read locking
+LOCK TABLES mysqltest1.t1 READ;
+let $my_stmt= UNLOCK TABLES;
+let $my_master_commit= false;
+let $my_slave_commit= false;
+--source include/rpl_stmt_seq.inc
+
+# With prior write locking
+LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ;
+let $my_stmt= UNLOCK TABLES;
+let $my_master_commit= true;
+let $my_slave_commit= true;
+--source include/rpl_stmt_seq.inc
+
+###############################################################
+# Cases with INDEXES
+###############################################################
+
+let $my_stmt= DROP INDEX my_idx6 ON mysqltest1.t6;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
-SHOW INDEX FROM d1.t6;
+SHOW INDEX FROM mysqltest1.t6;
 connection slave;
 --disable_query_log
 SELECT '-------- switch to slave --------' as "";
 --enable_query_log
-SHOW INDEX FROM d1.t6;
+SHOW INDEX FROM mysqltest1.t6;
 connection master;
 --disable_query_log
 SELECT '-------- switch to master -------' as "";
 --enable_query_log
-#
-let $my_stmt= CREATE INDEX my_idx5 ON d1.t5(f1);
+
+let $my_stmt= CREATE INDEX my_idx5 ON mysqltest1.t5(f1);
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
-SHOW INDEX FROM d1.t5;
+SHOW INDEX FROM mysqltest1.t5;
 connection slave;
 --disable_query_log
 SELECT '-------- switch to slave --------' as "";
 --enable_query_log
-SHOW INDEX FROM d1.t5;
+SHOW INDEX FROM mysqltest1.t5;
 connection master;
 --disable_query_log
 SELECT '-------- switch to master -------' as "";
 --enable_query_log
 
-#### DATABASES
-let $my_stmt= DROP DATABASE d2;
+###############################################################
+# Cases with DATABASE
+###############################################################
+
+let $my_stmt= DROP DATABASE mysqltest2;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
-SHOW DATABASES LIKE "d2";
+SHOW DATABASES LIKE "mysqltest2";
 connection slave;
 --disable_query_log
 SELECT '-------- switch to slave --------' as "";
 --enable_query_log
-SHOW DATABASES LIKE "d2";
+SHOW DATABASES LIKE "mysqltest2";
 connection master;
 --disable_query_log
 SELECT '-------- switch to master -------' as "";
 --enable_query_log
-#
-let $my_stmt= CREATE DATABASE d3;
+
+let $my_stmt= CREATE DATABASE mysqltest3;
+let $my_master_commit= true;
+let $my_slave_commit= true;
 --source include/rpl_stmt_seq.inc
-SHOW DATABASES LIKE "d3";
+SHOW DATABASES LIKE "mysqltest3";
 connection slave;
 --disable_query_log
 SELECT '-------- switch to slave --------' as "";
 --enable_query_log
-SHOW DATABASES LIKE "d3";
+SHOW DATABASES LIKE "mysqltest3";
 connection master;
 --disable_query_log
 SELECT '-------- switch to master -------' as "";
 --enable_query_log
 
+###############################################################
+# Cleanup
+###############################################################
 --disable_warnings
-DROP DATABASE IF EXISTS d1;
-DROP DATABASE IF EXISTS d2;
-DROP DATABASE IF EXISTS d3;
+DROP DATABASE IF EXISTS mysqltest1;
+DROP DATABASE IF EXISTS mysqltest2;
+DROP DATABASE IF EXISTS mysqltest3;
 --enable_warnings