MySQL Bugs: #42546: Backup: RESTORE fails, thinking it finds an existing table

Bug #42546	Backup: RESTORE fails, thinking it finds an existing table
Submitted:	2 Feb 2009 15:00	Modified:	14 Dec 2010 10:13
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	6.0-bzr	OS:	Linux
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any

Description:
Linux 32-bit; last revision:
horst@mysql.com-20090121133202-2ygkbxoxkuvkcmgb

Running the testcase of BUG#42519, with --engine=maria or with --engine=myisam, I get the same error after 10-15 seconds:

# 14:53:45 Query:  RESTORE FROM '/tmp/gentest28065.tmp' OVERWRITE  failed: 1684 Could not restore table `test`.`AAA`

Things fail before the engine's restore driver is created
('grep "myisam_backup::Restore" var/log/master.trace' shows no rows). They fail inside the restore kernel: during the execution of RESTORE,
- it drops the database (normal)
- then it creates the database (normal)
- then it creates a table with:

CREATE TABLE `AAA` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

T@8    : | | | | | | | | | >mysql_create_table
T@8    : | | | | | | | | | | >plugin_foreach_with_mask
T@8    : | | | | | | | | | | | mutex: LOCK_plugin (0x8d338c0) locking
T@8    : | | | | | | | | | | | mutex: LOCK_plugin (0x8d338c0) locked
T@8    : | | | | | | | | | | | mutex: LOCK_plugin (0x8d338c0) unlocking
T@8    : | | | | | | | | | | <plugin_foreach_with_mask
T@8    : | | | | | | | | | | mutex: LOCK_lock_db (0x8d2e6a0) locking
T@8    : | | | | | | | | | | mutex: LOCK_lock_db (0x8d2e6a0) locked
T@8    : | | | | | | | | | | >my_hash_first
T@8    : | | | | | | | | | | <my_hash_first
T@8    : | | | | | | | | | | mutex: LOCK_lock_db (0x8d2e6a0) unlocking
T@8    : | | | | | | | | | | >multi_alloc_root
T@8    : | | | | | | | | | | | >alloc_root
T@8    : | | | | | | | | | | | | enter: root: 0xab2ad830
T@8    : | | | | | | | | | | | | >my_malloc
T@8    : | | | | | | | | | | | | | my: size: 576  my_flags: 1040
T@8    : | | | | | | | | | | | | | exit: ptr: 0xa0988b8
T@8    : | | | | | | | | | | | | <my_malloc
T@8    : | | | | | | | | | | | | exit: ptr: 0xa0988c8
T@8    : | | | | | | | | | | | <alloc_root
T@8    : | | | | | | | | | | <multi_alloc_root
T@8    : | | | | | | | | | | >mdl_add_lock
T@8    : | | | | | | | | | | <mdl_add_lock
T@8    : | | | | | | | | | | mutex: LOCK_mdl (0x8d33c80) locking
T@8    : | | | | | | | | | | mutex: LOCK_mdl (0x8d33c80) locked
T@8    : | | | | | | | | | | >my_hash_first
T@8    : | | | | | | | | | | | exit: found key at 1
T@8    : | | | | | | | | | | <my_hash_first
T@8    : | | | | | | | | | | mutex: LOCK_mdl (0x8d33c80) unlocking
T@8    : | | | | | | | | | | >mdl_remove_lock
T@8    : | | | | | | | | | | <mdl_remove_lock
T@8    : | | | | | | | | | | >my_error
T@8    : | | | | | | | | | | | my: nr: 1050  MyFlags: 0  errno: 0
T@8    : | | | | | | | | | | | >my_message_sql
T@8    : | | | | | | | | | | | | error: error: 1050  message: 'Table 'AAA' already exists'

But that's weird, because the previous reference to table AAA before this error is from
T@16   : | | query: INSERT INTO `AAA` ( `time_key` ) VALUES ( 2 )
like this:
T@16   : | | | | | | | | | | | | | | error: error: 1146  message: 'Table 'test.AAA' doesn't exist'

So, T@16 says it doesn't exist (which is expected, as RESTORE dropped the database), but T@8 (RESTORE) says that it already exists.
I have verified that at the moment when RESTORE says that, the table files (.frm, .MYI, .MYD) do not exist on disk.
The "Table already exists" comes from here, in mysql_create_table():
  if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE))
  {
    if (lock_table_name_if_not_cached(thd, db, table_name, &target_lock_data))
    {
      result= TRUE;
      goto unlock;
    }
    if (!target_lock_data)
    {
      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;
    }
  }

