Implementation of MDEV-5491: CREATE OR REPLACE TABLE

Using CREATE OR REPLACE TABLE is be identical to

DROP TABLE IF EXISTS table_name;
CREATE TABLE ...;

Except that:

* CREATE OR REPLACE is be atomic (now one can create the same table between drop and create).
* Temporary tables will not shadow the table name for the DROP as the CREATE TABLE tells us already if we are using a temporary table or not.
* If the table was locked with LOCK TABLES, the new table will be locked with the same lock after it's created.

Implementation details:
- We don't anymore open the to-be-created table during CREATE TABLE, which the original code did.
  - There is no need to open a table we are planning to create. It's enough to check if the table exists or not.
- Removed some of duplicated code for CREATE IF NOT EXISTS.
- Give an error when using CREATE OR REPLACE with IF NOT EXISTS (conflicting options).
- As a side effect of the code changes, we don't anymore have to internally re-prepare prepared statements with CREATE TABLE if the table exists.
- Made one code path for all testing if log table are in use.
- Better error message if one tries to create/drop/alter a log table in use
- Added back disabled rpl_row_create_table test as it now seams to work and includes a lot of interesting tests.
- Added HA_LEX_CREATE_REPLACE to mark if we are using CREATE OR REPLACE
- Aligned CREATE OR REPLACE parsing code in sql_yacc.yy for TABLE and VIEW
- Changed interface for drop_temporary_table() to make it more reusable
- Changed Locked_tables_list::init_locked_tables() to work on the table object instead of the table list object. Before this it used a mix of both, which was not good.
- Locked_tables_list::unlock_locked_tables(THD *thd) now requires a valid thd argument. Old usage of calling this with 0 i changed to instead call Locked_tables_list::reset()
- Added functions Locked_tables_list:restore_lock() and Locked_tables_list::add_back_last_deleted_lock() to be able to easily add back a locked table to the lock list.
- Added restart_trans_for_tables() to be able to restart a transaction.
- DROP_ACL is required if one uses CREATE TABLE OR REPLACE.
- Added drop of normal and temporary tables in create_table_imp() if CREATE OR REPLACE was used.
- Added reacquiring of table locks in mysql_create_table() and mysql_create_like_table()




mysql-test/include/commit.inc:
  With new code we get fewer status increments
mysql-test/r/commit_1innodb.result:
  With new code we get fewer status increments
mysql-test/r/create.result:
  Added testing of create or replace with timeout
mysql-test/r/create_or_replace.result:
  Basic testing of CREATE OR REPLACE TABLE
mysql-test/r/partition_exchange.result:
  New error message
mysql-test/r/ps_ddl.result:
  Fewer reprepares with new code
mysql-test/suite/archive/discover.result:
  Don't rediscover archive tables if the .frm file exists
  (Sergei will look at this if there is a better way...)
mysql-test/suite/archive/discover.test:
  Don't rediscover archive tables if the .frm file exists
  (Sergei will look at this if there is a better way...)
mysql-test/suite/funcs_1/r/innodb_views.result:
  New error message
mysql-test/suite/funcs_1/r/memory_views.result:
  New error message
mysql-test/suite/rpl/disabled.def:
  rpl_row_create_table should now be safe to use
mysql-test/suite/rpl/r/rpl_row_create_table.result:
  Updated results after adding back disabled test
mysql-test/suite/rpl/t/rpl_create_if_not_exists.test:
  Added comment
mysql-test/suite/rpl/t/rpl_row_create_table.test:
  Added CREATE OR REPLACE TABLE test
mysql-test/t/create.test:
  Added CREATE OR REPLACE TABLE test
mysql-test/t/create_or_replace-master.opt:
  Create logs
mysql-test/t/create_or_replace.test:
  Basic testing of CREATE OR REPLACE TABLE
mysql-test/t/partition_exchange.test:
  Error number changed as we are now using same code for all log table change issues
mysql-test/t/ps_ddl.test:
  Fewer reprepares with new code
sql/handler.h:
  Moved things around a bit in a structure to get better alignment.
  Added HA_LEX_CREATE_REPLACE to mark if we are using CREATE OR REPLACE
  Added 3 elements to end of HA_CREATE_INFO to be able to store state to add backs locks in case of LOCK TABLES.
sql/log.cc:
  Reimplemented check_if_log_table():
  - Simpler and faster usage
  - Can give error messages
  
  This gives us one code path for allmost all error messages if log tables are in use
