MySQL Table does not exist error, but it does exis

Question

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?

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).

Any help would be appreciated.

exact query:

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

Answer 1

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

Answer 2

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

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.

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?

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.

Oh yeah and never change a running system :)

Answer 3

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.

Answer 4

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?