Connecting GlassFish/Payara to MySQL 8.x

Question

I had a maven JSF/JPA web application that was connected to MySQL 5.x developed using Netbeans 12. The application was running fine until I updated the MySQL version from 5.x to 8.x. Since that update, I can not configure the database to connect to the JSF application. The connection to MySQL 8.x is working within Netbeans, but not working when deploying the application.

The current configuration include EclipseLink 2.7.7, MySQL 8.0.23, and GlassFish 5(5.0.1) / Payara 5(5.2021.1). It is not possible to make a successful connection to MySQL. I also failed to establish connection inside JDBS Connection Pool of GlassFish and Payara admin consoles. Can someone please direct me to a source where MySQL version 8 is linked to Payara or GlassFish?

The error displayed in the Payara admin console is as follows.

An error has occurred Ping Connection Pool failed for pooConnection.
Connection could not be allocated because: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Please check the server.log for more details. 

The log file contains the following.

[javax.enterprise.resource.resourceadapter.com.sun.enterprise.connectors.service] [tid: _ThreadID=161 _ThreadName=admin-thread-pool::admin-listener(3)] [timeMillis: 1613549343463] [levelValue: 900] [[
  RAR8054: Exception while creating an unpooled [test] connection for pool [ pooConnection ], Connection could not be allocated because: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.]]

[2021-02-17T13:39:03.472+0530] [Payara 5.2021.1] [SEVERE] [] [org.glassfish.admingui] [tid: _ThreadID=139 _ThreadName=admin-thread-pool::admin-listener(1)] [timeMillis: 1613549343472] [levelValue: 1000] [[
  RestResponse.getResponse() gives FAILURE.  endpoint = 'http://localhost:4848/management/domain/resources/ping-connection-pool.json'; attrs = '{id=pooConnection}']]

Answer 1

Configure your connecion just in four steps:

1. Copy Mysql JDBC driver JAR to $PAYARA_HOME/glassfish/domains/$YOUR_DOMAIN/lib/ e. g.

   cp mysql-connector-java-8.0.22.jar /opt/payara5/glassfish/domains/domain1/lib/

2. Create XML resources descriptor file, name it glassfish-resources.xml. Specify apprpriate params:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//GlassFish.org//DTD GlassFish Application Server 3.1 Resource Definitions//EN" "http://glassfish.org/dtds/glassfish-resources_1_5.dtd">
<resources>
    <jdbc-connection-pool connection-creation-retry-interval-in-seconds="30" connection-validation-method="auto-commit" datasource-classname="com.mysql.cj.jdbc.MysqlDataSource" wrap-jdbc-objects="false" res-type="javax.sql.DataSource" name="mysql_mydb_rootPool" is-connection-validation-required="true" connection-creation-retry-attempts="10" validate-atmost-once-period-in-seconds="60">
        <property name="User" value="root"/>
        <property name="Password" value="secret"/>
        <property name="URL" value="jdbc:mysql://localhost:3306/voyager?zeroDateTimeBehavior=convertToNull&serverTimezone=UTC&useSSL=false"/>
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
        <property name="characterEncoding" value="utf-8"/>
    </jdbc-connection-pool>
    <jdbc-resource enabled="true" jndi-name="jdbc/mydb" object-type="user" pool-name="mysql_mydb_rootPool"/>
</resources>

3. Add resources to Payara

   $PAYARA_HOME/bin/asadmin add-resources glassfish-resources.xml

4. Restart your domain

   $PAYARA_HOME/bin/asadmin restart-domain

Answer 2

In order to connect to Payara Server, the only effective way I found was creating a Connection Pool directly in the Domain Admin Console.

It may seem a long way now, but after completing it will become second nature:

Download MySQL8 Java Connector available at https://dev.mysql.com/downloads/connector/j/ and unzip to any folder:

It will extract to something like: mysql-connector-java-8.0.23 2/mysql-connector-java-8.0.23.jar

1) Make sure you have Payara Server up and running:

cd PATH_TO_PAYARA/bin

2) Start/Restart it

./asadmin start-domain

Note: This will start domain1 by default

3) Install the MySQL8 Connector

./asadmin add-library PATH_TO_MYSQL_CONNECTOR.jar

4) (required) Restart Payara

./asadmin restart-domain

5) Access Admin Console at http://localhost:4848/common/index.jsf

6) In the sidebar navigate to "JDBC" -> "JDBC Connection Pools" menu

7) From there, click "New" to add new Connection Pool

You can have as many as you want. So if you have tried before, you may keep your pools there.

8) In: New JDBC Connection Pool (Step 1 of 2)

Pool Name: MySQL8Pool (or whatever you want)
Resource Type: javax.sql.DataSource
Database Driver Vendor: MySQL8

Click "Next"

9) Scroll down to "Adicional Properties"

Select all and "Delete Properties"

10) Add 6 Properties with the keys/values as:

DatabaseName  YOUR_DB_NAME
User YOUR_DB_USER
Password YOUR_DB_PASSWORD
ServerName localhost
PortNumber 3306
UseSSL false

Click "Save"

11) In the sidebar navigate to "JDBC" -> "JDBC Resources" menu

12) From there, click "New" to add new JDBC Resource

And fill:

JNDI Name: jdbc/MySQL8App
PoolName: MySQL8Pool

Click "Save"

From now own I assume you are using Maven.

13) In you pom.xml make sure you have Eclipse Persistence In you tag:

  <dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.core</artifactId>
    <version>2.7.7</version>
  </dependency>
  <dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.asm</artifactId>
    <version>2.7.7</version>
  </dependency>
  <dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.antlr</artifactId>
    <version>2.7.7</version>
  </dependency>
  <dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.jpa</artifactId>
    <version>2.7.7</version>
  </dependency>
  <dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.jpa.jpql</artifactId>
    <version>2.7.7</version>
  </dependency>
  <dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.moxy</artifactId>
    <version>2.7.7</version>
  </dependency>
  <dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>javax.persistence</artifactId>
    <version>2.2.1</version>
  </dependency> 

14) Create a persistence unit in

In persistence.xml file:

<persistence-unit name="mysql8PU" transaction-type="JTA">
  <jta-data-source>jdbc/MySQL8App</jta-data-source>
  <exclude-unlisted-classes>false</exclude-unlisted-classes>
  <shared-cache-mode>NONE</shared-cache-mode>
  <!--properties>
    <property name="javax.persistence.schema-generation.database.action" value="drop-and-create OR create OR complete remove this line"/-->
  </properties>
</persistence-unit>

Note that in jta-data-source it points to jdbc/MySQL8App and from now own it can be used any where in your code so that after built Payara is able to now we injected it.

PersistenceService.java

package com.your.package.services

import javax.enterprise.context.ApplicationScoped;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

@ApplicationScoped
public class PersistenceService {

  @PersistenceContext(unitName = "mysql8PU")
  EntityManager entityManager;
}

Now rebuild and rerun you project and everything should be fine!