Error "WFLYJCA0047: Connection is not valid" using docker image from Widfly and mysql

[Steve Dev]

When attempting to deploy a JSF application using MySQL which is hosted in localhost, the deployment is done properly locally using Widfly version 21.
When deploying using docker, the application failed due to a connection error with the Mysql database.
In order to test the connection of the docker image of widfly to Mysql, the deployment is done without the JSF application using the following docker file:

FROM jboss/wildfly:21.0.2.Final
RUN /opt/jboss/wildfly/bin/add-user.sh admin Admin
ADD ./dockerfolder/backend/main/mysql-connector-java-8.0.13.jar /opt/jboss/wildfly/modules/com/mysql/driver/main/mysql-connector-java-8.0.13.jar
ADD ./dockerfolder/backend/main/module.xml /opt/jboss/wildfly/modules/com/mysql/driver/main/module.xml
ADD ./dockerfolder/backend/main/mysql-connector-java-8.0.13.jar /opt/jboss/wildfly/modules/com/mysql/driver/main/mysql-connector-java-8.0.13.jar
ADD ./dockerfolder/backend/main/module.xml /opt/jboss/wildfly/modules/com/mysql/driver/main/module.xml
ADD ./dockerfolder/backend/main/mysql-connector-java-8.0.13.jar /opt/jboss/wildfly/modules/system/layers/base/com/mysql/driver/main/mysql-connector-java-8.0.13.jar
ADD ./dockerfolder/backend/main/module.xml /opt/jboss/wildfly/modules/system/layers/base/com/mysql/driver/main/module.xml
COPY ./dockerfolder/backend/standalone.xml /opt/jboss/wildfly/standalone/configuration/standalone.xml
CMD ["/opt/jboss/wildfly/bin/standalone.sh", "-b", "0.0.0.0", "-bmanagement", "0.0.0.0"]

All necessary files are copied from the local to the docker image and this is mentioned in the dockerfile:
The module.xml file:

<?xml version='1.0' encoding='UTF-8'?>

<module xmlns="urn:jboss:module:1.1" name="com.mysql.driver">

   <resources>
       <resource-root path="mysql-connector-java-8.0.13.jar"/>
   </resources>

   <dependencies>
       <module name="javax.api"/>
       <module name="javax.transaction.api"/>
   </dependencies>
</module>

The datasource in standalone.xml file

       <subsystem xmlns="urn:jboss:domain:datasources:6.0">
           <datasources>
               <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
                   <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
                   <driver>h2</driver>
                   <security>
                       <user-name>sa</user-name>
                       <password>sa</password>
                   </security>
               </datasource>
               <datasource jndi-name="java:/MySqlDS" pool-name="MySqlDS">
                   <connection-url>jdbc:mysql://127.0.0.1:3306/Mydatabase</connection-url>
                   <driver-class>com.mysql.cj.jdbc.Driver</driver-class>
                   <driver>mysql</driver>
                   <security>
                       <user-name>root</user-name>
                       <password>rootpassword</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>
               <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.driver">
                       <driver-class>com.mysql.cj.jdbc.Driver</driver-class>
                   </driver>
               </drivers>
           </datasources>
       </subsystem>

When trying to create a Datasource from "Application console", the error message "WFLYJCA0040: failed to invoke operation: WFLYJCA0047"  is displayed as shown in the following image:



So it seems that the error is not at the level of the JSF application but at the level of the connection from Docker image of Widfly to mysql .
While trying to scan the log file of Widfly, it was noticed that the error related to connection from Widfly to Mysql as shown in the log file as follows:

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at [e-mail]//org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:321)
... 35 more
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

Thanks

[Tim Holloway]

I am pretty sure that you should not be doing that in standalone.xml. While it has been a long time since I worked with JBoss, I believe that you want that information to be defined in the jboss-web.xml file.

JEE defines application deployment as coming from 2 major sources. One is the container-independent deployment descriptor, which is always /WEB-INF/web.xml. The other is the container-dependent deployment descriptor, which is, as its name implies, dependent on what container you are using. For Tomcat, it's a Context definition. For JBoss, it should be the jboss-web definition. Actually, since JBoss/Wildfly is a full-stack server, there are additional deployment descriptors for EARs, EJBs, and other things not supported by Tomcat, but the 2-part breakdown is the same. On the other hand, the standalone.xml file and its relatives are, if I am not mistaken, intended to configure the Wildfly server itself, and not a particular webapp deployed within the server. In other words, the equivalent of Tomcat's TOMCAT_HOME/conf/server.xml file.

I will warn again that you ABSOLUTELY, POSITIVELY, should NOT include any database driver JAR in your WAR. The driver jar should be copied into the server's library path, because the driver is used by the Connection Pool(s), and Connection pools are created, managed and owned by Wildfly, not the webapps.

You should, incidentally, be able to create and manage Connection Pools from the Wildfly Management webapp.

But I will have to recommend you to the Wildfly documentation for details. I am, as I said, out of date and out of practice.

One final observation: database setup for testing is completely different than it is for live operation. When doing unit testing, you are working with a limited mock-up of what Wildfly will be doing, and the details on configuration on behavior are going to be quite different from what you'll need in Wildfly itself.

