| Bug #12161 | Xa recovery and client disconnection | ||
|---|---|---|---|
| Submitted: | 25 Jul 2005 19:28 | Modified: | 9 Apr 2015 10:09 |
| Reporter: | Tonda David | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.07 | OS: | Linux (Linux (Fedora Core 3 / Red Hat)) |
| Assigned to: | Andrei Elkin | CPU Architecture: | Any |
[25 Jul 2005 19:28]
Tonda David
[26 Jul 2005 6:40]
Jan Lindström
Thank you for your bug report. I was able to repeat this bug using 5.0.11.
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);
Query OK, 0 rows affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> xa start 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (1);
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)
mysql> Killed
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 2 to server version: 5.0.11-beta-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> xa recover
-> ;
Empty set (0.00 sec)
This happens because if client is killed all transactions in that connection also are freed. Thus they are not active anymore. Now if you do xa recover in a new connection, this recover does not find any transactions in parepared stage. This is because server has not been crashed and does not do a full crash recovery.
Breakpoint 2, innobase_xa_recover(xid_t*, unsigned) (xid_list=0x8c69bb0,
len=128) at ha_innodb.cc:7081
7081 if (len == 0 || xid_list == NULL) {
(gdb) p len
$2 = 128
(gdb) p xid_list
$3 = (XID *) 0x8c69bb0
(gdb) step
7086 return(trx_recover_for_mysql(xid_list, len));
(gdb)
trx_recover_for_mysql (xid_list=0x8c69bb0, len=128) at trx0trx.c:1934
1934 int count = 0;
Current language: auto; currently c
(gdb)
1942 mutex_enter(&kernel_mutex);
(gdb) next
1944 trx = UT_LIST_GET_FIRST(trx_sys->trx_list);
(gdb) step
1946 while (trx) {
(gdb) p trx
$4 = (trx_t *) 0x0
(gdb) p trx_sys->trx_list
$5 = {count = 0, start = 0x0, end = 0x0}
Thus full recovery is necessary in this case.
[3 Apr 2006 3:27]
feng shao
I want to repair the bug in some steps 1) I modified THD::cleanup function in sql_class.cpp (Line 371) /** * appended by sf for Bug #12161 */ //{ // ha_rollback(this); // xid_cache_delete(&transaction.xid_state); //} #define SUPPORT_PENDING_TX_IN_MYSQL #ifdef SUPPORT_PENDING_TX_IN_MYSQL { if (transaction.xid_state.xa_state != XA_PREPARED) { ha_rollback(this); xid_cache_delete(&transaction.xid_state); } else { XID localxid = transaction.xid_state.xid; ha_semi_rollback(this); xid_cache_delete(&transaction.xid_state); xid_cache_insert(&localxid, XA_PREPARED); } } #else { ha_rollback(this); xid_cache_delete(&transaction.xid_state); } #endif 2) I added ha_semi_rollback macro in handler.h (Line 874) #define ha_semi_rollback(thd) (ha_semi_rollback_trans((thd), TRUE)) I added ha_semi_rollback_trans function in handler.cpp (Line 764) /** * It is the same as ha_rollback_trans function but not calling the innodbase rollback fn. * If needing, we can forbid to call the other rollback functions in other storage engines. **/ int ha_semi_rollback_trans(THD *thd, bool all) { int error=0; THD_TRANS *trans=all ? &thd->transaction.all : &thd->transaction.stmt; bool is_real_trans=all || thd->transaction.all.nht == 0; DBUG_ENTER("ha_rollback_trans"); if (thd->in_sub_stmt) { /* If we are inside stored function or trigger we should not commit or rollback current statement transaction. See comment in ha_commit_trans() call for more information. */ if (!all) DBUG_RETURN(0); DBUG_ASSERT(0); my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0)); DBUG_RETURN(1); } #ifdef USING_TRANSACTIONS if (trans->nht) { /* Close all cursors that can not survive ROLLBACK */ if (is_real_trans) /* not a statement commit */ thd->stmt_map.close_transient_cursors(); for (handlerton **ht=trans->ht; *ht; ht++) { int err; if (!strcmp((*ht)->name , "InnoDB")) {//added by sf for rounding the innobase rollback fn *ht = 0; continue; } if ((err= (*(*ht)->rollback)(thd, all))) { // cannot happen my_error(ER_ERROR_DURING_ROLLBACK, MYF(0), err); error=1; } statistic_increment(thd->status_var.ha_rollback_count,&LOCK_status); *ht= 0; } trans->nht=0; trans->no_2pc=0; if (is_real_trans) thd->transaction.xid_state.xid.null(); if (all) { thd->variables.tx_isolation=thd->session_tx_isolation; thd->transaction.cleanup(); } } #endif /* USING_TRANSACTIONS */ /* If a non-transactional table was updated, warn; don't warn if this is a slave thread (because when a slave thread executes a ROLLBACK, it has been read from the binary log, so it's 100% sure and normal to produce error ER_WARNING_NOT_COMPLETE_ROLLBACK. If we sent the warning to the slave SQL thread, it would not stop the thread but just be printed in the error log; but we don't want users to wonder why they have this message in the error log, so we don't send it. */ if (is_real_trans && (thd->options & OPTION_STATUS_NO_TRANS_UPDATE) && !thd->slave_thread) push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARNING_NOT_COMPLETE_ROLLBACK, ER(ER_WARNING_NOT_COMPLETE_ROLLBACK)); DBUG_RETURN(error); } It is the mostly same as ha_rollback_trans function expect adding some extra codes. 3) I modified innobase_close_connection in ha_innodb.cpp (Line 2260) /** appended by sf for Bug #12161 * */ //innobase_rollback_trx(trx); //trx_free_for_mysql(trx); //return(0); #define SUPPORT_PENDING_TX_IN_INNOBASE #ifdef SUPPORT_PENDING_TX_IN_INNOBASE if (trx->conc_state != TRX_PREPARED) { innobase_rollback_trx(trx); trx_free_for_mysql(trx); return(0); } else return -1; #else innobase_rollback_trx(trx); trx_free_for_mysql(trx); return(0); #endif I compiled the mysql source, then runned it with xa command. And i found that's ok to pend xa transaction when client disconnection.
[3 Apr 2006 12:48]
Sergei Golubchik
Unfortunately, it is not enough. The main problem is the binlog. With your solution, if you shutdown (or kill -9) MySQL when such a transaction is prepared, it will be lost - which should not happen for a prepared transaction. The dilemma is - if you write transaction into binlog on commit (as it is now), and not on XA PREPARE, you risk losing it in a crash (as above). If you write it on XA PREPARE, then another transaction may be logged between XA PREPARE and XA COMMIT of this transaction, and binlog currently cannot handle such an interleaving :(
[4 Apr 2006 3:17]
feng shao
I test my modified source code.
mysql> xa start '111';
mysql> insert into sf values (2);
mysql> xa end '111';
mysql> xa prepare '111';
then login in another terminal and kill -9 mysql_processId.
boot mysqld_safe again.
then back the first terminal and ./mysql
mysql> xa recover;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
| 1 | 3 | 0 | 111 |
+----------+--------------+--------------+------+
mysql> select * from sf;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.02 sec)
mysql> xa commit '111';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sf;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
That's ok. The prepared transaction wasn't lost.
And I don't know what's mean that "If you write it on XA PREPARE, then another transaction may be logged between XA PREPARE and XA
COMMIT of this transaction, and binlog currently cannot handle such an interleaving".
I think that If calling xid_cache_insert(&localxid, XA_PREPARED), the xid_state->in_thd = 0.
when another transaction T2 or session S2 executes 'xa commit/rollback XXX', Mysql code is
case SQLCOM_XA_COMMIT: (Sql_parse.cpp Line 4747)
if (!thd->transaction.xid_state.xid.eq(thd->lex->xid))
{
XID_STATE *xs=xid_cache_search(thd->lex->xid);
if (!xs || xs->in_thd)
my_error(ER_XAER_NOTA, MYF(0));
else
{
ha_commit_or_rollback_by_xid(thd->lex->xid, 1);
xid_cache_delete(xs);
send_ok(thd);
}
break;
}
....
It only executes origin pending transaction, not the new transaction T2, so won't log any records about T2 in these steps.
(It's same as the pending tx handling at mysql recovery phase)
[6 Apr 2006 5:56]
Yuan WANG
Hi, we're building a simple distributed transaction processing system on top of MySQL, and we are astonished in finding such restrictions from the manual. We know that it may be hard to fix these problems, but we still hope they can be got rid in the coming future, for any demanding applications just can not live with them. So, may I ask if your dear developers have any plan for these problems?
[28 Jun 2006 18:04]
Lars Thalmann
Probably solution needs to be discussed...
[11 Jan 2007 14:05]
Guilhem Bichot
A possible solution (which does not require interleaving). The idea would be: when the client does XA START xid; statements; XA END xid; XA PREPARE xid; then we binlog the above (as a whole) (it's like binlogging an ordinary transaction except that we wrap it into XA START + XA END + XA PREPARE instead of BEGIN + COMMIT). Later when the user does XA ROLLBACK or XA COMMIT, we binlog such statement. In the binlog, we end up with: XA START xid1; statements; XA END xid1; XA PREPARE xid1; something else maybe; XA START xid2; statements; XA END xid2; XA PREPARE xid2; something else maybe; XA COMMIT xid2; XA ROLLBACK xid2; And we would make THD::cleanup() to not rollback the prepared transaction. This would fix any persistency problem *I think*. The only big problem is that currently this solution cannot work because one single thread can have only one XA PREPARE'd transaction; but this could be fixed, or if this is forbidden by the XA standard, it could be fixed only in the replication slave thread and in the mysqlbinlog|mysql thread.
[24 Oct 2007 10:47]
Lars Thalmann
Needs some refactoring, new development. Currently scheduled for version 6.1.
[6 Jan 2011 19:24]
Justin Swanhart
What is the schedule for this bug now?
[8 Feb 2011 21:04]
kris vdc
I wish to set up a table with "accounts" on databases in several geographically diverse sites. Accounts can be bundled into "super accounts" spanning these databases. The business logic transaction can do a transfer of resources from a "super account" to somewhere else. XA enables this feature, allocating a part of the resource till the requirement has been met. A disconnection with loss of a resource breaks the transaction (not ACID). Any temporary workarounds I could use - NDB has the same problem ?
[6 Apr 2011 8:02]
Marko Mäkelä
When attempting to reproduce Bug #59641, I noticed that MySQL would roll back all XA PREPAREd transactions on shutdown as well. It should keep them in the PREPARED state, so that the application can decide whether to ROLLBACK or COMMIT after restart.
[10 Apr 2012 15:02]
Andrig Miller
Can this get better priority for being fixed. It's very old, and we use MySQL a lot for our testing and certification, but can not use it for our XA transaction recovery tests since this bug. Perhaps it could get moved up in priority and fixed in a MySQL 5.5.x release?
[10 Apr 2012 17:20]
Andrei Elkin
Sorry for making you to wait. A solution is actually designed to pass Innodb and general server arch review. A patch is expected to be ready this month. cheers, Andrei
[11 Jul 2012 9:15]
Michal Warecki
Andrei, When this patch is expected to be released? You wrote that it will be released in April but now is July.
[11 Jul 2012 9:41]
Andrei Elkin
The patch indeed was readied few weeks weeks ago. But it still needs merging with the main tree where some features had been added recently to make merging rather difficult to complete. Given that and the size of the work we got to see yet where the fixes will be eventually applied to.
[17 May 2013 18:10]
Sundar Raghavan
Our recent tests on 5.6 seems to show this bug still exists. yet, there is a discussion from 2012 that says the fix is pending merging to mainline code. This issue is affecting a few of the customer experience for a few users. Could someone give the community an update on what progress has been made on this issue? Is the patch still in progress? Appreciate the help.
[24 Jul 2013 5:37]
Guangpu Feng
Hi, feng shao The *ha_semi_rollback_trans* solution can partially solver this problem, but the binlog is lost when the client disconnects. I have an idea that can keep the binlog: suspend the disconnecting *thd* for later commit/rollback. For detail, see https://bugs.launchpad.net/percona-server/+bug/1204353
[13 Dec 2014 16:34]
Daniël van Eeden
This is still reproducable on 5.7.5-m15 and 5.6.22.
[9 Apr 2015 10:09]
David Moss
Thanks for your feedback. This has been fixed with the release of MySQL 5.7.7 and the following entry is in the changelog: Replication is now compatible with XA transactions. An XA transaction in PREPARED state is now persistent in the binary log until an explicit XA COMMIT or XA ROLLBACK statement is issued. In prior versions, an XA transaction that was in PREPARED state would be rolled back on clean server shutdown or client disconnect. Similarly, an XA transaction that was in PREPARED state would still exist in PREPARED state in case the server was shutdown abnormally and then started again, but the contents of the transaction could not be written to the binary log. As part of this feature a new event, XA_prepare_log_event, has been added to track XA transactions in the PREPARED state and enable them to be replicated. To finalize a two-phase XA transaction, the XA COMMIT or XA ROLLBACK is recorded separately in the binary log, possibly interleaving with other transactions. XA transactions committed with XA COMMIT ONE PHASE are logged as one part using XA_prepare_log_event. You can also find additional information about these changes here in the 5.7 documentation: http://dev.mysql.com/doc/refman/5.7/en/xa-restrictions.html
[9 Apr 2015 10:43]
David Moss
Posted by developer: Thanks for your feedback. This has been fixed with the release of MySQL 5.7.7 and the following entry is in the changelog: Replication is now compatible with XA transactions. An XA transaction in PREPARED state is now persistent in the binary log until an explicit XA COMMIT or XA ROLLBACK statement is issued. In prior versions, an XA transaction that was in PREPARED state would be rolled back on clean server shutdown or client disconnect. Similarly, an XA transaction that was in PREPARED state would still exist in PREPARED state in case the server was shutdown abnormally and then started again, but the contents of the transaction could not be written to the binary log. As part of this feature a new event, XA_prepare_log_event, has been added to track XA transactions in the PREPARED state and enable them to be replicated. To finalize a two-phase XA transaction, the XA COMMIT or XA ROLLBACK is recorded separately in the binary log, possibly interleaving with other transactions. XA transactions committed with XA COMMIT ONE PHASE are logged as one part using XA_prepare_log_event. You can also find additional information about these changes here in the 5.7 documentation: http://dev.mysql.com/doc/refman/5.7/en/xa-restrictions.html
[14 Jul 2015 19:55]
Ant Kutschera
I had a problem with this bug, and I can verify that it is no longer a problem with 5.7 on Linux FC 21. See https://developer.jboss.org/message/935799 for the test case. Thank you!
[5 May 2016 19:06]
Christian Ferrari
LIXA transaction manager has been fixed to handle the new correct behavior of MySQL: https://github.com/tiian/lixa/commit/b640c8321151b7d1f0c41b73ac80ee328bd3a0b7 MySQL version: 5.7.12 LIXA version: 0.9.4 Linux version: Ubuntu 16.04 64 bit
