MySQL crashed, InnoDB fails to recover

innodbMySQL

On MySQL 5.0 on CentOS 5.11, MySQL service has crashed and fails to start. Adding innodb_force_recovery=3 in /etc/my.cnf, creates the following error, which repeats with all table names of database DBNAME:

160511 10:10:54  InnoDB: Error: table 'DBNAMEnew/tablename1'
InnoDB: in InnoDB data dictionary has tablespace id ....,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.

Following MySQL troubleshooting, part "Tablespace Does Not Exist", it asks to perform 2 steps:

  1. Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.
  2. Issue DROP TABLE for the original table. That should successfully drop the table and InnoDB should print a warning to the error log that the .ibd file was missing.

/var/lib/mysql/ has DBNAME directory. I've created empty .frm files for all the tables appearing in the error, in DBNAMEnew directory and tried to start the service, which fails, similarly.
Also, since the mysqld wouldn't start, how can I "DROP TABLE" as instructed in the second step?

innodb settnigs in my.cnf

#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2400M
innodb_file_per_table
#innodb_data_home_dir = <directory>
innodb_file_io_threads = 4
innodb_force_recovery=3
#innodb_force_recovery=4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
#innodb_fast_shutdown
innodb_log_buffer_size = 4M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120

I've already backed up all /var/lib/mysql. Do you know how to recover from this crashing state?

Question UPDATE:
I've copied all, .frm files from DBNAME directory to DBNAMEnew. To have matching .frm file for this orphan table. Still getting the following

160512 09:10:52  InnoDB: Error: table 'DBNAMEnew/tablename1'
InnoDB: in InnoDB data dictionary has tablespace id ....,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.

QUESTION UPDATE
I've created a database with the same schema, and copied over the .frm files to the failing database. Now, getting the following errors:

160515 10:54:11  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 3602 935861248
160515 10:55:39  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 160515 10:55:41 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

Since MySQL fails to start, I'm unable to proceed. Is there any way to recover when InnoDB internal data dictionary is in such a state?

Best Answer

Empty frm files won't work because they have a specific syntax which is being checked by mysql.

[root@server test]# touch test_one.frm
[root@server test]# chown mysql test_one.frm
[root@server test]# mysql test

Although it is showing up in show table because it only checks for the existence of the file ...

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
....
| test_one       |
...
+----------------+

... any operations on it will fail.

mysql> show create table test_one;
ERROR 1033 (HY000): Incorrect information in file: './test/test_one.frm'

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

The documentation says:

Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.

Now, matching (same structure) is actually not mandatory if you only want to drop it but you need to have a valid frm file.

If you do this you should be able to drop the table afterwards:

mysql> create table test_recoverer (id int unsigned primary key);
Query OK, 0 rows affected (0.31 sec)

[root@server test]# cat test_recoverer.frm > test_one.frm

mysql> drop table test_one;
Query OK, 0 rows affected (0.11 sec)
Related Question