MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:dlenev Date:December 5 2006 12:44pm
Subject:bk commit into 5.1 tree (dlenev:1.2342)
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, 2006-12-05 15:44:34+03:00, dlenev@stripped +12 -0
  Proposed patch for bugs:
    #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT
  	  with locked tables"
    #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
    #24738  "CREATE TABLE ... SELECT is not isolated properly"
  
  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.
  Finally, 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).
  
  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.
  
  Questions for reviewer marked by QQ.

  mysql-test/r/create_select-big.result@stripped, 2006-12-05 15:44:31+03:00, dlenev@stripped +257 -0
    New BitKeeper file ``mysql-test/r/create_select-big.result''

  mysql-test/r/create_select-big.result@stripped, 2006-12-05 15:44:31+03:00, dlenev@stripped +0 -0

  mysql-test/r/trigger.result@stripped, 2006-12-05 15:44:30+03:00, dlenev@stripped +35 -0
    Added test case for bug#20903 "Crash when using CREATE TABLE .. SELECT
    and triggers".

  mysql-test/t/create_select-big.test@stripped, 2006-12-05 15:44:30+03:00, dlenev@stripped +378 -0
    New BitKeeper file ``mysql-test/t/create_select-big.test''

  mysql-test/t/create_select-big.test@stripped, 2006-12-05 15:44:30+03:00, dlenev@stripped +0 -0

  mysql-test/t/trigger.test@stripped, 2006-12-05 15:44:30+03:00, dlenev@stripped +26 -0
    Added test case for bug#20903 "Crash when using CREATE TABLE .. SELECT
    and triggers".

  sql/lock.cc@stripped, 2006-12-05 15:44:30+03: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, 2006-12-05 15:44:30+03:00, dlenev@stripped +5 -1
    Added declaration of auxiliary function table_cache_insert_placeholder()
    which is used for creation of table placeholders for name-locking.
    Added declaration of table_cache_has_placeholder() which is used
    for checking if there is pending CREATE TABLE ... SELECT operation
    for this table.
    MYSQL_OPEN_IGNORE_LOCKED_TABLES flag is no longer used. Instead 
    MYSQL_OPEN_TEMPORARY_ONLY option was added.

  sql/sql_base.cc@stripped, 2006-12-05 15:44:30+03:00, dlenev@stripped +201 -14
    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 linked into THD::open_tables list
    and automatically removed during close_thread_tables() call 
    2) 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.
    
    open_tables():
      Implemented logic described above.
      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_name_locked_table()/
    close_old_data_files()/table_is_used()/lock_tables()/
    remove_table_from_cache():
      Added support for create placeholders.
    Added auxiliary table_cache_insert_placeholder() routine which
    simplifies creation name-lock/create placeholder.
    Added table_cache_has_placeholder() method which is used for
    checking if there is pending CREATE TABLE ... SELECT for this
    table (i.e. there is create placeholder for it). 

  sql/sql_insert.cc@stripped, 2006-12-05 15:44:30+03:00, dlenev@stripped +139 -73
    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() to implement this approach.
    Also added auxiliary drop_open_table() routine which simplifies
    dropping of open table and used for clean-up after failed
    CREATE TABLE ... SELECT statement.

  sql/sql_parse.cc@stripped, 2006-12-05 15:44:30+03:00, dlenev@stripped +17 -1
    With 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, 2006-12-05 15:44:30+03:00, dlenev@stripped +23 -4
    Now mysql_create_table_internal(), mysql_create_like_table() and
    mysql_alter_table() not only check that destination table doesn't
    exist on disk but also check that there is no create placeholder
    in table cache for it (i.e. there is no CREATE TABLE ... SELECT
    operation in progress for it).

  sql/sql_yacc.yy@stripped, 2006-12-05 15:44:30+03:00, dlenev@stripped +1 -3
    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, 2006-12-05 15:44:30+03:00, dlenev@stripped +9 -1
    Introduced TABLE::create_placeholder member for distinguishing
    placeholders used for table creation from normal name-locks.
    Introduced TABLE_LIST::create member which marks elements of
    table list corresponds to the table to be created.
    Small cleanup. 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

--- 1.97/sql/lock.cc	2006-12-05 15:44:41 +03:00
+++ 1.98/sql/lock.cc	2006-12-05 15:44:41 +03:00
@@ -874,8 +874,6 @@
 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;
