SchrödingersMySQL表:存在，但不存在 [英] 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文件从数据库移动到数据库，读取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
相关但无济于事的问题:
Related, yet unhelpful questions:
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.
这篇关于SchrödingersMySQL表:存在，但不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