sql/log.h:
  New interface for check_if_log_table()
sql/slave.cc:
  More logging
sql/sql_alter.cc:
  New interface for check_if_log_table()
sql/sql_base.cc:
  More documentation
  Changed interface for drop_temporary_table() to make it more reusable
  Changed Locked_tables_list::init_locked_tables() to work on the table object instead of the table list object. Before this it used a mix of both, which was not good.
  Locked_tables_list::unlock_locked_tables(THD *thd) now requires a valid thd argument.  Old usage of calling this with 0 i changed to instead call Locked_tables_list::reset()
  Added functions Locked_tables_list:restore_lock() and Locked_tables_list::add_back_last_deleted_lock() to be able to easily add back a locked table to the lock list.
  Check for command number instead of open_strategy of CREATE TABLE was used.
  Added restart_trans_for_tables() to be able to restart a transaction.  This was needed in "create or replace ... select" between the drop table and the select.
sql/sql_base.h:
  Added and updated function prototypes
sql/sql_class.h:
  Added new prototypes to Locked_tables_list class
  Added extra argument to select_create to avoid double call to eof() or send_error()
  - I needed this in some edge case where the table was not created against expections.
sql/sql_db.cc:
  New interface for check_if_log_table()
sql/sql_insert.cc:
  Remember position to lock information so that we can reaquire table lock for LOCK TABLES + CREATE OR REPLACE TABLE SELECT. Later add back the lock by calling restore_lock().
  Removed one not needed indentation level in create_table_from_items()
  Ensure we don't call send_eof() or abort_result_set() twice.
sql/sql_lex.h:
  Removed variable that I temporarly added in an earlier changeset
sql/sql_parse.cc:
  Removed old test code (marked with QQ)
  Ensure that we have open_strategy set as TABLE_LIST::OPEN_STUB in CREATE TABLE
  Removed some IF NOT EXISTS code as this is now handled in create_table_table_impl().
  Set OPTION_KEEP_LOGS later. This code had to be moved as the test for IF EXISTS has changed place.
  DROP_ACL is required if one uses CREATE TABLE OR REPLACE.
sql/sql_partition_admin.cc:
  New interface for check_if_log_table()
sql/sql_rename.cc:
  New interface for check_if_log_table()
sql/sql_table.cc:
  New interface for check_if_log_table()
  Moved some code in mysql_rm_table() under a common test.
  - Safe as temporary tables doesn't have statistics.
  - !is_temporary_table(table) test was moved out from drop_temporary_table() and merged with upper level code.
  - Added drop of normal and temporary tables in create_table_imp() if CREATE OR REPLACE was used.
  - Added reacquiring of table locks in mysql_create_table() and mysql_create_like_table()
  - In mysql_create_like_table(), restore table->open_strategy() if it was changed.
  - Re-test if table was a view after opening it.
sql/sql_table.h:
  New prototype for mysql_create_table_no_lock()
sql/sql_yacc.yy:
  Added syntax for CREATE OR REPLACE TABLE
  Reuse new code for CREATE OR REPLACE VIEW
sql/table.h:
  Added name for enum type
sql/table_cache.cc:
  More DBUG

5 files changed, 241 insertions, 4 deletions

diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index ebcad5f4af4..8bb7339ce83 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -2014,6 +2014,8 @@ create table t1 (a int, b int);
 create table t1 (a int, b int) select 2,2;
 --error ER_TABLE_EXISTS_ERROR
 create table t1 like t2;
+--error ER_LOCK_WAIT_TIMEOUT
+create or replace table t1 (a int, b int) select 2,2;
 disconnect user1;
 connection default;
 select * from t1;