@@ -903,29 +901,11 @@
       }
     }
   }
-  /*
-    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.448/sql/mysql_priv.h	2006-12-05 15:44:41 +03:00
+++ 1.449/sql/mysql_priv.h	2006-12-05 15:44:41 +03:00
@@ -970,11 +970,15 @@
 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);
+TABLE *table_cache_insert_placeholder(THD *thd, const char *key,
+                                     uint key_length);
 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);
 bool wait_for_tables(THD *thd);
 bool table_is_used(TABLE *table, bool wait_for_name_lock);
+bool table_cache_has_placeholder(THD *thd, const char *db,
+                                 const char *table_name);
 TABLE *drop_locked_tables(THD *thd,const char *db, const char *table_name);
 void abort_locked_tables(THD *thd,const char *db, const char *table_name);
 void execute_init_command(THD *thd, sys_var_str *init_command_var,
@@ -1676,7 +1680,7 @@
 #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.355/sql/sql_base.cc	2006-12-05 15:44:41 +03:00
+++ 1.356/sql/sql_base.cc	2006-12-05 15:44:41 +03:00
@@ -884,7 +884,8 @@
       {
 	TABLE *table=(TABLE*) hash_element(&open_cache,idx);
 	if (!table->s->log_table &&
-            ((table->s->version) < refresh_version && table->db_stat))
+            ((table->s->version) < refresh_version &&
+             (table->db_stat || table->create_placeholder)))
 	{
 	  found=1;
           DBUG_PRINT("signal", ("Waiting for COND_refresh"));
@@ -1129,7 +1130,7 @@
   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 ||
@@ -1140,6 +1141,9 @@
   }
   else
   {
+    /* Placeholders should be handled by the first alternative. */
+    DBUG_ASSERT(!table->create_placeholder);
+
     if (table->s->flush_version != flush_version)
     {
       table->s->flush_version= flush_version;
@@ -1768,8 +1772,19 @@
   share->flush_version=0;
   table->in_use = thd;
   check_unused();
-  table->next = thd->open_tables;
-  thd->open_tables = table;
+  if (orig_table.create_placeholder)
+  {
+    /*
+      TABLE object is already in THD::open_tables list so
+      we just need to set TABLE::next correctly.
+    */
+    table->next= orig_table.next;
+  }
+  else
+  {
+    table->next= thd->open_tables;
+    thd->open_tables= table;
+  }
   table->tablenr=thd->current_tablenr++;
   table->used_fields=0;
   table->const_table=0;
@@ -1782,6 +1797,59 @@
 
 
 /*
+  Create and insert into table cache placeholder for table which will
+  prevent its opening (or creation) (a.k.a lock table name).
+
+  SYNOPSIS
+    table_cache_insert_placeholder()
+      thd         Thread context
+      key         Table cache key for name to be locked
+      key_length  Table cache key length
+
+  RETURN VALUE
+    non-0  Pointer to TABLE object used for name locking.
+    0      Failure (OOM).
+*/
+
+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);
+}
+
+
+/*
   Open a table.
 
   SYNOPSIS
@@ -1796,8 +1864,8 @@
                           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.
@@ -1857,8 +1925,13 @@
     }
   }
 
-  if (!(flags & MYSQL_OPEN_IGNORE_LOCKED_TABLES) &&
-      (thd->locked_tables || thd->prelocked_mode))
+  if (flags & MYSQL_OPEN_TEMPORARY_ONLY)
+  {
+    my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db, table_list->table_name);
+    DBUG_RETURN(0);
+  }
+
+  if (thd->locked_tables || thd->prelocked_mode)
   {						// Using table locks
     TABLE *best_table= 0;
     int best_distance= INT_MIN;
@@ -2001,6 +2074,14 @@
         continue;
       }
 
+      /* Avoid self-deadlocks by detecting self-dependencies. */
+      if (table->create_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);
+      }
+
       /*
         There is a refresh in progress for this table
         Wait until the table is freed or the thread is killed.
@@ -2043,8 +2124,45 @@
       DBUG_RETURN(NULL);
     }
 
+    /*
+      It is better to supress warnings in cases when it is likely that table
+      does not exist than to use mysql_reset_errors(). As latter call will
+      also destroy all other warnings.
+    */
+    bool save_no_warnings_for_error= thd->no_warnings_for_error;
+    if (table_list->create)
+      thd->no_warnings_for_error= 1;
+
     error= open_unireg_entry(thd, table, table_list, alias, key, key_length,
                              mem_root, (flags & OPEN_VIEW_NO_PARSE));
