MySQL Bugs: #17734: XA-datasource

Bug #17734	XA-datasource
Submitted:	27 Feb 2006 3:52	Modified:	31 Mar 2014 11:21
Reporter:	steve ni	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.0-beta	OS:	Windows (WinXP)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
I setup XA-datasource in JBOSS. MySQL is 5.0.18, connector/J is 5.0.0.beta. 
Because jboss always pool database into default ManagedConnectionPool. My program can work normally if connection is new. But it always throws XAER_OUTSIDE exception after some connections are cached in pool. Once I flush the pool (clear all connection from pool), my program works again (and fail again after several run)

I assume the reason is connection does not release some information correctly in last transction when it has been put into pool. So when program get it back from pool, it get conflict with current transction. 

I also set up track-connection-by-tx as true. It does not work as well. 

I use hibernate and spring.

How to repeat:
My MySQL-xa-datasource.xml excerpt:

  <xa-datasource>
    <jndi-name>jdbc/lams-ds</jndi-name>
	<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
	<xa-datasource-property name="URL">jdbc:mysql://localhost:3306/mysource?characterEncoding=utf8&amp;zeroDateTimeBehavior=convertToNull</xa-datasource-property>
	<user-name>root</user-name>
	<password></password>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>		
    <max-pool-size>5</max-pool-size>
    <min-pool-size>0</min-pool-size>
    
	<blocking-timeout-millis>2000</blocking-timeout-millis>
    <idle-timeout-minutes>2</idle-timeout-minutes>
	<track-connection-by-tx>true</track-connection-by-tx>
	<no-tx-separate-pools>false</no-tx-separate-pools> 
	
	<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
	<metadata>
	<type-mapping>mySQL</type-mapping>
	</metadata> 	
	
  </xa-datasource>

Two things to try. First, please try the latest nightly snapshot build of 5.0 from http://downloads.mysql.com/snapshots.php#connector-j as it has some fixes for local transactions.

Also make sure you've added <track-connection-by-tx/> to your <xa-datasource> configuration.

I downloaded latest connector:mysql-connector-java-5.0-nightly-20060227-bin.jar. I added  <track-connection-by-tx/> as well. But my programs get same exception.

I have similar problem with XAER_OUTSIDE in JBOSS with latest JConnector.

Look at this session:

SET NAMES cp1251;
SET character_set_results = NULL;
SHOW VARIABLES;
SHOW COLLATION;
SET autocommit=1;
SET sql_mode='STRICT_TRANS_TABLES';
SET autocommit=0;
SHOW VARIABLES LIKE 'tx_isolation';

>>> We get connection from pool
>>> in EJB method with TransactionAttributeType.NOT_SUPPORTED

SELECT 'Some sql statement';

>>> We put connection to pool

>>> We get connection from pool
>>> in EJB method with TransactionAttributeType.REQUIRES_NEW

XA START 0x6f64696e2f3338,0x31,0x101; -> XAER_OUTSIDE ERROR

...

Documentation say: “Within the context of a given client connection, XA transactions and local (non-XA) transactions are mutually exclusive. For example, if XA START has been issued to begin an XA transaction, a local transaction cannot be started until the XA transaction has been committed or rolled back. Conversely, if a local transaction has been started with START TRANSACTION, no XA statements can be used until the transaction has been committed or rolled back.”

I can fix situation by hands. For example:

SET NAMES cp1251;
SET character_set_results = NULL;
SHOW VARIABLES;
SHOW COLLATION;
SET autocommit=1;
SET sql_mode='STRICT_TRANS_TABLES';
SET autocommit=0;
SHOW VARIABLES LIKE 'tx_isolation';
SELECT 'Some sql statement';
COMMIT; <- Commit implicit transaction
XA START 0x6f64696e2f3338,0x31,0x101; <- OK
XA END 0x6f64696e2f3338,0x31,0x101; <- OK
XA COMMIT 0x6f64696e2f3338,0x31,0x101 ONE PHASE; <- OK

...

But what can I do under Jboss?

XAER_OUTSIDE exception has been eliminated in my case by "no-tx-separate-pools" addition to -ds.xml (see JBoss documentation)

Hi Victor,

I'm facing a similar issue, but in my case I think the error is been caused by the way Spring is configured, i.e. it is not properly starting the XA transaction.

So, I have a question for you: how do you set mysql (or Connector/J) to log the session?

TIA,

-- Felipe

I use MySQL log (file or general_log table in 5.1.11) filtered by session ID.

Please, try to repeat with a newer version of Connector/J, 5.0.4, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

In jboss4.0+, you need these three parameters to avoid the annoying XAER_OUTSIDE.

	<track-connection-by-tx>true</track-connection-by-tx>
	<new-connection-sql>set autocommit=1</new-connection-sql>
	<no-tx-separate-pools>true</no-tx-separate-pools>

By default, MySQL automatically sets autocommit to 0 when you create a new connection through MySQLXADataSource, new-connection-sql fixes it.

If no-tx-separate-pools exists, connections not involved in any global transaction will be kept in a separate pool, thus XAER_OUTSIDE will disappear.

Good luck!

I close this report as "Can't repeat" because a workaround was provided, there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.