Atomikos Forum

Hi,

Note: This is true only for MySQL Database.

I had a couple of posting on this forum stating that we see the following error with the usage of mysql database.

XAER_RMFAIL: The command cannot be executed when global transaction is in the  NON-EXISTING state

I narrow down the problem to the closing of connection (which returns the connection to the pool) was causing the issue..As long as you use the same connection for a given database during the entire transaction you won't see this error. In our case the error occurs intermittent. We have our own persistence framework which returns the connection back to the pool and when xa prepare uses a different connection it resulted in rollback. Here is a step thru ..

Start transaction
XA START (DB1 using db_connection1)
XA END
Here we close the connection
...some other business logic code ...
end transaction by calling commit
XA PREPARE
Note : If the above prepare use the db_connection1 it will succeed with
XA COMMIT 
else it will be
XA ROLLBACK

This is not a problem with atomikos, but rather handling of transaction by mysql. After looking in to their implementation i found mysql provides a globalvariable (pinGlobalTxToPhysicalConnection) and once you set that variable to true, it works without any issues.

Let me know if you need more information.

Hi,

Thanks for the feedback!

Would you be able to verify which of the two known problems is affected/solved at http://www.atomikos.com/Documentation/KnownProblems#MySQL_XA_bug ?

Thanks

This seems somewhat related to the bug, you had posted on atomikos webiste..but setting "com.atomikos.icatch.serial_jta_transactions" doesnt help.

Here is the mysql xa posting from atomikos site:
**********
This problem only happens if you access the same MySQL database more than once in the same transaction. A workaround can be setting the following property in jta.properties:

com.atomikos.icatch.serial_jta_transactions=false
*********

Following is my JUnit test case ...
I have two local mysql database ..with one table on each with a single text column...

Try running the test case by commenting out the following line in the code:

xads1.setPinGlobalTxToPhysicalConnection(true);
...
xads2.setPinGlobalTxToPhysicalConnection(true);

The testcase will fail...try running by uncommenting it and it will succeed..on the console you can see the mysql xa commands...

JUnit Testcase:

package net.dds.xa;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
import javax.transaction.*;
import junit.framework.TestCase;

import com.atomikos.jdbc.AtomikosDataSourceBean;
import com.atomikos.icatch.config.UserTransactionService;
import com.atomikos.icatch.config.UserTransactionServiceImp;

import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;

public class testMysqlAtomikosDatasourceBean extends TestCase {
    
    private AtomikosDataSourceBean atomikosDS1, atomikosDS2 = null;
    private UserTransactionService uts = null;
    private TransactionManager atomikosTM = null;
    
    protected void setUp() throws Exception{
        // Create MysqlXADatasource for database1
        MysqlXADataSource xads1 = new MysqlXADataSource();
        xads1.setUrl("JDBC:mysql://localhost/first");
        xads1.setUser("test");
        xads1.setPassword("test");
        xads1.setLogXaCommands(true);
        xads1.setPinGlobalTxToPhysicalConnection(true);
        
        //Create MysqlXADatasource for database2
        MysqlXADataSource xads2 = new MysqlXADataSource();
        xads2.setUrl("JDBC:mysql://localhost/second");
        xads2.setUser("test");
        xads2.setPassword("test");
        xads2.setLogXaCommands(true);
        xads2.setPinGlobalTxToPhysicalConnection(true);
        
        //Create AtomikosDatasourceBean for Datasource1
        atomikosDS1 = new AtomikosDataSourceBean();
        atomikosDS1.setXaDataSource(xads1);
        atomikosDS1.setUniqueResourceName("atomikos_db_first");
        atomikosDS1.setMinPoolSize(5);
        atomikosDS1.setMaxPoolSize(20);
        
        //Create AtomikosDatasourceBean for Datasource2
        atomikosDS2 = new AtomikosDataSourceBean();
        atomikosDS2.setXaDataSource(xads2);
        atomikosDS2.setUniqueResourceName("atomikos_db_second");
        atomikosDS2.setMinPoolSize(5);
        atomikosDS2.setMaxPoolSize(20);
        
        //Start Atomikos Transaction service
        uts = new UserTransactionServiceImp();
        
        Properties props = new Properties();
        props.put("com.atomikos.icatch.tm_unique_name", "AtomikosJunitTest");
        props.put("com.atomikos.icatch.console_log_level", "DEBUG");
        props.put("com.atomikos.icatch.serial_jta_transactions", "false");
        uts.init(props);
        
        atomikosTM = new com.atomikos.icatch.jta.UserTransactionManager();
    }
    
    protected void tearDown(){
        atomikosDS1.close();
        atomikosDS2.close();
        uts.shutdown(false);
    }
    
    public void testMysqlXAUsingDiffConnection() throws Exception{
        
        // Create a sql for testing
        String sqlQuery = "INSERT INTO MESSAGE(COMMAND) VALUES('JunitTestSqlQuery')";
        
        // Start Transaction
        atomikosTM.begin();
        
        // execute sql query on datasource1
        Connection ds1Connection = atomikosDS1.getConnection();
        PreparedStatement prepStatementDS1 = ds1Connection.prepareStatement(sqlQuery);
        prepStatementDS1.execute();
        prepStatementDS1.close();
        ds1Connection.close();
        
        // execute sql query on datasource2
        Connection ds2Connection = atomikosDS2.getConnection();
        PreparedStatement prepStatementDS2 = ds2Connection.prepareStatement(sqlQuery);
        prepStatementDS2.execute();
        prepStatementDS2.close();
        ds2Connection.close();
        
        atomikosTM.getTransaction().commit();
        
    }

}

Let me know your inputs.