13 Replies Latest reply on Apr 25, 2007 12:34 PM by Jess Balint

    any workaround for XA END [SUSPEND] not supported in MySQL 5

    Christian Macher Newbie

      I am using JBoss 4.2.GA with two XA datasources and MySQL 5.0.x

      The documentation of MqSQL 5 says:

      For XA END the SUSPEND [FOR MIGRATE] clause is not supported


      So I get the following error when I start a JBPM process that includes a Timer:
      13:25:02,468 WARN [TxConnectionManager] Connection error occured: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@4c2683[state=NORMAL mc=org.jboss.resource.adapter.
      jdbc.xa.XAManagedConnection@9aa95c handles=0 astUse=1176899102453 permit=true trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$PoolByCri@18a8bfa context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@ec0b80 xaResource=org.jboss.resource.connectionmanager.xa.JcaXAResourceWrapper@1748ba4 txSync=null]com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_INVAL: Invalid arguments (or unsupported command)
       at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:562)


      Is there any workaround for that?

      When I use two local TX datasources I run into the problem that JBoss 4.2.GA doesn't allow multiple local TX datasource in one transaction.

      Any help would be highly appreciated!

      Chris



        • 1. Re: any workaround for XA END [SUSPEND] not supported in MyS
          Mark Little Master

          Upgrade to JBossTS 4.2.3: that allows you to have multiple one-phase participants in the same transaction. Not recommended, but your risk.

          • 2. Re: any workaround for XA END [SUSPEND] not supported in MyS
            Weston M. Price Master

            Typically you can correct behavior like this by adding the

            
            <track-connection-by-tx/>
            
            


            element to your *-ds.xml file. This disables transaction interleaving (which BTW, most DB vendors don't support). Give that a whirl and let us know.






            • 3. Re: any workaround for XA END [SUSPEND] not supported in MyS
              Weston M. Price Master

               


              Upgrade to JBossTS 4.2.3: that allows you to have multiple one-phase participants in the same transaction. Not recommended, but your risk.


              And yes, there is that approach as well :-)




              • 4. Re: any workaround for XA END [SUSPEND] not supported in MyS
                Christian Macher Newbie

                Approach 1:

                I have downloaded the jbossts-jta-4.2.3.GA.zip file and copied the following jar files into my existing JBoss-4.2.GA installation.

                - jbossjta-integration.jar
                - jbossjta.jar
                - jbossts-common.jar

                With two local tx datasources deployed I still get the error:

                Could not enlist in transaction on entering meta-aware object!;


                Do I have to change any configuration to allow multiple one-phase participants in the same transaction?

                Approach 2:

                Added

                <track-connection-by-tx/>


                to my datasources.

                Now I get the following error messages during startup:

                15:16:35,406 INFO [testQueue] Bound to JNDI name: queue/testQueue
                15:16:35,421 INFO [UILServerILService] JBossMQ UIL service available at : localhost/127.0.0.1:8093
                15:16:35,453 ERROR [STDERR] Wed Apr 18 15:16:35 BST 2007 DEBUG: Executing XA statement: XA START 0x312d2d35336566666534633a6339363a34363236323833613a3465,0x2d35336566666534633a6339363a34363236323833613a3531,0x20003
                15:16:35,453 ERROR [STDERR] Wed Apr 18 15:16:35 BST 2007 DEBUG: Executing XA statement: XA END 0x312d2d35336566666534633a6339363a34363236323833613a3465,0x2d35336566666534633a6339363a34363236323833613a3531,0x20003
                15:16:35,453 ERROR [STDERR] Wed Apr 18 15:16:35 BST 2007 DEBUG: Executing XA statement: XA COMMIT 0x312d2d35336566666534633a6339363a34363236323833613a3465,0x2d35336566666534633a6339363a34363236323833613a3531,0x20003 ONE PHASE


                After a few lines where my jbpm-enterprise ear file is deployed I get the following:

                15:16:35,781 INFO [EARDeployer] Init J2EE application: file:/C:/servers/jboss-4.2.0.CR2/server/default/deploy/jbpm-enterprise-mysql.ear
                15:16:37,937 INFO [EjbModule] Deploying CommandServiceBean
                15:16:37,968 INFO [EjbModule] Deploying TimerServiceBean
                15:16:37,984 INFO [EjbModule] Deploying CommandListenerBean
                15:16:38,000 INFO [EjbModule] Deploying JobListenerBean
                15:16:38,312 INFO [BaseLocalProxyFactory] Bound EJB LocalHome 'CommandServiceBean' to jndi 'CommandServiceBean'
                15:16:38,328 INFO [ProxyFactory] Bound EJB Home 'CommandServiceBean' to jndi 'ejb/CommandServiceBean'
                15:16:38,343 INFO [BaseLocalProxyFactory] Bound EJB LocalHome 'TimerServiceBean' to jndi 'local/TimerServiceBean@31725673'
                15:16:38,406 ERROR [STDERR] Wed Apr 18 15:16:38 BST 2007 DEBUG: Executing XA statement: XA START 0x312d2d35336566666534633a6339363a34363236323833613a3630,0x2d35336566666534633a6339363a34363236323833613a3633,0x20003
                15:16:38,406 WARN [loggerI18N] [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] TransactionImple.enlistResource - XAResource.start returned: XAException.XAER_OUTSIDE for < 131075, 27, 25, 1--53effe4c:c96:4626283a:60-53effe4c:c96:4626283a:63
                15:16:38,406 ERROR [STDERR] com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_OUTSIDE: Some work is done outside global transaction
                15:16:38,406 ERROR [STDERR] at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:562)
                15:16:38,406 ERROR [STDERR] at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:545)
                15:16:38,406 ERROR [STDERR] at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.start(MysqlXAConnection.java:485)
                15:16:38,406 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.start(XAManagedConnection.java:121)


                Do you have any idea what I am doing wrong?

                • 5. Re: any workaround for XA END [SUSPEND] not supported in MyS
                  Weston M. Price Master

                  Could you please post the contents of your *-ds.xml file(s) as well as your MySQL driver version.

                  • 6. Re: any workaround for XA END [SUSPEND] not supported in MyS
                    Weston M. Price Master

                    One more thing to try if you don't mind, could you set the following in your XA datasource *-ds.xml file:

                    <isSameRM-override-value>false</isSameRM-override-value>



                    • 7. Re: any workaround for XA END [SUSPEND] not supported in MyS
                      Christian Macher Newbie

                      MySQL driver version: mysql-connector-java-5.0.4-bin.jar

                      The datasources:

                      1:

                      <xa-datasource>
                       <jndi-name>DefaultDS</jndi-name>
                       <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                       <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/jbossdb</xa-datasource-property>
                      
                       <user-name>jboss</user-name>
                       <password>jboss</password>
                       <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                      
                       <!-- This disables transaction interleaving (which BTW, most DB vendors don't support) -->
                       <track-connection-by-tx/>
                       <isSameRM-override-value>false</isSameRM-override-value>
                      
                       <!--pooling parameters-->
                       <min-pool-size>5</min-pool-size>
                       <max-pool-size>20</max-pool-size>
                       <blocking-timeout-millis>5000</blocking-timeout-millis>
                       <idle-timeout-minutes>15</idle-timeout-minutes>
                      
                       <!-- pooling criteria. USE AT MOST ONE-->
                       <!-- If you don't use JAAS login modules or explicit login
                       getConnection(usr,pw) but rely on user/pw specified above,
                       don't specify anything here -->
                      
                       <!-- If you supply the usr/pw from a JAAS login module -->
                       <security-domain/>
                      
                       <!-- if your app supplies the usr/pw explicitly getConnection(usr, pw) -->
                       <application-managed-security/>
                       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
                       <metadata>
                       <type-mapping>mySQL</type-mapping>
                       </metadata>
                       </xa-datasource>
                      


                      2:
                      <xa-datasource>
                       <jndi-name>JbpmDS</jndi-name>
                       <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                       <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/jbpm</xa-datasource-property>
                      
                       <user-name>jbpm</user-name>
                       <password>jbpm</password>
                       <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                      
                       <!-- This disables transaction interleaving (which BTW, most DB vendors don't support) -->
                       <track-connection-by-tx/>
                       <isSameRM-override-value>false</isSameRM-override-value>
                      
                       <!--pooling parameters-->
                       <min-pool-size>5</min-pool-size>
                       <max-pool-size>20</max-pool-size>
                       <blocking-timeout-millis>5000</blocking-timeout-millis>
                       <idle-timeout-minutes>15</idle-timeout-minutes>
                      
                       <!-- pooling criteria. USE AT MOST ONE-->
                       <!-- If you don't use JAAS login modules or explicit login
                       getConnection(usr,pw) but rely on user/pw specified above,
                       don't specify anything here -->
                      
                       <!-- If you supply the usr/pw from a JAAS login module -->
                       <security-domain/>
                      
                       <!-- if your app supplies the usr/pw explicitly getConnection(usr, pw) -->
                       <application-managed-security/>
                       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
                       <metadata>
                       <type-mapping>mySQL</type-mapping>
                       </metadata>
                       </xa-datasource>


                      I also added

                      <isSameRM-override-value>false</isSameRM-override-value>


                      But get the same errors as before:

                      XAER_OUTSIDE: Some work is done outside global transaction


                      • 8. Re: any workaround for XA END [SUSPEND] not supported in MyS
                        Weston M. Price Master

                        And more to the point:

                        http://bugs.mysql.com/bug.php?id=17734

                        It' looks like MySQL needs the

                        <no-tx-separate-pools/>
                        


                        element. This basically segrates connection pools based on whether or not the connection is used in a transaction and outside of a transaction. Some DBs (most notably Oracle) have shown this issue.


                        • 9. Re: any workaround for XA END [SUSPEND] not supported in MyS
                          Mark Little Master

                          To allow multiple one-phase resources in the same transaction you need to read page 18 of the JTA programmers guide.

                          • 10. Re: any workaround for XA END [SUSPEND] not supported in MyS
                            Christian Macher Newbie

                            Finally I added also

                            <no-tx-separate-pools/>

                            to my datasources and now it seems to be working.

                            But I still get the following errors when an XA command is executed:

                            16:13:14,890 ERROR [STDERR] Wed Apr 18 16:13:14 BST 2007 DEBUG: Executing XA statement: XA START 0x312d2d35336566666534633a3538313a34363236333435653a313166,0x2d35336566666534633a3538313a34363236333435653a313234,0x20003
                            16:13:14,890 INFO [STDOUT] Hibernate:
                             /* criteria query */ select
                             this_.ID_ as ID1_20_1_,
                             this_.VERSION_ as VERSION2_20_1_,
                             this_.KEY_ as KEY3_20_1_,
                             this_.START_ as START4_20_1_,
                             this_.END_ as END5_20_1_,
                             this_.ISSUSPENDED_ as ISSUSPEN6_20_1_,
                             this_.PROCESSDEFINITION_ as PROCESSD7_20_1_,
                             this_.ROOTTOKEN_ as ROOTTOKEN8_20_1_,
                             this_.SUPERPROCESSTOKEN_ as SUPERPRO9_20_1_,
                             processdef1_.ID_ as ID1_4_0_,
                             processdef1_.NAME_ as NAME3_4_0_,
                             processdef1_.DESCRIPTION_ as DESCRIPT4_4_0_,
                             processdef1_.VERSION_ as VERSION5_4_0_,
                             processdef1_.ISTERMINATIONIMPLICIT_ as ISTERMIN6_4_0_,
                             processdef1_.STARTSTATE_ as STARTSTATE7_4_0_
                             from
                             JBPM_PROCESSINSTANCE this_
                             inner join
                             JBPM_PROCESSDEFINITION processdef1_
                             on this_.PROCESSDEFINITION_=processdef1_.ID_
                             where
                             this_.END_ is null limit ?
                            16:13:14,921 ERROR [STDERR] Wed Apr 18 16:13:14 BST 2007 DEBUG: Executing XA statement: XA END 0x312d2d35336566666534633a3538313a34363236333435653a313166,0x2d35336566666534633a3538313a34363236333435653a313234,0x20003
                            16:13:14,921 ERROR [STDERR] Wed Apr 18 16:13:14 BST 2007 DEBUG: Executing XA statement: XA COMMIT 0x312d2d35336566666534633a3538313a34363236333435653a313166,0x2d35336566666534633a3538313a34363236333435653a313234,0x20003 ONE PHASE


                            Should I be concerned about that?


                            • 11. Re: any workaround for XA END [SUSPEND] not supported in MyS
                              Weston M. Price Master

                              As near as I can tell from the messages they are simply at DEBUG level. I am not sure why this is going to STDERR (or being flagged as such). The lack of an explicit exception would lead me to believe that this is simply some sort of trace mechanism in the MySQL driver code that, hopefully, can be turned off or at the very least be turned down.

                              • 12. Re: any workaround for XA END [SUSPEND] not supported in MyS
                                Christian Macher Newbie

                                Great. Thanks a lot for your help!

                                Cheers
                                Chris

                                • 13. Re: any workaround for XA END [SUSPEND] not supported in MyS
                                  Jess Balint Newbie

                                  If you upgrade to the latest JDBC driver (5.0.5 *1), this won't be printed directly to STDERR anymore. There is a connection property called logXaCommands *2 that will send them as DEBUG messages.

                                  Jess

                                  *1 Connector/J 5.0.5 Release Notes
                                  http://dev.mysql.com/doc/refman/5.0/en/cj-news-5-0-5.html
                                  *2 Connector/J Connection Properties
                                  http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html