MySQL commit called

[ihunter]

Hi Folks,
I've got a problem getting pooled connections cleaned up properly with MySQL. Given the config at the end of this email, I call code like...

InitialContext naming = new InitialContext();
Object pool = naming.lookup("java:/MinervaDS");
DataSource dataSource = (DataSource)pool;
dbConnection = dataSource.getConnection();

and then do dbConnection.close() as required. However, when the cleanup runs I get:-

[INFO,MinervaDS] javax.resource.ResourceException:
Unable to rollback DB connection: java.sql.SQLException:
Can't call commit when autocommit=true

I'm using BMT, tried setting autocommit false in config and code.

Have I missed something, or is something wrong here.

Many Thanks
Ian Hunter




==================================================
<?xml version="1.0" encoding="UTF-8"?>
<!-- This is where you can add and configure your MBeans
ATTENTION: The order of the listing here is the same order as
the MBeans are loaded. Therefore if a MBean depends on another
MBean to be loaded and started it has to be listed after all
the MBeans it depends on.
-->


<!-- ==================================================================== -->
<!-- Classloading -->
<!-- ==================================================================== -->

8083
<!-- Should resources and non-EJB classes be downloadable -->
true


<!-- ==================================================================== -->
<!-- JNDI -->
<!-- ==================================================================== -->

1099




<!-- ==================================================================== -->
<!-- Transactions -->
<!-- ==================================================================== -->

300

<!-- Use this attribute if you need to use a specific Xid
implementation
oracle.jdbc.xa.OracleXid
-->


<!-- Uncomment to use Tyrex (tyrex.exolab.org) transaction manager plugin
instead of the org.jboss.tm.TransactionManagerService and comment out
the TransactionManagerService above

domain.xml

-->





<!-- ==================================================================== -->
<!-- Security -->
<!-- ==================================================================== -->

<!-- JAAS security manager and realm mapping -->

org.jboss.security.plugins.JaasSecurityManager



<!-- ==================================================================== -->
<!-- JDBC -->
<!-- ==================================================================== -->


org.gjt.mm.mysql.Driver





org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl


TW
jdbc:mysql://@DB_SERVER@/tw

tw
[pword]

<!--
0
20
20
20

900000
true
900000
true
-->


BlockingTimeoutMillis=500000
IdelTimeoutEnabled=true
IdleTimeoutMinutes=30
MaxSize=20
CleanupIntervalMinutes=10
MinSize=0
MaxIdleTimeoutPercent=1.0
GCEnabled=true
GCIntervalMillis=60000
GCMinIdleTime=10000
AutoCommit=false




<!-- ==================================================================== -->
<!-- JBoss Server Management -->
<!-- ==================================================================== -->

5000


<!-- ==================================================================== -->
<!-- J2EE deployment -->
<!-- ==================================================================== -->


true
false
false
true
false


<!-- Uncomment to add embedded tomcat service

-->

<!-- Uncomment and set file URL to add Jetty service (you can set config more than once)

file URL to jetty.xml e.g. file:/usr/local/jboss/dist/conf/default/jetty.xml

-->


<!-- ==================================================================== -->
<!-- JBossMQ -->
<!-- ==================================================================== -->


<!-- The StateManager is used to keep JMS perisitent state data. -->
<!-- For example: what durable subscriptions are active. -->

jbossmq-state.xml


<!-- The PersistenceManager is used to store messages to disk. -->

../../db/jbossmq/


<!-- InvocationLayers are the different transport methods that can be used to access the server -->

java:/ConnectionFactory
java:/XAConnectionFactory



RMIConnectionFactory
RMIXAConnectionFactory



ConnectionFactory
XAConnectionFactory



UILConnectionFactory
UILXAConnectionFactory


<!-- The following three line create 3 topics named: testTopic, example, and bob -->




<!-- The following 9 line create 9 topics named: testQueue, controlQueue, A, B, -->
<!-- C, D, E, F, and ex -->











