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

• Submitted: 15 Oct 8:19
• Modified: 2 Nov 21:55
• Reporter: Lars-Erik Bjørk
• Status: Need Doc Info
• Category: Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0.88, 5.1.41, 6.0.14
• OS: Any
• Assigned to: Lars-Erik Bjørk
• Triage: Triaged: D3 (Medium)

[15 Oct 8: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 21: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 18: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 11:10] Konstantin Osipov

Review sent over on IRC.

[26 Oct 13: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 14:01] Lars-Erik Bjørk

Pushed into 6.0.14-alpha

[31 Oct 9: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)