MariaDB社区

标题: 谁来帮我看看问题出在哪？谢谢！ [打印本页]

作者: 07zdw 时间: 2008-1-4 21:44
标题: 谁来帮我看看问题出在哪？谢谢！
问题在附件，请问一下这个论坛怎么加图片？

mysql.png (8.2 KB, 下载次数: 200)

create_student_info.rar

641 Bytes, 下载次数: 2684

作者: kider 时间: 2008-1-4 23:38
你的语句看了，没有问题，在我的环境下运行了，是可以的，如下图：

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

作者: 07zdw 时间: 2008-1-5 20:34
硬盘空间还有，不知是不是以前删了还保留了部分信息（我是在data文件里面把它删了的)

作者: kider 时间: 2008-1-6 01:13
有关系！

作者: boxman 时间: 2008-1-16 16:05
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 时间: 2008-1-16 16:32
讲的对，原理就是这样的，分不同的表类型，分别对待。

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.

欢迎光临 MariaDB社区 (http://123.56.88.72/)