MySQL > Table doesn't exist. But it does (or it should)

Question

Just in case anyone still cares:

I had the same issue after copying a database directory directly using command

cp -r /path/to/my/database /var/lib/mysql/new_database

If you do this with a database that uses InnoDB tables, you will get this crazy 'table does not exist' error mentioned above.

The issue is that you need the ib* files in the root of the MySQL datadir (e.g. ibdata1, ib_logfile0 and ib_logfile1).

When I copied those it worked for me.

For me on Mac OS (MySQL DMG Installation) a simple restart of the MySQL server solved the problem. I am guessing the hibernation caused it.

I get this issue when the case for the table name I'm using is off. So table is called 'db' but I used 'DB' in select statement. Make sure the case is the same.

This error can also occur when setting lower_case_table_names to 1, and then trying to access tables that were created with the default value for that variable. In that case you can revert it to the previous value and you will be able to read the table.

1. stop mysqld
2. backup mysql folder: cp -a /var/lib/mysql /var/lib/mysql-backup
3. copy database folder from old machine to /var/lib/mysql
4. override ib* (ib_logfile* , ibdata ) from old database
5. start mysqld
6. dump dabase
7. mysqldump >dbase.mysql
8. stop mysql service
9. remove /var/lib/mysql
10. rename /var/lib/mysql-backup to /var/lib/mysql
11. start mysqld
12. create the database
13. mysqldump < dbase.mysql