Shut Down MySQL during InnoDB rollback after crash

Question

I am puzzled once again.

We had a server crash last night during some huge queries. Upon restart in the morning MySQL went into recovery mode. I needed to shut down the MySQL service and it is waiting for... I assume the background rollback for uncommitted transactions as the log says but it's been at it on a HOT server for over 9 hours with no end in sight.

Can the process be safely interrupted and resumed in background while service restarts? Or do we gotta wait it out gasp.

Any input or ideas (or even info about what's going on) would be super helpful. In advance, thank you.

We are running MySQL 5.5.

Here's the log output:

140930  9:04:30  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 41217466, file name .\mysql-bin.001584
InnoDB: Starting in background the rollback of uncommitted transactions
140930  9:26:14  InnoDB: Rolling back trx with id B214637, 15850890 rows to undo

InnoDB: Progress in percents: 1140930  9:26:14  InnoDB: Waiting for the background threads to start
140930  9:26:15 InnoDB: 1.1.8 started; log sequence number 1661733560991
140930  9:26:15 [Note] Server hostname (bind-address): '(null)'; port: 3306
140930  9:26:16 [Note]   - '(null)' resolves to '0.0.0.0';
140930  9:26:16 [Note] Server socket created on IP: '0.0.0.0'.
140930  9:26:18 [Note] Event Scheduler: Loaded 0 events
140930  9:26:18 [Note] wampmysqld: ready for connections.
Version: '5.5.24-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
140930 12:19:36 [Note] wampmysqld: Normal shutdown

140930 12:19:36 [Note] Event Scheduler: Purging the queue. 0 events
140930 12:19:38 [Warning] wampmysqld: Forcing close of thread 20  user: 'root'

140930 12:19:38 [Warning] wampmysqld: Forcing close of thread 19  user: 'root'

140930 12:19:38 [Warning] wampmysqld: Forcing close of thread 17  user: 'root'

140930 12:19:38  InnoDB: Starting shutdown...
140930 12:20:44  InnoDB: Waiting for 1 active transactions to finish
140930 12:21:47  InnoDB: Waiting for 200 pages to be flushed
140930 12:21:49  InnoDB: Waiting for 1 active transactions to finish
140930 12:22:55  InnoDB: Waiting for 1 active transactions to finish
140930 12:24:01  InnoDB: Waiting for 1 active transactions to finish
140930 12:25:07  InnoDB: Waiting for 1 active transactions to finish
140930 12:26:12  InnoDB: Waiting for 1 active transactions to finish
 ... for 9 hours

Answer 1

No, rollbacks cannot be done in the background. Both that and the double write checks have to be done before accepting connections again (meaning that if you kill it, you will have to start again on restart). REDO process, however, can and is done in the background.

What you can do is kill mysql (again) and restart it skipping that step:

/etc/init.d/mysqld start --innodb_force_recovery = 3

Then drop the offending table and recover it from the backups, that may be faster than performing a huge rollback.

For the future, try to avoid huge changes on a single transaction precisely to avoid rollbacks, which can be several times more expensive that the actual commit. I can provide you with some tools to help you do this, if they are administrative changes. If the error was (by) a developer, I can only recommend you the option --i-am-a-dummy. :-)