MySQL – Shut Down During InnoDB Rollback After Crash

There is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1, which should have grown immensely.

If you kill the mysqld process and restart mysql, it will just pickup where it left off as part of the crash recovery cycle.

DISCLAIMER : Not Responsible for Data Loss

What you could do may result in data loss for other tables, but there is something you can do to circumvent InnoDB's normal crash recovery cycle.

There is a startup option called innodb_force_recovery, which allows you to bypass various stages of InnoDB crash recovery.

According to MySQL Documentation on Forcing InnoDB Recovery, here are the settings and its effects:

1 (SRV_FORCE_IGNORE_CORRUPT)

Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevent the master thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Do not run transaction rollbacks after crash recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)

Do not do the redo log roll-forward in connection with recovery.

With transactional changes buried in the UNDO and REDO logs, you run the risk of

• losing data meant to be written
• keeping data meant to be deleted

In case you expect bad side effects, backup the entire /var/lib/mysql and put it somewhere in case you want to copy ibdata1, ib_logfile0, and ib_logfile1 and retry normal recovery.

If mysql is fully up in one of the modes

• mysqldump all data except the offending table
• shutdown mysql
• remove everything in /var/lib/mysql except /var/lib/mysql/mysql
• start mysql
• reload the mysqldump

CAVEAT : Make sure you backup everything !!!

I hope this helps !!!

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.