+
+    thd->no_warnings_for_error= save_no_warnings_for_error;
+
+    if (error > 0 && thd->net.last_errno == ER_NO_SUCH_TABLE &&
+        table_list->create)
+    {
+      /*
+        Table to be created, so we need to create placeholder in table-cache.
+      */
+      my_free((gptr)table, MYF(0));
+      thd->clear_error();
+      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->create_placeholder= 1;
+      table->next= thd->open_tables;
+      thd->open_tables= table;
+      VOID(pthread_mutex_unlock(&LOCK_open));
+      DBUG_RETURN(table);
+    }
+
     if (error > 0)
     {
       my_free((gptr)table, MYF(0));
@@ -2253,8 +2371,26 @@
 
 
 /*
-  Reopen all tables with closed data files
-  One should have lock on LOCK_open when calling this
+  Reopen all tables with closed data files.
+
+  SYNOPSIS
+    reopen_tables()
+      thd         Thread context
+      get_locks   Should we get locks after reopening tables ?
+      in_refresh  QQ: don't quite get this parameter...
+
+  NOTES
+    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()).
+
+    One should have lock on LOCK_open when calling this.
+
+  RETURN VALUES
+    FALSE  Success
+    TRUE   Error
 */
 
 bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
@@ -2358,6 +2494,15 @@
 	}
         close_handle_and_leave_table_as_lock(table);
       }
+      else if (table->create_placeholder)
+      {
+        /*
+          We also have to "close" create placeholder here. Otherwise we can
+          get deadlocks. For example, in case of concurrent execution of
+          CREATE TABLE t1 SELECT * FROM t2 and RENAME TABLE t2 TO t1.
+        */
+        table->create_placeholder= 0;
+      }
     }
   }
   if (found)
@@ -2413,7 +2558,8 @@
       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->db_stat && search->s->version < refresh_version) ||
+           search->create_placeholder))
         DBUG_RETURN(1);
     }
   } while ((table=table->next));
@@ -2455,6 +2601,45 @@
 
 
 /*
+  Check if table is name-locked for creation by some other thread.
+
+  SYNOPSIS
+    table_cache_has_placeholder()
+      thd         Thread context
+      db          Tame of database for table in question
+      table_name  Table name
+
+  RETURN VALUES
+    TRUE   If table is name-locked for creation by some other thread.
+    FALSE  Otherwise.
+*/
+
+bool table_cache_has_placeholder(THD *thd, const char *db,
+                                 const char *table_name)
+{
+  char key[MAX_DBKEY_LENGTH];
+  uint key_length;
+  HASH_SEARCH_STATE state;
+  TABLE *search;
+  DBUG_ENTER("table_cache_has_placeholder");
+
+  key_length=(uint) (strmov(strmov(key,db)+1,table_name)-key)+1;
+  for (search= (TABLE*) hash_first(&open_cache, (byte*) key, key_length,
+                                   &state);
+      search ;
+      search= (TABLE*) hash_next(&open_cache, (byte*) key, key_length,
+                                 &state))
+  {
+    if (search->in_use == thd)
+      continue;
+    if (search->create_placeholder)
+        DBUG_RETURN(1);
+  }
+  DBUG_RETURN(0);
+}
+
+
+/*
   drop tables from locked list
 
   SYNOPSIS
@@ -3336,7 +3521,8 @@
       DBUG_RETURN(-1);
     for (table= tables; table; table= table->next_global)
     {
-      if (!table->placeholder() && !table->schema_table)
+      if (!table->placeholder() && !table->schema_table &&
+          !table->table->create_placeholder)
 	*(ptr++)= table->table;
     }
 
@@ -3391,7 +3577,8 @@
 
       for (table= tables; table != first_not_own; table= table->next_global)
       {
-        if (!table->placeholder() && !table->schema_table)
+        if (!table->placeholder() && !table->schema_table &&
+            !table->table->create_placeholder)
         {
           table->table->query_id= thd->query_id;
           if (check_lock_and_start_stmt(thd, table->table, table->lock_type))
@@ -6180,7 +6367,7 @@
       {
         DBUG_PRINT("info", ("Table was in use by other thread"));
         in_use->some_tables_deleted=1;
-        if (table->db_stat)
+        if (table->db_stat || table->create_placeholder)
         {
           DBUG_PRINT("info", ("Found another active instance of the table"));
   	  result=1;

--- 1.230/sql/sql_insert.cc	2006-12-05 15:44:41 +03:00
+++ 1.231/sql/sql_insert.cc	2006-12-05 15:44:41 +03:00
@@ -71,6 +71,8 @@
 static void unlink_blobs(register TABLE *table);
 #endif
 static bool check_view_insertability(THD *thd, TABLE_LIST *view);
+static void drop_open_table(THD *thd, TABLE *table, const char *db,
+                            const char *table_name);
 
 /* Define to force use of my_malloc() if the allocated memory block is big */
 
