5 Replies Latest reply on May 2, 2002 11:21 AM by David Jencks

    MySQL commit called

    Ian Hunter Newbie

      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 -->




        • 1. Re: MySQL commit called
          Ian Hunter Newbie

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

          • 2. Re: MySQL commit called
            David Jencks Master

            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.

            • 3. Re: MySQL commit called
              Mark Matthews Newbie

              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?

              • 4. Re: MySQL commit called
                Ian Hunter Newbie

                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



                • 5. Re: MySQL commit called
                  David Jencks Master

                  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.