薛定谔 MySQL 表:存在，但不存在 [英] Schrödingers MySQL table: exists, yet it does not
问题描述
我犯了最奇怪的错误.
有时，在创建或更改表时，我会收到表已存在"错误.但是，DROP TABLE 返回#1051 - 未知表".所以我得到了一张我无法创建、无法删除的表.
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.
当我尝试删除数据库时，mysqld 崩溃了.有时它有助于创建另一个具有不同名称的数据库，有时则没有.
When I try to drop the database, mysqld crashes. Sometimes it helps to create another db with different name, sometimes it does not.
我使用一个包含约 50 个表的数据库，全部为 InnoDB.不同的表会出现此问题.
I use a DB with ~50 tables, all InnoDB. This problem occurs with different tables.
我在 Windows、Fedora 和 Ubuntu、MySQL 5.1 和 5.5 上遇到过这种情况.使用 PDO、PHPMyAdmin 或命令行时的行为相同.我使用 MySQL Workbench 来管理我的架构 - 我看到了一些相关的错误(结束线和其他内容)，但没有一个与我相关.
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.
不，它不是视图，而是一张桌子.所有名称均为小写.
No, it is not a view, it is a table. All names are lowercase.
我尝试了所有我能用 google 搜索的东西 - 刷新表，将 .frm 文件从 db 移动到 db，读取 mysql 日志，除了重新安装整个该死的东西之外没有任何帮助.
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 文件，但创建表"仍然以错误结尾(如果不存在则创建表"也是如此') 和删除数据库崩溃 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
相关但无益的问题:
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
推荐答案
当数据目录中缺少数据文件但表定义文件存在或反之亦然时，我已经看到了这个问题.如果您使用的是 innodb_file_per_table，请检查数据目录以确保您拥有相关表的 .frm 文件和 .ibd 文件.如果是MYISAM，应该有.frm、.MYI和.MYD文件.
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 .frm file and .ibd file for the table in question. If it's MYISAM, there should be a .frm, .MYI and a .MYD file.
问题通常可以通过手动删除孤立文件来解决.
The problem can usually be resolved by deleting the orphaned file manually.
这篇关于薛定谔 MySQL 表:存在，但不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
