Wildfly 9.0.0.cr1 Unable to connect to MySQL database

[travis_salyer]

I have been working on setting up a Domain Controller in the "full" profile, and I am running into problems getting the MySQL connection to work properly.

After setting it up and clicking on the "Test Connection" button on the Configuration > Connector > Datasources page, a dialog pops up stating "Failed to create JDBC connection: There are no running servers bound to this datasource.". My database is setup and running on the MySQL version 5.6.16, and can access it from the command prompt. I have also bound the latest MySQL connector of version 5.1.24, in the jdbc-driver page of the Management Model dialog.

Further more, upon enabling the datasource the following line is output in the console.

2015-06-03 10:59:48,317 INFO  [org.jboss.as.controller.management-deprecated] (XNIO-1 task-10) WFLYCTL0028: Attribute enabled is deprecated, and it might be removed in future version!

My domain.xml datasource is as followed:

                    <datasource jta="true" jndi-name="java:/prioritization_tool" pool-name="prioritization_tool" enabled="true" use-ccm="true" statistics-enabled="true">

                        <connection-url>jdbc:mysql://localhost:3306/prioritization_tool</connection-url>

                        <driver>mysql</driver>

                        <pool>

                            <min-pool-size>10</min-pool-size>

                            <initial-pool-size>0</initial-pool-size>

                            <max-pool-size>100</max-pool-size>

                            <prefill>true</prefill>

                            <allow-multiple-users>false</allow-multiple-users>

                        </pool>

                        <security>

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

                            <password>sa</password>

                        </security>

                        <validation>

                            <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>

                            <check-valid-connection-sql>select 1</check-valid-connection-sql>

                            <validate-on-match>false</validate-on-match>

                            <background-validation>true</background-validation>

                            <use-fast-fail>false</use-fast-fail>

                            <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>

                        </validation>

                        <timeout>

                            <set-tx-query-timeout>false</set-tx-query-timeout>

                            <blocking-timeout-millis>30000</blocking-timeout-millis>

                            <idle-timeout-minutes>5</idle-timeout-minutes>

                            <query-timeout>0</query-timeout>

                            <use-try-lock>0</use-try-lock>

                            <allocation-retry>0</allocation-retry>

                            <allocation-retry-wait-millis>0</allocation-retry-wait-millis>

                        </timeout>

                        <statement>

                            <prepared-statement-cache-size>250</prepared-statement-cache-size>

                            <share-prepared-statements>true</share-prepared-statements>

                        </statement>

                    </datasource>

                    <drivers>

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

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

                        </driver>

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

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

                        </driver>

                    </drivers>

A screenshot of the MySQL Connector data:

Any help on this matter would be greatly appreciated!

Thanks

Travis

[jaikiran]

This looks like a admin console specific error message that's being thrown. It's coming from here core/VerifyConnectionOp.java at 7e4263ab61e938b7d9e075e8e322ca77a9f195e9 · hal/core · GitHub and I don't have any knowledge of that code to understand what it's trying to check. Someone from the console team like harald.pehl might be able to help.

[harald.pehl]

Could you give some more details about your setup. Clicking in the lower left corner of the admin console will bring up a dialog with version numbers. Some details about your domain / server groups and server might help as well. The error message you see is caused because you don't have any running servers in your domain. In order to verify a connection we need to assign it to a running server.

I recommend to use the regular UI under configuration / datasources to configure and test a datasource. These screen use the most recent management operations. The management model browser in your screenshot is more like a generic tool to access any kind of management resources.

Let me know if that solves your problems.

[travis_salyer]

As far as the version numbers:

More information on my setup:

I am trying to create a domain cluster with a few slave nodes attached to it, using the "full" profile. My slave nodes are currently connected to the domain controlled with an active deployment deployed it them. Currently, I am working to connect the database to these slave nodes. The way I have it set up currently is the database is located on the domain controller, and I need to be able to get the slaves access to that database.

One question I have is, while creating the datasources on the domain controller, does the database need to be on the domain controller or does it have to go onto each slave node?

As far as my configuration goes, I was using the UI under configuration / datasources to configure the connection. I used the Management Model dialog to add the mysql-connector driver onto the server. I am unsure if this was the right way to bind this driver.

Domain / Server groups configurations:

In the host-master.xml file the only thing I changed was the <interface name="management"> tag to reflect the controllers ip address.

In the host-slave.xml file on the slave nodes, I have configured the <security-realm name="management"> tag to be able to connect to the domain controller. As well as, the <servers> tag which is shown below.

<servers>

        <server name="server-one" group="cluster-ha" auto-start="false"/>

        <server name="server-two" group="cluster-ha" auto-start="false">

            <socket-bindings port-offset="150"/>

        </server>

</servers>

Please let me know if you need any additional information.

[harald.pehl]

Thanks for the additional info. As this is something which pops up once in a while, I wrote a document in the GitBook of the HAL management console: JDBC Driver / DataSource Setup | HAL Management Console.

Please note that due to a bug in HAL ([HAL-617] Test connection fails every time - JBoss Issue Tracker), you need to setup the datasource using the CLI. This will be fixed in WildFly 9.0.0.Final which is to be released soon.

[mayerw01]

Maybe I am mistaken. But if you define your url 'dbc:mysql://localhost:3306/...' you should have a db-server up on the same host where your wildfly is running. Otherwise you should replace the localhost with the real host name.

[travis_salyer]

The issue being described in the link seems to match the issues that I am seeing in my instance.

On a side not I discovered that part of my issue has to do with not placing the mysql driver in the modules folder of the slave node, which was not described in any of the instructions on any of the sites and guides that I have encountered while setting this up. It seems like to should have been pretty obvious to do, but it was something that was overlooked on my part.

At this point the error message being displayed in the slave node console matches the message described in your link.

As far as the setup everything seems to match what is shown in the HAL Management Console guide you have linked.

Should the database work as expected even though the Test Connection button may have a bug?

[travis_salyer]

Wolfgang Mayer,

My database server is located on the same computer as the Wildfly server instance. As far as the database URL in the datasource tags, any address referencing this computer seems to have the same result (i.e. localhost, computers IP address, or computer name).

[harald.pehl]

Yes you're right. The document assumes that you have a running MySQL database called "test" @ localhost. I've updated the GitBook to make this more obvious.

[harald.pehl]

If you've installed the JDBC driver as module like described in the linked document, your application should be able to use the datasource. Please let me know if you still face errors.

[mayerw01]

DataSource configuration - WildFly 8 - Project Documentation Editor mentions "The recommended way to install a JDBC driver into WildFly 8 is to deploy it as a regular JAR deployment." So does the driver deployment not work when starting WildFly as a domain? Anyway 'Install as Module' seems to be a workaorond. At least it works on my installation as well for for MySQL as for Oracle.

[harald.pehl]

For WildFly 9 running in domain mode the recommended way to install a driver is to install it as module. Drivers installed as deployment won't be recognized in domain mode. For standalone mode both options will work though.

The documentation for WildFly 9 is work in progress and needs to be updated.