Bug #49636 ALTER TABLE can create table with no name

• Submitted: 11 Dec 2009 23:15
• Modified: 5 Oct 2012 15:42
• Reporter: Michal Jarosz
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.1.36
• OS: Windows (Vista)
• CPU Architecture: Any
• Tags: ALTER TABLE, regression, rename

[11 Dec 2009 23:15] Michal Jarosz

Description:
Renaming table to `.` will result in table with no name. No data is returned from information_schema.tables.

This doesn't seem to occur when creating a table with such name.

Tested with both MyISAM and InnoDB

How to repeat:
CREATE SCHEMA test;
USE test;

CREATE TABLE t1 (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (ID)
);

-- !!!
ALTER TABLE t1 RENAME TO `.`;
-- !!!
/*
There's now a table with no name in this database. On disk there is a file called .frm for InnoDB or .MYD and .MYI for MyISAM.
*/

SHOW TABLES; --will show one empty row
SELECT * FROM information_schema.tables; --will show ERROR 1103 (42000): Incorrect table name '';

-- DROP SCHEMA test;  
/* 
This is the only way to remove the corrupted table using DML I have found.
Deleting files on disk also works (although renaming them doesn't)
*/

[11 Dec 2009 23:50] MySQL Verification Team

Thank you for the bug report. Not repeatable on 5.0:

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.42-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >CREATE SCHEMA test;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >USE test;
Database changed
mysql 5.1 >
mysql 5.1 >CREATE TABLE t1 (
    ->   ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (ID)
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql 5.1 >ALTER TABLE t1 RENAME TO `.`;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >show tables;
+----------------+
| Tables_in_test |
+----------------+
|                |
+----------------+
1 row in set (0.09 sec)

mysql 5.1 >exit
Bye

c:\dbs>50

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.89-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > USE test;
Database changed
mysql 5.0 >
mysql 5.0 > CREATE TABLE t1 (
    ->   ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (ID)
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql 5.0 >
mysql 5.0 > -- !!!
mysql 5.0 > ALTER TABLE t1 RENAME TO `.`;
ERROR 1103 (42000): Incorrect table name '.'
mysql 5.0 >

[4 Nov 2010 22:37] Michael Widenius

This problem doesn't exists in MariaDB 5.1.51

[5 Oct 2012 15:42] Paul DuBois

Noted in 5.7.0 changelog.

Using ALTER TABLE to rename a table to . resulted in a table with no
name.