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;

Probably you have no SELECT privileges. – Dejan Marjanović Jul 18 '11 at 01:56 — Dejan Marjanović, Jul 18 '11 at 01:56
I have SELECT privileges. First thing I checked. – Brandon Jul 18 '11 at 02:01 — Brandon, Jul 18 '11 at 02:01

score 2 · Answer 1 · answered Jul 21 '11 at 20:43

2

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.

answered Jul 21 '11 at 20:43

Keng

52,011
32
81
111

score 1 · Answer 2 · edited Oct 10 '14 at 12:47

1

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.

edited Oct 10 '14 at 12:47

JoelC

3,664
9
33
38

answered Oct 10 '14 at 12:39

trivi

11
1

score 0 · Accepted Answer · answered Jun 11 '12 at 13:17

0

Turns out they weren't tables, and I never actually figured out what they are :(

answered Jun 11 '12 at 13:17

Brandon

16,382
12
55
88

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

3 Answers3