Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

Flushed the table I was about to move

Placed a read lock on it (even though nothing was writing to it and it wasn't in the replication stream)

Copied the .frm and .ibd over to a filesystem w/ some spare room

Unlocked the table

Truncated that table - this freed up enough space for the optimize to finish have replication start chugging along again.

Stop slaving/shutdown mysql

Copy the file out of tmp back to the data dir

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?

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 !!!
–
RolandoMySQLDBASep 28 '11 at 17:29

1 Answer
1

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:

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

ALTER TABLE tablename DISCARD TABLESPACE;

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

ALTER TABLE tablename IMPORT TABLESPACE;

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.