Xampp mysql innodb recovery failed after pc crash

Question

So it's happened to me a few times that i have this issue. I didn't realised why it's happening i just know after restarting pc mysql won't start.

So i was reinstalling xampp and messing around few times untill i got it work. Last time when it happened i looked at mysql.err file and noticed errors are about corrupted files. So i deleted those databases and mysql started

But today i have seen the right reason. I was sitting by pc when it suddenly got frozen and restarted. After that mysql won't start.

Looking at my localhost/xampp status page it says mysql deactivated

Went to check logs, actually mysql.err file and there it says recovery from crash and innodb can't recover from crash.

Here are the logs

2014-03-05 11:18:11 7829 mysqld_safe Starting mysqld daemon with databases from  /opt/lampp/var/mysql
2014-03-05 11:18:11 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2014-03-05 11:18:11 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-03-05 11:18:11 8235 [Note] Plugin 'FEDERATED' is disabled.
2014-03-05 11:18:11 8235 [Note] InnoDB: The InnoDB memory heap is disabled
2014-03-05 11:18:11 8235 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-03-05 11:18:11 8235 [Note] InnoDB: Compressed tables use zlib 1.2.8
2014-03-05 11:18:11 8235 [Note] InnoDB: Not using CPU crc32 instructions
2014-03-05 11:18:11 8235 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2014-03-05 11:18:11 8235 [Note] InnoDB: Completed initialization of buffer pool
2014-03-05 11:18:11 8235 [Note] InnoDB: Highest supported file format is Barracuda.
2014-03-05 11:18:11 8235 [Note] InnoDB: The log sequence numbers 11011943 and 11011943 in ibdata files do not match the log sequence number 11012198 in the ib_logfiles!
2014-03-05 11:18:11 8235 [Note] InnoDB: Database was not shutdown normally!
2014-03-05 11:18:11 8235 [Note] InnoDB: Starting crash recovery.
2014-03-05 11:18:11 8235 [Note] InnoDB: Reading tablespace information from the .ibd   files...
2014-03-05 11:18:11 8235 [ERROR] InnoDB: Attempted to open a previously opened  tablespace. Previous tablespace wpdemo/wp_terms uses space ID: 93 at filepath: ./wpdemo/wp_terms.ibd. Cannot open tablespace wordpress/wp_evg_feeds which uses space ID: 93 at filepath: ./wordpress/wp_evg_feeds.ibd
2014-03-05 11:18:11 7f3ca4a11700  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./wordpress/wp_evg_feeds.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
2014-03-05 11:18:11 7829 mysqld_safe mysqld from pid file /opt/lampp/var/mysql/aleksandar-PC.pid ended

Last time when i got an error i didn't realised it was because of pc crash and i didn't needed files, so i just deleted corrupted database files and mysql started. Problem is now I CAN'T delete any file, i need those databases back and working. Any suggestions?

Answer 1

So renaming broken files to for example wp_terms.ibd.bkp so mysql can skip those tables and start. Now when mysql recovered and started, i shut it down regularly. Than renamed .bkp files back to normal table files and started mysql back. Now when mysql is shut down regularly it won't check for recovery and started normally. The files that are supposed to be broken are working now. And everything is back to normal. What a logic!