• unknown's avatar
    Fix for: · c5a82455
    unknown authored
      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.
    
    Also note that we decided to postpone introduction of some tests
    for concurrent behaviour of CREATE TABLE ... SELECT till 5.1.
    The main reason for this is absence in 5.0 ability to set @@debug
    variable at runtime, which can be circumvented only by using several
    test files with individual .opt files. Since the latter is likely
    to slowdown test-suite unnecessary we chose not to push this tests
    into 5.0, but run them manually for this version and later push
    their optimized version into 5.1
    
    
    mysql-test/r/create.result:
      Extended test coverage for CREATE TABLE ... SELECT. In particular added
      tests for bug #24508 "Inconsistent results of CREATE TABLE ... SELECT
      when temporary table exists" and bug #20662 "Infinite loop in CREATE
      TABLE IF NOT EXISTS ... SELECT with locked tables".
    mysql-test/r/trigger.result:
      Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT
      and triggers"
    mysql-test/t/create.test:
      Extended test coverage for CREATE TABLE ... SELECT. In particular added
      tests for bug #24508 "Inconsistent results of CREATE TABLE ... SELECT
      when temporary table exists" and bug #20662 "Infinite loop in CREATE
      TABLE IF NOT EXISTS ... SELECT with locked tables".
    mysql-test/t/trigger.test:
      Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT
      and triggers"
    sql/lock.cc:
      Now for creation of name-lock placeholder in lock_table_name() we use
      auxiliary function table_cache_insert_placeholder().
    sql/mysql_priv.h:
      Made build_table_path() function available outside of sql_table.cc file.
      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 table_cache_has_open_placeholder() function which
      can be used for checking if table cache contains an open placeholder for
      the table and if this placeholder was created by another thread.
      (This function is needed only in 5.0 where we use it in various versions
       of CREATE TABLE in order to protect it from concurrent CREATE TABLE
       ... SELECT operations for the table. Starting from 5.1 we use different
       approach so it is going to be removed there).
      Made close_old_data_files() static within sql_base.cc file. 
      Added auxiliary drop_open_table() routine.
      Moved declaration of refresh_version to table.h header to make it
      accessible from inline methods of TABLE class.
      MYSQL_OPEN_IGNORE_LOCKED_TABLES flag is no longer used. Instead
      MYSQL_OPEN_TEMPORARY_ONLY option was added.
    sql/sql_base.cc:
      Added support for the new approach to the handling of CREATE TABLE
      ... SELECT for base tables.
      
      Now we try to open and lock table to be created at the same time as
      the rest of tables used by this statement. If such table does not
      exist at this moment we create and place in the table cache special
      placeholder for it which prevents its creation or any other usage
      by other threads.
      
      Note significant distinctions of this placeholder from the placeholder
      used for normal name-lock: 1) It is treated like open table by other
      name-locks so it does not allow name-lock taking operations like DROP
      TABLE or RENAME TABLE to proceed. 2) it is linked into THD::open_tables
      list and automatically removed during close_thread_tables() call.
      
      open_tables():
        Implemented logic described above. To do this added
        auxiliary check_if_table_exists() function.
        Removed support for MYSQL_OPEN_IGNORE_LOCKED_TABLES option
        which is no longer used.
        Added MYSQL_OPEN_TEMPORARY_ONLY which is used to restrict
        search for temporary tables only.
      close_cached_tables()/close_thread_table()/reopen_tables()/
      close_old_data_files()/table_is_used()/remove_table_from_cache():
        Added support for open placeholders (note that we also use them
        when we need to re-open tables during flush).
      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 table_cache_has_open_placeholder() function which can be
      used for checking if table cache contains an open placeholder for
      the table and if this placeholder was created by another thread.
      (This function is needed only in 5.0 where we use it in various versions
       of CREATE TABLE in order to protect it from concurrent CREATE TABLE
       ... SELECT operations for the table. Starting from 5.1 we use different
       approach so it is going to be removed there).
    sql/sql_handler.cc:
      Adjusted mysql_ha_mark_tables_for_reopen() routine to properly
      handle placeholders which now can be linked into open tables
      list.
    sql/sql_insert.cc:
      Introduced new approach to handling of base tables in CREATE TABLE
      ... SELECT statement.
      
      Now we try to open and lock table to be created at the same time as
      the rest of tables used by this statement. If such table does not
      exist at this moment we create and place in the table cache special
      placeholder for it which prevents its creation or any other usage
      by other threads. By doing this we avoid races which existed with
      previous approach in which we created, opened and locked target in
      separate step without any special protection.
      This also allows properly calculate prelocking set in cases when
      target table already exists and has some on insert triggers.
        
      Note that we don't employ the same approach for temporary tables
      (this is okay as such tables are unaffected by other threads).
      
      Changed create_table_from_items() and select_create methods to
      implement this approach.
    sql/sql_parse.cc:
      The new approach to handling of CREATE TABLE ... SELECT for
      base tables assumes that all tables (including table to be
      created) are opened and (or) locked at the same time.
      So in cases when we create base table we have to pass to
      open_and_lock_tables() table list which includes target table.
    sql/sql_prepare.cc:
      The new approach to handling of CREATE TABLE ... SELECT for
      base tables assumes that all tables (including table to be
      created) are opened and (or) locked at the same time.
      So in cases when we create base table we have to pass to
      open_and_lock_tables() table list which includes target table.
    sql/sql_table.cc:
      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). Note that starting from 5.1 we
      use different approach in order to to protect CREATE TABLE ... SELECT
      from concurrent CREATE TABLE (ALTER TABLE ... RENAME) operations,
      the latter simply take name-locks on table before its creation
      (on target table name before renaming).
      
      Also made build_table_path() available from other files and
      asjusted calls to reopen_name_locked_table(), which now takes
      extra argument, which controls linking of open table into
      THD::open_tables list.
    sql/sql_trigger.cc:
      reopen_name_locked_tables() now has one more argument which controls
      linking of opened table into the THD::open_tables list.
    sql/sql_yacc.yy:
      The new approach to handling of CREATE TABLE ... SELECT statement
      for base tables assumes that all tables including table to be
      created are open and (or) locked at the same time. Therefore
      we need to set correct lock for target table.
    sql/table.h:
      Moved declaration of refresh_version variable from mysql_priv.h
      to make it accessible from inline methods of TABLE class. 
      Renamed TABLE::locked_by_flush member to open_placeholder since
      now it is also used for taking exclusive name-lock and not only
      by flush. 
      Introduced TABLE::is_name_opened() helper method which can be used
      to distinguish TABLE instances corresponding to open tables or
      placeholders for them from closed instances (e.g. due to their old
      version). Also introduced TABLE::needs_reopen_or_name_lock() helper
      which allows to check if TABLE instance corresponds to outdated
      version of table or to name-lock placeholder.
      Introduced TABLE_LIST::create member which marks elements of
      table list corresponds to the table to be created.
      Adjusted TABLE_LIST::placeholder() method to take into account 
      name-lock placeholders for tables to be created (this, for example,
      allows to properly handle such placeholders in lock_tables()).
    c5a82455
trigger.test 52.6 KB