Atomikos Forum

We are using Atomikos 3.6.5 for XA. It works fine with MySQL 5.1.x in our product. But when we upgraded MySQL to 5.5.13 or the latest 5.5.15, we started to see this wired exception in log file:

2011-08-17 08:41:27,852  WARN [main] (Slf4jConsole.java:82) - atomikos
connection pool 'A0328225B650C0313FE638003C79C26A': error while trying
to recycle
com.atomikos.datasource.pool.CreateConnectionException: Error
executing testQuery
      at com.atomikos.jdbc.AtomikosXAPooledConnection.testUnderlyingConnection(AtomikosXAPooledConnection.java:100)
      at com.atomikos.datasource.pool.AbstractXPooledConnection.createConnectionProxy(AbstractXPooledConnection.java:43)
      at com.atomikos.datasource.pool.ConnectionPool.recycleConnectionIfPossible(ConnectionPool.java:78)
      at com.atomikos.datasource.pool.ConnectionPool.borrowConnection(ConnectionPool.java:107)
      at com.atomikos.jdbc.AbstractDataSourceBean.getConnection(AbstractDataSourceBean.java:289)
      at com.atomikos.jdbc.AbstractDataSourceBean.getConnection(AbstractDataSourceBean.java:341)
      at org.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceConnectionProvider.java:92)
      at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
      at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
      at org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:74)
      at $Proxy47.prepareStatement(Unknown Source)
      at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
      at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
      at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
      at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1577)
      at org.hibernate.loader.Loader.doQuery(Loader.java:696)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
      at org.hibernate.loader.Loader.doList(Loader.java:2232)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
      at org.hibernate.loader.Loader.list(Loader.java:2124)
      at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
      at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
      at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
      at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
      at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
      at com.resolve.persistence.util.ResolveInterceptor.onSave(ResolveInterceptor.java:657)
      at org.hibernate.event.def.AbstractSaveEventListener.substituteValuesIfNecessary(AbstractSaveEventListener.java:417)
      at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:293)
      at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:204)
      at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:144)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210)
      at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
      at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50)
      at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
      at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:563)
      at org.hibernate.impl.SessionImpl.save(SessionImpl.java:551)
      at org.hibernate.impl.SessionImpl.save(SessionImpl.java:547)
      at com.resolve.persistence.util.HibernateUtil.getNextSequence(HibernateUtil.java:949)
      at com.resolve.util.SequenceUtils.<clinit>(SequenceUtils.java:21)
      at com.resolve.rsview.main.Main.initSequenceNumbers(Main.java:878)
      at com.resolve.rsview.main.Main.init(Main.java:169)
      at com.resolve.rsview.main.Main.init(Main.java:88)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1529)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1468)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1398)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:512)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:450)
      at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:290)
      at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
      at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:287)
      at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:189)
      at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:557)
      at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:842)
      at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:416)
      at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:261)
      at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:192)
      at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
      at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4205)
      at org.apache.catalina.core.StandardContext.start(StandardContext.java:4704)
      at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:799)
      at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:779)
      at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:601)
      at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:675)
      at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.java:601)
      at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:502)
      at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1315)
      at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:324)
      at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:142)
      at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1061)
      at org.apache.catalina.core.StandardHost.start(StandardHost.java:840)
      at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
      at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:463)
      at org.apache.catalina.core.StandardService.start(StandardService.java:525)
      at org.apache.catalina.core.StandardServer.start(StandardServer.java:754)
      at org.apache.catalina.startup.Catalina.start(Catalina.java:595)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
      at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
Caused by: java.sql.SQLException: XAER_RMFAIL: The command cannot be
executed when global transaction is in the  IDLE state
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
      at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1465)
      at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeQuery(StatementWrapper.java:746)
      at com.atomikos.jdbc.AtomikosXAPooledConnection.testUnderlyingConnection(AtomikosXAPooledConnection.java:96)
      ... 85 more


If switched back to MySQL 5.1.58, the exception disappeared.
Did anybody see this problem before? It seems the problem happened when Atomikos connection pool was trying to execute the testing query. Not sure if this is a problem of Atomikos or MySQL.

Does anybody know any workaround for this problem? We already set the Atomikos properties described at http://www.atomikos.com/Documentation/KnownProblems#MySQL_XA_bug


Thanks a lot for help,
Yu

Seems like MySQL 5.5 does not accept SQL outside of XA transaction scopes (which is what the testQuery does). You might want to check in the MySQL forums?

HTH

Can you be more specific about "MySQL 5.5 does not accept SQL outside of XA transaction scopes "?  It seems normal JDBC SQLs work fine with MySQL 5.5 (no XA, with or without transaction) in our test programs.

Has Atomikos been tested with MySQL 5.5 or not?

Thanks

We (Atomikos) did not test with MySQL 5.5 - not sure if anybody else did.