谁来帮我看看问题出在哪？谢谢！

kider

你的语句看了，没有问题，在我的环境下运行了，是可以的，如下图：


提示：怀疑是你磁盘没有空间了...

kider

有关系！

boxman

Errno: 121 means that you already have the table defined in your InnoDB tablespace, but the .frm file is not there.


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/e ... oting_datadict.html

[ 本帖最后由 boxman 于 2008-1-16 16:08 编辑 ]

kider

讲的对，原理就是这样的，分不同的表类型，分别对待。

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.