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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.