Fix for:

  Bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT
              with locked tables"
  Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
  Bug #24738 "CREATE TABLE ... SELECT is not isolated properly"
  Bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when
              temporary table exists"

Deadlock occured when one tried to execute CREATE TABLE IF NOT
EXISTS ... SELECT statement under LOCK TABLES which held
read lock on target table.
Attempt to execute the same statement for already existing
target table with triggers caused server crashes.
Also concurrent execution of CREATE TABLE ... SELECT statement
and other statements involving target table suffered from
various races (some of which might've led to deadlocks).
Finally, attempt to execute CREATE TABLE ... SELECT in case
when a temporary table with same name was already present
led to the insertion of data into this temporary table and
creation of empty non-temporary table.
 
All above problems stemmed from the old implementation of CREATE
TABLE ... SELECT in which we created, opened and locked target
table without any special protection in a separate step and not
with the rest of tables used by this statement.
This underminded deadlock-avoidance approach used in server
and created window for races. It also excluded target table
from prelocking causing problems with trigger execution.

The patch solves these problems by implementing new approach to
handling of CREATE TABLE ... SELECT for base tables.
We try to open and lock table to be created at the same time as
the rest of tables used by this statement. If such table does not
exist at this moment we create and place in the table cache special
placeholder for it which prevents its creation or any other usage
by other threads.
We still use old approach for creation of temporary tables.

Note that we have separate fix for 5.0 since there we use slightly
different less intrusive approach.


mysql-test/r/create.result:
  Extended test coverage for CREATE TABLE ... SELECT. In particular added
  tests for bug #24508 "Inconsistent results of CREATE TABLE ... SELECT
  when temporary table exists" and bug #20662 "Infinite loop in CREATE
  TABLE IF NOT EXISTS ... SELECT with locked tables".
mysql-test/r/trigger.result:
  Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT
  and triggers"
mysql-test/t/create.test:
  Extended test coverage for CREATE TABLE ... SELECT. In particular added
  tests for bug #24508 "Inconsistent results of CREATE TABLE ... SELECT
  when temporary table exists" and bug #20662 "Infinite loop in CREATE
  TABLE IF NOT EXISTS ... SELECT with locked tables".
mysql-test/t/trigger.test:
  Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT
  and triggers"
sql/lock.cc:
  Now for creation of name-lock placeholder lock_table_name() uses
  auxiliary function table_cache_insert_placeholder().
sql/mysql_priv.h:
  Removed declaration of non-existing build_table_path() routine.
  The former mysql_create_table_internal() was renamed to
  mysql_create_table_no_lock() and now exposed to other modules to
  give them opportunity of creation of tables in cases when name-lock
  is already obtained.
  reopen_name_locked_table() now has 3rd argument which controls linking
  in of table being opened into THD::open_tables (this is useful in
  cases when placeholder used for name-locking is already linked into
  this list).
  Added declaration of auxiliary function table_cache_insert_placeholder()
  which is used for creation of table placeholders for name-locking.
  Added declaration of lock_table_name_if_not_cached() which can be
  used to take an exclusive name-lock on table if there are no records
  for it in table cache.
  Changed signature of unlink_open_table() function to simplify its use
  and make it useful for table placeholders and tables that are only open.
  Added auxiliary drop_open_table() routine.
  Moved declaration of refresh_version to table.h header to make it
  accessible from inline methods of TABLE class.
  MYSQL_OPEN_IGNORE_LOCKED_TABLES flag is no longer used. Instead
  MYSQL_OPEN_TEMPORARY_ONLY option was added.
sql/sql_base.cc:
  Added support for the new approach to the handling of CREATE TABLE
  ... SELECT for base tables.
  
  Now we try to open and lock table to be created at the same time as
  the rest of tables used by this statement. If such table does not
  exist at this moment we create and place in the table cache special
  placeholder for it which prevents its creation or any other usage
  by other threads.
  
  Note significant distinctions of this placeholder from the placeholder
  used for normal name-lock: 1) It is treated like open table by other
  name-locks so it does not allow name-lock taking operations like DROP
  TABLE or RENAME TABLE to proceed. 2) it is linked into THD::open_tables
  list and automatically removed during close_thread_tables() call
    
  open_tables():
    Implemented logic described above. To do this added
    auxiliary check_if_table_exists() function.
    Removed support for MYSQL_OPEN_IGNORE_LOCKED_TABLES option
    which is no longer used.
    Added MYSQL_OPEN_TEMPORARY_ONLY which is used to restrict
    search for temporary tables only.
  close_cached_tables()/close_thread_table()/reopen_tables()/
  close_old_data_files()/table_is_used()/remove_table_from_cache():
    Added support for open placeholders (note that we also use them
    when we need to re-open tables during flush).
  unlink_open_table():
    Changed function signature to simplify its use and to make
    useful for open placeholders and tables which are only
    open and not locked.
  Added auxiliary drop_open_table() routine.
  reopen_name_locked_table():
    Now has 3rd argument which controls linking in of table being
    opened into THD::open_tables (this is useful in cases when
    placeholder used for name-locking is already linked into
    this list).
  Added auxiliary table_cache_insert_placeholder() routine which
  simplifies creation of placeholders used for name-locking.
  Added lock_table_name_if_not_cached() which can be used to take
  an exclusive name-lock on table if there are no records for it
  in table cache.
