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 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.36 OS:Microsoft Windows (Vista)
Assigned to:
Tags: ALTER TABLE, regression, rename
Triage: Triaged: D3 (Medium)

[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] Miguel Solorzano
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.