Wednesday, April 9, 2014

Mysql not starting.. corrupt innodb database

While I was trying to get mysql started on a Ubuntu Precise 12.04 server, I encountered the following error, in /var/log/mysql/error.log

140409 18:47:40 [Note] Event Scheduler: Loaded 0 events
140409 18:47:40 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.35-0ubuntu0.12.04.2-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
140409 18:47:40  InnoDB: Assertion failure in thread 140649490065152 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
13:17:40 UTC - mysqld got signal 6 ;
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.

Sounds like a possible corruption in innodb tables!!


Steps to fix this


1. Stop mysql service.
sudo service mysql stop

2. Backup /var/lib/mysql/ib*
3. Add the following line into /etc/mysql/my.cnf
innodb_force_recovery = 4
4. Restart mysql service.
sudo service mysql start

5. Dump all tables:# mysqldump -u root -p<password> -A > dump.sql
6. Drop all databases which need recovery.
You can find the InnoDB tables which might be corrupted using the query below,
SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';

7. Stop mysql service.
8. Remove /var/lib/mysql/ib*
9. Comment out innodb_force_recovery in /etc/mysql/my.cnf
10. Restart mysql service. Look at mysql error log. By default it should be /var/lib/mysql/server/hostname.com.err to see how it creates new ib* files.
11. Restore databases from the dump:mysql < dump.sql

No comments:

Post a Comment