<!-- Used for backwards compatability with JBossMQ versions before 1.0.0 -->

ConnectionFactory
QueueConnectionFactory


ConnectionFactory
TopicConnectionFactory


<!-- For Message Driven Beans -->

DefaultJMSProvider
org.jboss.jms.jndi.JBossMQProvider
java:/XAConnectionFactory
java:/XAConnectionFactory


StdJMSPool
org.jboss.jms.asf.StdServerSessionPoolFactory



<!-- Make sure you change EmbeddedTomcat to Jetty if you are using Jetty -->

Default
:service=ContainerFactory
:service=Jetty


<!-- Uncomment this and disable previous J2eeDeployer entry to enable an alternative "scoped" deployment

Default
:service=ContainerFactory
:service=Jetty

-->

<!-- ==================================================================== -->
<!-- JBossCX setup, for J2EE connector architecture support -->
<!-- ==================================================================== -->




<!-- Minerva no transaction connection manager factory.
Use this for resource adapters that don't support
transactions.
-->

MinervaNoTransCMFactory

org.jboss.pool.connector.jboss.MinervaNoTransCMFactory




<!-- Minerva local transaction connection manager factory.

Use this for resource adapters that support "local"
transactions. -->

MinervaSharedLocalCMFactory

org.jboss.pool.connector.jboss.MinervaSharedLocalCMFactory




<!-- Minerva XA transaction connection manager factory

Use this for resource adapters that support "xa"
transactions. -->

MinervaXACMFactory

org.jboss.pool.connector.jboss.MinervaXACMFactory




<!--
Connection factory for the Minerva JDBC resource adapter. This
points at the same database as DefaultDS.
-->


MinervaDS
JCA:service=RARDeployer

Minerva JDBC LocalTransaction ResourceAdapter


ConnectionURL=jdbc:mysql://@DB_SERVER@/teamwarrior



MinervaSharedLocalCMFactory

<!-- See the documentation for the specific connection manager
implementation you are using for the properties you can set -->

# Pool type - uncomment to force, otherwise it is the default
#PoolConfiguration=per-factory

# Connection pooling properties - see
# org.jboss.pool.PoolParameters
MinSize=0
MaxSize=10
Blocking=true
GCEnabled=false
IdleTimeoutEnabled=false
InvalidateOnError=false
TrackLastUsed=false
GCIntervalMillis=120000
GCMinIdleMillis=1200000
IdleTimeoutMillis=1800000
MaxIdleTimeoutPercent=1.0
AutoCommit=false


<!-- Principal mapping configuration -->

org.jboss.resource.security.ManyToOnePrincipalMapping


userName=twarrior
password=twarri0r



<!-- This is an example of using a resource adapter that supports XA
transactions. The Minerva XA resource adapter requires an
XADataSource to be in JNDI somewhere. JBoss doesn't include a
database with an XA-compliant JDBC driver, so this will need to
be configured to use whatever XADataSource implementation you
have.


MinervaXADS

Put your XADataSource implementation class here





XAMinervaDS
JCA:service=RARDeployer

Minerva JDBA XA Resource Adapter


XADataSourceName=java:/MinervaXADS



MinervaXACMFactory


# Pool type - uncomment to force, otherwise it is the default
#PoolConfiguration=per-factory

# Connection pooling properties - see
# org.jboss.pool.PoolParameters
MinSize=0
MaxSize=10
Blocking=true
GCEnabled=false
IdleTimeoutEnabled=false
InvalidateOnError=false
TrackLastUsed=false
GCIntervalMillis=120000
GCMinIdleMillis=1200000
IdleTimeoutMillis=1800000
MaxIdleTimeoutPercent=1.0



org.jboss.resource.security.ManyToOnePrincipalMapping


userName=sa
password=


-->

<!-- JMS XA Resource adapter, use this to get transacted JMS in beans -->

