MySQL table does not exist error, but table does, in fact exist.

So I had a strange MySQL error that made me bash my head against my desk for a good amount of time. I'm writing this in the hopes that I will save someone else from similar pains. Here were my symptoms:

  • Got the error: 1146: Table 'database.table' doesn't exist when issuing a SELECT query. I could confirm, however, through both webmin and the command line that the table, to the contrary, did exist.
  • I was able to log into the MySQL command line as the same user that was running the sql statement as I was in my php code and was able to run the query, see the table and the database it contained (so, in other words, there were no privilege problems from the command line).
  • All my tables are ALWAYS named in all lowercase letters to kill the possibility of there being some kind of uppercase/lowercase error (this however, can be a problem that would create similar results).
  • I was able to issue commands like SHOW TABLES with the same user that wasn't able to issue SELECT statements earlier
  • I was only able to successfully connect to the database as one of my several MySQL users.
  • I had recently populated the database via a mysqldump from another server.
  • Running CentOS.
  • Using the mysqli interface.

There's nothing really online that diagnosed such a strange set of coincidences (which is why I'm writing this) but here is the fix...

To me, being able to log in as only one user was the biggest red flag... so I started checking the table mysql.user. I noticed that I had repeats of the same users with different domains, and privileges etc. I immediately deleted all repeated users thinking that they MIGHT cause some kind of access conflict (although I doubted it). Then I updated the column host to "%" (meaning that each of the users could log in from anywhere), and I tested... no joy, still the same problems.

Then, I found that the password hashes were different lengths. Some were 16 and some were 32. I found out that was a hold out from MySQL 3, and the database that I had conducted a dump from was MySQL 5. I then edited the MySQL config file (my.conf) and set old_passwords=0 (was set at 1). Then I updates all the user's passwords to the same one via UPDATE mysql.user SET password=PASSWORD('password here');. I then flushed the privileges and tested... AND IT WORKED.

Evidently, having the 16 digit password hashes and the 32 digit hashes on the same table created the screwy error.

Obviously, this fix created some big vulnerabilities in my database. Having all the passwords the same, and having all the users being able to log in from where ever they feel like it are unacceptable risks, I trust if you are able to follow this tutorial this far that you can close those holes without further instruction.

Same Problem

Hello,

I am currently getting the same error saying it doesnt exit when it does exist. I tried your steps in order, setting the old_passwords paramter to 0, and then setting all the user passwords, and then flushing privileges. Although I still cant access it. Is there any steps im missing?

Have you tried...

Have you tried to issue "more basic" commands with your user like "SHOW TABLES" that don't use any selecting? There really is a myriad of reasons why you could be getting this error, and this was one of the least documented (at the time I wrote this), which probably also means it's one of the least common as well. Immediately, I would make 100% sure your user has privileges on the given database... that will be the source of most people's problems.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer