2

One of our mysql instances crashed and failed to start: 

120422 10:04:13  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

I've failed to understand what should I do in order to remove this temporary table from the TABLESPACE.

How should I proceed?

Nick Chammas
  • 14,810
  • 17
  • 76
  • 124
smintz
  • 175
  • 2
  • 5

2 Answers2

3

There are those rare times when an InnoDB table gets stuck in the ibdata1 deeply that you must try forecing mysql to start up in spite of it.

Try using innodb_force_recovery in /etc/my.cnf (Option values 1-6)

[mysqld]
innodb_force_recovery = 1

then restart mysql.

This should at least allow a mysqldump

mysqldump -u... -p... --routines --triggers --all-databases > /root/MySQLData.sql

Once you have mysqldumped all data, remove the innodb_force_recovery

Then, perform a complete cleanup of InnoDB as follows

service mysql stop
mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_old/mysql/* /var/lib/mysql/mysql/.
chown -R mysql:mysql /var/lib/mysql
service mysql start
mysql -u... -p... < /root/MySQLData.sql

After service mysql start, new InnoDB files (ibdata1, ib_logfile0, ib_logfile1) are created

Give it a Try !!!

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

Before proceeding with any recovery steps, always take a backup! Recovery could sometimes further corrupt your database.

Check to see if permissions or ownership could have changed for you database files, which does not allow MySQL to open the necessary tablespace files.

You might have a situation where temporary .ibd file was deleted by MySQL, but MySQL crashed before finishing clean up.

If the .frm file is still present, then:

  1. Drop the table with the missing .ibd, enclose table name in backticks.

Otherwise you will need to create a mock .frm file on another MySQL server or instance. Here are the steps:

  1. Try to remember or see if binary logs could give you a hint on what table you might have been altering.
  2. Go to another MySQL server or instance with same version of MySQL.
  3. Create an empty table with matching schema, enclose table name in backticks.
  4. Copy the resulting .frm file to the location where you have the missing .ibd file. In Unix shell, enclose file name in double quotes.
  5. Start the crashed MySQL server.
  6. Drop the table with the missing .ibd, enclose table name in backticks.

These instructions are also available in MySQL Manual as stated in the error message, in somewhat different form: Troubleshooting InnoDB Data Dictionary Operations.

dabest1
  • 2,120
  • 15
  • 21