Tables apparently dropped but are not!
This is very weird! Tables seem it have been dropped, but they are actually there -- I can query them. The tables are osdial_list and osdial_lists. I do NOT see them in /var/lib/mysql/osdial (where they should be) but I can query them. Here's the CLI output that demonstrates the issue:
[root@di-sda-34 osdial]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 571299 Server version: 5.1.53-MariaDB Source distribution MariaDB [osdial]> show tables like 'osdial_l%'; +------------------------------+ | Tables_in_osdial (osdial_l%) | +------------------------------+ | osdial_lead_filters | | osdial_lead_recycle | | osdial_live_agents | | osdial_live_inbound_agents | | osdial_log | +------------------------------+ 5 rows in set (0.00 sec) MariaDB [osdial]> select list_id from osdial_lists limit 5; +---------+ | list_id | +---------+ | 10 | | 11 | | 998 | | 999 | | 2100 | +---------+ 5 rows in set (0.00 sec) MariaDB [osdial]> select lead_id from osdial_list limit 5; +---------+ | lead_id | +---------+ | 4025 | | 4026 | | 4027 | | 4028 | | 4030 | +---------+ 5 rows in set (0.00 sec)
Note that the tables osdial_list and osdial_lists do not show up, but I can query them. How can this be? I am very confused! ANy help GREATLY appreciated.
jriggin
Answer
A possible reason could be - somebody deleted osdial_list.frm
and osdial_lists.frm
files. Thus, SHOW TABLES
(that simply lists the contents of a database directory) does not see them. But the server has opened them and has cached the table definitions before the .frm files were deleted, so you still can query them - the table is taken from the cache, it is not opened again (which would've failed). You can run FLUSH TABLES
, it will empty the open table cache, and this strange behavior will disappear. In the future, it's better to use DROP TABLE
to remove a table.