Mysql – InnoDB: Tablespace is missing for table

According to MySQL Certification Guide :

The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting it. This involves reclaiming unused space resulting from deletes and updates, and coalescing records that have become split and stored non-contiguously. OPTIMIZE TABLE also sorts the index pages if they are out of order and updates the index statistics

Also works for InnoDB tables, but maps to ALTER TABLE, which rebuilds the table. This updates index statistics and frees space in the clustered index.

So In Case of InnoDB :

Innodb is ACID compliant, the optimize table simply copies all records into a new table

1. If the index pages are not sorted, sort them,
2. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
3. If the table has deleted or split rows, repair the table.

And if you try to kill it when it's running you will not loose any records.

Additionally, case of InnoDB with innodb_defragment=1, there will be no ROLLBACK required, as in this case OPTIMIZE TABLE is incremental.

What was suggested to you thus far is what can be done to bring the database to a consistent state.

Here is what you need to know about InnoDB.

First of all here is the InnoDB Architecture in Pictorial Form

Look at the Picture. What components are essentially for InnoDB's self healing (sounds better that crash recovery)?

• The Double Write Buffer has the cache of changed blocks to be used for recovery.
• The Insert Buffer handles updates to nonunique indexes
• The InnoDB transaction logs (ib_logfile0,ib_logfile1) contain Redo Playback Info also used in recovery.
• There are undo logs (1023 of them, the maximum number of concurrent transactions)

You need three files for recovery

• ibdata1
• ib_logfile0
• ib_logfile1

The whole datadir folder (/var/lib/mysql) needs to be restored from the same moment in time it was being backed up. If there are no physical copies of datadir from the same moment in time, then log sequence numbers for future transactions can never be referenced correctly.

If you do not trust your host in this matter, perhaps you can get MySQL started with innodb_force_recovery set to an appropriate value.

Here are the values from the MySQL Documentation

1 (SRV_FORCE_IGNORE_CORRUPT)

Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Does not run transaction rollbacks after crash recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)

Does not do the redo log roll-forward in connection with recovery.

Once you select the value you want, start up MySQL with it. Then, perform a mysqldump of all the data. Keep that mysqldump somewhere.

• I would set innodb_force_recovery to 6. Restart mysql. Do the mysqldump (MyData6.sql)
• Change it to 5. Restart mysql. Do the mysqldump (MyData5.sql)
• Change it to 4. Restart mysql. Do the mysqldump (MyData4.sql)
• Change it to 3. Restart mysql. Do the mysqldump (MyData3.sql)
• Change it to 2. Restart mysql. Do the mysqldump (MyData2.sql)
• Change it to 1. Restart mysql. Do the mysqldump (MyData1.sql)

You now have 6 snapshots of the data based on how much could be recovered. You would then have to load each MySQLDump into a separate instance of MySQL. You would have to then peruse the data and determine if enough of the data has been recovered. Percona has a Data Recovery Toolkit that would do all this way more efficiently than I am saying it.

My answer is simply a poor man's approach to this.

I hope this helps !!!