Bug #48067 A temp table with the same name as an existing table, makes drop database fail

• Submitted: 15 Oct 2009 6:19
• Modified: 12 Aug 2010 19:03
• Reporter: Lars-Erik Bjørk
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0.88, 5.1.41, 5.6, 6.0.14
• OS: Any
• Assigned to: Jon Olav Hauglid
• CPU Architecture: Any

[15 Oct 2009 6:19] Lars-Erik Bjørk

Description:
This bug is reported by Jørgen Løland as a related problem to bug#30099.

If you create a temporary table with the same name as an existing
table, drop database will fail.

How to repeat:
> create database dbase;
> use dbase;
> create table t (i int);
> insert into t values (1);
> create temporary table t (i int);
> insert into t values (2);
> drop database dbase;
ERROR 1010 (HY000): Error dropping database (can't rmdir './dbase', errno: 39)
> use dbase;
> select * from t;
+------+
| i    |
+------+
|    1 | 
+------+

Notice that the result is from the base table t, not the temporary table t.

[15 Oct 2009 19:27] Bugs System

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/87027

3655 [e-mail]	2009-10-15
      This is a patch for bug#48067
      "A temp table with the same name as an existing table, makes drop
      database fail"
      
      When dropping the database, mysql_rm_known_files() reads the contents
      of the database directory, and creates a TABLE_LIST object, for each
      .frm file encountered. Temporary tables, however, are not associated 
      with any .frm file.
      
      The list of tables to drop are passed to mysql_rm_table_part2().
      This method prefers temporary tables over regular tables, so if
      there is a temporary table with the same name as a regular, the
      temporary is removed, leaving the regular table intact.
      Regular tables are only deleted if there are no temporary tables
      with the same name.
      
      This fix ensures, that for all TABLE_LIST objects that are created
      by mysql_rm_known_files(), 'open_type' is set to 'OT_BASE_ONLY', to
      indicate that this is a regular table. In all cases in
      mysql_rm_table_part2() where we prefer a temporary table to a
      non-temporary table, we chek if 'open_type' equals 'OT_BASE_ONLY'.
     @ mysql-test/r/temp_table.result
        The expected result of the test.
     @ mysql-test/t/temp_table.test
        Test based on the bug report.
     @ sql/sql_db.cc
        For all TABLE_LIST objects that are created by mysql_rm_known_files(),
        'open_type' is set to 'OT_BASE_ONLY', to indicate that this is a
        regular table.
     @ sql/sql_table.cc
        Check if 'open_type' is set to 'OT_BASE_ONLY, every place a temporary table is
        preferred to a non-temporary table.

[20 Oct 2009 16:17] Valeriy Kravchuk

Verified on 5.1.41 also:

mysql> create database dbase;
Query OK, 1 row affected (0.00 sec)

mysql> use dbase;
Database changed
mysql> create table t (i int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> create temporary table t (i int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (2);
Query OK, 1 row affected (0.00 sec)

mysql> drop database dbase;
ERROR 1010 (HY000): Error dropping database (can't rmdir './dbase', errno: 66)
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.41-debug |
+--------------+
1 row in set (0.00 sec)

[24 Oct 2009 9:10] Konstantin Osipov

Review sent over on IRC.

[26 Oct 2009 12:43] Bugs System

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/88146

3672 [e-mail]	2009-10-26
      This is a patch for bug#48067
      "A temp table with the same name as an existing table, makes drop
      database fail"
            
      When dropping the database, mysql_rm_known_files() reads the contents
      of the database directory, and creates a TABLE_LIST object, for each
      .frm file encountered. Temporary tables, however, are not associated 
      with any .frm file.
            
      The list of tables to drop are passed to mysql_rm_table_part2().
      This method prefers temporary tables over regular tables, so if
      there is a temporary table with the same name as a regular, the
      temporary is removed, leaving the regular table intact.
      Regular tables are only deleted if there are no temporary tables
      with the same name.
            
      This fix ensures, that for all TABLE_LIST objects that are created
      by mysql_rm_known_files(), 'open_type' is set to 'OT_BASE_ONLY', to
      indicate that this is a regular table. In all cases in
      mysql_rm_table_part2() where we prefer a temporary table to a
      non-temporary table, we chek if 'open_type' equals 'OT_BASE_ONLY'.
     @ mysql-test/r/temp_table.result
        The expected result of the test.
     @ mysql-test/t/temp_table.test
        Test based on the bug report.
     @ sql/sql_db.cc
        For all TABLE_LIST objects that are created by mysql_rm_known_files(),
        'open_type' is set to 'OT_BASE_ONLY', to indicate that this is a
        regular table.
     @ sql/sql_table.cc
        Check if 'open_type' is set to 'OT_BASE_ONLY, every place a temporary table is
        preferred to a non-temporary table.

[26 Oct 2009 13:01] Lars-Erik Bjørk

Pushed into 6.0.14-alpha

[31 Oct 2009 8:19] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091031081410-qkxmjsdzjmj840aq) (version source revid:[e-mail]-20091026125143-5jbj89lc8hbn39t9) (merge vers: 6.0.14-alpha) (pib:13)

[14 Jan 2010 7:54] Valeriy Kravchuk

Bug #50319 was marked as a duplicate of this one.

[14 Jan 2010 16:11] Hema Sridharan

Hi, I verified this and the issue still appears in 6.0 and 5.X(please see BUG#50319 for more details). Drop database fails if temporary table is existing in db. I will change the status of this bug to verified state.

[23 Jun 2010 10:40] Konstantin Osipov

Hello Jon Olav,

apparently we forgot the patch in 6.0 tree.
Could you please find out what's going on with this bug in 5.5?
Thanks,

-- 
kostja

[23 Jun 2010 11:34] Bugs System

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/111893

3061 Jon Olav Hauglid	2010-06-23
      Backport from mysql-6.0-codebase of:
      ------------------------------------------------------------
      revno: 3672
      committer: [e-mail]
      branch nick: 48067-mysql-6.0-codebase-bugfixing
      timestamp: Mon 2009-10-26 13:51:43 +0100
      message:
        This is a patch for bug#48067
        "A temp table with the same name as an existing table, makes drop
        database fail"
              
        When dropping the database, mysql_rm_known_files() reads the contents
        of the database directory, and creates a TABLE_LIST object, for each
        .frm file encountered. Temporary tables, however, are not associated 
        with any .frm file.
              
        The list of tables to drop are passed to mysql_rm_table_part2().
        This method prefers temporary tables over regular tables, so if
        there is a temporary table with the same name as a regular, the
        temporary is removed, leaving the regular table intact.
        Regular tables are only deleted if there are no temporary tables
        with the same name.
              
        This fix ensures, that for all TABLE_LIST objects that are created
        by mysql_rm_known_files(), 'open_type' is set to 'OT_BASE_ONLY', to
        indicate that this is a regular table. In all cases in
        mysql_rm_table_part2() where we prefer a temporary table to a
        non-temporary table, we chek if 'open_type' equals 'OT_BASE_ONLY'.
     @ mysql-test/r/temp_table.result
        The expected result of the test.
     @ mysql-test/t/temp_table.test
        Test based on the bug report.
     @ sql/sql_db.cc
        For all TABLE_LIST objects that are created by mysql_rm_known_files(),
        'open_type' is set to 'OT_BASE_ONLY', to indicate that these are
        regular tables.
     @ sql/sql_table.cc
        Check if 'open_type' is set to 'OT_BASE_ONLY, every place a temporary table is
        preferred to a non-temporary table.

[24 Jun 2010 9:37] Jon Olav Hauglid

Backport pushed to mysql-trunk-runtime (Ver 5.5.6).

[4 Aug 2010 7:51] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:03] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:19] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 9:01] Bugs System

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (pib:20)

[12 Aug 2010 19:03] Paul DuBois

Noted in 5.5.6 changelog.

DROP DATABASE failed if there was a TEMPORARY table with the same
name as a non-TEMPORARY table in the database.