JmsXA
JCA:service=RARDeployer
JMS Adapter
MinervaXACMFactory
<!-- See the documentation for the specific connection manager
implementation you are using for the properties you can set -->

# Pool type - uncomment to force, otherwise it is the default
#PoolConfiguration=per-factory

# Connection pooling properties - see
# org.jboss.pool.PoolParameters
MinSize=0
MaxSize=10
Blocking=true
GCEnabled=false
IdleTimeoutEnabled=false
InvalidateOnError=false
TrackLastUsed=false
GCIntervalMillis=120000
GCMinIdleMillis=1200000
IdleTimeoutMillis=1800000
MaxIdleTimeoutPercent=1.0


<!-- Principal mapping configuration -->
org.jboss.resource.security.ManyToOnePrincipalMapping




<!-- ==================================================================== -->
<!-- Auto deployment -->
<!-- ==================================================================== -->


J2EE:service=J2eeDeployer;
JCA:service=RARDeployer

../deploy,../deploy/lib


<!-- ==================================================================== -->
<!-- JMX adaptors -->
<!-- ==================================================================== -->






10

8082


<!-- ==================================================================== -->
<!-- Mail Connection Factory -->
<!-- ==================================================================== -->

Mail
mail.properties
twarrior
twarri0r


<!-- ==================================================================== -->
<!-- Uncomment to enable JMX monitoring of the bean cache

-->
<!-- ==================================================================== -->

<!-- ==================================================================== -->
<!-- Scheduler Service -->
<!-- ==================================================================== -->
<!-- Uncomment this to enable Scheduling - ->











<!- - -->

<!-- ==================================================================== -->
<!-- Add your custom MBeans here -->
<!-- ==================================================================== -->


../../jetty
file:../conf/jetty/jetty.xml
../../jetty/etc/webdefault.xml
true
true



<!-- Commented out until we have a more stable Traffic system -->

[ihunter]

Sorry, forgot to mention system is JBoss2.4.3 with Jetty!

[davidjencks]

The simplest solutions are:

1. use XADataSourceLoader rather than the jca implementation in 2.4.x

2. switch to firebird database with the jca-jdbc driver

3. switch to jboss 3 rc2 coming out in a few hours.

A more difficult solution is to backport the local jca-jdbc wrapper from jboss 3 cvs or rc2.

I thought I fixed this problem with autocommit at some time before 3.0 started, so you might try 2.4.4 or 2.4.5 also.

[pylet]

I'm the developer for the MySQL driver. I'd like to understand what's going on here, in case it's a problem with the driver.

If the driver throws the exception claiming that you can't call commit() while autoCommit=true, then the driver thinks you haven't called setAutoCommit(false) and are trying to demarcate transactions (which isn't allowed by the JDBC spec, and was actually tested for in the JDBC-1.1 testsuite, which is why this check is in there).

My question is it the JDBC driver that's broken, or the JCA implementation in 2.4.x that's broken?

[ihunter]

David,

Thanks for the reply.

> 1. use XADataSourceLoader rather than the jca implementation in 2.4.x

Done. all OK


> 3. switch to jboss 3 rc2 coming out in a few hours.

We're just about to launch, so I think I'll have to wait before moving to V3.

> I thought I fixed this problem with autocommit at some time before 3.0 started, so you might try 2.4.4 or 2.4.5 also.
My mistake - this *was* 2.4.4. Anyhow I've got it working and hope to hope to v3 ASAP.

Again, Thanks
Ian

[davidjencks]

I checked the cvs log. It looks like no 2.4 versions have autocommit set false. Only the 3.0 and 3.1 jca local wrappers do anything with autocommit, and only the new Local... wrapper does anything properly (autocommit outside an explicitly started transaction).

I ran most of the 3.0 testsuite against mysql a few weeks ago and didn't notice and major problems so I think the mysql driver is working ok.