CODEWITHSUNDEEP
mysqlcrashinnodb
wesley-hall
wesley-hall

Reputation: 163

Database crashed, need help recovering

I try to restart, and get this error:

ERROR! MySQL server PID file could not be found! Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/www.mysitecom.pid).

Look at the log.. see a ton of this : 

140502 14:10:24 InnoDB: The InnoDB memory heap is disabled
140502 14:10:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140502 14:10:24 InnoDB: Compressed tables use zlib 1.2.3
140502 14:10:24 InnoDB: Using Linux native AIO
140502 14:10:24 InnoDB: Initializing buffer pool, size = 128.0M
140502 14:10:24 InnoDB: Completed initialization of buffer pool
140502 14:10:24 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140502 14:10:24  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Error: trying to add tablespace 12 of name './roundcube/searches.ibd'
InnoDB: to the tablespace memory cache, but tablespace
InnoDB: 12 of name './d6nspw4f_dell/wp_users.ibd' already exists in the tablespace
InnoDB: memory cache!

Was able to restart by adding innodb_force_recovery = 4 to the sql config file, but all sites that touch the db are now broken and can’t be written to.

When I try to do a mysql dump of all tables, I get the error : 

mysqldump: Got error: 1146: Table 'x3hmcpl_hmcpl.blocked_ips' doesn't exist when using LOCK TABLES

Any help at all would be appreciated. We don't have a database guy here and I'm stuck with the task of trying to fix it as quickly as possible.

Upvotes: 1

Views: 5031

Answers (2)

Neminath
Neminath

Reputation: 95

The same issue occurred, and we resolved it by following these steps: 1.Edited the my.cnf file to include the parameter innodb_force_recovery=4. 2.Created a new .frm file using the command touch tablename.frm. 3.Set the correct file permissions using chown mysql tablename.frm. 4.Waited for 2-3 minutes for the changes to take effect. 5.Restarted MySQL (optional: disable foreign key checks if needed). 6.Commented out the innodb_force_recovery = 4 line in my.cnf, as leaving it active could cause MySQL synchronization issues. 7.Restarted MySQL again.

Below is more information about innodb_force_recovery: Start with the lowest level (1) and gradually increase if necessary. The levels are: 1: (SRV_FORCE_IGNORE_CORRUPT) Ignores corrupted pages. 2: (SRV_FORCE_NO_BACKGROUND) Prevents MySQL from running background operations. 3: (SRV_FORCE_NO_TRX_UNDO) Disables transaction rollbacks. 4: (SRV_FORCE_NO_IBUF_MERGE) Prevents insert buffer merges. 5: (SRV_FORCE_NO_UNDO_LOG_SCAN) Disables purging. 6: (SRV_FORCE_NO_LOG_REDO) Disables the redo log.

Upvotes: 0

akuzminsky
akuzminsky

Reputation: 2258

innodb_force_recovery = 4 puts InnoDB in read-only mode, it's not supposed to repair corrupted InnoDB tablespace.

Take a dump of all databases (mysqldump -A > mydb.sql) and recreate MySQL datadir.

Upvotes: 2

Related Questions