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

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

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.

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

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.

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.

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

Answer 2

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.

Answer 3

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 4

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.

Answer 5

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

Answer 6

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.

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.

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.