MySQL Bugs: #12162: XA transactions and recovery

Bug #12162	XA transactions and recovery - problem with transaction IDs
Submitted:	25 Jul 2005 19:33	Modified:	16 Aug 2005 4:10
Reporter:	Tonda David	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.07	OS:	Linux (Linux (Fedora core3 / Red Hat))
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
When using XA transactions ,if you prepare several transactions with the same name (crashing the 
server several times) then you get several times that transaction name when 
you use xa recover. If you then use xa rollback (or commit) to finish those 
transactions then the server only commits/rollback the 1st one. You need to 
call xa rollback/xa commit serveral times until you have nothing in xa 
recover.

How to repeat:
Start a XA transaction called 'test'
After preparing the transaction just KILL the server
Restart the server , do a xa recovery -> you get the 'test' transaction information
Start another XA transaction with the same name 'test'
Prepare the transaction and kill the server
Restart the server , do a xa recovery -> you get 2 transactions called 'test'
If you try : xa commit 'test' it will commit only the 1st one (same for rollback)

Suggested fix:
The server should read the logs and prevent you from starting a transaction with the same ID that one in the recovery mode.

Thank you for your bug report. I was able to repeat this bug using 5.0.11.

jan@hundin:~/mysql-5.0/client> ./mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.11-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table t1(a int) engine = innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> xa start 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(5);
Query OK, 1 row affected (0.00 sec)

mysql> xa end 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> xa prepare 'test';
Query OK, 0 rows affected (0.00 sec)

jan@hundin:~/mysql-5.0/client> ./mysql test -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.11-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> xa start 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(5);
Query OK, 1 row affected (0.00 sec)

mysql> xa end 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> xa prepare 'test';
Query OK, 0 rows affected (0.00 sec)

InnoDB: Starting in background the rollback of uncommitted transactions
050726  8:44:59  InnoDB: Rollback of non-prepared transactions completed
050726  8:44:59  InnoDB: Started; log sequence number 0 47724
050726  8:44:59 [Note] Recovering after a crash using binlog
050726  8:44:59 [Note] Starting crash recovery...
050726  8:44:59  InnoDB: Starting recovery for XA transactions...
050726  8:44:59  InnoDB: Transaction 0 1280 in prepared state after recovery
050726  8:44:59  InnoDB: Transaction contains changes to 1 rows
050726  8:44:59  InnoDB: Transaction 0 770 in prepared state after recovery
050726  8:44:59  InnoDB: Transaction contains changes to 1 rows
050726  8:44:59  InnoDB: 2 transactions in prepared state after recovery
050726  8:44:59 [Note] Found 2 prepared transaction(s) in InnoDB
050726  8:44:59 [Note] ignore xid 'test'
050726  8:44:59 [Note] ignore xid 'test'
050726  8:44:59 [Warning] Found 2 prepared XA transactions
050726  8:44:59 [Note] Crash recovery finished.
050726  8:44:59  InnoDB: Starting recovery for XA transactions...
050726  8:44:59  InnoDB: Transaction 0 1280 in prepared state after recovery
050726  8:44:59  InnoDB: Transaction contains changes to 1 rows
050726  8:44:59  InnoDB: Transaction 0 770 in prepared state after recovery
050726  8:44:59  InnoDB: Transaction contains changes to 1 rows
050726  8:44:59  InnoDB: 2 transactions in prepared state after recovery
050726  8:44:59 [Note] Found 2 prepared transaction(s) in InnoDB
050726  8:44:59 [Note] ignore xid 'test'
050726  8:44:59 [Note] ignore xid 'test'
050726  8:44:59 [Warning] Found 2 prepared XA transactions

This bug is most probable in MySQL Server because it should not support starting two XA transactions with identical XID.

Regards,
    JanL

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28233

fixed in 5.0.12

Documented 5.0.12 changelog:

 <literal>XA</literal> allowed two active transactions to be started with the same XID. (Bug #12162)