SQL SHOW TABLES lists a bunch of tables, but can't SELECT with them (No such table)

Question

I have a database called apsc and if I run SHOW TABLES; on it, these are the results:

mysql> show tables;
+------------------------------------+
| Tables_in_apsc                     |
+------------------------------------+
| aps_application                    |
| aps_application_backup             |
| aps_application_resource           |
| aps_package                        |
| aps_package_configuration          |
| aps_package_global_setting         |
| aps_package_resource_configuration |
| aps_package_resource_setting       |
| aps_package_series                 |
| aps_package_service                |
| aps_registry_object                |
| aps_registry_object_setting        |
| aps_registry_object_tag            |
| aps_resource                       |
| aps_resource_backup                |
| aps_resource_requirement           |
| aps_resource_requirement_backup    |
| aps_settings_sequenses             |
+------------------------------------+
18 rows in set (0.00 sec)

However, if I run SELECT * FROM aps_application I get this:

mysql> SELECT * FROM aps_application;
ERROR 1146 (42S02): Table 'apsc.aps_application' doesn't exist

In my /var/lib/mysql/apsc/ directory are a bunch of .frm files which leads me to believe these tables are InnoDB. However, if they were merely corrupt or missing from the data/log files in /var/lib/mysql/apsc/ibdata1 they should show up as table in use or what not, as I've had that problem with other InnoDB tables recently.

I believe these tables are part of Plesk as I had overwritten by databases files at some point and received errors about a missing view aps_application. Plesk is working fine now, so I doubt the table is corrupt.

Also, SHOW CREATE TABLE aps_application and SHOW CREATE VIEW aps_application both fail with the same error as select.

Edit: I'm logged in as root with full permissions. To check this I switched tables and SELECT worked like a charm. Also, If I am in phpMyAdmin and select this database it shows 0 tables unless I run SHOW TABLES; in the SQL tab;

Answer 1

ok, stab in the dark here but what if you fully qualify the table name? also, try to change the table name by putting a '_' after the name and see if you can select it then.

Answer 2

I had a similar issue. In my case it was the case sensitivity. select * from users is different from select * from USERS. So maybe you can try using the table name in upper case or the casing it was created with.