薛定谔 MySQL 表：存在，但不存在

Schr?dingers MySQL table: exists, yet it does not

提问by Corkscreewe

I am having the weirdest error of all.

我有最奇怪的错误。

Sometimes, when creating or altering tables, I get the 'table already exists' error. However, DROP TABLE returns '#1051 - unknown table'. So I got a table I cannot create, cannot drop.

有时，在创建或更改表时，我会收到“表已存在”错误。但是，DROP TABLE 返回“#1051 - 未知表”。所以我得到了一张我无法创建、无法删除的表。

When I try to drop the database, mysqld crashes. Sometimes it helps to create another db with different name, sometimes it does not.

当我尝试删除数据库时，mysqld 崩溃了。有时它有助于创建另一个具有不同名称的数据库，有时则没有。

I use a DB with ~50 tables, all InnoDB. This problem occurs with different tables.

我使用一个包含约 50 个表的数据库，所有的都是 InnoDB。不同的表会出现此问题。

I experienced this on Windows, Fedora and Ubuntu, MySQL 5.1 and 5.5. Same behaviour, when using PDO, PHPMyAdmin or commandline. I use MySQL Workbench to manage my schema - I saw some related errors (endlines and stuff), however none of them were relevant for me.

我在 Windows、Fedora 和 Ubuntu、MySQL 5.1 和 5.5 上遇到过这种情况。使用 PDO、PHPMyAdmin 或命令行时的行为相同。我使用 MySQL Workbench 来管理我的架构 - 我看到了一些相关的错误（结束线和东西），但是它们都与我无关。

No, it is not a view, it is a table. All names are lowercase.

不，这不是视图，而是一张桌子。所有名称均为小写。

I tried everything I could google - flushing tables, moving .frm files from db to db, reading mysql log, nothing helped but reinstalling the whole damn thing.

我尝试了所有我能用谷歌搜索的东西——刷新表，将 .frm 文件从 db 移动到 db，读取 mysql 日志，除了重新安装整个该死的东西之外没有任何帮助。

'Show tables' reveals nothing, 'describe' table says 'table doesn't exist,' there is no .frm file, yet 'create table' still ends with an error (and so does 'create table if not exists') and dropping database crashes mysql

“显示表”什么也没显示，“描述”表说“表不存在”，没有 .frm 文件，但“创建表”仍然以错误结束（“如果不存在则创建表”也是如此）和删除数据库会导致 mysql 崩溃

Related, yet unhelpful questions:

相关但无益的问题：

• Mysql 1050 Error "Table already exists" when in fact, it does not

• MySQL Table does not exist error, but it does exist

• Mysql 1050 错误“表已经存在”，但实际上并没有

• MySQL表不存在错误，但它确实存在

Edit:

编辑：

mysql> use askyou;
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> create table users_has_friends (id int primary key);
ERROR 1050 (42S01): Table '`askyou`.`users_has_friends`' already exists

mysql> drop table users_has_friends;
ERROR 1051 (42S02): Unknown table 'users_has_friends'

And such, all the same: table doesn't exist, yet cannot be created;

如此，同样：表不存在，但无法创建；

mysql> drop database askyou;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Names change, this is not the only table / database I've run into problems with

名称更改，这不是我遇到问题的唯一表/数据库

回答by sreimer

I've seen this issue when the data file is missing in the data directory but the table definition file exists or vise-versa. If you're using innodb_file_per_table, check the data directory to make sure you have both an .frmfile and .ibd file for the table in question. If it's MYISAM, there should be a .frm, .MYIand a .MYDfile.

当数据目录中缺少数据文件但表定义文件存在或反之亦然时，我已经看到了这个问题。如果您使用的是 innodb_file_per_table，请检查数据目录以确保您拥有.frm相关表的文件和 .ibd 文件。如果是 MYISAM，则应该有一个.frm,.MYI和一个.MYD文件。

The problem can usually be resolved by deleting the orphaned file manually.

该问题通常可以通过手动删除孤立文件来解决。

回答by ESG

Going on a wild guess here, but it seems like innodb still has an entry for your tables in a tablespace, probably in ibdata. If you reallydon't need anyof the data, or if you have backups, try the following:

在这里进行一个疯狂的猜测，但似乎 innodb 仍然在表空间中为您的表提供了一个条目，可能在ibdata. 如果您真的不需要任何数据，或者您有备份，请尝试以下操作：

1. Delete all schemas (excluding mysql)
2. shut down the database
3. Make sure that all folders in your data directory have been removed properly (again, excluding mysql)
4. delete ibdata and log files
5. restart the database. It should recreate the tablespace and logs from scratch.

1. 删除所有模式（不包括 mysql）
2. 关闭数据库
3. 确保数据目录中的所有文件夹都已正确删除（同样，不包括 mysql）
4. 删除 ibdata 和日志文件
5. 重新启动数据库。它应该从头开始重新创建表空间和日志。