[Steve Dev]

As mentioned in the previous message, I did not deploy the application in order to test the behavior of Widfly in the docker. This means that the war file is not copied, i just launch Wildfly and try to create a datasource to Mysql
The standalone.xml and module.xml files were created locally and then copied to the docker image. please check this link.
So the error is that when launching Wildfly on docker image, the connection does not work to Mysql despite the same configuration working well locally.
The test is done using Wildfly's "Console Application" which is accessible through "http://localhost:9990/console"
In the following image a capture taken from the Wildfly server launched on the docker image:

Now the configuration that works locally doesn't work in the docker image.
The error indicated in the Wildfly log file is the following:

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.driver@8.0.13//com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at [e-mail]//org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:321)
... 35 more
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

So in summary, The war file is not copied, and Wildfly cannot connect to Mysql.

[Tim Holloway]

OK, I am enlightened. The first things to check are that the mysql server is, in fact, running on the local host (NOTE in a moment), listening on port 3306 (because I think that 3305 is sometimes used) AND that the mysql server is configured for external connection.

That's just basics. Now for the NOTE: If all of the above is working when running Wildfly outside of a container, as I think you've been saying, there's ONE MORE "gotcha"!

"localhost" refers to the CONTAINER that your Wildfly is running in. Which is probably NOT the same container (or VM, or whatever) that MySQL is in. In particular, containerized systems often put a MySQL server in one container and the Wildfly (or whatever database client) in another container. Containers are miniature virtual machines, so each container is its own localhost, unrelated to any other container's localhost.

So what to do? Originally, you'd use container linking, but that's now deprecated in favor of container Virtual Private Networks. Basically, you set up both the MySQL server and the Wildfly container on the same virtual net, and instead of referring to the Mysql server as "localhost", give it a container hostname and use THAT as the server hostname in your JDBC URL.

This sort of stuff is commonly set up via Docker Compose and you should be able to find plenty of similar examples on the Internet. It applies pretty much to any sort of networking like this, not just Wildfly. I'm sorry I cannot offer any examples myself, but I've been using Ansible instead of Docker Compose. Which is actually a bit more awkward, but fits better into my overall provisioning schemes.

[Steve Dev]

Thanks Tim Holloway, you underlined a lot of important things and your intervention was very useful.

The container runs on their own address ip, in order to get the allocated address ip, the below command can be used:

$ sudo docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' container_name

The container name can be found by executing the following command:

sudo docker ps -a

Regarding the deployment of the current JSF application, the issue was resolved and there are two ways to complete the deployment:

1-Using docker Wildfly image to host the JSF application and Mysql that is hosted locally

With this way, the application is built into Wildfly docker image but it can not be executed because the Wildfly docker image can't access to Mysql that is hosted locally.
The solution was to run the docker image of Wildfly with the option "--net host,", this will allow the Wildfly docker image  to access to the host machine with the address 127.0.0.1. So the command that should be used to run the Wildfly docker image is:

sudo docker run --net host -p 8080:8080 -p 9990:9990 -d image-id

Now it will be possible to set the connection string in the Wildfly docker image with the following way:

jdbc:mysql://127.0.0.1:3306/Mydatabase

2-Using docker compose with two docker container service

This way requires two containers service: one for the JSF application using Wilfly image and the other is for Mysql, in this case it is important to add the network in each service identified in yml file and also it is required to add a test to the JSF application container service to wait until the Mysql container service finished creation and initialization the database if there is a initialization sql query. Below an example of yml file used for docker compose deployment:

version: "3.8"
services:
mysql_db:
   image: mysql:8.0
   networks:
     - jsf-app-network    
   container_name: mysql_db
   restart: always
   volumes:
     - ./database-initialization:/docker-entrypoint-initdb.d/  
   ports:
     - 3306:3306  
   environment:
     - MYSQL_ROOT_PASSWORD=rootpassword
     - MYSQL_DATABASE=Mydatabase
   healthcheck:
     test: mysql --user=root --password=rootpassword  Mydatabase -e 'select count(*) from user;'
     interval: 10s
     timeout: 300s
     retries: 10
api_service:
   image: jboss/wildfly:21.0.2.Final
   networks:
     - jsf-app-network  
   container_name: api_service
   build:
     context: .
     dockerfile: DockerfileJDK
   restart: on-failure
   ports:
     - 8080:8080
     - 9990:9990
   environment:
     MYSQL_USER: root
     MYSQL_PASSWORD: rootpassword
     MYSQL_DATABASE: Mydatabase
     MYSQL_HOST: mysql_db
   depends_on:
     mysql_db:
         condition: service_healthy
networks:
 jsf-app-network:
   external: false
   name: jsf-app-network

As noted in this yml file, the container of the JSF application service should wait for the Mysql container service to finish creation and initialization of the database, this check is done with the healthcheck attribute in JSF container service and the attribute depends_on in the Mysql container service.
The verification in the Mysql container service will be done by performing a query on the created database, in this way we can know that the database is finished initialization and is ready.

[Tim Holloway]