diff --git a/mysql-test/t/create_or_replace-master.opt b/mysql-test/t/create_or_replace-master.opt
new file mode 100644
index 00000000000..e94228f2f33
--- /dev/null
+++ b/mysql-test/t/create_or_replace-master.opt
@@ -0,0 +1 @@
+--log-output=TABLE,FILE --general-log=1 --slow-query-log=1
diff --git a/mysql-test/t/create_or_replace.test b/mysql-test/t/create_or_replace.test
new file mode 100644
index 00000000000..b776be23b08
--- /dev/null
+++ b/mysql-test/t/create_or_replace.test
@@ -0,0 +1,234 @@
+#
+# Check CREATE OR REPLACE ALTER TABLE
+#
+
+--source include/have_innodb.inc
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+#
+# Create help table
+#
+
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES(1),(2),(3);
+
+--echo #
+--echo # Check first syntax and wrong usage
+--echo #
+
+--error ER_WRONG_USAGE
+CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int);
+--error ER_WRONG_USAGE
+create or replace trigger trg before insert on t1 for each row set @a:=1;
+
+# check that we don't try to create a log table in use
+--error ER_BAD_LOG_STATEMENT
+create or replace table mysql.general_log (a int);
+--error ER_BAD_LOG_STATEMENT
+create or replace table mysql.slow_log (a int);
+
+--echo #
+--echo # Usage when table doesn't exist
+--echo #
+
+CREATE OR REPLACE TABLE t1 (a int);
+--error ER_TABLE_EXISTS_ERROR
+CREATE TABLE t1 (a int);
+DROP TABLE t1;
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
+--error ER_TABLE_EXISTS_ERROR
+CREATE TEMPORARY TABLE t1 (a int, b int, c int);
+DROP TEMPORARY TABLE t1;
+
+--echo #
+--echo # Testing with temporary tables
+--echo #
+
+CREATE OR REPLACE TABLE t1 (a int);
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int);
+SHOW CREATE TABLE t1;
+DROP TEMPORARY TABLE t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+# Test also with InnoDB
+create temporary table t1 (i int) engine=InnoDB;
+create or replace temporary table t1 (a int, b int) engine=InnoDB;
+create or replace temporary table t1 (j int);
+show create table t1;
+drop table t1;
+
+# Using lock tables on normal tables with create or replace on temp tables
+CREATE OR REPLACE TABLE t1 (a int);
+LOCK TABLES t1 write;
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int);
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine= innodb;
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int) engine= innodb;
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine=myisam;
+SHOW CREATE TABLE t1;
+DROP TEMPORARY TABLE t1;
+SHOW CREATE TABLE t1;
+# Verify that table is still locked
+--error ER_TABLE_NOT_LOCKED
+CREATE OR REPLACE TABLE t2 (a int);
+DROP TABLE t1;
+UNLOCK TABLES;
+
+#
+# Using CREATE SELECT
+#
+
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int) SELECT * from t2;
+SELECT * FROM t1;
+CREATE OR REPLACE TEMPORARY TABLE t1 (b int) SELECT * from t2;
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+CREATE TEMPORARY TABLE t1 AS SELECT a FROM t2;
+CREATE TEMPORARY TABLE IF NOT EXISTS t1(a int, b int) SELECT 1,2 FROM t2;
+create or replace table t1 as select 1;
+show create table t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Testing with normal tables
+--echo #
+
+CREATE OR REPLACE TABLE t1 (a int);
+CREATE OR REPLACE TABLE t1 (a int, b int);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a int) SELECT * from t2;
+SELECT * FROM t1;
+TRUNCATE TABLE t1;
+CREATE TABLE IF NOT EXISTS t1 (a int) SELECT * from t2;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i int);
+CREATE OR REPLACE TABLE t1 AS SELECT 1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+# Using lock tables with CREATE OR REPLACE
+CREATE OR REPLACE TABLE t1 (a int);
+LOCK TABLES t1 write,t2 write;
+CREATE OR REPLACE TABLE t1 (a int, b int);
+# Verify if table is still locked
+SELECT * FROM t1;
+INSERT INTO t1 values(1,1);
+CREATE OR REPLACE TABLE t1 (a int, b int, c int);
+INSERT INTO t1 values(1,1,1);
+--error ER_TABLE_NOT_LOCKED
+CREATE OR REPLACE TABLE t3 (a int);
+UNLOCK TABLES;
+DROP TABLE t1;
+
+# Using lock tables with CREATE OR REPLACE ... SELECT
+CREATE OR REPLACE TABLE t1 (a int);
+LOCK TABLES t1 write,t2 write;
+CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2;
+# Verify if table is still locked
+SELECT * FROM t2;
+SELECT * FROM t1;
+SELECT * FROM t1;
+INSERT INTO t1 values(1,1,1);
+CREATE OR REPLACE TABLE t1 (a int, b int, c int, d int);
+INSERT INTO t1 values(1,1,1,1);
+--error ER_TABLE_NOT_LOCKED
+CREATE OR REPLACE TABLE t3 (a int);
+UNLOCK TABLES;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a int);
+LOCK TABLES t1 write,t2 write, t1 as t1_read read;
+CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2;
+SELECT * FROM t1;
+SELECT * FROM t2;
+--error ER_TABLE_NOT_LOCKED
+SELECT * FROM t1 as t1_read;
+DROP TABLE t1;
+UNLOCK TABLES;
+
+--echo #
+--echo # Test also with InnoDB (transactional engine)
+--echo #
+
+create table t1 (i int) engine=innodb;
+lock table t1 write;
+create or replace table t1 (j int);
+unlock tables;
+show create table t1;
+drop table t1;
+
+create table t1 (i int) engine=InnoDB;
+lock table t1 write, t2 write;
+create or replace table t1 (j int) engine=innodb;
+unlock tables;
+drop table t1;
+
+create table t1 (i int) engine=InnoDB;
+create table t3 (i int) engine=InnoDB;
+insert into t3 values(1),(2),(3);
+lock table t1 write, t2 write, t3 write;
+create or replace table t1 (a int, i int) engine=innodb select t2.a,t3.i from t2,t3;
+unlock tables;
+select * from t1 order by a,i;
+drop table t1,t3;
+
+--echo #
+--echo # Testing CREATE .. LIKE
+--echo #
+
+create or replace table t1 like t2;
+create or replace table t1 like t2;
+show create table t1;
+drop table t1;
+create table t1 (b int);
+lock tables t1 write, t2 read;
+create or replace table t1 like t2;
+SELECT * FROM t1;
+INSERT INTO t1 values(1);
+CREATE OR REPLACE TABLE t1 like t2;
+INSERT INTO t1 values(2);
+unlock tables;
+show create table t1;
+drop table t1;
+
+--echo #
+--echo # Test with prepared statements
+--echo #
+
+prepare stmt1 from 'create or replace table t1 select * from t2';
+execute stmt1;
+select * from t1;
+execute stmt1;
+select * from t1;
+drop table t1;
+execute stmt1;
+select * from t1;
+deallocate prepare stmt1;
+drop table t1;
+
+--echo #
+--echo # Test with views
+--echo #
+
+create view t1 as select 1;
+create table if not exists t1 (a int);
+--error ER_BAD_TABLE_ERROR
+create or replace table t1 (a int);
+--error ER_BAD_TABLE_ERROR
+drop table t1;
+drop view t1;
+
+#
+# Cleanup
+#
+DROP TABLE t2;
diff --git a/mysql-test/t/partition_exchange.test b/mysql-test/t/partition_exchange.test
index d7dfd6f543e..e538bee16cd 100644
--- a/mysql-test/t/partition_exchange.test
+++ b/mysql-test/t/partition_exchange.test
@@ -439,7 +439,7 @@ CREATE TABLE t LIKE general_log;
 ALTER TABLE t PARTITION BY RANGE (UNIX_TIMESTAMP(event_time) DIV 1)
 (PARTITION p0 VALUES LESS THAN (123456789),
  PARTITION pMAX VALUES LESS THAN MAXVALUE);
