Does anyone know under what conditions you can receive an 1146: Table '<database>.<table>' doesn't exist error when your table does, in fact, exist?
有谁知道在什么条件下您可以收到1146:表' <数据库> 。
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";
10 个解决方案
#1
44
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/mysql to /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 (select and describe) will fail, with the error Mysql error: table db.tableName doesn't exist. You'll see the .frm file in the db directory, and wonder why.
如果你从/var/lib/mysql MySQL数据目录复制到/道路/ /新/ dir,但只有复制数据库文件夹(比如MySQL,wpdb、电子商务等)和你有innodb表,innodb表将显示在“显示表”但查询(选择和描述)将失败,错误MySQL错误:数据库表。表名不存在。您将在db目录中看到.frm文件，并想知道为什么。
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*文件。
#2
4
Using the mysqlcheck would be in order in this case - so you can discard table sanity problems & repair them if neeeded.
在这种情况下，使用mysqlcheck是合适的——因此您可以丢弃表完整性问题，并在需要时修复它们。
#3
2
Could it be that your one server is a linux box? Mysql is case sensitive on linux but insensitive on windows.
您的一个服务器可能是linux box吗?Mysql在linux上区分大小写，但在windows上不区分大小写。
#4
2
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转储，它也传输了密码和用户信息。新的服务器已经用具有16char长度散列的根用户初始化，但是我的旧服务器使用的是较新的32 char散列长度。
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.
我必须进入我的。conf将旧密码设置为0(每次我尝试更新数据库时，新更新是16个字符)。然后通过命令UPDATE mysql将所有密码更新为相同。用户设置密码=密码('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%确定。
#5
1
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驱动程序将我的查询改为小写，因此我无法用它访问我的数据库(使用混合大小写字母)，尽管我的代码使用了正确的混合字母。
#6
0
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相同的登录?
#7
0
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会显示但不能工作。
#8
0
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.
表显示为“show tables”，但是选择或描述失败，因为表没有如您所描述的现有消息。这些文件是我所期望的。
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.
系统正常运行了好几个月，然后在服务mysqld重新启动后，将/etc/my.cnf设置为table_cache为512而不是256，系统就发生了侧移。
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.
最后我升级了从服务器来控制和重新加载系统，现在我想知道是什么原因导致了这个错误，如果在centos 6.4上选择xfs仍然是一个稳定的选择，或者罪魁祸首是mysql 5.1。
Oh yeah and never change a running system :)
哦，是的，永远不要改变运行系统:)
#9
0
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不再包括在内，但我的安装基本上和你在雪豹上看到的一样，我认为，而不是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控制面板启动sqmyld的结果，而不是我在命令行上启动它。(当迁移时，这个有点过时的控制面板会忘记你告诉它不要启动。)
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.
这似乎是一个所有权问题，可能也适用于其他系统。
#10
0
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.
为了解决这个问题，我在我的。cnf文件上写了"lower_case_table_names=1"。
#1
44
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/mysql to /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 (select and describe) will fail, with the error Mysql error: table db.tableName doesn't exist. You'll see the .frm file in the db directory, and wonder why.
如果你从/var/lib/mysql MySQL数据目录复制到/道路/ /新/ dir,但只有复制数据库文件夹(比如MySQL,wpdb、电子商务等)和你有innodb表,innodb表将显示在“显示表”但查询(选择和描述)将失败,错误MySQL错误:数据库表。表名不存在。您将在db目录中看到.frm文件，并想知道为什么。
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*文件。
#2
4
Using the mysqlcheck would be in order in this case - so you can discard table sanity problems & repair them if neeeded.
在这种情况下，使用mysqlcheck是合适的——因此您可以丢弃表完整性问题，并在需要时修复它们。
#3
2
Could it be that your one server is a linux box? Mysql is case sensitive on linux but insensitive on windows.
您的一个服务器可能是linux box吗?Mysql在linux上区分大小写，但在windows上不区分大小写。
#4
2
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转储，它也传输了密码和用户信息。新的服务器已经用具有16char长度散列的根用户初始化，但是我的旧服务器使用的是较新的32 char散列长度。
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.
我必须进入我的。conf将旧密码设置为0(每次我尝试更新数据库时，新更新是16个字符)。然后通过命令UPDATE mysql将所有密码更新为相同。用户设置密码=密码('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%确定。
#5
1
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驱动程序将我的查询改为小写，因此我无法用它访问我的数据库(使用混合大小写字母)，尽管我的代码使用了正确的混合字母。
#6
0
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相同的登录?
#7
0
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会显示但不能工作。
#8
0
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.
表显示为“show tables”，但是选择或描述失败，因为表没有如您所描述的现有消息。这些文件是我所期望的。
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.
系统正常运行了好几个月，然后在服务mysqld重新启动后，将/etc/my.cnf设置为table_cache为512而不是256，系统就发生了侧移。
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.
最后我升级了从服务器来控制和重新加载系统，现在我想知道是什么原因导致了这个错误，如果在centos 6.4上选择xfs仍然是一个稳定的选择，或者罪魁祸首是mysql 5.1。
Oh yeah and never change a running system :)
哦，是的，永远不要改变运行系统:)
#9
0
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不再包括在内，但我的安装基本上和你在雪豹上看到的一样，我认为，而不是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控制面板启动sqmyld的结果，而不是我在命令行上启动它。(当迁移时，这个有点过时的控制面板会忘记你告诉它不要启动。)
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.
这似乎是一个所有权问题，可能也适用于其他系统。
#10
0
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.
为了解决这个问题，我在我的。cnf文件上写了"lower_case_table_names=1"。