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 existwhen issuing a
SELECTquery. 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 TABLESwith the same user that wasn't able to issue
- 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.