Take the 2-minute tour ×

So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for some more space.

This datadir is a file system used ONLY as mysql's datadir so there wasn't anything else to free up.

I had a 4 gig innodb test table that wasn't part of the replication stream so I figured I'd try something to see if it'd work, and being a test environment I wasn't too worried if things went horribly wrong.

Here's the steps I took

  1. Flushed the table I was about to move
  2. Placed a read lock on it (even though nothing was writing to it and it wasn't in the replication stream)
  3. Copied the .frm and .ibd over to a filesystem w/ some spare room
  4. Unlocked the table
  5. Truncated that table - this freed up enough space for the optimize to finish have replication start chugging along again.
  6. Stop slaving/shutdown mysql
  7. Copy the file out of tmp back to the data dir
  8. Restart mysql

Nothing shows up in the .err log, things look good. I connect and use mydb; and see the table I was messing with in show tables. But, if I try

select * from testtable limit 10;

I get the error

ERROR 1146 (42S02): Table 'mydb.testtable' doesn't exist

From what I can tell so far I can read from all the other tables just fine and replication started back up w/o any complaints.

Is there anything I can do to recover from this point? I can rebuild it from scratch if need be but was curious what others thought about this venture in general. Was there anything about the series of steps I took that would have ended up w/ more flawless results?

What if this wasn't a test server I couldn't just 'do it live' and see what happens? What would have the best way to free up space temporarily on a production slave if I had to like that?

share|improve this question
This question would be incredibly helpful to those out there that have shot themselves in feet with AK47s unintentionally hosing InnoDB tables. +1 for making this situation more widely known within DBA.SE !!! –  RolandoMySQLDBA Sep 28 '11 at 17:29

1 Answer 1

up vote 5 down vote accepted

The biggest thing most people forget about TRUNCATE TABLE is that TRUNCATE TABLE is DDL and not DML. In InnoDB, the metadata within ibdata1 contains a numbered list of InnoDB tables. Using TRUNCATE TABLE causes the internal metadata id of the InnoDB table to shift. This happens because TRUNCATE TABLE effectively does the following:

Example: To truncate an InnoDB Table called mydb.mytb

USE mydb

The new mytb would thus have a different internal metadata id.

When you copied the .ibd file to some other place, the .ibd contains within it the original internal metadata id. Simply putting the .ibd file back does not cause a reconciliation of the internal metadata id with that of the one in ibdata1.

What you should have done is this:

Copy the .ibd file of the InnoDB table. Then, run this


To bring it back later on, copy the .ibd file back into datadir and then run


This would have preserved the internal metadata id.

Make sure .frm is always present.

I once helped a client restore 30 InnoDB tables he hosed in the same manner. I had to use another DB server and play some games with adding and dropping InnoDB tables to hunt down the correct internal metadata id.

The client found this article : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file . We used it and it helped a great deal. I hope it helps you.

share|improve this answer
I tried doing a test to see if I could successfully do this. I created a single int column innodb table, inserted 3 rows, flushed table with read lock. copied the .idb /tmp, unlocked tables, ran alter table innotest2 discard tablespace; Got an OK. Tried a select, got an error as expected, copied the file back, ran alter table innotest2 import table space. and I was greated with "ERROR 1030 (HY000): Got error -1 from storage engine ". I did I miss something there? –  atxdba Sep 28 '11 at 16:58
I know that was slightly different than what you suggested but the mysql manual was saying discard table space would delete the .idb file so i copied it first –  atxdba Sep 28 '11 at 16:59
@atxdba - Sorry, copy should be first. Updating answer... –  RolandoMySQLDBA Sep 28 '11 at 17:25

Your Answer


