XA Datasource for Application Data Mysql


#1

Hi,
I use Wildfly 11. I want to store my application data in a mysql database.I read that I should use XA datasources if I want to use more than one datasource. So I tried to add a XA Datasource via the Wildfly Manager, but I always get this error when I test the connection:

Unexpected HTTP response: 500

Request
{
    "address" => [
        ("subsystem" => "datasources"),
        ("xa-data-source" => "ProcessDBXADS")
    ],
    "operation" => "test-connection-in-pool"
}

Response

Internal Server Error
{
    "outcome" => "failed",
    "failure-description" => "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid",
    "rolled-back" => true
}

What am I doing wrong? The database exists, user and password are correct.
standalone.xml

  <xa-datasource jndi-name="java:/ProcessDBXADS" pool-name="ProcessDBXADS" enabled="true" use-ccm="false">
                    <xa-datasource-property name="ServerName">
                        localhost
                    </xa-datasource-property>
                    <xa-datasource-property name="DatabaseName">
                        testprocessdb2
                    </xa-datasource-property>
                    <driver>mysql-connector-java-5.1.47-bin.jar_com.mysql.jdbc.Driver_5_1</driver>
                    <security>
                        <user-name>xxxx</user-name>
                        <password>xxxx</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                        <background-validation>true</background-validation>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                    </validation>
                </xa-datasource>

Before I tried it with an Non-XA Datasource and it works:

 <datasource jta="true" jndi-name="java:/ProcessDBDS" pool-name="ProcessDBDS" enabled="false" use-ccm="false">
                    <connection-url>jdbc:mysql://localhost:3306/testprocessdb2</connection-url>
                    <driver-class>com.mysql.jdbc.Driver</driver-class>
                    <driver>mysql-connector-java-5.1.47-bin.jar_com.mysql.jdbc.Driver_5_1</driver>
                    <security>
                        <user-name>xxxx</user-name>
                        <password>xxxx</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                        <background-validation>true</background-validation>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                    </validation>
                </datasource>

Thank you for you help,
Nicole


#2

Hi Nicole,

never used a MySQL database system fpr WildFly, but for an Informix database I have set

        <driver>ifxjdbc.jar</driver>
        <xa-datasource-class>com.informix.jdbcx.IfxXADataSource</xa-datasource-class>
        <xa-datasource-property name="IfxIFXHOST">os-ifx</xa-datasource-property>
        <xa-datasource-property name="PortNumber">1528</xa-datasource-property>
        <xa-datasource-property name="DatabaseName">wildfly14</xa-datasource-property>
        <xa-datasource-property name="ServerName">ifx121</xa-datasource-property>

So for me it seems that you are at least missing the properties

<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
<xa-datasource-property name="PortNumber">3306</xa-datasource-property>

Regards, Frank


#3

Hi Frank,
thank you! Now it works. I added it to the standalone.xml.
But I am confused why the wildfly manager did not made it right by himself. :confused:
The wildfly manager only added this line:

<driver>mysql-connector-java-5.1.47-bin.jar_com.mysql.jdbc.Driver_5_1</driver>

Thank you!