MySQL Bugs: #17546: create table Error 121

Bug #17546	create table Error 121
Submitted:	18 Feb 2006 14:38	Modified:	18 Feb 2006 18:13
Reporter:	Jim Risc	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Windows (windows 2000)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
You cannot create again a manually deleted table.

How to repeat:
1. use test
2. create a new table e.g. "testtable"
3. stop server
4. delete file "..data\test\testtable.*"
5. start server
6. try to create table "testtable" -> errno 121!

repair, analyze, optimize don't work: cause table does not exists!

Suggested fix:
there should be no problem to create a table again...

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

You can't delete manually InnoDB .frm table file since the table isn't removed
from the table-space file. MyISAM not has that problem because all
the 3 files can be removed.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use db1
Database changed
mysql> show variables like "%storage%";
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.01 sec)

mysql> create table tb5 (id int);
Query OK, 0 rows affected (0.06 sec)

mysql> exit
Bye

c:\mysql\bin>del ..\data\db1\tb5.*

c:\mysql\bin>mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb5 (id int);
Query OK, 0 rows affected (0.05 sec)

mysql> drop table tb5;
Query OK, 0 rows affected (0.00 sec)

c:\mysql\bin>mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "%storage%";
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql> create table tb5 (id int);
Query OK, 0 rows affected (0.08 sec)

mysql> exit
Bye

c:\mysql\bin>del ..\data\db1\tb5.*

c:\mysql\bin>mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb5 (id int);
ERROR 1005 (HY000): Can't create table '.\db1\tb5.frm' (errno: 121)
mysql>

c:\mysql\bin>mysqld-nt --standalone --console --default-storage-engine=innodb
060218 16:08:07  InnoDB: Started; log sequence number 0 50056
060218 16:08:07 [Note] mysqld-nt: ready for connections.
Version: '5.0.19-nt-log'  socket: ''  port: 3306  Source distribution
060218 16:08:56  InnoDB: Error: table `db1/tb5` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html