Howto set useCursorFetch=true in XA datasource

[cjohn001]

Hello together,

following the description here MySQL Translators · GitBook I am trying to set the useCursorFetch property on a XA datasource. Unfortunately I have not found a description yet how to do this. I solved the topic for an non-XA datasource as follows:

  $JBOSS_CLI --connect --command="data-source add \

    --name=${DB_NAME} \

    --jndi-name=java:/${DB_NAME} \

    --user-name=${DB_USER} \

    --password=${DB_PASS} \

    --driver-name=mysql \

    --connection-url=jdbc:mysql://${DB_URI}/${DB_NAME}?useCursorFetch=true \

    --use-ccm=false \

    --max-pool-size=25 \

    --blocking-timeout-wait-millis=5000 \

    --statistics-enabled=true \

    --enabled=true"

As far as I have understood, the comand to create a xa datasource should look as follows:

$JBOSS_CLI --connect --command="xa-data-source add \

    --name=${DB_NAME} \

    --jndi-name=java:/${DB_NAME} \

    --user-name=${DB_USER} \

    --password=${DB_PASS} \

    --driver-name=mysql \

    --valid-connection-checker-class-name=com.mysql.cj.jdbc.integration.jboss.MysqlValidConnectionChecker \

    --exception-sorter-class-name=com.mysql.cj.jdbc.integration.jboss.ExtendedMysqlExceptionSorter \

    --xa-datasource-properties=[ServerName=db, PortNumber=3306, DatabaseName=${DB_NAME}]\

    --use-ccm=false \

    --statistics-enabled=true \

    --enabled=true"

I assume I have to add useCursorFetch=true somehow to  -xa-datasource-properties, Unfortunately I have not found a description how to correctly do this. Would be great if someone could provide help. Thanks!

Best regards,

Christoph

[rareddy]

On connection url you can set as

"jdbc:mysql://localhost/?useCursorFetch=true"

[cjohn001]

Hello Ramesh,

well, I set it via connection url in the non-xa case. In the case of XA I have not found a way to set the connection url directly, but rather set

--xa-datasource-properties=[ServerName=db, PortNumber=3306, DatabaseName=${DB_NAME}]

Unfortunately, I have not found a list of the property attributes yet for xa-datasource-properties, but just configuration examples which do not show how to set the connection url. Can you tell me how the attribute has to be set?

[rareddy]

For the list properties you would need to see the respective JDBC DataSource source file or their documentation. Looking at MySQL datasource file, to above "xa-datasource-properties" you can add "UseCursorFetch=true" to set the property.

[cjohn001]

Hello Ramesh,

thanks. Looks like the attribute is accepted. However, I have seen that I got an exception during boot which I believe might also be responsible for my problems when calling the virtual teiid procedures. When calling the virtual procedure I also see  XAException.XAER_RMERR. I just double checked, the error exists if I use the xa source with and without UseCursorFetch.

The log says:

2019-04-21 21:24:48,862 WARN  [com.arjuna.ats.jta] (Periodic Recovery)  ARJUNA016027: Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: com.mysql.cj.jdbc

.MysqlXAException: XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency

  at com.mysql@8.0.13//com.mysql.cj.jdbc.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:344)

  at com.mysql@8.0.13//com.mysql.cj.jdbc.MysqlXAConnection.recover(MysqlXAConnection.java:189)

  at com.mysql@8.0.13//com.mysql.cj.jdbc.MysqlXAConnection.recover(MysqlXAConnection.java:117)

  at [e-mail]//org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.recover(XAManagedConnection.java:373)

  at [e-mail]//org.jboss.jca.core.tx.jbossts.XAResourceWrapperImpl.recover(XAResourceWrapperImpl.java:185)

  at [e-mail]//org.jboss.jca.core.tx.jbossts.XAResourceWrapperStatImpl.recover(XAResourceWrapperStatImpl.java:144)

  at org.jboss.jts//com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecoveryFirstPass(XARecoveryModule.java:609)

  at org.jboss.jts//com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:201)

  at org.jboss.jts//com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:146)

  at org.jboss.jts//com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:765)

  at org.jboss.jts//com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:377)

Caused by: java.sql.SQLException: XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency

  at com.mysql@8.0.13//com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

  at com.mysql@8.0.13//com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

  at com.mysql@8.0.13//com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

  at com.mysql@8.0.13//com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1218)

My config is:

                <xa-datasource jndi-name="java:/my_nutri_diary" pool-name="my_nutri_diary" enabled="true" use-ccm="false" statistics-enabled="true">

                    <xa-datasource-property name="ServerName">

                        db

                    </xa-datasource-property>

                    <xa-datasource-property name="PortNumber">

                        3306

                    </xa-datasource-property>

                    <xa-datasource-property name="DatabaseName">

                        my_nutri_diary

                    </xa-datasource-property>

                    <driver>mysql</driver>

                    <security>

                        <user-name>user</user-name>

                        <password>passwd</password>

                    </security>

                </xa-datasource>

                <drivers>

                    <driver name="h2" module="com.h2database.h2">

                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                    </driver>

                    <driver name="teiid-local" module="org.jboss.teiid">

                        <driver-class>org.teiid.jdbc.TeiidDriver</driver-class>

                        <xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>

                    </driver>

                    <driver name="teiid" module="org.jboss.teiid.client">

                        <driver-class>org.teiid.jdbc.TeiidDriver</driver-class>

                        <xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>

                    </driver>

                    <driver name="mysql" module="com.mysql">

                        <xa-datasource-class>com.mysql.cj.jdbc.MysqlXADataSource</xa-datasource-class>

                    </driver>

                </drivers>

Do you have an idea what is going wrong here? Interestingly I can access the database from my app. So the exception seems not to block teiid entirely.

I will try to switch back to the jdbc source now. Maybe the problems with my virtual procedure calls derive from a wrong source configuration rather than a wrong function in ddl file

[shawkins]

> Do you have an idea what is going wrong here? Interestingly I can access the database from my app. So the exception seems not to block teiid entirely.

This is an XA recovery related exception, not anything that is specific to Teiid.  See the exception root "at org.jboss.jts//com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:377)"

Recovery is for when things crash in the middle of an XA transaction - Chapter 13. Datasource Management - Red Hat Customer Portal

This seems to be a known issue: [PS-4800] Recovery of prepared XA transactions seems broken in 8.0 - Percona JIRA

You can ignore this for now if you wish.

[cjohn001]

Hello Steven,

in the meantime I switched back to the non-xa configuration. But thanks, then nothing is wrong with my configuration, good to know I am wondering what would be your recommandation for my Teiid setup. I have just a single database instance below Teiid. The XA stuff seems to be just required if I have multiple shared databases running below Teiid. Is there anything regarding transaction support which I would loose in case of a non-xa data source setup ?

[shawkins]

> I have just a single database instance below Teiid. The XA stuff seems to be just required if I have multiple shared databases running below Teiid. Is there anything regarding transaction support which I would loose in case of a non-xa data source setup ?

For just a single database it's better to just use a local transaction datasource, rather than an XA one.  See 13.4. Last Resource Commit Optimization (LRCO) - Red Hat Customer Portal  - the WildFly transaction manager will correctly handle up to 1 local transaction resource in an XA transaction.