@@ -2763,8 +2765,8 @@
 ***************************************************************************/
 
 /*
-  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()
@@ -2779,19 +2781,29 @@
                           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.
+
+    QQ: May be it is better to get rid of this inconsistency and pre-open
+        even temporary tables ? OTOH it is likely to complicate code in
+        open_table().
+
+    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
@@ -2817,6 +2829,25 @@
   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->create_placeholder)
+  {
+    /* 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;
@@ -2848,8 +2879,15 @@
       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.
 
@@ -2859,10 +2897,6 @@
     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);
@@ -2870,52 +2904,66 @@
                             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 cluster.
+
+          QQ: What should we do in the second case ? Return an error ?
+        */
+        DBUG_ASSERT(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))
+        {
+          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);
 }
 
@@ -2985,6 +3033,7 @@
   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);
 }
 
@@ -3060,23 +3109,54 @@
   {
     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;
 }
 
+
+/*
+  Auxiliary routine which closes and drops open table.
+
+  SYNOPSIS
+    drop_open_table()
+      thd         Thread handle
+      table       TABLE object for table to be dropped
+      db_name     Name of database for this table
+      table_name  Name of this table
+
+  NOTES
+    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.
+*/
+
+static 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.
+      QQ: may be it is better to move this signal from this function?
+    */
+    thd->open_tables= unlink_open_table(thd, thd->open_tables, table);
+    quick_rm_table(table_type, db_name, table_name, 0);
+    VOID(pthread_mutex_unlock(&LOCK_open));
+  }
+}
+
+
 void select_create::abort()
 {
-  VOID(pthread_mutex_lock(&LOCK_open));
   if (thd->extra_lock)
   {
     mysql_unlock_tables(thd, thd->extra_lock);
@@ -3086,24 +3166,10 @@
   {
     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));
 }
 
 

--- 1.585/sql/sql_parse.cc	2006-12-05 15:44:41 +03:00
+++ 1.586/sql/sql_parse.cc	2006-12-05 15:44:41 +03:00
@@ -2948,7 +2948,13 @@
       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
@@ -2956,6 +2962,7 @@
         */
         if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
         {
+          create_table= lex->unlink_first_table(&link_to_local);
           TABLE_LIST *duplicate;
           if ((duplicate= unique_table(thd, create_table, select_tables)))
           {
@@ -2982,6 +2989,12 @@
           }
         }
 
+        /*
+          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,
@@ -3001,6 +3014,9 @@
 	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.372/sql/sql_table.cc	2006-12-05 15:44:41 +03:00
+++ 1.373/sql/sql_table.cc	2006-12-05 15:44:41 +03:00
@@ -3436,7 +3436,7 @@
   VOID(pthread_mutex_lock(&LOCK_open));
   if (!internal_tmp_table && !(create_info->options & HA_LEX_CREATE_TMP_TABLE))
   {
-    if (!access(path,F_OK))
+    if (table_cache_has_placeholder(thd, db, table_name) || !access(path,F_OK))
     {
       if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
         goto warn;
@@ -4641,9 +4641,20 @@
   }
   else
   {
+    bool exists;
     dst_path_length= build_table_filename(dst_path, sizeof(dst_path),
                                           db, table_name, reg_ext, 0);
-    if (!access(dst_path, F_OK))
+
+    /*
+      This is temporary solution as this critical section should
+      actually cover most of mysql_create_like_table() function.
+      See bugs #18950 and #23667 for more information.
+    */
+    pthread_mutex_lock(&LOCK_open);
+    exists= (table_cache_has_placeholder(thd, db, table_name) ||
+             !access(dst_path, F_OK));
+    pthread_mutex_unlock(&LOCK_open);
+    if (exists)
       goto table_exists;
   }
 
