CODEWITHSUNDEEP
mysqldatabaseinnodb
Christopher Dias
Christopher Dias

Reputation: 19

Missing .frm and .idb files, cant remove table

I have a weird scenario where a technician deleted the .frm and .idb files for a table from one of my servers, fortunately it was a non critical table. I'm trying to recreate the table however when trying to use the CREATE TABLE, DROP TABLE or ALTER TABLE I get errors cos the tablespace still exists. I'm not really able to move the good tables to a new schema, drop the old schema then recreate the table that way as there are critical tables still in the schema which cannot be taken offline. Any thoughts on how I get rid of this "broken" table?

Upvotes: 0

Views: 3028

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562348

I created a test table:

mysql> use test;

mysql> create table test ( id serial primary key );

mysql> insert into test () values ();

Check that the files exist in my datadir:

$ cd /usr/local/var/mysql/test
$ ls
db.opt   test.frm test.ibd

Do the unthinkable!

$ rm test.*

Now I have a catch-22 situation:

mysql> show tables;
Empty set (0.00 sec)

mysql> create table test (id serial primary key);
ERROR 1050 (42S01): Table 'test' already exists

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test.test'

What to do?

mysql> create schema recovery;

mysql> create table recovery.test ( id serial primary key );

mysql> system cp /usr/local/var/mysql/recovery/test.frm /usr/local/var/mysql/test/

The .frm file stores table metadata in MySQL 5.x. Now that I have a .frm file, MySQL lets me query the table.

mysql> select * from test;
+----+
| id |
+----+
|  1 |
+----+

That's funny — how does it know about that row of data? Didn't I rm the .ibd file?

Answer: Files are not truly deleted while a process still has a filehandle open to it. Since I have not restarted the MySQL Server process, the .ibd tablespace file still exists, even though I unlinked it from the datadir!

Anyway, I can now drop the table. This removes the .frm and .ibd AND closes the filehandles to them, so they are truly deleted from the filesystem.

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

And finally I can now create a new table of the same name, even with different columns.

mysql> create table test ( bar int primary key );
Query OK, 0 rows affected (0.02 sec)

Upvotes: 2

Related Questions