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.