---error ER_WRONG_USAGE
+--error ER_BAD_LOG_STATEMENT
 ALTER TABLE t EXCHANGE PARTITION p0 WITH TABLE general_log;
 ALTER TABLE general_log ENGINE = CSV;
 SET @@global.general_log = @old_general_log_state;
diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test
index c34800976c7..21355ca42b7 100644
--- a/mysql-test/t/ps_ddl.test
+++ b/mysql-test/t/ps_ddl.test
@@ -1610,7 +1610,7 @@ call p_verify_reprepare_count(0);
 # Base table with name of table to be created exists
 --error ER_TABLE_EXISTS_ERROR
 execute stmt;
-call p_verify_reprepare_count(1);
+call p_verify_reprepare_count(0);
 --error ER_TABLE_EXISTS_ERROR
 execute stmt;
 call p_verify_reprepare_count(0);
@@ -1622,7 +1622,7 @@ execute stmt;
 call p_verify_reprepare_count(0);
 --error ER_TABLE_EXISTS_ERROR
 execute stmt;
-call p_verify_reprepare_count(1);
+call p_verify_reprepare_count(0);
 drop temporary table t2;
 --error ER_TABLE_EXISTS_ERROR
 execute stmt;
@@ -1641,7 +1641,7 @@ drop table t2;
 create view t2 as select 1;
 --error ER_TABLE_EXISTS_ERROR,9999
 execute stmt;
-call p_verify_reprepare_count(1);
+call p_verify_reprepare_count(0);
 --error ER_TABLE_EXISTS_ERROR,9999
 execute stmt;
 call p_verify_reprepare_count(0);