So, target_lock_data==0. I dug, and it means that lock_table_name_if_not_cached() returned 0 and put 0 in target_lock_data, which in turns implies that something happened mdl_try_acquire_exclusive_lock() (weird or normal, I don't know).

As it's all in the SQL layer (table creation, metadata locks), and it happens even before the MyISAM or Maria restore drivers are created, I'm filing it as a "Server:backup" bug.
Note that in this tree, Maria uses the default lock-based driver.

How to repeat:
Run the testcase of BUG#42519, with --engine=myisam or --engine=maria.

Related to bug#42895.

Marking it "in progress" to indicate it is waiting for Bug#42895/WL#4844.

I can add to Guilhem's analyze that mdl_try_acquire_exclusive_lock() finds an existent MDL on the non-existent table. MDL rates this as a lock conflict. mysql_create_table() interprets this as ER_TABLE_EXISTS_ERROR.

However, this is a race condition. Another session tries to open the non-existent table. In open_table() is does first take a shared MDL on the table. This works independently from the table. open_table() would fail on open of the frm file and then release the MDL. But if our session starts CREATE TABLE and tries the exclusive lock just between these two steps in open_table(), it finds the MDL.

Here is the result file for a repeatable test case:

#
# Bug#42546 - Backup: RESTORE fails, thinking it finds an existing table
#
CREATE DATABASE mysql_db1;
#
# connection con1
# Start insert on the not-yet existing table
# Wait after taking the MDL
# Need to sync twice. Number 1 is consumed by General Log.
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish
                 EXECUTE 2';
INSERT INTO mysql_db1.t1 VALUES(1,"def");
#
# connection default
# Wait for General Log to reach sync point.
SET DEBUG_SYNC= 'now WAIT_FOR locked';
# Continue General Log.
SET DEBUG_SYNC= 'now SIGNAL finish';
# Wait for INSERT to reach sync point.
SET DEBUG_SYNC= 'now WAIT_FOR locked';
# Now INSERT has a MDL on the non-existent table mysql_db1.t1.
# Try to create that table.
CREATE TABLE mysql_db1.t1 (c1 INT, c2 VARCHAR(100), KEY(c1)) ENGINE=MyISAM;
ERROR 42S01: Table 't1' already exists
# Continue INSERT.
SET DEBUG_SYNC= 'now SIGNAL finish';
#
# connection con1
ERROR 42S02: Table 'mysql_db1.t1' doesn't exist
#
# connection default
SET DEBUG_SYNC= 'RESET';
DROP DATABASE mysql_db1;

Note that this is independent from RESTORE.
The sync point belongs here:

=== modified file 'sql/sql_base.cc'
--- sql/sql_base.cc     2009-05-12 16:41:11 +0000
+++ sql/sql_base.cc     2009-06-09 10:10:39 +0000
@@ -2570,6 +2588,7 @@ bool open_table(THD *thd, TABLE_LIST *ta
       DEBUG_SYNC(thd, "before_open_table_wait_refresh");
       DBUG_RETURN(TRUE);
     }
+    DEBUG_SYNC(thd, "after_open_table_mdl_shared");
   }
 
   /*

This could be deemed a server bug. But to avoid that, we should have
a locking scheme for RESTORE, which prevents other sessions, even to
open the tables in restore.

Ingo,
your analysis of a "race" between CREATE TABLE and SELECT on a non-existent table is correct here.

An MDL bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76910

2804 Jon Olav Hauglid	2009-06-23
      Bug#42546 Backup: RESTORE fails, thinking it finds an existing table
      
      The problem occured when a MDL locking conflict happened for a non-existent 
      table between a CREATE and a INSERT statement. The code for CREATE 
      interpreted this lock conflict to mean that the table existed, 
      which meant that the statement failed when it should not have.
      
      This patch changes the code for CREATE and CREATE LIKE when not in
      LOCK TABLES mode, to wait until it can get an exclusive metadata lock
      on the table name. This means that for the test case in the bug 
      description, CREATE will wait until INSERT completes.
      The patch also changes the way CREATE and CREATE LIKE checks for
      ER_TABLE_EXISTS_ERROR. This is now done by first checking the TDC
      and then for a .frm-file.
      
      For CREATE LIKE, the patch changes the locking order. Now, the
      exclusive metadata lock on the target table is taken first before
      the shared metadata lock on the source table. This is done to
      prevent deadlock.
      
      Note that the problem remains for LOCK TABLES mode where we cannot 
      wait for exclusive metadata lock for fear of deadlock. It also 
      remains for ALTER TABLE RENAME where changing the locking order
      is difficult. 
      
      Test case based on the bug description added to create.test.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/79765

2837 Jon Olav Hauglid	2009-07-31
      Bug#42546 Backup: RESTORE fails, thinking it finds an existing table
      
      The problem occured when a MDL locking conflict happened for a non-existent 
      table between a CREATE and a INSERT statement. The code for CREATE 
      interpreted this lock conflict to mean that the table existed, 
      which meant that the statement failed when it should not have.
      The problem could occur for CREATE TABLE, CREATE TABLE LIKE and
      ALTER TABLE RENAME.
      
      This is the first part of a patch to fix the problem. It includes
      changes for CREATE TABLE and CREATE TABLE LIKE backported from the
      mysql-6.1-fk tree.
      
      The patch prohibits CREATE TABLE and CREATE TABLE LIKE under 
      LOCK TABLES. Note that this is an incompatible change and must 
      be reflected in the documentation. Affected test cases have been
      updated.
      
      CREATE now uses normal open_and_lock_tables() code to acquire 
      exclusive locks. This means that for the test case in the bug 
      description, CREATE will wait until INSERT completes so that 
      it can get the exclusive lock. This resolves the reported bug.
      
      Bug42546.test contains tests for CREATE TABLE and CREATE TABLE LIKE.
      These tests will be merged into an existing test file before the 
      final commit is made.
      
      A complete patch with ALTER TABLE RENAMED will be committed once
      this partial patch has been approved.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/80833

2860 Konstantin Osipov	2009-08-14
      A pre-requisite for a fix for Bug#42546 "Backup: RESTORE fails, thinking it
      finds an existing table"
      Back-port from WL 148 "Foreign keys" feature tree a patch
      that introduced Prelocking_strategy class -- a way to parameterize
      open_tables() behaviour, implemented by Dmitry Lenev.
     @ sql/sql_base.cc
        Implement different prelocking strategies. Use an instance of
        prelocking_strategy in open_tables().
     @ sql/sql_class.h
        Add declarations for class Prelocking_strategy.
     @ sql/sql_lex.h
        Add a helper method to access last table of the global table list
        (lex->query_tables).
     @ sql/sql_parse.cc
        Use a special prelocking strategy when locking tables for LOCK TABLES.
     @ sql/sql_table.cc
        Use normal open_and_lock_tables_derived() in ALTER TABLE.
     @ sql/sql_yacc.yy
        Modify the grammar to not pollute the global table list with tables
        that should not be opened.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81394

2874 Jon Olav Hauglid	2009-08-24
      Bug#42546 Backup: RESTORE fails, thinking it finds an existing table
            
      The problem occured when a MDL locking conflict happened for a non-existent 
      table between a CREATE and a INSERT statement. The code for CREATE 
      interpreted this lock conflict to mean that the table existed, 
      which meant that the statement failed when it should not have.
      The problem could occur for CREATE TABLE, CREATE TABLE LIKE and
      ALTER TABLE RENAME.
      
      This patch fixes the problem for CREATE TABLE and CREATE TABLE LIKE.
      It is based on code backported from the mysql-6.1-fk tree written
      by Dmitry Lenev. CREATE now uses normal open_and_lock_tables() code 
      to acquire exclusive locks. This means that for the test case in the bug 
      description, CREATE will wait until INSERT completes so that it can 
      get the exclusive lock. This resolves the reported bug.
      
      The patch also prohibits CREATE TABLE and CREATE TABLE LIKE under 
      LOCK TABLES. Note that this is an incompatible change and must 
      be reflected in the documentation. Affected test cases have been
      updated.
            
      mdl_sync.test contains tests for CREATE TABLE and CREATE TABLE LIKE.
      
      Fixing the issue for ALTER TABLE RENAME is beyond the scope of this
      patch. ALTER TABLE cannot be prohibited from working under LOCK TABLES
      as this could seriously impact customers and a proper fix would require
      a significant rewrite.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090824135126-2rngffvth14a8bpj) (version source revid:kostja@sun.com-20090814152205-a9yt8p219kfenbbd) (merge vers: 5.4.4-alpha) (pib:11)

Pushed to mysql-next-bugfixing (5.4.4-alpha)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81597

2798 Jon Olav Hauglid	2009-08-26
      Followup to Bug#42546 Backup: RESTORE fails, thinking it finds an existing table
      
      This patch updates lowercase_table2.test with the changed error message
      CREATE TABLE produces if it fails because it finds an matching TABLE_SHARE 
      in the TDC even if the .FRM/.MYD has been removed from disk.
      
      With the changes introduced in Bug#42546, CREATE TABLE uses open_tables()
      which will find the TDC entry and fail in open_table_from_share() with
      ER_FILE_NOT_FOUND. Before, CREATE TABLE would not use open_tables() and
      fail with ER_TABLE_EXISTS_ERROR upon finding the TDC entry in
      mysql_create_table_no_lock().

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090828095047-jbux7pie1yjnwpce) (version source revid:jon.hauglid@sun.com-20090826115648-5vtj0mqj4767q00v) (merge vers: 5.4.4-alpha) (pib:11)

Not in any released version. No changelog entry needed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/93205

3003 Konstantin Osipov	2009-12-08
      Backport of:
      ----------------------------------------------------------
      revno: 2617.69.24
      committer: Konstantin Osipov <kostja@sun.com>
      branch nick: 5.4-42546
      timestamp: Fri 2009-08-14 19:22:05 +0400
      message:
        A pre-requisite for a fix for Bug#42546 "Backup: RESTORE fails, thinking it
        finds an existing table"
        Back-port from WL 148 "Foreign keys" feature tree a patch
        that introduced Prelocking_strategy class -- a way to parameterize
        open_tables() behaviour, implemented by Dmitry Lenev.
      
      (Part of WL#4284).
     @ sql/sql_base.cc
        Implement different prelocking strategies. Use an instance of
        prelocking_strategy in open_tables().
     @ sql/sql_class.h
        Add declarations for class Prelocking_strategy.
     @ sql/sql_lex.h
        Add a helper method to access last table of the global table list
        (lex->query_tables).
     @ sql/sql_parse.cc
        Use a special prelocking strategy when locking tables for LOCK TABLES.
     @ sql/sql_table.cc
        Use normal open_and_lock_tables_derived() in ALTER TABLE.
     @ sql/sql_yacc.yy
        Modify the grammar to not pollute the global table list with tables
        that should not be opened.

Pushed to mysql-next-4284 (5.6.0-beta).

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:jon.hauglid@sun.com-20091210114616-pq08i17urjudwqkc) (pib:16)

Not present in any released version. No changelog entry needed.

Setting report to Need Merge pending push of Celosia into release tree.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)

No changelog entry needed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107353

3010 Jon Olav Hauglid	2010-05-04
      Followup to Bug#42546 Backup: RESTORE fails, thinking it finds an existing table
      
      Fixes a bug where bool* was used as an argument to a function where the parameter
      was of type bool.

Pushed followup to mysql-trunk-runtime (Ver 5.5.5-m3).

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

No changelog entry needed.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:marko.makela@oracle.com-20100603095032-v5ptkkzt1bhz0m1d) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)

Setting back to Documenting status because this introduces a change in behaviour.

(Paul: I'll handle this.)

See BUG#58909.

Documented behaviour change in the 5.5.3, 5.6.1, and 6.0.14 changelogs as follows:

        *Incompatible Change* CREATE TABLE statements (including 
        CREATE TABLE ... LIKE) are now prohibited whenever a LOCK 
        TABLES statement is in effect.

Also updated 5.5/5.6/6.0 versions of "CREATE TABLE Syntax" and "Other Table-Locking Notes" (subsection of "LOCK TABLES and UNLOCK TABLES Syntax").

Closed.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

See bug #58927

Addition to changelog entry:

One consequence of this change is that CREATE TABLE ... LIKE makes
the same checks as CREATE TABLE and does not just copy the .frm file.
This means that if the current SQL mode is different from the mode in
effect when the original table was created, the table definition
might be considered invalid for the new mode and the statement will
fail.