Mysql 1050 错误“表已经存在”，但实际上并没有
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3302476/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql 1050 Error "Table already exists" when in fact, it does not
提问by Citizen
I'm adding this table:
我正在添加这个表：
CREATE TABLE contenttype (
contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT,
class VARBINARY(50) NOT NULL,
packageid INT UNSIGNED NOT NULL,
canplace ENUM('0','1') NOT NULL DEFAULT '0',
cansearch ENUM('0','1') NOT NULL DEFAULT '0',
cantag ENUM('0','1') DEFAULT '0',
canattach ENUM('0','1') DEFAULT '0',
isaggregator ENUM('0', '1') NOT NULL DEFAULT '0',
PRIMARY KEY (contenttypeid),
UNIQUE KEY packageclass (packageid, class)
);
And I get a 1050 "table already exists"
我得到一个 1050“表已经存在”
But the table does NOT exist. Any ideas?
但该表不存在。有任何想法吗？
EDIT: more details because everyone seems to not believe me :)
编辑：更多细节，因为每个人似乎都不相信我:)
DESCRIBE contenttype
yields:
产量：
1146 - Table 'gunzfact_vbforumdb.contenttype' doesn't exist
1146 - 表 'gunzfact_vbforumdb.contenttype' 不存在
and
和
CREATE TABLE gunzfact_vbforumdb.contenttype(
contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT ,
class VARBINARY( 50 ) NOT NULL ,
packageid INT UNSIGNED NOT NULL ,
canplace ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cansearch ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cantag ENUM( '0', '1' ) DEFAULT '0',
canattach ENUM( '0', '1' ) DEFAULT '0',
isaggregator ENUM( '0', '1' ) NOT NULL DEFAULT '0',
PRIMARY KEY ( contenttypeid ) ,
Yields:
产量：
1050 - Table 'contenttype' already exists
1050 - 表 'contenttype' 已经存在
回答by NullUserException
Sounds like you have Schroedinger's table...
听起来你有薛定谔的桌子......
Seriously now, you probably have a broken table. Try:
说真的，你可能有一张破桌子。尝试：
DROP TABLE IF EXISTS contenttypeREPAIR TABLE contenttype- If you have sufficient permissions, delete the data files (in /mysql/data/db_name)
DROP TABLE IF EXISTS contenttypeREPAIR TABLE contenttype- 如果您有足够的权限，请删除数据文件（在 /mysql/data/db_name 中）
回答by wredny
from MySQL Log:
来自 MySQL 日志：
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.
回答by Craig Walker
I got this same error, and REPAIR TABLE (from @NullUserException's answer) didn't help.
我遇到了同样的错误，REPAIR TABLE（来自@NullUserException 的回答）没有帮助。
I eventually found this solution:
我最终找到了这个解决方案：
sudo mysqladmin flush-tables
For me, without the sudo, I got the following error:
对我来说，没有sudo，我收到以下错误：
mysqladmin: refresh failed; error: 'Access denied; you need the RELOAD privilege for this operation'
(Running on OS X 10.6)
（在 OS X 10.6 上运行）
回答by Joe Chryst
You may need to flush the table cache. For example:
您可能需要刷新表缓存。例如：
DROP TABLE IF EXISTS `tablename` ;
FLUSH TABLES `tablename` ; /* or exclude `tablename` to flush all tables */
CREATE TABLE `tablename` ...
回答by Neal
I've been fighting with this all day: I have a Perl script that builds a set of tables by first doing a DROP IF EXISTS ...on them and then CREATEing them. The DROPsucceeded, but on CREATEI got this error message: table already exists
我一整天都在与这个问题作斗争：我有一个 Perl 脚本，它通过首先DROP IF EXISTS ...对它们做 a然后CREATE对它们进行ing 来构建一组表。的DROP成功，但CREATE我得到这个错误信息： table already exists
I finally got to the bottom of it: The new version of MySQL that I'm using has a default engine of InnoDB ("show engine \G;") I changed it in the my.cnf file to default to MyISAM, re-started MySQL, and now I no longer get the "table already exists" error.
我终于弄明白了：我正在使用的新版本的 MySQL 有一个默认引擎 InnoDB（“show engine \G;”）我在 my.cnf 文件中将它更改为默认为 MyISAM，重新 -启动 MySQL，现在我不再收到“表已存在”错误。
回答by websheep
Encountering the same problem (create InnoDB table) this is what finally worked for me:
遇到同样的问题（创建 InnoDB 表）这最终对我有用：
DROP DATABASE `having_issues`;
I checked on a file basis, permissions, tried to REPAIR and FLUSH but nothing worked.
我在文件基础上检查了权限，尝试修复和冲洗但没有任何效果。
So if this is an option, move all working tables to another DATABASE, drop the old one (you might have to manually remove any files from the database folder before the drop to work), rename the new one, and you 'should' be back on your way. Apparently, whatever gets 'cached' using InnoDB is dropped along with the original database.
因此，如果这是一个选项，请将所有工作表移动到另一个数据库，删除旧表（您可能必须在删除之前手动从数据库文件夹中删除任何文件才能工作），重命名新表，并且您“应该”回你的路上。显然，使用 InnoDB 获得“缓存”的任何内容都与原始数据库一起删除。
回答by gmize
I also encountered this problem where trying to Create a table said it already exists and Dropping the table said it didn't exist.
我也遇到过这个问题，尝试创建一个表说它已经存在，而删除表说它不存在。
I did "FLUSH TABLES" and it cleared the problem.
我做了“FLUSH TABLES”，它解决了问题。
回答by Anton
I had this problem on Win7 in Sql Maestro for MySql 12.3. Enormously irritating, a show stopper in fact. Nothing helped, not even dropping and recreating the database. I have this same setup on XP and it works there, so after reading your answers about permissions I realized that it must be Win7 permissions related. So I ran MySql as administrator and even though Sql Maestro was run normally, the error disappeared. So it must have been a permissions issue between Win7 and MySql.
我在 Sql Maestro for MySql 12.3 的 Win7 上遇到了这个问题。非常令人恼火，实际上是一个节目塞子。没有任何帮助，甚至没有删除和重新创建数据库。我在 XP 上有同样的设置，它在那里工作，所以在阅读你关于权限的答案后，我意识到它必须与 Win7 权限相关。所以我以管理员身份运行 MySql，即使 Sql Maestro 运行正常，错误也消失了。所以肯定是Win7和MySql之间的权限问题。
回答by Nakul91
Same problem occurred with me while creating a view. The view was present earlier then due to some changes it got removed But when I tried to add it again it was showing me "view already exists" error message.
创建视图时我也遇到了同样的问题。由于一些更改，该视图在此之前就已存在，但是当我尝试再次添加它时，它显示“视图已存在”错误消息。
Solution:
解决方案：
You can do one thing manually.
您可以手动做一件事。
- Go to the MySQL folder where you have installed it
- Go to the data folder inside it.
- Choose your database and go inside it.
- Data base creates ".frm" format files.
- delete the particular table's file.
- Now create the table again.
- 转到您安装它的 MySQL 文件夹
- 转到其中的数据文件夹。
- 选择您的数据库并进入其中。
- 数据库创建“.frm”格式文件。
- 删除特定表的文件。
- 现在再次创建表。
It will create the table successfully.
它将成功创建表。
回答by Ian Lewis
I was having huge issues with Error 1050 and 150.
我遇到了错误 1050 和 150 的大问题。
The problem, for me was that I was trying to add a constraint with ON DELETE SET NULLas one of the conditions.
对我来说，问题是我试图添加一个约束ON DELETE SET NULL作为条件之一。
Changing to ON DELETE NO ACTIONallowed me to add the required FK constraints.
更改为ON DELETE NO ACTION允许我添加所需的 FK 约束。
Unfortunately the MySql error messages are utterly unhelpful so I had to find this solution iteratively and with the help of the answers to the question above.
不幸的是，MySql 错误消息完全没有帮助，所以我不得不在上述问题的答案的帮助下反复找到这个解决方案。
