MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:dlenev Date:May 11 2007 5:51pm
Subject:bk commit into 5.1 tree (dlenev:1.2510) BUG#20662
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of dlenev. When dlenev does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-05-11 21:51:03+04:00, dlenev@stripped +17 -0
  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@stripped, 2007-05-11 21:50:59+04:00, dlenev@stripped +94 -0
    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/create_select-big.result@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +164 -0
    New BitKeeper file ``mysql-test/r/create_select-big.result''

  mysql-test/r/create_select-big.result@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +0 -0

  mysql-test/r/trigger.result@stripped, 2007-05-11 21:50:59+04:00, dlenev@stripped +35 -0
    Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT
    and triggers"

  mysql-test/t/create.test@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +112 -0
    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/create_select-big.test@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +268 -0
    New BitKeeper file ``mysql-test/t/create_select-big.test''

  mysql-test/t/create_select-big.test@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +0 -0

  mysql-test/t/trigger.test@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +26 -0
    Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT
    and triggers"

  sql/lock.cc@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +3 -23
    Now for creation of name-lock placeholder lock_table_name() uses
    auxiliary function table_cache_insert_placeholder().

  sql/mysql_priv.h@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +16 -6
    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@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +408 -41
    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@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +16 -9
    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@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +102 -77
    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@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +17 -1
    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@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +14 -1
    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@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +149 -79
    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@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +1 -1
    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@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +1 -3
    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@stripped, 2007-05-11 21:51:00+04:00, dlenev@stripped +39 -4
    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.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	dlenev
# Host:	mockturtle.local
# Root:	/home/dlenev/src/mysql-5.1-cts-3

--- 1.106/sql/lock.cc	2007-05-11 21:51:11 +04:00
+++ 1.107/sql/lock.cc	2007-05-11 21:51:11 +04:00
@@ -892,8 +892,6 @@ end:
 int lock_table_name(THD *thd, TABLE_LIST *table_list, bool check_in_use)
 {
   TABLE *table;
-  TABLE_SHARE *share;
-  char *key_buff;
   char  key[MAX_DBKEY_LENGTH];
   char *db= table_list->db;
   uint  key_length;
@@ -921,29 +919,11 @@ int lock_table_name(THD *thd, TABLE_LIST
       }
     }
   }
-  /*
-    Create a table entry with the right key and with an old refresh version
-    Note that we must use my_multi_malloc() here as this is freed by the
-    table cache
-  */
-  if (!my_multi_malloc(MYF(MY_WME | MY_ZEROFILL),
-                       &table, sizeof(*table),
-                       &share, sizeof(*share),
-                       &key_buff, key_length,
-                       NULL))
-    DBUG_RETURN(-1);
-  table->s= share;
-  share->set_table_cache_key(key_buff, key, key_length);
-  share->tmp_table= INTERNAL_TMP_TABLE;  // for intern_close_table
-  table->in_use= thd;
-  table->locked_by_name=1;
-  table_list->table=table;
 
