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

MySQL Table does not exist error, but it does exist

提问by Troy Knapp

Does anyone know under what conditions you can receive an 1146: Table '<database>.<table>' doesn't existerror when your table does, in fact, exist?

有谁知道在什么条件下1146: Table '<database>.<table>' doesn't exist当您的表确实存在时您会收到错误？

I use the same code on 5 servers, only one that I recently rented is showing this error, so I suspect it may be a settings or install error of some kind. I can execute my sql statement from the command line just fine. I can, obviously, see the table from the command line as well. I don't get any connection errors when I establish a connection (I'm using mysqli, btw).

我在 5 个服务器上使用相同的代码，只有我最近租用的一个显示此错误，所以我怀疑这可能是某种设置或安装错误。我可以从命令行执行我的 sql 语句就好了。显然，我也可以从命令行查看表格。建立连接时我没有收到任何连接错误（我正在使用 mysqli，顺便说一句）。

Any help would be appreciated.

任何帮助，将不胜感激。

exact query:

准确查询：

$sql = "SELECT DISTINCT(mm_dic_word) AS word FROM spider.mm_dictionary WHERE mm_dic_deleted=0";

采纳答案by Troy Knapp

Basically, I believe the problem that I was experiencing was due to differing password hash lengths. In my case, I got a new server, did a complete mysql dump on it which transferred passwords and user info also. The new server was already initialized with a root user that had a 16char length hash, but my old server was using the newer 32 char hash lengths.

基本上，我相信我遇到的问题是由于不同的密码哈希长度。就我而言，我得到了一个新服务器，在它上面做了一个完整的 mysql 转储，它也传输了密码和用户信息。新服务器已经使用具有 16 个字符长度的散列的 root 用户进行了初始化，但我的旧服务器使用的是较新的 32 个字符散列长度。

I had to go into my.conf set the old passwords setting to 0 (other wise every time I tried updating the database, the new update was 16 chars in length). I then updated all the passwords to be the same via the command UPDATE mysql.user SET password=PASSWORD('password here');, then I flushed privileges.

我不得不进入 my.conf 将旧密码设置为 0（否则每次我尝试更新数据库时，新更新的长度为 16 个字符）。UPDATE mysql.user SET password=PASSWORD('password here');然后我通过命令将所有密码更新为相同，然后我刷新了权限。

Obviously, having every user with the same password is a really bad idea, so I changed them one by one after I confirmed that it was working.

显然，让每个用户都使用相同的密码是一个非常糟糕的主意，所以我在确认它可以正常工作后一一更改。

I typed up a blog entry that goes into some of the other things I did that didn't work here, before I happened upon this solution (just in case one or more of those changes effected my outcome) however, I think that the above solution to be complete... but I haven't tried to reproduce the error so I can't be 100% sure.

在我遇到这个解决方案之前，我输入了一个博客条目，其中涉及我所做的一些在这里不起作用的其他事情（以防万一这些更改中的一个或多个影响了我的结果）但是，我认为上述解决方案是完整的...但我没有尝试重现错误，所以我不能 100% 确定。

回答by Isaac

This just happened to me and after a while I found the answer on a blog article, and wanted to put it here as well.

这只是发生在我身上，过了一段时间我在一篇博客文章中找到了答案，并想把它放在这里。

If you copy the MySQL data directory from /var/lib/mysqlto /path/to/new/dir, but only copy the database folders (i.e. mysql, wpdb, ecommerce, etc) AND you do have innodb tables, your innodb tables will show up in 'show tables' but queries on them (selectand describe) will fail, with the error Mysql error: table db.tableName doesn't exist. You'll see the .frmfile in the db directory, and wonder why.

如果您从/var/lib/mysqlto复制 MySQL 数据目录/path/to/new/dir，但只复制数据库文件夹（即mysql、wpdb、ecommerce等）并且您确实有 innodb 表，则您的 innodb 表将显示在“显示表”中，但对它们的查询（select和describe）将失败，有错误Mysql error: table db.tableName doesn't exist。您将.frm在 db 目录中看到该文件，并想知道为什么。

For innodb tables, it's important to copy over the ib*files, which in my case were ibdata1, ib_logfile0, and ib_logfile1. Once I did the transfer making sure to copy those over, everything worked as expected.

InnoDB表，它的拷贝过来是非常重要的ib*文件，这在我的情况是ibdata1，ib_logfile0和ib_logfile1。一旦我完成转移并确保将它们复制过来，一切都按预期进行。

If your my.cnf file contains "innodb_file_per_table" the .ibd file will be present in the db directory but you still need the ib* files.

如果您的 my.cnf 文件包含“innodb_file_per_table”，则 .ibd 文件将出现在 db 目录中，但您仍然需要 ib* 文件。

回答by David Ramirez

Using the mysqlcheck would be in order in this case - so you can discard table sanity problems & repair them if neeeded.

在这种情况下，可以使用 mysqlcheck - 因此您可以丢弃表健全性​​问题并在需要时修复它们。

回答by Jan Thom?

Could it be that your one server is a linux box? Mysql is case sensitive on linux but insensitive on windows.

难道你的一台服务器是一个 linux 机器？Mysql 在 linux 上区分大小写，但在 windows 上不区分大小写。

