23

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";
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Troy Knapp
  • 517
  • 3
  • 8
  • 17
  • Can you show us the exact query where you are getting this problem? – Jasdeep Singh Nov 23 '10 at 20:29
  • Can you reach the database using the terminal? – Wouter Dorgelo Nov 23 '10 at 20:33
  • @ Enrico Pallazzo Yes, I can ssh into the box, reach the database and execute sql from that table just fine. – Troy Knapp Nov 23 '10 at 20:35
  • @ajreal - I don't mean to be rude, but if I can execute the above query from the command line, then the database and the table obviously match. – Troy Knapp Nov 23 '10 at 22:26
  • @Troy Knapp - Provided the connection using for command line and php mysqli is identical, can double check that? – ajreal Nov 24 '10 at 05:49
  • @ajreal - and triple checked, and quadruple checked, etc. lol. When I conducted a mysql dump, it added a bunch more user listings, multiples of root etc. and I was thinking that having multiple rows for the same user might be causing those errors, so I deleted all the duplicates... still no joy. I looked further and noticed that the hashes of all my passwords were 16chars, the column is set to char(41) though... I'm wondering if that has anything to do with it, but I certainly don't want to go changing passwords half cocked. – Troy Knapp Nov 24 '10 at 12:38
  • @Troy Knapp - how about `select Host, Db from mysql.db;` – ajreal Nov 24 '10 at 12:42
  • @ajreal - the database 'spider' is owned by both 'localhost' and '%'. Meanwhile I discovered that 'old_password=1' in my config file. Several of the other usernames and passwords that were imported with the mysql dump had the full 32char hashes. The version I'm running is MySQL 5.0.77, so having the default set to use old passwords seems to be odd to me. – Troy Knapp Nov 24 '10 at 14:12
  • @Troy Knapp - Best bet is to http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html – ajreal Nov 24 '10 at 14:15
  • @ajreal - I figured it out, set old_password=0, updated all the passwords for all the users via UPDATE mysql.user SET password=PASSWORD('insert password here'); which gave me all 32char hashes. If you write up an answer I'll accept it, since you helped to lead me in the right direction. – Troy Knapp Nov 29 '10 at 15:07

10 Answers10

47

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.

Marcin Orlowski
  • 72,056
  • 11
  • 123
  • 141
Isaac
  • 1,505
  • 15
  • 8
  • 3
    I should say the answer that worked for my situation, hopefully yours too :) – Isaac Oct 03 '11 at 19:22
  • did this, now mysql does not start anymore, "Job failed to start". More detail: i copied the files from one mariadb on an archlinux to another mariadb on mint/ubuntu. maybe the format between the two mariadb versions is incompatible... – donquixote Feb 13 '15 at 20:53
  • I really think this should work but it does not for me. I made an archive of /var/lib, imported it into my new docker container so I have all the ib* files and everything else, but it still says tables don't exist that it lists. – Spencer Williams Feb 24 '16 at 22:11
  • Worked, but now a lot of tables I previously deleted are back. It's not really a problem, just strange. – G_V Oct 29 '16 at 11:09
  • I moved from Windows to Ubuntu and only copied my database folder woth the idb and frm files, what can I do? Please help, my heart's choking, I had manually entered over a hundred records into 2 tables and 1000+ in one table!! – hysabone.com Nov 20 '18 at 08:30
4

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

David Ramirez
  • 216
  • 2
  • 8
2

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

Jan Thomä
  • 13,296
  • 6
  • 55
  • 83
  • All of them are Linux boxes, this one is CentOS all the others are Debian... don't know if that helps. Of course, I can execute the sql via my local testing mirror which is windows, so it's not a case issue. – Troy Knapp Nov 23 '10 at 20:36
2

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.

Troy Knapp
  • 517
  • 3
  • 8
  • 17
2

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.

Allan Zeidler
  • 317
  • 2
  • 7
1

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.

Ray
  • 396
  • 3
  • 10
0

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

Michael Will
  • 105
  • 1
  • 6
0

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.

Phreditor
  • 469
  • 4
  • 4
0

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.

gdelfino
  • 11,053
  • 6
  • 44
  • 48
-1

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?

mveerman
  • 38,827
  • 3
  • 22
  • 14
  • I'm thinking this is the closest answer so far. I've been getting some weird behavior when connecting to the server. I can only successfully connect as one user (via php, using the terminal, I can log in as any) for whatever reason. That user appears to have full grants on the database and table in question, I even performed a GRANT ALL PRIVILEGES on the database in question, and I still get the same error. This is really weird. – Troy Knapp Nov 23 '10 at 22:20