-  if (my_hash_insert(&open_cache, (byte*) table))
-  {
-    my_free((gptr) table,MYF(0));
+  if (!(table= table_cache_insert_placeholder(thd, key, key_length)))
     DBUG_RETURN(-1);
-  }
+
+  table_list->table=table;
 
   /* Return 1 if table is in use */
   DBUG_RETURN(test(remove_table_from_cache(thd, db, table_list->table_name,

--- 1.505/sql/mysql_priv.h	2007-05-11 21:51:11 +04:00
+++ 1.506/sql/mysql_priv.h	2007-05-11 21:51:11 +04:00
@@ -794,8 +794,6 @@ check_and_unset_inject_value(int value)
 
 #endif
 
-uint build_table_path(char *buff, size_t bufflen, const char *db,
-                      const char *table, const char *ext);
 void write_bin_log(THD *thd, bool clear_error,
                    char const *query, ulong query_length);
 
@@ -970,6 +968,12 @@ bool mysql_create_table(THD *thd,const c
                         List<create_field> &fields, List<Key> &keys,
                         bool tmp_table, uint select_field_count,
                         bool use_copy_create_info);
+bool mysql_create_table_no_lock(THD *thd, const char *db,
+                                const char *table_name,
+                                HA_CREATE_INFO *create_info,
+                                List<create_field> &fields, List<Key> &keys,
+                                bool tmp_table, uint select_field_count,
+                                bool use_copy_create_info);
 
 bool mysql_alter_table(THD *thd, char *new_db, char *new_name,
                        HA_CREATE_INFO *create_info,
@@ -1027,7 +1031,11 @@ TABLE_SHARE *get_cached_table_share(cons
 TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type update);
 TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT* mem,
 		  bool *refresh, uint flags);
-bool reopen_name_locked_table(THD* thd, TABLE_LIST* table);
+bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list, bool link_in);
+TABLE *table_cache_insert_placeholder(THD *thd, const char *key,
+                                      uint key_length);
+bool lock_table_name_if_not_cached(THD *thd, const char *db,
+                                   const char *table_name, TABLE **table);
 TABLE *find_locked_table(THD *thd, const char *db,const char *table_name);
 bool reopen_tables(THD *thd,bool get_locks,bool in_refresh);
 bool close_data_tables(THD *thd,const char *db, const char *table_name);
@@ -1195,7 +1203,9 @@ void add_join_on(TABLE_LIST *b,Item *exp
 void add_join_natural(TABLE_LIST *a,TABLE_LIST *b,List<String> *using_fields,
                       SELECT_LEX *lex);
 bool add_proc_to_list(THD *thd, Item *item);
-TABLE *unlink_open_table(THD *thd,TABLE *list,TABLE *find);
+void unlink_open_table(THD *thd, TABLE *find, bool unlock);
+void drop_open_table(THD *thd, TABLE *table, const char *db_name,
+                     const char *table_name);
 void update_non_unique_table_error(TABLE_LIST *update,
                                    const char *operation,
                                    TABLE_LIST *duplicate);
@@ -1630,7 +1640,7 @@ extern double log_01[32];
 extern ulonglong log_10_int[20];
 extern ulonglong keybuff_size;
 extern ulonglong thd_startup_options;
-extern ulong refresh_version, thread_id;
+extern ulong thread_id;
 extern ulong binlog_cache_use, binlog_cache_disk_use;
 extern ulong aborted_threads,aborted_connects;
 extern ulong delayed_insert_timeout;
@@ -1773,7 +1783,7 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd, 
 #define MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK      0x0001
 #define MYSQL_LOCK_IGNORE_FLUSH                 0x0002
 #define MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN        0x0004
-#define MYSQL_OPEN_IGNORE_LOCKED_TABLES         0x0008
+#define MYSQL_OPEN_TEMPORARY_ONLY               0x0008
 
 void mysql_unlock_tables(THD *thd, MYSQL_LOCK *sql_lock);
 void mysql_unlock_read_tables(THD *thd, MYSQL_LOCK *sql_lock);

--- 1.397/sql/sql_base.cc	2007-05-11 21:51:11 +04:00
+++ 1.398/sql/sql_base.cc	2007-05-11 21:51:11 +04:00
@@ -97,7 +97,7 @@ static bool open_new_frm(THD *thd, TABLE
                          uint db_stat, uint prgflag,
                          uint ha_open_flags, TABLE *outparam,
                          TABLE_LIST *table_desc, MEM_ROOT *mem_root);
-static void close_old_data_files(THD *thd, TABLE *table, bool abort_locks,
+static void close_old_data_files(THD *thd, TABLE *table, bool morph_locks,
                                  bool send_refresh);
 static bool reopen_table(TABLE *table);
 static bool
@@ -688,6 +688,8 @@ static void close_handle_and_leave_table
   MEM_ROOT *mem_root= &table->mem_root;
   DBUG_ENTER("close_handle_and_leave_table_as_lock");
 
+  DBUG_ASSERT(table->db_stat);
+
   /*
     Make a local copy of the table share and free the current one.
     This has to be done to ensure that the table share is removed from
@@ -934,8 +936,22 @@ bool close_cached_tables(THD *thd, bool 
       for (uint idx=0 ; idx < open_cache.records ; idx++)
       {
 	TABLE *table=(TABLE*) hash_element(&open_cache,idx);
+        /*
+          Note that we wait here only for tables which are actually open, and
+          not for placeholders with TABLE::open_placeholder set. Waiting for
+          latter will cause deadlock in the following scenario, for example:
+
+          conn1: lock table t1 write;
+          conn2: lock table t2 write;
+          conn1: flush tables;
+          conn2: flush tables;
+
+          It also does not make sense to wait for those of placeholders that
+          are employed by CREATE TABLE as in this case table simply does not
+          exist yet.
+        */
 	if (!table->s->log_table &&
-            ((table->s->version) < refresh_version && table->db_stat))
+            (table->needs_reopen_or_name_lock() && table->db_stat))
 	{
 	  found=1;
           DBUG_PRINT("signal", ("Waiting for COND_refresh"));
@@ -1249,10 +1265,10 @@ bool close_thread_table(THD *thd, TABLE 
   TABLE *table= *table_ptr;
   DBUG_ENTER("close_thread_table");
   DBUG_ASSERT(table->key_read == 0);
-  DBUG_ASSERT(table->file->inited == handler::NONE);
+  DBUG_ASSERT(!table->file || table->file->inited == handler::NONE);
 
   *table_ptr=table->next;
-  if (table->s->version != refresh_version ||
+  if (table->needs_reopen_or_name_lock() ||
       thd->version != refresh_version || !table->db_stat)
   {
     VOID(hash_delete(&open_cache,(byte*) table));
@@ -1260,6 +1276,12 @@ bool close_thread_table(THD *thd, TABLE 
   }
   else
   {
+    /*
+      Open placeholders have TABLE::db_stat set to 0, so they should be
+      handled by the first alternative.
+    */
+    DBUG_ASSERT(!table->open_placeholder);
+
     /* Free memory and reset for next loop */
     table->file->ha_reset();
     table->in_use=0;
@@ -1778,18 +1800,32 @@ static void relink_unused(TABLE *table)
 }
 
 
-/*
-  Remove all instances of table from the current open list
-  Free all locks on tables that are done with LOCK TABLES
- */
+/**
+    @brief  Remove all instances of table from thread's open list and
+            table cache.
+
+    @param  thd     Thread context
+    @param  find    Table to remove
+    @param  unlock  TRUE  - free all locks on tables removed that are
+                            done with LOCK TABLES
+                    FALSE - otherwise
+
+    @note When unlock parameter is FALSE or current thread doesn't have
+          any tables locked with LOCK TABLES tables are assumed to be
+          not locked (for example already unlocked).
+*/
 
-TABLE *unlink_open_table(THD *thd, TABLE *list, TABLE *find)
+void unlink_open_table(THD *thd, TABLE *find, bool unlock)
 {
   char key[MAX_DBKEY_LENGTH];
   uint key_length= find->s->table_cache_key.length;
-  TABLE *start=list,**prev,*next;
-  prev= &start;
+  TABLE *list, **prev, *next;
+  DBUG_ENTER("unlink_open_table");
+
+  safe_mutex_assert_owner(&LOCK_open);
 
+  list= thd->open_tables;
+  prev= &thd->open_tables;
   memcpy(key, find->s->table_cache_key.str, key_length);
   for (; list ; list=next)
   {
@@ -1797,7 +1833,7 @@ TABLE *unlink_open_table(THD *thd, TABLE
     if (list->s->table_cache_key.length == key_length &&
 	!memcmp(list->s->table_cache_key.str, key, key_length))
     {
-      if (thd->locked_tables)
+      if (unlock && thd->locked_tables)
 	mysql_lock_remove(thd, thd->locked_tables,list);
       VOID(hash_delete(&open_cache,(byte*) list)); // Close table
     }
@@ -1810,7 +1846,41 @@ TABLE *unlink_open_table(THD *thd, TABLE
   *prev=0;
   // Notify any 'refresh' threads
   broadcast_refresh();
-  return start;
+  DBUG_VOID_RETURN;
+}
+
+
+/**
+    @brief Auxiliary routine which closes and drops open table.
+
+    @param  thd         Thread handle
+    @param  table       TABLE object for table to be dropped
+    @param  db_name     Name of database for this table
+    @param  table_name  Name of this table
+
+    @note This routine assumes that table to be closed is open only
+          by calling thread so we needn't wait until other threads
+          will close the table. It also assumes that table to be
+          dropped is already unlocked.
+*/
+
+void drop_open_table(THD *thd, TABLE *table, const char *db_name,
+                     const char *table_name)
+{
+  if (table->s->tmp_table)
+    close_temporary_table(thd, table, 1, 1);
+  else
+  {
+    handlerton *table_type= table->s->db_type;
+    VOID(pthread_mutex_lock(&LOCK_open));
+    /*
+      unlink_open_table() also tells threads waiting for refresh or close
+      that something has happened.
+    */
+    unlink_open_table(thd, table, FALSE);
+    quick_rm_table(table_type, db_name, table_name, 0);
+    VOID(pthread_mutex_unlock(&LOCK_open));
+  }
 }
 
 
@@ -1867,6 +1937,11 @@ void wait_for_condition(THD *thd, pthrea
       table_list  TABLE_LIST object for table to be open, TABLE_LIST::table
                   member should point to TABLE object which was used for
                   name-locking.
+      link_in     TRUE  - if TABLE object for table to be opened should be
+                          linked into THD::open_tables list.
+                  FALSE - placeholder used for name-locking is already in
+                          this list so we only need to preserve TABLE::next
+                          pointer.
 
   NOTE
     This function assumes that its caller already acquired LOCK_open mutex.
@@ -1876,7 +1951,7 @@ void wait_for_condition(THD *thd, pthrea
     TRUE  - Error
 */
 
-bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list)
+bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list, bool link_in)
 {
   TABLE *table= table_list->table;
   TABLE_SHARE *share;
@@ -1907,11 +1982,32 @@ bool reopen_name_locked_table(THD* thd, 
   }
 
   share= table->s;
+  /*
+    We want to prevent other connections from opening this table until end
+    of statement as it is likely that modifications of table's metadata are
+    not yet finished (for example CREATE TRIGGER have to change .TRG file,
+    or we might want to drop table if CREATE TABLE ... SELECT fails).
+    This also allows us to assume that no other connection will sneak in
+    before we will get table-level lock on this table.
+  */
   share->version=0;
   table->in_use = thd;
   check_unused();
-  table->next = thd->open_tables;
-  thd->open_tables = table;
+
+  if (link_in)
+  {
+    table->next= thd->open_tables;
+    thd->open_tables= table;
+  }
+  else
+  {
+    /*
+      TABLE object should be already in THD::open_tables list so we just
+      need to set TABLE::next correctly.
+    */
+    table->next= orig_table.next;
+  }
+
   table->tablenr=thd->current_tablenr++;
   table->used_fields=0;
   table->const_table=0;
@@ -1921,6 +2017,173 @@ bool reopen_name_locked_table(THD* thd, 
 }
 
 
+/**
+    @brief Create and insert into table cache placeholder for table
+           which will prevent its opening (or creation) (a.k.a lock
+           table name).
+
+    @param thd         Thread context
+    @param key         Table cache key for name to be locked
+    @param key_length  Table cache key length
+
+    @return Pointer to TABLE object used for name locking or 0 in
+            case of failure.
+*/
+
+TABLE *table_cache_insert_placeholder(THD *thd, const char *key,
+                                      uint key_length)
+{
+  TABLE *table;
+  TABLE_SHARE *share;
+  char *key_buff;
+  DBUG_ENTER("table_cache_insert_placeholder");
+
+  safe_mutex_assert_owner(&LOCK_open);
+
+  /*
+    Create a table entry with the right key and with an old refresh version
+    Note that we must use my_multi_malloc() here as this is freed by the
+    table cache
+  */
+  if (!my_multi_malloc(MYF(MY_WME | MY_ZEROFILL),
+                       &table, sizeof(*table),
+                       &share, sizeof(*share),
+                       &key_buff, key_length,
+                       NULL))
+    DBUG_RETURN(NULL);
+
+  table->s= share;
+  share->set_table_cache_key(key_buff, key, key_length);
+  share->tmp_table= INTERNAL_TMP_TABLE;  // for intern_close_table
+  table->in_use= thd;
+  table->locked_by_name=1;
+
+  if (my_hash_insert(&open_cache, (byte*)table))
+  {
+    my_free((gptr) table, MYF(0));
+    DBUG_RETURN(NULL);
+  }
+
+  DBUG_RETURN(table);
+}
+
+
+/**
+    @brief Obtain an exclusive name lock on the table if it is not cached
+           in the table cache.
+
+    @param      thd         Thread context
+    @param      db          Name of database
+    @param      table_name  Name of table
+    @param[out] table       Out parameter which is either:
+                            - set to NULL if table cache contains record for
+                              the table or
+                            - set to point to the TABLE instance used for
+                              name-locking.
+
+    @note This function takes into account all records for table in table
+          cache, even placeholders used for name-locking. This means that
+          'table' parameter can be set to NULL for some situations when
+          table does not really exist.
+
+    @retval  TRUE   Error occured (OOM)
+    @retval  FALSE  Success. 'table' parameter set according to above rules.
+*/
+
+bool lock_table_name_if_not_cached(THD *thd, const char *db,
+                                   const char *table_name, TABLE **table)
+{
+  char key[MAX_DBKEY_LENGTH];
+  uint key_length;
+  DBUG_ENTER("lock_table_name_if_not_cached");
+
+  key_length= (uint)(strmov(strmov(key, db) + 1, table_name) - key) + 1;
+  VOID(pthread_mutex_lock(&LOCK_open));
+
+  if (hash_search(&open_cache, (byte *)key, key_length))
+  {
+    VOID(pthread_mutex_unlock(&LOCK_open));
+    DBUG_PRINT("info", ("Table is cached, name-lock is not obtained"));
+    *table= 0;
+    DBUG_RETURN(FALSE);
+  }
+  if (!(*table= table_cache_insert_placeholder(thd, key, key_length)))
+  {
+    VOID(pthread_mutex_unlock(&LOCK_open));
+    DBUG_RETURN(TRUE);
+  }
+  (*table)->open_placeholder= 1;
+  (*table)->next= thd->open_tables;
+  thd->open_tables= *table;
+  VOID(pthread_mutex_unlock(&LOCK_open));
+  DBUG_RETURN(FALSE);
+}
+
+
+/**
+    @brief Check that table exists in table definition cache, on disk
+           or in some storage engine.
+
+    @param  thd          Thread context
+    @param  table        Table list element
+    @param  exists[out]  Out parameter which is set to TRUE if table
+                         exists and to FALSE otherwise.
+
+    @note This function assumes that caller owns LOCK_open mutex.
+          It also assumes that the fact that there are no name-locks
+          on the table was checked beforehand.
+
+    @note If there is no .FRM file for the table but it exists in one
+          of engines (e.g. it was created on another node of NDB cluster)
+          this function will fetch and create proper .FRM file for it.
+
+    @retval  TRUE   Some error occured
+    @retval  FALSE  No error. 'exists' out parameter set accordingly.
+*/
+
+bool check_if_table_exists(THD *thd, TABLE_LIST *table, bool *exists)
+{
+  char path[FN_REFLEN];
+  int rc;
+  DBUG_ENTER("check_if_table_exists");
+
+  safe_mutex_assert_owner(&LOCK_open);
+
+  *exists= TRUE;
+
+  if (get_cached_table_share(table->db, table->table_name))
+    DBUG_RETURN(FALSE);
+
+  build_table_filename(path, sizeof(path) - 1, table->db, table->table_name,
+                       reg_ext, 0);
+
+  if (!access(path, F_OK))
+    DBUG_RETURN(FALSE);
+
+  /* .FRM file doesn't exist. Check if some engine can provide it. */
+
+  rc= ha_create_table_from_engine(thd, table->db, table->table_name);
+
+  if (rc < 0)
+  {
+    /* Table does not exists in engines as well. */
+    *exists= FALSE;
+    DBUG_RETURN(FALSE);
+  }
+  else if (!rc)
+  {
+    /* Table exists in some engine and .FRM for it was created. */
+    DBUG_RETURN(FALSE);
+  }
+  else /* (rc > 0) */
+  {
+    my_printf_error(ER_UNKNOWN_ERROR, "Failed to open '%-.64s', error while "
+                    "unpacking from engine", MYF(0), table->table_name);
+    DBUG_RETURN(TRUE);
+  }
+}
+
+
 /*
   Open a table.
 
@@ -1936,12 +2199,17 @@ bool reopen_name_locked_table(THD* thd, 
                           MYSQL_LOCK_IGNORE_FLUSH - Open table even if
                           someone has done a flush or namelock on it.
                           No version number checking is done.
-                          MYSQL_OPEN_IGNORE_LOCKED_TABLES - Open table
-                          ignoring set of locked tables and prelocked mode.
+                          MYSQL_OPEN_TEMPORARY_ONLY - Open only temporary
+                          table not the base table or view.
 
   IMPLEMENTATION
     Uses a cache of open tables to find a table not in use.
 
+    If table list element for the table to be opened has "create" flag
+    set and table does not exist, this function will automatically insert
+    a placeholder for exclusive name lock into the open tables cache and
+    will return the TABLE instance that corresponds to this placeholder.
+
   RETURN
     NULL  Open failed.  If refresh is set then one should close
           all other tables and retry the open.
@@ -2014,6 +2282,12 @@ TABLE *open_table(THD *thd, TABLE_LIST *
     }
   }
 
+  if (flags & MYSQL_OPEN_TEMPORARY_ONLY)
+  {
+    my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db, table_list->table_name);
+    DBUG_RETURN(0);
+  }
+
   /*
     The table is not temporary - if we're in pre-locked or LOCK TABLES
     mode, let's try to find the requested table in the list of pre-opened
@@ -2021,8 +2295,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
     open not pre-opened tables in pre-locked/LOCK TABLES mode.
     TODO: move this block into a separate function.
   */
-  if (!(flags & MYSQL_OPEN_IGNORE_LOCKED_TABLES) &&
-      (thd->locked_tables || thd->prelocked_mode))
+  if (thd->locked_tables || thd->prelocked_mode)
   {						// Using table locks
     TABLE *best_table= 0;
     int best_distance= INT_MIN;
@@ -2204,7 +2477,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
       c1: name lock t2; -- blocks
       c2: open t1; -- blocks
     */
-    if (table->s->version != refresh_version && !table->s->log_table)
+    if (table->needs_reopen_or_name_lock() && !table->s->log_table)
     {
       DBUG_PRINT("note",
                  ("Found table '%s.%s' with different refresh version",
@@ -2217,6 +2490,14 @@ TABLE *open_table(THD *thd, TABLE_LIST *
         continue;
       }
 
+      /* Avoid self-deadlocks by detecting self-dependencies. */
+      if (table->open_placeholder && table->in_use == thd)
+      {
+	VOID(pthread_mutex_unlock(&LOCK_open));
+        my_error(ER_UPDATE_TABLE_USED, MYF(0), table->s->table_name.str);
+        DBUG_RETURN(0);
+      }
+
       /*
         Back off, part 1: mark the table as "unused" for the
         purpose of name-locking by setting table->db_stat to 0. Do
@@ -2233,6 +2514,14 @@ TABLE *open_table(THD *thd, TABLE_LIST *
         and wait till the operation is complete: when any
         operation that juggles with table->s->version completes,
         it broadcasts COND_refresh condition variable.
+        If 'old' table we met is in use by current thread we return
+        without waiting since in this situation it's this thread
+        which is responsible for broadcasting on COND_refresh
+        (and this was done already in close_old_data_files()).
+        Good example of such situation is when we have statement
+        that needs two instances of table and FLUSH TABLES comes
+        after we open first instance but before we open second
+        instance.
       */
       if (table->in_use != thd)
       {
@@ -2273,6 +2562,40 @@ TABLE *open_table(THD *thd, TABLE_LIST *
     while (open_cache.records > table_cache_size && unused_tables)
       VOID(hash_delete(&open_cache,(byte*) unused_tables)); /* purecov: tested */
 
+    if (table_list->create)
+    {
+      bool exists;
+
+      if (check_if_table_exists(thd, table_list, &exists))
+      {
+        VOID(pthread_mutex_unlock(&LOCK_open));
+        DBUG_RETURN(NULL);
+      }
+
+      if (!exists)
+      {
+        /*
+          Table to be created, so we need to create placeholder in table-cache.
+        */
+        if (!(table= table_cache_insert_placeholder(thd, key, key_length)))
+        {
+          VOID(pthread_mutex_unlock(&LOCK_open));
+          DBUG_RETURN(NULL);
+        }
+        /*
+          Link placeholder to the open tables list so it will be automatically
+          removed once tables are closed. Also mark it so it won't be ignored
+          by other trying to take name-lock.
+        */
+        table->open_placeholder= 1;
+        table->next= thd->open_tables;
+        thd->open_tables= table;
+        VOID(pthread_mutex_unlock(&LOCK_open));
+        DBUG_RETURN(table);
+      }
+      /* Table exists. Let us try to open it. */
+    }
+
     /* make a new table */
     if (!(table=(TABLE*) my_malloc(sizeof(*table),MYF(MY_WME))))
     {
@@ -2489,9 +2812,24 @@ bool close_data_tables(THD *thd,const ch
 }
 
 
-/*
-  Reopen all tables with closed data files
-  One should have lock on LOCK_open when calling this
+/**
+    @brief Reopen all tables with closed data files.
+
+    @param thd         Thread context
+    @param get_locks   Should we get locks after reopening tables ?
+    @param in_refresh  Are we in FLUSH TABLES ? TODO: It seems that
+                       we can remove this parameter.
+
+    @note Since this function can't properly handle prelocking and
+          create placeholders it should be used in very special
+          situations like FLUSH TABLES or ALTER TABLE. In general
+          case one should just repeat open_tables()/lock_tables()
+          combination when one needs tables to be reopened (for
+          example see open_and_lock_tables()).
+
+    @note One should have lock on LOCK_open when calling this.
+
+    @return FALSE in case of success, TRUE - otherwise.
 */
 
 bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
@@ -2537,7 +2875,7 @@ bool reopen_tables(THD *thd,bool get_loc
       if (in_refresh)
       {
 	table->s->version=0;
-	table->locked_by_flush=0;
+	table->open_placeholder= 0;
       }
     }
   }
@@ -2564,13 +2902,21 @@ bool reopen_tables(THD *thd,bool get_loc
 }
 
 
-/*
-  Close handlers for tables in list, but leave the TABLE structure
-  intact so that we can re-open these quickly
-  abort_locks is set if called from flush_tables.
+/**
+    @brief Close handlers for tables in list, but leave the TABLE structure
+           intact so that we can re-open these quickly.
+
+    @param thd           Thread context
+    @param table         Head of the list of TABLE objects
+    @param morph_locks   TRUE  - remove locks which we have on tables being closed
+                                 but ensure that no DML or DDL will sneak in before
+                                 we will re-open the table (i.e. temporarily morph
+                                 our table-level locks into name-locks).
+                         FALSE - otherwise
+    @param send_refresh  Should we awake waiters even if we didn't close any tables?
 */
 
-void close_old_data_files(THD *thd, TABLE *table, bool abort_locks,
+void close_old_data_files(THD *thd, TABLE *table, bool morph_locks,
 			  bool send_refresh)
 {
   bool found= send_refresh;
@@ -2582,19 +2928,41 @@ void close_old_data_files(THD *thd, TABL
       Reopen marked for flush. But close log tables. They are flushed only
       explicitly on FLUSH LOGS
     */
-    if (table->s->version != refresh_version && !table->s->log_table)
+    if (table->needs_reopen_or_name_lock() && !table->s->log_table)
     {
       found=1;
       if (table->db_stat)
       {
-	if (abort_locks)
+	if (morph_locks)
 	{
-	  mysql_lock_abort(thd,table, TRUE);	// Close waiting threads
-	  mysql_lock_remove(thd, thd->locked_tables,table);
-	  table->locked_by_flush=1;		// Will be reopened with locks
+          /*
+            Wake up threads waiting for table-level lock on this table
+            so they won't sneak in when we will temporarily remove our
+            lock on it. This will also give them a chance to close their
+            instances of this table.
+          */
+          mysql_lock_abort(thd, table, TRUE);
+          mysql_lock_remove(thd, thd->locked_tables, table);
+          /*
+            We want to protect the table from concurrent DDL operations
+            (like RENAME TABLE) until we will re-open and re-lock it.
+          */
+	  table->open_placeholder= 1;
 	}
         close_handle_and_leave_table_as_lock(table);
       }
+      else if (table->open_placeholder)
+      {
+        /*
+          We come here only in close-for-back-off scenario. So we have to
+          "close" create placeholder here to avoid deadlocks (for example,
+          in case of concurrent execution of CREATE TABLE t1 SELECT * FROM t2
+          and RENAME TABLE t2 TO t1). In close-for-re-open scenario we will
+          probably want to let it stay.
+        */
+        DBUG_ASSERT(!morph_locks);
+        table->open_placeholder= 0;
+      }
     }
   }
   if (found)
@@ -2630,10 +2998,10 @@ bool table_is_used(TABLE *table, bool wa
                                     key_length, &state))
     {
       DBUG_PRINT("info", ("share: 0x%lx  locked_by_logger: %d "
-                          "locked_by_flush: %d  locked_by_name: %d "
+                          "open_placeholder: %d  locked_by_name: %d "
                           "db_stat: %u  version: %lu",
                           (ulong) search->s, search->locked_by_logger,
-                          search->locked_by_flush, search->locked_by_name,
+                          search->open_placeholder, search->locked_by_name,
                           search->db_stat,
                           search->s->version));
       if (search->in_use == table->in_use)
@@ -2649,8 +3017,7 @@ bool table_is_used(TABLE *table, bool wa
       */
       if (!search->locked_by_logger &&
           (search->locked_by_name && wait_for_name_lock ||
-           search->locked_by_flush ||
-           (search->db_stat && search->s->version < refresh_version)))
+           (search->is_name_opened() && search->needs_reopen_or_name_lock())))
         DBUG_RETURN(1);
     }
   } while ((table=table->next));
@@ -6637,7 +7004,7 @@ bool remove_table_from_cache(THD *thd, c
       {
         DBUG_PRINT("info", ("Table was in use by other thread"));
         in_use->some_tables_deleted=1;
-        if (table->db_stat)
+        if (table->is_name_opened())
         {
           DBUG_PRINT("info", ("Found another active instance of the table"));
   	  result=1;
@@ -7012,7 +7379,7 @@ has_two_write_locked_tables_with_auto_in
   for (TABLE_LIST *table= tables; table; table= table->next_global)
   {
     /* we must do preliminary checks as table->table may be NULL */
-    if (!table->placeholder() && !table->schema_table &&
+    if (!table->placeholder() &&
         table->table->found_next_number_field &&
         (table->lock_type >= TL_WRITE_ALLOW_WRITE))
     {

--- 1.261/sql/sql_insert.cc	2007-05-11 21:51:11 +04:00
+++ 1.262/sql/sql_insert.cc	2007-05-11 21:51:11 +04:00
@@ -2261,7 +2261,7 @@ bool delayed_insert::handle_inserts(void
 
   thd.proc_info="insert";
   max_rows= delayed_insert_limit;
-  if (thd.killed || table->s->version != refresh_version)
+  if (thd.killed || table->needs_reopen_or_name_lock())
   {
     thd.killed= THD::KILL_CONNECTION;
     max_rows= ULONG_MAX;                     // Do as much as possible
@@ -2962,8 +2962,8 @@ bool select_insert::send_eof()
 ***************************************************************************/
 
 /*
-  Create table from lists of fields and items (or open existing table
-  with same name).
+  Create table from lists of fields and items (or just return TABLE
+  object for pre-opened existing table).
 
   SYNOPSIS
     create_table_from_items()
@@ -2978,19 +2978,25 @@ bool select_insert::send_eof()
                           of fields for the table (corresponding fields will
                           be added to the end of 'extra_fields' list)
       lock         out    Pointer to the MYSQL_LOCK object for table created
-                          (open) will be returned in this parameter. Since
-                          this table is not included in THD::lock caller is
-                          responsible for explicitly unlocking this table.
+                          (or open temporary table) will be returned in this
+                          parameter. Since this table is not included in
+                          THD::lock caller is responsible for explicitly
+                          unlocking this table.
       hooks
 
   NOTES
-    If 'create_info->options' bitmask has HA_LEX_CREATE_IF_NOT_EXISTS
-    flag and table with name provided already exists then this function will
-    simply open existing table.
-    Also note that create, open and lock sequence in this function is not
-    atomic and thus contains gap for deadlock and can cause other troubles.
-    Since this function contains some logic specific to CREATE TABLE ... SELECT
-    it should be changed before it can be used in other contexts.
+    This function behaves differently for base and temporary tables:
+    - For base table we assume that either table exists and was pre-opened
+      and locked at open_and_lock_tables() stage (and in this case we just
+      emit error or warning and return pre-opened TABLE object) or special
+      placeholder was put in table cache that guarantees that this table
+      won't be created or opened until the placeholder will be removed
+      (so there is an exclusive lock on this table).
+    - We don't pre-open existing temporary table, instead we either open
+      or create and then open table in this function.
+
+    Since this function contains some logic specific to CREATE TABLE ...
+    SELECT it should be changed before it can be used in other contexts.
 
   RETURN VALUES
     non-zero  Pointer to TABLE object for table created or opened
@@ -3016,6 +3022,25 @@ static TABLE *create_table_from_items(TH
   bool not_used;
   DBUG_ENTER("create_table_from_items");
 
+  DBUG_EXECUTE_IF("sleep_create_select_before_check_if_exists", my_sleep(6000000););
+
+  if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE) &&
+      create_table->table->db_stat)
+  {
+    /* Table already exists and was open at open_and_lock_tables() stage. */
+    if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
+    {
+      create_info->table_existed= 1;		// Mark that table existed
+      push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+                          ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
+                          create_table->table_name);
+      DBUG_RETURN(create_table->table);
+    }
+
+    my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
+    DBUG_RETURN(0);
+  }
+
   tmp_table.alias= 0;
   tmp_table.timestamp_field= 0;
   tmp_table.s= &share;
@@ -3047,8 +3072,15 @@ static TABLE *create_table_from_items(TH
       cr_field->flags &= ~NOT_NULL_FLAG;
     extra_fields->push_back(cr_field);
   }
+
+  DBUG_EXECUTE_IF("sleep_create_select_before_create", my_sleep(6000000););
+
   /*
-    create and lock table
+    Create and lock table.
+
+    Note that we either creating (or opening existing) temporary table or
+    creating base table on which name we have exclusive lock. So code below
+    should not cause deadlocks or races.
 
     We don't log the statement, it will be logged later.
 
@@ -3058,63 +3090,74 @@ static TABLE *create_table_from_items(TH
     don't want to delete from it) 2) it would be written before the CREATE
     TABLE, which is a wrong order. So we keep binary logging disabled when we
     open_table().
-    NOTE: By locking table which we just have created (or for which we just
-    have have found that it already exists) separately from other tables used
-    by the statement we create potential window for deadlock.
-    TODO: create and open should be done atomic !
   */
   {
     tmp_disable_binlog(thd);
-    if (!mysql_create_table(thd, create_table->db, create_table->table_name,
-                            create_info, *extra_fields, *keys, 0,
-                            select_field_count, 0))
+    if (!mysql_create_table_no_lock(thd, create_table->db,
+                                    create_table->table_name,
+                                    create_info, *extra_fields, *keys, 0,
+                                    select_field_count, 0))
     {
-      /*
-        If we are here in prelocked mode we either create temporary table
-        or prelocked mode is caused by the SELECT part of this statement.
-      */
-      DBUG_ASSERT(!thd->prelocked_mode ||
-                  create_info->options & HA_LEX_CREATE_TMP_TABLE ||
-                  thd->lex->requires_prelocking());
 
-      /*
-        NOTE: We don't want to ignore set of locked tables here if we are
-              under explicit LOCK TABLES since it will open gap for deadlock
-              too wide (and also is not backward compatible).
-      */
+      if (create_info->table_existed &&
+          !(create_info->options & HA_LEX_CREATE_TMP_TABLE))
+      {
+        /*
+          This means that someone created table underneath server
+          or it was created via different mysqld front-end to the
+          cluster. We don't have much options but throw an error.
+        */
+        my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
+        DBUG_RETURN(0);
+      }
+
+      DBUG_EXECUTE_IF("sleep_create_select_before_open", my_sleep(6000000););
+
+      if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE))
+      {
+        VOID(pthread_mutex_lock(&LOCK_open));
+        if (reopen_name_locked_table(thd, create_table, FALSE))
+        {
+          quick_rm_table(create_info->db_type, create_table->db,
+                         table_case_name(create_info, create_table->table_name),
+                         0);
+        }
+        else
+          table= create_table->table;
+        VOID(pthread_mutex_unlock(&LOCK_open));
+      }
+      else
+      {
+        if (!(table= open_table(thd, create_table, thd->mem_root, (bool*) 0,
+                                MYSQL_OPEN_TEMPORARY_ONLY)) &&
+            !create_info->table_existed)
+        {
+          /*
+            This shouldn't happen as creation of temporary table should make
+            it preparable for open. But let us do close_temporary_table() here
+            just in case.
+          */
+          close_temporary_table(thd, create_table);
+        }
+      }
 
-      if (! (table= open_table(thd, create_table, thd->mem_root, (bool*) 0,
-                               (MYSQL_LOCK_IGNORE_FLUSH |
-                                ((thd->prelocked_mode == PRELOCKED) ?
-                                 MYSQL_OPEN_IGNORE_LOCKED_TABLES:0)))))
-        quick_rm_table(create_info->db_type, create_table->db,
-                       table_case_name(create_info, create_table->table_name),
-                       0);
     }
     reenable_binlog(thd);
     if (!table)                                   // open failed
       DBUG_RETURN(0);
   }
 
-  /*
-    FIXME: What happens if trigger manages to be created while we are
-           obtaining this lock ? May be it is sensible just to disable
-           trigger execution in this case ? Or will MYSQL_LOCK_IGNORE_FLUSH
-           save us from that ?
-  */
+  DBUG_EXECUTE_IF("sleep_create_select_before_lock", my_sleep(6000000););
+
   table->reginfo.lock_type=TL_WRITE;
   hooks->prelock(&table, 1);                    // Call prelock hooks
   if (! ((*lock)= mysql_lock_tables(thd, &table, 1,
                                     MYSQL_LOCK_IGNORE_FLUSH, &not_used)))
   {
-    VOID(pthread_mutex_lock(&LOCK_open));
-    hash_delete(&open_cache,(byte*) table);
-    VOID(pthread_mutex_unlock(&LOCK_open));
-    quick_rm_table(create_info->db_type, create_table->db,
-		   table_case_name(create_info, create_table->table_name), 0);
+    if (!create_info->table_existed)
+      drop_open_table(thd, table, create_table->db, create_table->table_name);
     DBUG_RETURN(0);
   }
-  table->file->extra(HA_EXTRA_WRITE_CACHE);
   DBUG_RETURN(table);
 }
 
@@ -3217,6 +3260,7 @@ select_create::prepare(List<Item> &value
   if (check_that_all_fields_are_given_values(thd, table, table_list))
     DBUG_RETURN(1);
   table->mark_columns_needed_for_insert();
+  table->file->extra(HA_EXTRA_WRITE_CACHE);
   DBUG_RETURN(0);
 }
 
@@ -3319,24 +3363,19 @@ bool select_create::send_eof()
 
     table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
     table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
-    VOID(pthread_mutex_lock(&LOCK_open));
-    mysql_unlock_tables(thd, thd->extra_lock);
-    if (!table->s->tmp_table)
+    if (thd->extra_lock)
     {
-      if (close_thread_table(thd, &table))
-        broadcast_refresh();
+      mysql_unlock_tables(thd, thd->extra_lock);
+      thd->extra_lock=0;
     }
-    thd->extra_lock=0;
-    table=0;
-    VOID(pthread_mutex_unlock(&LOCK_open));
   }
   return tmp;
 }
 
+
 void select_create::abort()
 {
   DBUG_ENTER("select_create::abort");
-  VOID(pthread_mutex_lock(&LOCK_open));
 
   /*
     We roll back the statement, including truncating the transaction
@@ -3364,24 +3403,10 @@ void select_create::abort()
   {
     table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
     table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
-    handlerton *table_type=table->s->db_type;
-    if (!table->s->tmp_table)
-    {
-      ulong version= table->s->version;
-      table->s->version= 0;
-      hash_delete(&open_cache,(byte*) table);
-      if (!create_info->table_existed)
-        quick_rm_table(table_type, create_table->db,
-                       create_table->table_name, 0);
-      /* Tell threads waiting for refresh that something has happened */
-      if (version != refresh_version)
-        broadcast_refresh();
-    }
-    else if (!create_info->table_existed)
-      close_temporary_table(thd, table, 1, 1);
+    if (!create_info->table_existed)
+      drop_open_table(thd, table, create_table->db, create_table->table_name);
     table=0;                                    // Safety
   }
-  VOID(pthread_mutex_unlock(&LOCK_open));
   DBUG_VOID_RETURN;
 }
 

--- 1.664/sql/sql_parse.cc	2007-05-11 21:51:11 +04:00
+++ 1.665/sql/sql_parse.cc	2007-05-11 21:51:11 +04:00
@@ -2164,7 +2164,13 @@ mysql_execute_command(THD *thd)
       select_lex->options|= SELECT_NO_UNLOCK;
       unit->set_limit(select_lex);
 
-      if (!(res= open_and_lock_tables(thd, select_tables)))
+      if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
+      {
+        lex->link_first_table_back(create_table, link_to_local);
+        create_table->create= TRUE;
+      }
+
+      if (!(res= open_and_lock_tables(thd, lex->query_tables)))
       {
         /*
           Is table which we are changing used somewhere in other parts
@@ -2173,6 +2179,7 @@ mysql_execute_command(THD *thd)
         if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
         {
           TABLE_LIST *duplicate;
+          create_table= lex->unlink_first_table(&link_to_local);
           if ((duplicate= unique_table(thd, create_table, select_tables, 0)))
           {
             update_non_unique_table_error(create_table, "CREATE", duplicate);
@@ -2198,6 +2205,12 @@ mysql_execute_command(THD *thd)
           }
         }
 
+        /*
+          FIXME Temporary hack which will go away once Kostja pushes
+                his uber-fix for ALTER/CREATE TABLE.
+        */
+        lex->create_info.table_existed= 0;
+
         if ((result= new select_create(create_table,
 				       &lex->create_info,
 				       lex->create_list,
@@ -2217,6 +2230,9 @@ mysql_execute_command(THD *thd)
 	lex->create_list.empty();
 	lex->key_list.empty();
       }
+      else if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
+        create_table= lex->unlink_first_table(&link_to_local);
+
     }
     else
     {

--- 1.413/sql/sql_table.cc	2007-05-11 21:51:11 +04:00
+++ 1.414/sql/sql_table.cc	2007-05-11 21:51:11 +04:00
@@ -3196,7 +3196,7 @@ static HA_CREATE_INFO *copy_create_info(
   Create a table
 
   SYNOPSIS
-    mysql_create_table_internal()
+    mysql_create_table_no_lock()
     thd			Thread object
     db			Database
     table_name		Table name
@@ -3213,6 +3213,11 @@ static HA_CREATE_INFO *copy_create_info(
   DESCRIPTION
     If one creates a temporary table, this is automatically opened
 
+    Note that this function assumes that caller already have taken
+    name-lock on table being created or used some other way to ensure
+    that concurrent operations won't intervene. mysql_create_table()
+    is a wrapper that can be used for this.
+
     no_log is needed for the case of CREATE ... SELECT,
     as the logging will be done later in sql_insert.cc
     select_field_count is also used for CREATE ... SELECT,
@@ -3223,7 +3228,7 @@ static HA_CREATE_INFO *copy_create_info(
     TRUE  error
 */
 
-bool mysql_create_table_internal(THD *thd,
+bool mysql_create_table_no_lock(THD *thd,
                                 const char *db, const char *table_name,
                                 HA_CREATE_INFO *lex_create_info,
                                 List<create_field> &fields,
@@ -3239,7 +3244,7 @@ bool mysql_create_table_internal(THD *th
   HA_CREATE_INFO *create_info;
   handler	*file;
   bool		error= TRUE;
-  DBUG_ENTER("mysql_create_table_internal");
+  DBUG_ENTER("mysql_create_table_no_lock");
   DBUG_PRINT("enter", ("db: '%s'  table: '%s'  tmp: %d",
                        db, table_name, internal_tmp_table));
 
@@ -3581,7 +3586,7 @@ warn:
 
 
 /*
-  Database locking aware wrapper for mysql_create_table_internal(),
+  Database and name-locking aware wrapper for mysql_create_table_no_lock(),
 */
 
 bool mysql_create_table(THD *thd, const char *db, const char *table_name,
@@ -3591,6 +3596,7 @@ bool mysql_create_table(THD *thd, const 
                         uint select_field_count,
                         bool use_copy_create_info)
 {
+  TABLE *name_lock= 0;
   bool result;
   DBUG_ENTER("mysql_create_table");
 
@@ -3611,11 +3617,44 @@ bool mysql_create_table(THD *thd, const 
   creating_table++;
   pthread_mutex_unlock(&LOCK_lock_db);
 
-  result= mysql_create_table_internal(thd, db, table_name, create_info,
-                                      fields, keys, internal_tmp_table,
-                                      select_field_count,
-                                      use_copy_create_info);
+  if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE))
+  {
+    if (lock_table_name_if_not_cached(thd, db, table_name, &name_lock))
+    {
+      result= TRUE;
+      goto unlock;
+    }
+    if (!name_lock)
+    {
+      if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
+      {
+        push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+                            ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
+                            table_name);
+        create_info->table_existed= 1;
+        result= FALSE;
+      }
+      else
+      {
+        my_error(ER_TABLE_EXISTS_ERROR,MYF(0),table_name);
+        result= TRUE;
+      }
+      goto unlock;
+    }
+  }
+
+  result= mysql_create_table_no_lock(thd, db, table_name, create_info,
+                                     fields, keys, internal_tmp_table,
+                                     select_field_count,
+                                     use_copy_create_info);
 
+unlock:
+  if (name_lock)
+  {
+    pthread_mutex_lock(&LOCK_open);
+    unlink_open_table(thd, name_lock, FALSE);
+    pthread_mutex_unlock(&LOCK_open);
+  }
   pthread_mutex_lock(&LOCK_lock_db);
   if (!--creating_table && creating_database)
     pthread_cond_signal(&COND_refresh);
@@ -3818,7 +3857,7 @@ void close_cached_table(THD *thd, TABLE 
     thd->lock=0;			// Start locked threads
   }
   /* Close all copies of 'table'.  This also frees all LOCK TABLES lock */
-  thd->open_tables=unlink_open_table(thd,thd->open_tables,table);
+  unlink_open_table(thd, table, TRUE);
 
   /* When lock on LOCK_open is freed other threads can continue */
   broadcast_refresh();
@@ -3894,7 +3933,7 @@ static int prepare_for_restore(THD* thd,
     to finish the restore in the handler later on
   */
   pthread_mutex_lock(&LOCK_open);
-  if (reopen_name_locked_table(thd, table))
+  if (reopen_name_locked_table(thd, table, TRUE))
   {
     unlock_table_name(thd, table);
     pthread_mutex_unlock(&LOCK_open);
@@ -4026,7 +4065,7 @@ static int prepare_for_repair(THD *thd, 
     to finish the repair in the handler later on.
   */
   pthread_mutex_lock(&LOCK_open);
-  if (reopen_name_locked_table(thd, table_list))
+  if (reopen_name_locked_table(thd, table_list, TRUE))
   {
     unlock_table_name(thd, table_list);
     pthread_mutex_unlock(&LOCK_open);
@@ -4632,7 +4671,7 @@ bool mysql_create_like_table(THD* thd, T
                              HA_CREATE_INFO *lex_create_info,
                              Table_ident *table_ident)
 {
-  TABLE *tmp_table;
+  TABLE *tmp_table, *name_lock= 0;
   char src_path[FN_REFLEN], dst_path[FN_REFLEN];
   char src_table_name_buff[FN_REFLEN], src_db_name_buff[FN_REFLEN];
   uint dst_path_length;
@@ -4641,14 +4680,14 @@ bool mysql_create_like_table(THD* thd, T
   char *src_db;
   char *src_table= table_ident->table.str;
   int  err;
-  bool res= TRUE, unlock_dst_table= FALSE;
+  bool res= TRUE;
   enum legacy_db_type not_used;
   HA_CREATE_INFO *create_info;
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   char tmp_path[FN_REFLEN];
 #endif
   char ts_name[FN_LEN];
-  TABLE_LIST src_tables_list, dst_tables_list;
+  TABLE_LIST src_tables_list;
   DBUG_ENTER("mysql_create_like_table");
 
   if (!(create_info= copy_create_info(lex_create_info)))
@@ -4756,6 +4795,10 @@ bool mysql_create_like_table(THD* thd, T
   }
   else
   {
+    if (lock_table_name_if_not_cached(thd, db, table_name, &name_lock))
+      goto err;
+    if (!name_lock)
+      goto table_exists;
     dst_path_length= build_table_filename(dst_path, sizeof(dst_path),
                                           db, table_name, reg_ext, 0);
     if (!access(dst_path, F_OK))
@@ -4843,28 +4886,21 @@ bool mysql_create_like_table(THD* thd, T
         char buf[2048];
         String query(buf, sizeof(buf), system_charset_info);
         query.length(0);  // Have to zero it since constructor doesn't
-        uint counter;
-
-        /*
-          Here we open the destination table. This is needed for
-          store_create_info() to work. The table will be closed
-          by close_thread_tables() at the end of the statement.
-        */
-        if (open_tables(thd, &table, &counter, 0))
-          goto err;
-
-        bzero((gptr)&dst_tables_list, sizeof(dst_tables_list));
-        dst_tables_list.db= table->db;
-        dst_tables_list.table_name= table->table_name;
 
         /*
-          lock destination table name, to make sure that nobody
-          can drop/alter the table while we execute store_create_info()
+          Here we open the destination table, on which we already have
+          name-lock. This is needed for store_create_info() to work.
+          The table will be closed by unlink_open_table() at the end
+          of this function.
         */
-        if (lock_and_wait_for_table_name(thd, &dst_tables_list))
+        table->table= name_lock;
+        VOID(pthread_mutex_lock(&LOCK_open));
+        if (reopen_name_locked_table(thd, table, FALSE))
+        {
+          VOID(pthread_mutex_unlock(&LOCK_open));
           goto err;
-        else
-          unlock_dst_table= TRUE;
+        }
+        VOID(pthread_mutex_unlock(&LOCK_open));
 
         IF_DBUG(int result=) store_create_info(thd, table, &query,
                                                create_info);
@@ -4899,10 +4935,10 @@ table_exists:
     my_error(ER_TABLE_EXISTS_ERROR, MYF(0), table_name);
 
 err:
-  if (unlock_dst_table)
+  if (name_lock)
   {
     pthread_mutex_lock(&LOCK_open);
-    unlock_table_name(thd, &dst_tables_list);
+    unlink_open_table(thd, name_lock, FALSE);
     pthread_mutex_unlock(&LOCK_open);
   }
   DBUG_RETURN(res);
@@ -5349,7 +5385,7 @@ bool mysql_alter_table(THD *thd,char *ne
                        uint order_num, ORDER *order, bool ignore,
                        ALTER_INFO *alter_info, bool do_send_ok)
 {
-  TABLE *table,*new_table=0;
+  TABLE *table, *new_table= 0, *name_lock= 0;
   int error= 0;
   char tmp_name[80],old_name[32],new_name_buff[FN_REFLEN];
   char new_alias_buff[FN_REFLEN], *table_name, *db, *new_alias, *alias;
@@ -5486,6 +5522,18 @@ view_err:
     DBUG_RETURN(TRUE);
   table->use_all_columns();
 
+  List_iterator<Alter_drop> drop_it(alter_info->drop_list);
+  List_iterator<create_field> def_it(fields);
+  List_iterator<Alter_column> alter_it(alter_info->alter_list);
+  List<create_field> create_list;		// Add new fields here
+  List<Key> key_list;				// Add new keys here
+  List_iterator<create_field> find_it(create_list);
+  List_iterator<Key> key_it(keys);
+  List_iterator<create_field> field_it(create_list);
+  List<key_part_spec> key_parts;
+
+  KEY *key_info=table->key_info;
+
   /* Check that we are not trying to rename to an existing table */
   if (new_name)
   {
@@ -5522,13 +5570,21 @@ view_err:
       }
       else
       {
+        if (lock_table_name_if_not_cached(thd, new_db, new_name, &name_lock))
+          DBUG_RETURN(TRUE);
+        if (!name_lock)
+        {
+	  my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_alias);
+	  DBUG_RETURN(TRUE);
+        }
+
         build_table_filename(new_name_buff, sizeof(new_name_buff),
                              new_db, new_name_buff, reg_ext, 0);
         if (!access(new_name_buff, F_OK))
 	{
 	  /* Table will be closed in do_command() */
 	  my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_alias);
-	  DBUG_RETURN(TRUE);
+          goto err;
 	}
       }
     }
@@ -5563,12 +5619,10 @@ view_err:
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   if (prep_alter_part_table(thd, table, alter_info, create_info, old_db_type,
                             &partition_changed, &fast_alter_partition))
-  {
-    DBUG_RETURN(TRUE);
-  }
+    goto err;
 #endif
   if (check_engine(thd, new_name, create_info))
-    DBUG_RETURN(TRUE);
+    goto err;
   new_db_type= create_info->db_type;
   if (create_info->row_type == ROW_TYPE_NOT_USED)
     create_info->row_type= table->s->row_type;
@@ -5581,7 +5635,7 @@ view_err:
   {
     DBUG_PRINT("info", ("doesn't support alter"));
     my_error(ER_ILLEGAL_HA, MYF(0), table_name);
-    DBUG_RETURN(TRUE);
+    goto err;
   }
   
   thd->proc_info="setup";
@@ -5640,7 +5694,19 @@ view_err:
     if (!error && (new_name != table_name || new_db != db))
     {
       thd->proc_info="rename";
-      /* Then do a 'simple' rename of the table */
+      /*
+        Then do a 'simple' rename of the table. First we need to close all
+        instances of 'source' table.
+      */
+      close_cached_table(thd, table);
+      /*
+        Then, we want check once again that target table does not exist.
+        Actually the order of these two steps does not matter since
+        earlier we took name-lock on the target table, so we do them
+        in this particular order only to be consistent with 5.0, in which
+        we don't take this name-lock and where this order really matters.
+        TODO: Investigate if we need this access() check at all.
+      */
       if (!access(new_name_buff,F_OK))
       {
 	my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_name);
@@ -5649,8 +5715,6 @@ view_err:
       else
       {
 	*fn_ext(new_name)=0;
-        table->s->version= 0;                   // Force removal of table def
-	close_cached_table(thd, table);
 	if (mysql_rename_table(old_db_type,db,table_name,new_db,new_alias, 0))
 	  error= -1;
         else if (Table_triggers_list::change_table_name(thd, db, table_name,
@@ -5682,6 +5746,8 @@ view_err:
       table->file->print_error(error, MYF(0));
       error= -1;
     }
+    if (name_lock)
+      unlink_open_table(thd, name_lock, FALSE);
     VOID(pthread_mutex_unlock(&LOCK_open));
     table_list->table= NULL;                    // For query cache
     query_cache_invalidate3(thd, table_list, 0);
@@ -5718,11 +5784,6 @@ view_err:
       create_info->tablespace= tablespace;
   }
   restore_record(table, s->default_values);     // Empty record for DEFAULT
-  List_iterator<Alter_drop> drop_it(alter_info->drop_list);
-  List_iterator<create_field> def_it(fields);
-  List_iterator<Alter_column> alter_it(alter_info->alter_list);
-  List<create_field> create_list;		// Add new fields here
-  List<Key> key_list;				// Add new keys here
   create_field *def;
 
   /*
@@ -5793,7 +5854,7 @@ view_err:
 	if (def->sql_type == MYSQL_TYPE_BLOB)
 	{
 	  my_error(ER_BLOB_CANT_HAVE_DEFAULT, MYF(0), def->change);
-	  DBUG_RETURN(TRUE);
+          goto err;
 	}
 	if ((def->def=alter->def))              // Use new default
           def->flags&= ~NO_DEFAULT_VALUE_FLAG;
@@ -5804,13 +5865,12 @@ view_err:
     }
   }
   def_it.rewind();
-  List_iterator<create_field> find_it(create_list);
   while ((def=def_it++))			// Add new columns
   {
     if (def->change && ! def->field)
     {
       my_error(ER_BAD_FIELD_ERROR, MYF(0), def->change, table_name);
-      DBUG_RETURN(TRUE);
+      goto err;
     }
     if (!def->after)
       create_list.push_back(def);
@@ -5828,7 +5888,7 @@ view_err:
       if (!find)
       {
 	my_error(ER_BAD_FIELD_ERROR, MYF(0), def->after, table_name);
-	DBUG_RETURN(TRUE);
+        goto err;
       }
       find_it.after(def);			// Put element after this
     }
@@ -5837,13 +5897,13 @@ view_err:
   {
     my_error(ER_BAD_FIELD_ERROR, MYF(0),
              alter_info->alter_list.head()->name, table_name);
-    DBUG_RETURN(TRUE);
+    goto err;
   }
   if (!create_list.elements)
   {
     my_message(ER_CANT_REMOVE_ALL_FIELDS, ER(ER_CANT_REMOVE_ALL_FIELDS),
                MYF(0));
-    DBUG_RETURN(TRUE);
+    goto err;
   }
 
   /*
@@ -5851,11 +5911,6 @@ view_err:
     for which some fields exists.
   */
 
-  List_iterator<Key> key_it(keys);
-  List_iterator<create_field> field_it(create_list);
-  List<key_part_spec> key_parts;
-
-  KEY *key_info=table->key_info;
   for (uint i=0 ; i < table->s->keys ; i++,key_info++)
   {
     char *key_name= key_info->name;
@@ -5959,7 +6014,7 @@ view_err:
 	  !my_strcasecmp(system_charset_info,key->name,primary_key_name))
       {
 	my_error(ER_WRONG_NAME_FOR_INDEX, MYF(0), key->name);
-	DBUG_RETURN(TRUE);
+        goto err;
       }
     }
   }
@@ -6201,6 +6256,7 @@ view_err:
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   if (fast_alter_partition)
   {
+    DBUG_ASSERT(!name_lock);
     DBUG_RETURN(fast_alter_partition_table(thd, table, alter_info,
                                            create_info, table_list,
                                            &create_list, &key_list,
@@ -6261,11 +6317,12 @@ view_err:
     We don't log the statement, it will be logged later.
   */
   tmp_disable_binlog(thd);
-  error= mysql_create_table(thd, new_db, tmp_name,
-                            create_info,create_list,key_list,1,0,0);
+  error= mysql_create_table_no_lock(thd, new_db, tmp_name,
+                                    create_info, create_list,
+                                    key_list, 1, 0, 0);
   reenable_binlog(thd);
   if (error)
-    DBUG_RETURN(error);
+    goto err;
 
   /* Open the table if we need to copy the data. */
   if (need_copy_table)
@@ -6526,17 +6583,6 @@ view_err:
 	      current_pid, thd->thread_id);
   if (lower_case_table_names)
     my_casedn_str(files_charset_info, old_name);
-  if (new_name != table_name || new_db != db)
-  {
-    if (!access(new_name_buff,F_OK))
-    {
-      error=1;
-      my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_name_buff);
-      VOID(quick_rm_table(new_db_type, new_db, tmp_name, FN_IS_TMP));
-      VOID(pthread_mutex_unlock(&LOCK_open));
-      goto err;
-    }
-  }
 
 #if !defined( __WIN__)
   if (table->file->has_transactions())
@@ -6546,7 +6592,6 @@ view_err:
       Win32 and InnoDB can't drop a table that is in use, so we must
       close the original table before doing the rename
     */
-    table->s->version= 0;                	// Force removal of table def
     close_cached_table(thd, table);
     table=0;					// Marker that table is closed
     no_table_reopen= TRUE;
@@ -6556,6 +6601,21 @@ view_err:
     table->file->extra(HA_EXTRA_FORCE_REOPEN);	// Don't use this file anymore
 #endif
 
+  if (new_name != table_name || new_db != db)
+  {
+    /*
+      Check that there is no table with target name. See the
+      comment describing code for 'simple' ALTER TABLE ... RENAME.
+    */
+    if (!access(new_name_buff,F_OK))
+    {
+      error=1;
+      my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_name_buff);
+      VOID(quick_rm_table(new_db_type, new_db, tmp_name, FN_IS_TMP));
+      VOID(pthread_mutex_unlock(&LOCK_open));
+      goto err;
+    }
+  }
 
   error=0;
   save_old_db_type= old_db_type;
@@ -6606,7 +6666,6 @@ view_err:
     */
     if (table)
     {
-      table->s->version= 0;            	        // Force removal of table def
       close_cached_table(thd,table);
     }
     VOID(pthread_mutex_unlock(&LOCK_open));
@@ -6647,7 +6706,6 @@ view_err:
     */
     if (table)
     {
-      table->s->version= 0;              	// Force removal of table def
       close_cached_table(thd,table);
     }
     VOID(quick_rm_table(old_db_type, db, old_name, FN_IS_TMP));
@@ -6673,7 +6731,6 @@ view_err:
     {						// This shouldn't happen
       if (table)
       {
-        table->s->version= 0;                   // Force removal of table def
 	close_cached_table(thd,table);		// Remove lock for table
       }
       VOID(pthread_mutex_unlock(&LOCK_open));
@@ -6730,6 +6787,13 @@ view_err:
   table_list->table=0;				// For query cache
   query_cache_invalidate3(thd, table_list, 0);
 
+  if (name_lock)
+  {
+    pthread_mutex_lock(&LOCK_open);
+    unlink_open_table(thd, name_lock, FALSE);
+    pthread_mutex_unlock(&LOCK_open);
+  }
+
 end_temporary:
   my_snprintf(tmp_name, sizeof(tmp_name), ER(ER_INSERT_INFO),
 	      (ulong) (copied + deleted), (ulong) deleted,
@@ -6749,6 +6813,12 @@ err1:
     VOID(quick_rm_table(new_db_type, new_db, tmp_name, FN_IS_TMP));
 
 err:
+  if (name_lock)
+  {
+    pthread_mutex_lock(&LOCK_open);
+    unlink_open_table(thd, name_lock, FALSE);
+    pthread_mutex_unlock(&LOCK_open);
+  }
   DBUG_RETURN(TRUE);
 }
 /* mysql_alter_table */

--- 1.566/sql/sql_yacc.yy	2007-05-11 21:51:11 +04:00
+++ 1.567/sql/sql_yacc.yy	2007-05-11 21:51:11 +04:00
@@ -1582,9 +1582,7 @@ create:
 	  lex->sql_command= SQLCOM_CREATE_TABLE;
 	  if (!lex->select_lex.add_table_to_list(thd, $5, NULL,
 						 TL_OPTION_UPDATING,
-						 (using_update_log ?
-						  TL_READ_NO_INSERT:
-						  TL_READ)))
+						 TL_WRITE))
 	    MYSQL_YYABORT;
 	  lex->create_list.empty();
 	  lex->key_list.empty();

--- 1.167/sql/table.h	2007-05-11 21:51:11 +04:00
+++ 1.168/sql/table.h	2007-05-11 21:51:11 +04:00
@@ -299,6 +299,8 @@ typedef struct st_table_share
 } TABLE_SHARE;
 
 
+extern ulong refresh_version;
+
 /* Information for one open table */
 enum index_hint_type
 {
@@ -314,8 +316,8 @@ struct st_table {
   handler	*file;
 #ifdef NOT_YET
   struct st_table *used_next, **used_prev;	/* Link to used tables */
-#endif
   struct st_table *open_next, **open_prev;	/* Link to open tables */
+#endif
   struct st_table *next, *prev;
 
   THD	*in_use;                        /* Which thread uses this */
@@ -431,7 +433,24 @@ struct st_table {
   my_bool force_index;
   my_bool distinct,const_table,no_rows;
   my_bool key_read, no_keyread;
-  my_bool locked_by_flush;
+  /*
+    Placeholder for an open table which prevents other connections
+    from taking name-locks on this table. Typically used with
+    TABLE_SHARE::version member to take an exclusive name-lock on
+    this table name -- a name lock that not only prevents other
+    threads from opening the table, but also blocks other name
+    locks. This is achieved by:
+    - setting open_placeholder to 1 - this will block other name
+      locks, as wait_for_locked_table_name will be forced to wait,
+      see table_is_used for details.
+    - setting version to 0 - this will force other threads to close
+      the instance of this table and wait (this is the same approach
+      as used for usual name locks).
+    An exclusively name-locked table currently can have no handler
+    object associated with it (db_stat is always 0), but please do
+    not rely on that.
+  */
+  my_bool open_placeholder;
   my_bool locked_by_logger;
   my_bool no_replicate;
   my_bool locked_by_name;
@@ -492,7 +511,13 @@ struct st_table {
     read_set= &def_read_set;
     write_set= &def_write_set;
   }
-
+  /* Is table open or should be treated as such by name-locking? */
+  inline bool is_name_opened() { return db_stat || open_placeholder; }
+  /*
+    Is this instance of the table should be reopen or represents a name-lock?
+  */
+  inline bool needs_reopen_or_name_lock()
+  { return s->version != refresh_version; }
 };
 
 enum enum_schema_table_state
@@ -888,6 +913,12 @@ typedef struct st_table_list
     used for implicit LOCK TABLES only and won't be used in real statement.
   */
   bool          prelocking_placeholder;
+  /*
+    This TABLE_LIST object corresponds to the table to be created
+    so it is possible that it does not exist (used in CREATE TABLE
+    ... SELECT implementation).
+  */
+  bool          create;
 
   enum enum_schema_table_state schema_table_state;
   void calc_md5(char *buffer);
@@ -895,7 +926,11 @@ typedef struct st_table_list
   int view_check_option(THD *thd, bool ignore_failure);
   bool setup_underlying(THD *thd);
   void cleanup_items();
-  bool placeholder() {return derived || view || schema_table || !table; }
+  bool placeholder()
+  {
+    return derived || view || schema_table || create && !table->db_stat ||
+           !table;
+  }
   void print(THD *thd, String *str);
   bool check_single_table(st_table_list **table, table_map map,
                           st_table_list *view);
--- New file ---
+++ mysql-test/r/create_select-big.result	07/05/11 21:51:00
drop table if exists t1,t2,t3,t4,t5;
set session debug="+d,sleep_create_select_before_create";
create table t1 select 1 as i;;
create table t1 (j char(5));
ERROR 42S01: Table 't1' already exists
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 select 1 as i;;
create table t1 select "Test" as j;
ERROR 42S01: Table 't1' already exists
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t3 (j char(5));
create table t1 select 1 as i;;
create table t1 like t3;
ERROR 42S01: Table 't1' already exists
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 select 1 as i;;
rename table t3 to t1;
ERROR 42S01: Table 't1' already exists
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 select 1 as i;;
alter table t3 rename to t1;
ERROR 42S01: Table 't1' already exists
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 select 1 as i;;
alter table t3 rename to t1, add k int;
ERROR 42S01: Table 't1' already exists
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t3;
set session debug="-d,sleep_create_select_before_create:+d,sleep_create_select_before_open";
create table t1 select 1 as i;;
drop table t1;
create table t1 select 1 as i;;
rename table t1 to t2;
drop table t2;
create table t1 select 1 as i;;
select * from t1;
i
1
drop table t1;
create table t1 select 1 as i;;
insert into t1 values (2);
select * from t1;
i
1
2
drop table t1;
set @a:=0;
create table t1 select 1 as i;;
create trigger t1_bi before insert on t1 for each row set @a:=1;
select @a;
@a
0
drop table t1;
set session debug="-d,sleep_create_select_before_open:+d,sleep_create_select_before_lock";
create table t1 select 1 as i;;
drop table t1;
create table t1 select 1 as i;;
rename table t1 to t2;
drop table t2;
create table t1 select 1 as i;;
select * from t1;
i
1
drop table t1;
create table t1 select 1 as i;;
insert into t1 values (2);
select * from t1;
i
1
2
drop table t1;
set @a:=0;
create table t1 select 1 as i;;
create trigger t1_bi before insert on t1 for each row set @a:=1;
select @a;
@a
0
drop table t1;
set session debug="-d,sleep_create_select_before_lock:+d,sleep_create_select_before_check_if_exists";
create table t1 (i int);
create table if not exists t1 select 1 as i;;
drop table t1;
Warnings:
Note	1050	Table 't1' already exists
create table t1 (i int);
set @a:=0;
create table if not exists t1 select 1 as i;;
create trigger t1_bi before insert on t1 for each row set @a:=1;
Warnings:
Note	1050	Table 't1' already exists
select @a;
@a
0
select * from t1;
i
1
drop table t1;
set session debug="-d,sleep_create_select_before_check_if_exists";
create table t2 (a int);
create table t4 (b int);
lock table t4 write;
select 1;
1
1
create table t3 as select * from t4;;
create table t1 select * from t2, t3;;
unlock tables;
select * from t1;
a	b
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t3;
lock table t4 read;
select 1;
1
1
rename table t4 to t3;;
create table if not exists t1 select 1 as i from t2, t3;;
create table t5 (j int);
rename table t5 to t1;
unlock tables;
Warnings:
Note	1050	Table 't1' already exists
select * from t1;
j
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `j` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2, t3;

--- New file ---
+++ mysql-test/t/create_select-big.test	07/05/11 21:51:00
# Tests for various aspects of CREATE TABLE ... SELECT implementation
#
# Note that we don't test general CREATE TABLE ... SELECT functionality here as
# it is already covered by create.test. We are more interested in extreme cases.
#
# This test takes rather long time so let us run it only in --big-test mode
--source include/big_test.inc
# We are using some debug-only features in this test
--source include/have_debug.inc

# Create auxilliary connections
connect (addconroot1, localhost, root,,);
connect (addconroot2, localhost, root,,);
connect (addconroot3, localhost, root,,);
connection default;

--disable_warnings
drop table if exists t1,t2,t3,t4,t5;
--enable_warnings


#
# Tests for concurrency problems.
#
# We introduce delays between various stages of table creation
# and check that other statements dealing with this table cannot
# interfere during those delays.
#
# What happens in situation when other statement messes with
# table to be created before it is created ?
# Concurrent CREATE TABLE
set session debug="+d,sleep_create_select_before_create";
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
--error ER_TABLE_EXISTS_ERROR
create table t1 (j char(5));
connection default;
--reap
show create table t1;
drop table t1;
# Concurrent CREATE TABLE ... SELECT
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
--error ER_TABLE_EXISTS_ERROR
create table t1 select "Test" as j;
connection default;
--reap
show create table t1;
drop table t1;
# Concurrent CREATE TABLE LIKE
create table t3 (j char(5));
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
--error ER_TABLE_EXISTS_ERROR
create table t1 like t3;
connection default;
--reap
show create table t1;
drop table t1;
# Concurrent RENAME TABLE
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
--error ER_TABLE_EXISTS_ERROR
rename table t3 to t1;
connection default;
--reap
show create table t1;
drop table t1;
# Concurrent ALTER TABLE RENAME
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
--error ER_TABLE_EXISTS_ERROR
alter table t3 rename to t1;
connection default;
--reap
show create table t1;
drop table t1;
# Concurrent ALTER TABLE RENAME which also adds column
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
--error ER_TABLE_EXISTS_ERROR
alter table t3 rename to t1, add k int;
connection default;
--reap
show create table t1;
drop table t1, t3;
# What happens if other statement sneaks in after the table
# creation but before its opening ?
set session debug="-d,sleep_create_select_before_create:+d,sleep_create_select_before_open";
# Concurrent DROP TABLE
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
drop table t1;
connection default;
--reap
# Concurrent RENAME TABLE
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
rename table t1 to t2;
connection default;
--reap
drop table t2;
# Concurrent SELECT
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
select * from t1;
connection default;
--reap
drop table t1;
# Concurrent INSERT
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
insert into t1 values (2);
connection default;
--reap
select * from t1;
drop table t1;
# Concurrent CREATE TRIGGER 
set @a:=0;
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
create trigger t1_bi before insert on t1 for each row set @a:=1;
connection default;
--reap
select @a;
drop table t1;
# Okay, now the same tests for the potential gap between open and lock
set session debug="-d,sleep_create_select_before_open:+d,sleep_create_select_before_lock";
# Concurrent DROP TABLE
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
drop table t1;
connection default;
--reap
# Concurrent RENAME TABLE
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
rename table t1 to t2;
connection default;
--reap
drop table t2;
# Concurrent SELECT
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
select * from t1;
connection default;
--reap
drop table t1;
# Concurrent INSERT
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
insert into t1 values (2);
connection default;
--reap
select * from t1;
drop table t1;
# Concurrent CREATE TRIGGER 
set @a:=0;
--send create table t1 select 1 as i;
connection addconroot1;
--sleep 2
create trigger t1_bi before insert on t1 for each row set @a:=1;
connection default;
--reap
select @a;
drop table t1;
# Some tests for case with existing table
set session debug="-d,sleep_create_select_before_lock:+d,sleep_create_select_before_check_if_exists";
create table t1 (i int);
# Concurrent DROP TABLE
--send create table if not exists t1 select 1 as i;
connection addconroot1;
--sleep 2
drop table t1;
connection default;
--reap
# Concurrent CREATE TRIGGER 
create table t1 (i int);
set @a:=0;
--send create table if not exists t1 select 1 as i;
connection addconroot1;
--sleep 2
create trigger t1_bi before insert on t1 for each row set @a:=1;
connection default;
--reap
select @a;
select * from t1;
drop table t1;
set session debug="-d,sleep_create_select_before_check_if_exists";


# Test for some details of CREATE TABLE ... SELECT implementation.
#
# We check that create placeholder is handled properly if we have
# to reopen tables in open_tables().
# This test heavily relies on current implementation of name-locking/
# table cache so it may stop working if it changes. OTOH it such problem
# will serve as warning that such changes should not be done lightly.
create table t2 (a int);
create table t4 (b int);
connection addconroot2;
lock table t4 write;
select 1;
connection addconroot1;
# Create placeholder/name-lock for t3
--send create table t3 as select * from t4;
--sleep 2
connection default;
# This statement creates placeholder for t1, then opens t2,
# then meets name-lock for t3 and then reopens all tables
--send create table t1 select * from t2, t3;
--sleep 2
connection addconroot2;
unlock tables;
connection addconroot1;
--reap
connection default;
--reap
select * from t1;
show create table t1;
drop table t1, t3;
# Now similar test which proves that we really temporarily
# remove placeholder when we reopen tables.
connection addconroot2;
lock table t4 read;
select 1;
connection addconroot1;
# Create name-lock for t3 
--send rename table t4 to t3;
--sleep 2
connection default;
# This statement creates placeholder for t1, then opens t2,
# then meets name-lock for t3 and then reopens all tables
--send create table if not exists t1 select 1 as i from t2, t3;
--sleep 2
connection addconroot3;
# We should be able to take name-lock on table t1 as we should not have
# open placeholder for it at this point (otherwise it is possible to
# come-up with situation which will lead to deadlock, e.g. think of
# concurrent CREATE TABLE t1 SELECT * FROM t2 and RENAME TABLE t2 TO t1)
create table t5 (j int);
# This statement takes name-lock on t1 and therefore proves 
# that there is no active open placeholder for it.
rename table t5 to t1;
connection addconroot2;
unlock tables;
connection addconroot1;
--reap
connection default;
--reap
select * from t1;
show create table t1;
drop table t1, t2, t3;


--- 1.63/mysql-test/r/trigger.result	2007-05-11 21:51:11 +04:00
+++ 1.64/mysql-test/r/trigger.result	2007-05-11 21:51:11 +04:00
@@ -1414,4 +1414,39 @@ id	val
 DROP TRIGGER trg27006_a_insert;
 DROP TRIGGER trg27006_a_update;
 drop table t1,t2;
+drop table if exists t1, t2, t3;
+create table t1 (i int);
+create trigger t1_bi before insert on t1 for each row set new.i = 7;
+create trigger t1_ai after insert on t1 for each row set @a := 7;
+create table t2 (j int);
+insert into t2 values (1), (2);
+set @a:="";
+create table if not exists t1 select * from t2;
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+i
+7
+7
+select @a;
+@a
+7
+drop trigger t1_bi;
+drop trigger t1_ai;
+create table t3 (isave int);
+create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
+create table if not exists t1 select * from t2;
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+i
+7
+7
+1
+2
+select * from t3;
+isave
+1
+2
+drop table t1, t2, t3;
 End of 5.0 tests

--- 1.73/mysql-test/t/trigger.test	2007-05-11 21:51:11 +04:00
+++ 1.74/mysql-test/t/trigger.test	2007-05-11 21:51:11 +04:00
@@ -1737,4 +1737,30 @@ DROP TRIGGER trg27006_a_insert;
 DROP TRIGGER trg27006_a_update;
 drop table t1,t2;
 
+#
+# Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
+#
+
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+create table t1 (i int);
+create trigger t1_bi before insert on t1 for each row set new.i = 7;
+create trigger t1_ai after insert on t1 for each row set @a := 7;
+create table t2 (j int);
+insert into t2 values (1), (2);
+set @a:="";
+create table if not exists t1 select * from t2;
+select * from t1;
+select @a;
+# Let us check that trigger that involves table also works ok.
+drop trigger t1_bi;
+drop trigger t1_ai;
+create table t3 (isave int);
+create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
+create table if not exists t1 select * from t2;
+select * from t1;
+select * from t3;
+drop table t1, t2, t3;
+
 --echo End of 5.0 tests

--- 1.91/sql/sql_trigger.cc	2007-05-11 21:51:11 +04:00
+++ 1.92/sql/sql_trigger.cc	2007-05-11 21:51:11 +04:00
@@ -279,7 +279,7 @@ bool mysql_create_or_drop_trigger(THD *t
   /* 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;

--- 1.139/mysql-test/r/create.result	2007-05-11 21:51:11 +04:00
+++ 1.140/mysql-test/r/create.result	2007-05-11 21:51:11 +04:00
@@ -782,6 +782,100 @@ t1	CREATE TABLE `t1` (
   `i` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295
 drop table t1;
+create table t1 select * from t2;
+ERROR 42S02: Table 'test.t2' doesn't exist
+create table t1 select * from t1;
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
+create table t1 (primary key(a)) select "b" as b;
+ERROR 42000: Key column 'a' doesn't exist in table
+create table t1 (a int);
+create table if not exists t1 select 1 as a, 2 as b;
+ERROR 21S01: Column count doesn't match value count at row 1
+drop table t1;
+create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+create table t1 (i int);
+create table t1 select 1 as i;
+ERROR 42S01: Table 't1' already exists
+create table if not exists t1 select 1 as i;
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+i
+1
+create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
+select * from t1;
+i
+1
+alter table t1 add primary key (i);
+create table if not exists t1 (select 2 as i) union all (select 2 as i);
+ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
+select * from t1;
+i
+1
+2
+drop table t1;
+create temporary table t1 (j int);
+create table if not exists t1 select 1;
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+j
+1
+drop temporary table t1;
+select * from t1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+drop table t1;
+ERROR 42S02: Unknown table 't1'
+create table t1 (i int);
+insert into t1 values (1), (2);
+lock tables t1 read;
+create table t2 select * from t1;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+create table if not exists t2 select * from t1;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables;
+create table t2 (j int);
+lock tables t1 read;
+create table t2 select * from t1;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+create table if not exists t2 select * from t1;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables;
+lock table t1 read, t2 read;
+create table t2 select * from t1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+create table if not exists t2 select * from t1;
+ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+unlock tables;
+lock table t1 read, t2 write;
+create table t2 select * from t1;
+ERROR 42S01: Table 't2' already exists
+create table if not exists t2 select * from t1;
+Warnings:
+Note	1050	Table 't2' already exists
+select * from t1;
+i
+1
+2
+unlock tables;
+drop table t2;
+lock tables t1 read;
+create temporary table t2 select * from t1;
+create temporary table if not exists t2 select * from t1;
+Warnings:
+Note	1050	Table 't2' already exists
+select * from t2;
+i
+1
+2
+1
+2
+unlock tables;
+drop table t1, t2;
 create table t1 (upgrade int);
 drop table t1;
 End of 5.0 tests

--- 1.92/mysql-test/t/create.test	2007-05-11 21:51:11 +04:00
+++ 1.93/mysql-test/t/create.test	2007-05-11 21:51:11 +04:00
@@ -673,6 +673,117 @@ alter table t1 max_rows=100000000000;
 show create table t1;
 drop table t1;
 
+
+#
+# Tests for errors happening at various stages of CREATE TABLES ... SELECT
+#
+# (Also checks that it behaves atomically in the sense that in case
+#  of error it is automatically dropped if it has not existed before.)
+#
+# Error during open_and_lock_tables() of tables
+--error ER_NO_SUCH_TABLE
+create table t1 select * from t2;
+# Rather special error which also caught during open tables pahse
+--error ER_UPDATE_TABLE_USED
+create table t1 select * from t1;
+# Error which happens before select_create::prepare()
+--error ER_CANT_AGGREGATE_2COLLATIONS
+create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
+# Error during table creation
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+create table t1 (primary key(a)) select "b" as b;
+# Error in select_create::prepare() which is not related to table creation
+create table t1 (a int);
+--error ER_WRONG_VALUE_COUNT_ON_ROW
+create table if not exists t1 select 1 as a, 2 as b;
+drop table t1;
+# Finally error which happens during insert
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);
+# What happens if table already exists ?
+create table t1 (i int);
+--error ER_TABLE_EXISTS_ERROR
+create table t1 select 1 as i;
+create table if not exists t1 select 1 as i;
+select * from t1;
+# Error before select_create::prepare()
+--error ER_CANT_AGGREGATE_2COLLATIONS
+create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
+select * from t1;
+# Error which happens during insertion of rows
+alter table t1 add primary key (i);
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+create table if not exists t1 (select 2 as i) union all (select 2 as i);
+select * from t1;
+drop table t1;
+
+
+# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent
+# results of CREATE TABLE ... SELECT when temporary table exists").
+# In this situation we either have to create non-temporary table and
+# insert data in it or insert data in temporary table without creation
+# of permanent table. Since currently temporary tables always shadow
+# permanent tables we adopt second approach.
+create temporary table t1 (j int);
+create table if not exists t1 select 1;
+select * from t1;
+drop temporary table t1;
+--error ER_NO_SUCH_TABLE
+select * from t1;
+--error ER_BAD_TABLE_ERROR
+drop table t1;
+
+
+#
+# CREATE TABLE ... SELECT and LOCK TABLES
+#
+# There is little sense in using CREATE TABLE ... SELECT under
+# LOCK TABLES as it mostly does not work. At least we check that
+# the server doesn't crash, hang and produces sensible errors.
+# Includes test for bug #20662 "Infinite loop in CREATE TABLE
+# IF NOT EXISTS ... SELECT with locked tables".
+create table t1 (i int);
+insert into t1 values (1), (2);
+lock tables t1 read;
+--error ER_TABLE_NOT_LOCKED
+create table t2 select * from t1;
+--error ER_TABLE_NOT_LOCKED
+create table if not exists t2 select * from t1;
+unlock tables;
+create table t2 (j int);
+lock tables t1 read;
+--error ER_TABLE_NOT_LOCKED
+create table t2 select * from t1;
+# This should not be ever allowed as it will undermine
+# lock-all-at-once approach
+--error ER_TABLE_NOT_LOCKED
+create table if not exists t2 select * from t1;
+unlock tables;
+lock table t1 read, t2 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create table t2 select * from t1;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create table if not exists t2 select * from t1;
+unlock tables;
+lock table t1 read, t2 write;
+--error ER_TABLE_EXISTS_ERROR
+create table t2 select * from t1;
+# This is the only case which really works.
+create table if not exists t2 select * from t1;
+select * from t1;
+unlock tables;
+drop table t2;
+
+# OTOH CREATE TEMPORARY TABLE ... SELECT should work
+# well under LOCK TABLES.
+lock tables t1 read;
+create temporary table t2 select * from t1;
+create temporary table if not exists t2 select * from t1;
+select * from t2;
+unlock tables;
+drop table t1, t2;
+
+
 #
 # Bug#21772: can not name a column 'upgrade' when create a table
 #
@@ -714,6 +825,7 @@ TRUNCATE table t2;
 INSERT INTO t2 select * from t1;
 SELECT * from t2;
 drop table t1,t2;
+
 
 #
 # Test incorrect database names

--- 1.93/sql/sql_handler.cc	2007-05-11 21:51:11 +04:00
+++ 1.94/sql/sql_handler.cc	2007-05-11 21:51:11 +04:00
@@ -682,7 +682,7 @@ int mysql_ha_flush(THD *thd, TABLE_LIST 
     while (*table_ptr)
     {
       if ((mode_flags & MYSQL_HA_FLUSH_ALL) ||
-          ((*table_ptr)->s->version != refresh_version))
+          (*table_ptr)->needs_reopen_or_name_lock())
       {
         /* The first time it is required, lock for close_thread_table(). */
         if (! did_lock && ! is_locked)
@@ -783,15 +783,22 @@ void mysql_ha_mark_tables_for_reopen(THD
   safe_mutex_assert_owner(&LOCK_open);
   for (; table; table= table->next)
   {
-    TABLE_LIST *hash_tables;
-    if ((hash_tables= (TABLE_LIST*) hash_search(&thd->handler_tables_hash,
-                                                (byte*) table->alias,
-                                                strlen(table->alias) + 1)))
+    /*
+      Some elements in open table list, for example placeholders used for
+      name-locking, can have alias set to 0.
+    */
+    if (table->alias)
     {
-      /* Mark table as ready for reopen. */
-      hash_tables->table= NULL;
-      /* End open index/table scans. */
-      table->file->ha_index_or_rnd_end();
+      TABLE_LIST *hash_tables;
+      if ((hash_tables= (TABLE_LIST*) hash_search(&thd->handler_tables_hash,
+                                                  (byte*) table->alias,
+                                                  strlen(table->alias) + 1)))
+      {
+        /* Mark table as ready for reopen. */
+        hash_tables->table= NULL;
+        /* End open index/table scans. */
+        table->file->ha_index_or_rnd_end();
+      }
     }
   }
   DBUG_VOID_RETURN;

--- 1.212/sql/sql_prepare.cc	2007-05-11 21:51:11 +04:00
+++ 1.213/sql/sql_prepare.cc	2007-05-11 21:51:11 +04:00
@@ -1492,8 +1492,21 @@ static bool mysql_test_create_table(Prep
 
   if (select_lex->item_list.elements)
   {
+    if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
+    {
+      lex->link_first_table_back(create_table, link_to_local);
+      create_table->create= TRUE;
+    }
+
+    if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0))
+      DBUG_RETURN(TRUE);
+
+    if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
+      create_table= lex->unlink_first_table(&link_to_local);
+
     select_lex->context.resolve_in_select_list= TRUE;
-    res= select_like_stmt_test_with_open(stmt, tables, 0, 0);
+
+    res= select_like_stmt_test(stmt, 0, 0);
   }
 
   /* put tables back for PS rexecuting */
Thread
bk commit into 5.1 tree (dlenev:1.2510) BUG#20662dlenev11 May