@@ -5329,7 +5340,14 @@
       VOID(pthread_mutex_lock(&LOCK_open));
       /* Then do a 'simple' rename of the table */
       error=0;
-      if (!access(new_name_buff,F_OK))
+      /*
+        QQ: How the fact that later close_cached_table() call
+            temporarily unlocks LOCK_open affects this check ?
+            The same question is valid for other places where
+            table_cache_has_placeholder() is called.
+      */
+      if (table_cache_has_placeholder(thd, new_db, new_name) ||
+          !access(new_name_buff,F_OK))
       {
 	my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_name);
 	error= -1;
@@ -6208,7 +6226,8 @@
     my_casedn_str(files_charset_info, old_name);
   if (new_name != table_name || new_db != db)
   {
-    if (!access(new_name_buff,F_OK))
+    if (table_cache_has_placeholder(thd, new_db, new_name) ||
+        !access(new_name_buff,F_OK))
     {
       error=1;
       my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_name_buff);

--- 1.507/sql/sql_yacc.yy	2006-12-05 15:44:41 +03:00
+++ 1.508/sql/sql_yacc.yy	2006-12-05 15:44:41 +03:00
@@ -1228,9 +1228,7 @@
 	  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))
 	    YYABORT;
 	  lex->create_list.empty();
 	  lex->key_list.empty();

--- 1.153/sql/table.h	2006-12-05 15:44:41 +03:00
+++ 1.154/sql/table.h	2006-12-05 15:44:41 +03:00
@@ -298,8 +298,8 @@
   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 */
@@ -401,6 +401,8 @@
   my_bool insert_or_update;             /* Can be used by the handler */
   my_bool alias_name_used;		/* true if table_name is alias */
   my_bool get_fields_in_item_tree;      /* Signal to fix_field */
+  /* Name lock placeholder for table to be created. */
+  my_bool create_placeholder;
 
   REGINFO reginfo;			/* field connections */
   MEM_ROOT mem_root;
@@ -815,6 +817,12 @@
     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;
 
   void calc_md5(char *buffer);
   void set_underlying_merge();
--- New file ---
+++ mysql-test/r/create_select-big.result	06/12/05 15:44:31
drop table if exists t1,t2,t3,t4,t5;
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;
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 './test/t1.frm' 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 t1 select * from t2, t3;;
create table t1 (j int);
drop table t1;
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, t2, t3;

--- New file ---
+++ mysql-test/t/create_select-big.test	06/12/05 15:44:30
# 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 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;
# QQ: What about error before 'select_create' creation ?
# 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
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
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").
# We should not be confused by presence of temporary table when
# we are creating base table.
#
# QQ: should not we rather create base table ?
#
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;
# QQ: may be it is worth to disallow this statement since
# it 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;


#
# 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 t1 select * from t2, t3;
--sleep 2
connection addconroot3;
# We should be able to create and drop t1 as we should not have create
# placeholder for it at this point (otherwise it is possible to come-up
# with situation which will lead to deadlock, e.g. in think of concurrent
# CREATE TABLE t1 SELECT * FROM t2 and RENAME TABLE t2 TO t1)
create table t1 (j int);
drop table t1;
connection addconroot2;
unlock tables;
connection addconroot1;
--reap
connection default;
--reap
select * from t1;
show create table t1;
drop table t1, t2, t3;


--- 1.49/mysql-test/r/trigger.result	2006-12-05 15:44:41 +03:00
+++ 1.50/mysql-test/r/trigger.result	2006-12-05 15:44:41 +03:00
@@ -1185,4 +1185,39 @@
 2	2
 13	13
 drop table t1;
+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.53/mysql-test/t/trigger.test	2006-12-05 15:44:41 +03:00
+++ 1.54/mysql-test/t/trigger.test	2006-12-05 15:44:41 +03:00
@@ -1440,4 +1440,30 @@
 drop table t1;
 
 
+#
+# 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
Thread
bk commit into 5.1 tree (dlenev:1.2342)dlenev5 Dec