Yep. That's basically what I was referring to. So let me wrap this up with a few observations.

First, your .yml (YAML) file is a Docker Compose file, for the benefit of anyone who might be wondering. One thing to note, incidentally, about Docker Compose is that all of the containers in the compose file start by default at the same time. That is. in parallel. While you have chosen one way to ensure that Wildfly doesn't talk to MySQL before it's ready, and it's an effective solution for MySQL, you can also simply declare dependencies in the Docker Compose file such that one container won't be started until after the containers it depends on have finished starting up. Note that for things like a database server "started" may not be the same as "all services available", however. The contained process(es) may not all start up immediately, but that depends on them, not on Docker.



Secondly, while assigning a fixed external IP address to Wildfly is perfectly legal, it's also common to simply use port assignments on the Docker container definition and expose the process under the container hosts' primary IP address. This is a useful thing to do to help ensure elasticity.

As an example:

I host quite a few web applications on my domain. Some are Tomcat servers, some are Spring Boot servers, some are other things like Apache. Their actual public access points are handled by a master server (option for cluster of servers) running as reverse web proxies. The proxy for "blog.mousetech.com" might actually point to container host "docker3" and its port 3010, which is docker-mapped to an Apache Wordpress server's port 80. Docker3 might also map its port 3020 to another container Apache instance running the Nagios system monitor where 3020 maps to its port 80.

This saves me a lot of IP addresses, but also, since in the case of JEE servers, it needs to be done anyway, since JEE can only directly listen on ports 80 and 443  or be running as an administrative user, which is a big security no-no. So as long  I have to proxy anyway, why mess with an extra backend IP address.

And with that, I think we're probably all running happy.

[Steve Dev]

And especially for Wildfly, it is important to prepare the module.xml and standalone.xml files..
For the module.xml file

this is done for example on ubunto with the following command
1- Go to bin directory of Wildfly :

cd /wildfly-21.0.2.Final/bin

2- Run Wildfly server with the following command :

./standalone.sh&

3-Generate the module.xml file with the following command:

[standalone@localhost:9990 /] module add --name=com.mysql.driver --dependencies=javax.api,javax.transaction.api --resources=/PATH/TO/mysql-connector-java-8.0 .13.jar

4- Copy the file mysql-connector-java-8.0 .13.jar in the  generated location for the the module

/wildfly-21.0.2.Final/modules/system/layers/base/com/mysql/driver/main

5-Copy generated folder
From

/wildfly-21.0.2.Final/modules/com/mysql/driver/main

to

/wildfly-21.0.2.Final/modules/system/layers/base/com/mysql/driver/main

For the standalone.xml file

The standalone.xml file should be modified by adding the drivers of the Mysql connector, knowing that the class com.mysql.jdbc.Driver is deprecated. The new driver class is com.mysql.cj.jdbc.Driver .
This is done with the following command:

[standalone@localhost:9990 /] /subsystem=datasources/jdbc-driver=mysql/:add(driver-module-name=com.mysql.driver,driver-name=mysql,jdbc-compliant=false,driver-class- name=com.mysql.cj.jdbc.Driver)

This will add the following line to the standalon.xml file

<datasources>
  <datasource >
    ...
   
  </datasource>
  <drivers>
 
    ...
   
    <driver name="mysql" module="com.mysql.driver">
<driver-class>com.mysql.cj.jdbc.Driver</driver-class>
    </driver>
  </drivers>
 
</datasources>  

And finaly when deploying on a Wildfly docker image, it is important to copy these files to the same locations,the following dockerfile can be used as an example:
FROM jboss/wildfly:21.0.2.Final RUN /opt/jboss/wildfly/bin/add-user.sh admin Admin ADD ./Myapplication/main/mysql-connector-java-8.0.13.jar /opt/jboss/wildfly/modules/com/mysql/driver/main/mysql-connector-java-8.0.13.jar ADD ./Myapplication/main/module.xml /opt/jboss/wildfly/modules/com/mysql/driver/main/module.xml ADD ./Myapplication/main/mysql-connector-java-8.0.13.jar /opt/jboss/wildfly/modules/system/layers/base/com/mysql/driver/main/mysql-connector-java-8.0.13.jar ADD ./Myapplication/main/module.xml /opt/jboss/wildfly/modules/system/layers/base/com/mysql/driver/main/module.xml COPY ./Myapplication/standalone.xml /opt/jboss/wildfly/standalone/configuration/standalone.xml COPY ./Myapplication/SF_App.war /opt/jboss/wildfly/standalone/deployments/ CMD ["/opt/jboss/wildfly/bin/standalone.sh", "-b", "0.0.0.0", "-bmanagement", "0.0.0.0"]

[Tim Holloway]

The module.xml and standalone.xml files are specific to Wildfly. Other webapp servers will typically have equivalents, since these are the server-dependent Deployment Descriptors, but the exact name, format, and location of those equivalents will generally be different.

Thanks for the Dockerfile info. I'm going to change it from "quote" to "code" tags for readability and cross-post this thread  to the Cloud/Virtualization forum for the benefit of the general public. Also will make sure it appears in the Wildfly/JBoss forum.