回答by Allan Zeidler

This happened to me when I was trying to select a table using UPPERCASE and the table name was lowercase.

当我尝试使用大写字母选择表并且表名是小写时，这发生在我身上。

So, to solve this question, I put "lower_case_table_names=1" on my.cnf file.

因此，为了解决这个问题，我在 my.cnf 文件中放置了“lower_case_table_names=1”。

回答by Ray

I had this kind of behaviour once. Later on I discovered that the JDBC driver I used changed my query to lower case, so I couldn't reach my database (which used mixed case letters) with it, although my code was using the correct mixed letters.

我曾经有过这种行为。后来我发现我使用的 JDBC 驱动程序将我的查询更改为小写，所以我无法使用它访问我的数据库（使用混合大小写字母），尽管我的代码使用了正确的混合字母。

回答by mveerman

If you're logged in as someone who doesn't have permission to view that database/table then you'll probably get that result. Are you using the same login on the command line as you are through mysqli?

如果您以无权查看该数据库/表的身份登录，那么您可能会得到该结果。您是否在命令行上使用与通过 mysqli 相同的登录名？

回答by gdelfino

It could be related to having InnoDB and MyISAM tables together. If you copy the database files, the MyISAM will be fine and the InnoDB will show up but fail to work.

这可能与将 InnoDB 和 MyISAM 表放在一起有关。如果您复制数据库文件，MyISAM 将正常运行，InnoDB 将显示但无法工作。

回答by Michael Will

I have seen this on a centos 6.4 system with mysql 5.1 and an xfs filesystem.

我在带有 mysql 5.1 和 xfs 文件系统的 centos 6.4 系统上看到过这个。

The tables show with 'show tables' but a select or describe fails with the table not existing message as you described. The files are where I expect them to be.

表格显示为“显示表格”，但选择或描述失败，表格不存在，如您所描述的那样。这些文件是我希望它们存在的地方。

The system was running fine for months, then after a service mysqld restart after changing /etc/my.cnf to set table_cache to 512 instead of 256, it went sideways.

系统运行了好几个月，然后在更改 /etc/my.cnf 将 table_cache 设置为 512 而不是 256 后重新启动服务 mysqld 后，它横盘整理。

According to arcconf the raid controller thinks everything is fine. xfs_check does not find anything. the system-event-list of IPMI is clear. dmesg shows some complaints by iptables about connection tracking and dropping packages, so we may have been DOS'd, but since there is nothing really running outside facing on the server I don't see how it could affect mysql data integrity?

根据 arcconf，raid 控制器认为一切正常。xfs_check 没有找到任何东西。IPMI 的系统事件列表是明确的。dmesg 显示了 iptables 关于连接跟踪和丢弃包的一些抱怨，所以我们可能已经被 DOS 拒绝了，但是由于服务器上没有真正面向外部运行的东西，我看不出它会如何影响 mysql 数据完整性？

I ended up promoting the slave to master and reloading the system, and now am wondering what could have caused the error, and if the choice of xfs on centos 6.4 is still a stable choice, or if the culprit was mysql 5.1.

我最终将slave提升为master并重新加载系统，现在想知道可能导致错误的原因是什么，在centos 6.4上选择xfs是否仍然是一个稳定的选择，或者罪魁祸首是mysql 5.1。

Oh yeah and never change a running system :)

哦，是的，永远不要改变正在运行的系统:)

回答by Phreditor

Mac OS X? STOP, don't recopy anything yet...

Mac OS X？停止，不要重新复制任何东西...

I had this problem a couple of times on Mavericks. MySQL is no longer included, but my install is essentially the same as what you'd expect to find on Snow Leopard, I think, rather than MAMP or something.

我在小牛队遇到过几次这个问题。MySQL 不再包括在内，但我的安装基本上与您期望在 Snow Leopard 上找到的相同，我认为，而不是 MAMP 或其他东西。

After migrating from one computer to another I had this problem. It was the result of the MySQL control panel starting mysqld, rather than my starting it on the command line. (When migrating, this somewhat obsolete control panel forgets that you told it NOT to start on boot.)

从一台计算机迁移到另一台计算机后，我遇到了这个问题。这是 MySQL 控制面板启动 mysqld 的结果，而不是我在命令行上启动它的结果。（在迁移时，这个有点过时的控制面板会忘记您告诉它不要在启动时启动。）

Look at the processes (top or activity monitor), on my system: if owner is root, it was started by launched and doesn't work properly; the correct process will have _mysql as owner.

查看我系统上的进程（顶部或活动监视器）：如果所有者是 root，则它是由启动启动的并且无法正常工作；正确的进程将 _mysql 作为所有者。

Sometimes, I have both process running side by side!

有时，我让两个进程并排运行！

Oddly, you can do everything, including use mysql via command line. However, even though innodb tables are listed they generate a do not exist error on querying.

奇怪的是，您可以做任何事情，包括通过命令行使用 mysql。但是，即使列出了 innodb 表，它们也会在查询时生成不存在错误。

This seems to be an ownership issue, which may apply on other systems as well.

这似乎是一个所有权问题，也可能适用于其他系统。