Bug #59641 Prepared XA transaction in system after hard crash causes future shutdown hang
Submitted: 20 Jan 2011 19:09 Modified: 15 Oct 2012 13:37
Reporter: Daniel Smythe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.54 OS:Any
Assigned to:
Tags: XA. hang
Triage: Triaged: D1 (Critical)

[20 Jan 2011 19:09] Daniel Smythe
Description:
If MySQL crashes ( simulated with kill -9 ) while an XA transaction is prepared, the XA transaction remains in the system after startup.

If this XA transaction is not manually COMMIT or ROLLBACK, it plagues the system by indefinitely hanging future MySQL clean shutdowns.

How to repeat:
mysql> use test;
Database changed

mysql> create table t1 ( id int ) ENGINE=INNODB;
Query OK, 0 rows affected (0.10 sec)

mysql> XA START '123';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> XA END '123';
Query OK, 0 rows affected (0.00 sec)

mysql> XA PREPARE '123';
Query OK, 0 rows affected (0.04 sec)

At this point, from another terminal, issue a kill -9 on mysqld

When MySQL is available again, simply do a clean shutdown. It will hang indefinitely.

Here is a err.log of the InnoDB crash recovery, all the way up to the shutdown hang. Also curious of note, is the double printout of the same XA recovery data.

110120 12:00:21  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Transaction 0 770 was in the XA prepared state.
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 0 row operations to undo
InnoDB: Trx id counter is 0 1280
110120 12:00:21  InnoDB: Started; log sequence number 0 46220
110120 12:00:21 [Note] Recovering after a crash using linux-bin
110120 12:00:21 [Note] Starting crash recovery...
110120 12:00:21  InnoDB: Starting recovery for XA transactions...
110120 12:00:21  InnoDB: Transaction 0 770 in prepared state after recovery
110120 12:00:21  InnoDB: Transaction contains changes to 5 rows
110120 12:00:21  InnoDB: 1 transactions in prepared state after recovery
110120 12:00:21 [Note] Found 1 prepared transaction(s) in InnoDB
110120 12:00:21 [Warning] Found 1 prepared XA transactions
110120 12:00:21 [Note] Crash recovery finished.
110120 12:00:21  InnoDB: Starting recovery for XA transactions...
110120 12:00:21  InnoDB: Transaction 0 770 in prepared state after recovery
110120 12:00:21  InnoDB: Transaction contains changes to 5 rows
110120 12:00:21  InnoDB: 1 transactions in prepared state after recovery
110120 12:00:21 [Note] Found 1 prepared transaction(s) in InnoDB
110120 12:00:21 [Warning] Found 1 prepared XA transactions
InnoDB: Starting in background the rollback of uncommitted transactions
110120 12:00:21  InnoDB: Rollback of non-prepared transactions completed
110120 12:00:21 [Note] Event Scheduler: Loaded 0 events
110120 12:00:21 [Note] /home/dsmythe/binaries/mysql/bin/mysqld: ready for connections.
Version: '5.1.54-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
110120 12:00:40 [Note] /home/dsmythe/binaries/mysql/bin/mysqld: Normal shutdown

110120 12:00:40 [Note] Event Scheduler: Purging the queue. 0 events
110120 12:00:42  InnoDB: Starting shutdown...

Suggested fix:
Shutdown should succeed.
[26 Apr 2011 8:24] Dipashree Banerjee
Is there any update on this bug?
[15 Oct 2012 13:37] Erlend Dahl
Fixed in 5.1.58.