回答by RJTatWork

The fix turns out to be easy; at least what I worked out, worked for me. Create a table "zzz" on another MySQL instance, where zzz is the problem table name. (i.e. if the table is called schrodinger, substitute that for zzz whever written.) It does not matter what the definition of the table is. It's a temporary dummy; Copy the zzz.frm file to the database directory on server where the table should be, making sure file ownership and permissions are still correct on the file. On MySQL, you can now do "show tables;", and the table zzz will be there. mysql> drop table zzz; ...should now works. Clear any zzz.MYD or ZZZ.MYI files in the directory if necessary.

事实证明，修复很容易；至少我的工作对我有用。在另一个 MySQL 实例上创建一个表“zzz”，其中 zzz 是问题表名称。（即，如果该表被称为薛定谔，则无论写成什么，都将其替换为 zzz。）该表的定义是什么并不重要。这是一个临时的假人；将 zzz.frm 文件复制到服务器上表所在的数据库目录，确保文件所有权和权限在文件上仍然正确。在 MySQL 上，您现在可以执行“show tables;”，并且表 zzz 将在那里。mysql> 删除表 zzz; ...现在应该可以工作了。如有必要，清除目录中的所有 zzz.MYD 或 ZZZ.MYI 文件。

回答by sxf

This is an old question but I just hit the same issue and an answerin one of the related issues linked at the top was just what I needed and far less drastic than deleting files, tables, shutting down the server etc.

这是一个老问题，但我只是遇到了同样的问题，顶部链接的相关问题之一的答案正是我所需要的，远没有删除文件、表格、关闭服务器等那么激烈。

mysqladmin -uxxxxxx -pyyyyy flush-tables

回答by ozataman

I doubt this is a direct answer to the question case here, but here is how I solved this exact perceived problemon my OS X Lion system.

我怀疑这是对这里问题的直接回答，但这是我在 OS X Lion 系统上解决这个确切感知问题的方法。

I frequently create/drop tables for some analytics jobs I have scheduled. At some point, I started getting table already exists errors half-way through my script. A server restart typically solved the issue, but that was too annoying of a solution.

我经常为我安排的一些分析工作创建/删除表。在某些时候，我开始在我的脚本中途获取表已经存在的错误。服务器重启通常可以解决这个问题，但这对解决方案来说太烦人了。

Then I noticed in the local error log file this particular line:

然后我在本地错误日志文件中注意到这一行：

[Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive

This gave me the idea that maybe if my tables contained capital letters, MySQL would be fooled into thinking they are still there even after I had dropped them. That turned out to be the case and switching to using only lowercase letters for table names made the problem go away.

这给了我一个想法，如果我的表包含大写字母，MySQL 会被愚弄，认为即使在我删除它们之后它们仍然存在。事实证明情况确实如此，切换到只使用小写字母作为表名使问题消失了。

It is likely the result of some misconfiguration in my case, but hopefully this error case will help someone waste less time trying to find a solution.

在我的情况下，这可能是一些错误配置的结果，但希望这个错误案例能帮助人们减少浪费时间来寻找解决方案。

回答by Sebastian

In my case the problem was solved by changing the ownership of the mysql data directory to the user that ran the application. (In my case it was a Java application running Jetty webserver.)

在我的情况下，问题是通过将 mysql 数据目录的所有权更改为运行应用程序的用户来解决的。（就我而言，它是一个运行 Jetty 网络服务器的 Java 应用程序。）

Even though mysql was running and other apps could use it properly, this app had a problem with that. After changing the data directory ownership and resetting the user's password, everything worked properly.

尽管 mysql 正在运行并且其他应用程序可以正常使用它，但此应用程序存在问题。更改数据目录所有权并重置用户密码后，一切正常。

回答by joao albuquerque

If will are stock with this error 1051 and you only want to delete the database and import this again do this steps and all gonna be just fine....

如果将出现此错误 1051 并且您只想删除数据库并再次导入，请执行以下步骤，一切都会好起来的....

in Unix envoriment AS root:

在 Unix 环境中作为root：

• rm -rf /var/lib/mysql/YOUR_DATABASE;
• OPTIONAL -> mysql_upgrade --force
• mysqlcheck -uUSER -pPASS YOUR_DATABASE
• mysqladmin -uUSER -pPASS drop YOUR_DATABASE
• mysqladmin -uUSER -pPASS create YOUR_DATABASE
• mysql -uUSER -pPASS YOUR_DATABASE < IMPORT_FILE

• rm -rf /var/lib/mysql/YOUR_DATABASE;
• 可选 -> mysql_upgrade --force
• mysqlcheck -uUSER -pPASS YOUR_DATABASE
• mysqladmin -uUSER -pPASS drop YOUR_DATABASE
• mysqladmin -uUSER -pPASS 创建 YOUR_DATABASE
• mysql -uUSER -pPASS YOUR_DATABASE < IMPORT_FILE

Regards, Christus

问候， 克里斯托斯

回答by MoChaMan

I had this problem and hoped deleting the IBD file would help as posted above but it made no difference . MySQL only recreated a new IBD file . In my case, there are actually similar tables in other databases in the same MySQL instance . Since the FRM file was missing , I copied the FRM file from the similar table in another database , restarted MySQL and the table worked correctly .

我遇到了这个问题，希望删除 IBD 文件会有所帮助，如上所述，但没有任何区别。MySQL 只重新创建了一个新的 IBD 文件。就我而言，在同一个 MySQL 实例中的其他数据库中实际上也有类似的表。由于缺少 FRM 文件，我从另一个数据库中的类似表中复制了 FRM 文件，重新启动 MySQL 并且该表正常工作。

回答by user2911374

I ran into this error after I created a table and deleted it, then wanted to create it again. In my case, I had a self-contained dump file so I dropped my schema, recreated it and imported tables and data using the dump file.

我在创建表并删除它后遇到了这个错误，然后想再次创建它。就我而言，我有一个独立的转储文件，所以我删除了我的架构，重新创建它并使用转储文件导入表和数据。

回答by Pete Kelley

It happens at our site (but rarely) usually when an "event" happens while running certain scripts that do a lot of rebuilding. Events include network outages or power problems.
What I do for this on the very rare occasions it happens - I use the heavy-handed approach:

它发生在我们的站点（但很少）通常是在运行某些执行大量重建的脚本时发生“事件”。事件包括网络中断或电源问题。
我在极少数情况下为此做的事情 - 我使用严厉的方法：

• I needed to simply get rid-of-and-rebuild the particular table. I'm usually in a position that this is OK since the table is being built. (Your situation may be different if you need to recover data)
• As an admin, go into the mysql installation (on windows its may be "...program files/mysql/MySQL Server xx/data/<schemaname>
• Find the offending file with the table name in the <schemaname> folder - and delete it.
• Check for orphaned temporary files and delete them too. #...frm files if they happen to be there.
• MySQL will let you CREATE the table again

• 我需要简单地摆脱并重建特定的表。我通常认为这是可以的，因为桌子正在建造。（如果您需要恢复数据，您的情况可能会有所不同）
• 作为管理员，进入 mysql 安装（在 Windows 上它可能是“...program files/mysql/MySQL Server xx/data/<schemaname>
• 在 <schemaname> 文件夹中找到具有表名的违规文件 - 并将其删除。
• 检查孤立的临时文件并删除它们。#...frm 文件，如果它们碰巧在那里。
• MySQL会让你再次创建表

I've had this problem on a couple of different databases over a long time (years). It was a stumper because the contradicting messages. The first time I did a variation of the deleting/rebuilding/renaming database as described in the other answers and managed to get things going, but it definitely takes longer that way. Lucky for me it's always happened to reference tables that are being rebuilt - DROP'd and CREATEd - typically in the morning. Rarely got the problem but came to recognize it as a special quirky case. (I'll restate : if you need to recover the data look to the other solutions.)

很长一段时间（几年），我在几个不同的数据库上都遇到过这个问题。这是一个绊脚石，因为相互矛盾的信息。我第一次按照其他答案中的描述对删除/重建/重命名数据库进行了修改，并设法使事情顺利进行，但这样做肯定需要更长的时间。对我来说幸运的是，正在重建的引用表总是发生 - DROP'd 和 CREATEd - 通常是在早上。很少遇到这个问题，但后来意识到这是一个特殊的古怪案例。（我会重申：如果您需要恢复数据，请查看其他解决方案。）

• it isn't a table belonging to another user, or in another database
• it isn't the upper/lower case issue, I use all lower-case, but that was an interesting issue!
• it was extra extra frustrating seeing responses with variations of "it definitely was <there/not-there/some-other-user-table-case> and you're just not doing it right" :)
• the table didn't show on "show tables"
• the table was (always was/had been) an INNODB table.
• trying to DROPthe table gave the error message that the table doesn't exist.
• but trying to CREATEthe table gave the error message that the table already exists.
• using mysql 5.0 or 5.1
• REPAIRis ineffective for this problem

• 它不是属于另一个用户或另一个数据库中的表
• 这不是大写/小写的问题，我全部使用小写，但这是一个有趣的问题！
• 看到带有“它肯定是 <there/not-there/some-other-user-table-case> 而你只是做得不对”的变体的回复更加令人沮丧:)
• 该表未显示在“显示表”上
• 该表是（一直是/曾经是）一个 INNODB 表。
• 尝试DROP表给出了该表不存在的错误消息。
• 但是尝试创建表给出了该表已经存在的错误消息。
• 使用 mysql 5.0 或 5.1
• REPAIR对这个问题无效