MySQL Table does not exist error, but it does exist

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

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.

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.

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.

Answer 2

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