sql/sql_handler.cc:
  Adjusted mysql_ha_mark_tables_for_reopen() routine to properly
  handle placeholders which now can be linked into open tables
  list.
sql/sql_insert.cc:
  Introduced new approach to handling of base tables in CREATE TABLE
  ... SELECT statement.
  
  Now we try to open and lock table to be created at the same time as
  the rest of tables used by this statement. If such table does not
  exist at this moment we create and place in the table cache special
  placeholder for it which prevents its creation or any other usage
  by other threads. By doing this we avoid races which existed with
  previous approach in which we created, opened and locked target in
  separate step without any special protection.
  This also allows properly calculate prelocking set in cases when
  target table already exists and has some on insert triggers.
  
  Note that we don't employ the same approach for temporary tables
  (this is okay as such tables are unaffected by other threads).
  
  Changed create_table_from_items() and methods of select_create
  class to implement this approach.
sql/sql_parse.cc:
  The new approach to handling of CREATE TABLE ... SELECT for
  base tables assumes that all tables (including table to be
  created) are opened and (or) locked at the same time.
  So in cases when we create base table we have to pass to
  open_and_lock_tables() table list which includes target table.
sql/sql_prepare.cc:
  The new approach to handling of CREATE TABLE ... SELECT for
  base tables assumes that all tables (including table to be
  created) are opened and (or) locked at the same time.
  So in cases when we create base table we have to pass to
  open_and_lock_tables() table list which includes target table.
sql/sql_table.cc:
  Changed mysql_create_table(), mysql_create_like_table() and
  mysql_alter_table() (in rename case) to obtain exclusive name-lock
  on the non-temporary table which is going to be created (to which
  we going to rename). This ensures that not only destination table
  doesn't exist on disk but also that there are no placeholder in 
  table cache for it (i.e. there is no CREATE TABLE ... SELECT operation
  in progress for it). Note that to avoid deadlocks while taking these
  name-locks this code assumes that existence of any record for table in
  table cache (even name-lock) means that table exists. Altough such
  check can lead to false positives these should occur only in case of
  highly concurrent DDL operations on the table and should not break
  binary logging.
  
  Renamed mysql_create_table_internal() to mysql_create_table_no_lock()
  and made it accessible from other files to give them ability to create
  table in situation when name-lock is already obtained or not relevant.
  
  Adjusted calls to reopen_name_locked_table(), which now takes
  extra argument, which controls linking of open table into
  THD::open_tables list.
  
  Removed redundant setting of table's 'version' field before calls
  to close_cached_table(). This function will set it to 0 itself
  anyway.
sql/sql_trigger.cc:
  reopen_name_locked_tables() now has one more argument which controls
  linking of opened table into the THD::open_tables list.
sql/sql_yacc.yy:
  The new approach to handling of CREATE TABLE ... SELECT statement
  for base tables assumes that all tables including table to be
  created are open and (or) locked at the same time. Therefore
  we need to set correct lock for target table.
sql/table.h:
  Moved declaration of refresh_version variable from mysql_priv.h
  to make it accessible from inline methods of TABLE class.
  Renamed TABLE::locked_by_flush member to open_placeholder since
  now it is also used for taking exclusive name-lock and not only
  by flush. 
  Introduced TABLE::is_name_opened() helper method which can be used
  to distinguish TABLE instances corresponding to open tables or
  placeholders for them from closed instances (e.g. due to their old
  version). Also introduced TABLE::needs_reopen_or_name_lock() helper
  which allows to check if TABLE instance corresponds to outdated
  version of table or to name-lock placeholder.
  Introduced TABLE_LIST::create member which marks elements of
  table list corresponds to the table to be created.
  Adjusted TABLE_LIST::placeholder() method to take into account 
  name-lock placeholders for tables to be created (this, for example,
  allows to properly handle such placeholders in lock_tables()).
  Finally, moved currently unused TABLE::open_next/open_prev
  members under ifdef NOT_YET.
mysql-test/r/create_select-big.result:
  New BitKeeper file ``mysql-test/r/create_select-big.result''
mysql-test/t/create_select-big.test:
  New BitKeeper file ``mysql-test/t/create_select-big.test''

1 files changed, 1 insertions, 1 deletions

diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 89ac444efa9..bdfcef23101 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -279,7 +279,7 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
   /* We also don't allow creation of triggers on views. */
   tables->required_type= FRMTYPE_TABLE;
 
-  if (reopen_name_locked_table(thd, tables))
+  if (reopen_name_locked_table(thd, tables, TRUE))
   {
     unlock_table_name(thd, tables);
     goto end;