Persistence unit complains datasource is closed even when it is not

Question

I have a persistence unit that worked on Apache TomEE 7.0.4(eclipselink 2.6.4), but after upgrade to TomEE 7.1.0(eclipselink 2.7.4), JPA calls failed after some time. The persistence.xml is:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence">
  <persistence-unit name="xxxPU" transaction-type="JTA">
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <jta-data-source>xxxDB</jta-data-source>
    <properties>
      <property name="openjpa.jdbc.DBDictionary" value="mysql"/>
      <property name="openjpa.Log" value="DefaultLevel=WARN, Tool=INFO"/>
    </properties>
  </persistence-unit>
</persistence>

The datasource defined in tomee.xml is

  <Resource id="xxxDB" type="javax.sql.DataSource">
        UserName = xxxx
        Password = yyyy
        JdbcDriver = com.mysql.jdbc.Driver
        JdbcUrl = jdbc:mysql://localhost/dbname
        JtaManaged = true
    factory = org.apache.tomcat.jdbc.pool.DataSourceFactory
    ConnectionProperties = autoReconnect=true;autoReconnectForPools=true;zeroDateTimeBehavior=convertToNull;useUnicode=yes;characterEncoding=UTF-8;useSSL=false
    defaultAutoCommit = false
    testOnBorrow = true
    validationQuery = SELECT 1
    validationInterval = 30000
  </Resource>

The failure happens after sometime with the log here:

15-May-2019 19:12:49.236 SEVERE [ajp-nio-8009-exec-10] org.apache.openejb.core.transaction.EjbTransactionUtil.handleSystemException EjbTransactionUtil.handleSystemException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.v20180807-4be1041): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
Error Code: 0

Apparently the mysql connection is closed, but actually is not. I have a servlet that when HTTP GET, proves the datasource is OK:

@Resource(name=xxxDB)
DataSource dataSource;

....

try (Connection connection = dataSource.getConnection()) {
// do a query to prove connection OK
}

I have cron job that GET this servlet every hour. Connection is OK even after JPA throws com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

My environment Java 8, TomEE 7.1.0, Eclipselink 2.7.3

It looks like you try to use an *existing* connection object after ``close() `` was called on it. — Sascha, May 15 '19 at 12:04
I think you will find it really is closed. The library / JDBC driver / whatever would *know*. The actual problem is how did the connection get closed *when you didn't expect it to happen*. — Stephen C, May 15 '19 at 12:08
First, the webapp worked before upgrade; second I cannot close the connection used by the Persistence Unit, it creates a connection using the JNDI referenced datasource — cpliu338, May 15 '19 at 12:55
Stephen C: of course the connection closed. When this happens eclipselink is supposed to autoReconnect based on the Resource properties defined in tomee.xml — cpliu338, May 15 '19 at 12:58
https://stackoverflow.com/questions/46137173/exception-in-connection-with-mysql-through-jdbc seems to be relevant. I have replaced mysql-connector-java to version 8.0.11. But I will need to wait till connections expire tomorrow to see whether the problem goes away. — cpliu338, May 15 '19 at 13:31
Transaction type is JTA - are you within a transaction when you get the exception? The connection is tied to the JTA transaction, so it could be some process really is closing it on you and your JPA logic. Try turning on logging within the driver to see if it might show more details if the version upgrade doesn't help. — Chris, May 15 '19 at 15:18
No, it was just a read (select) statement and not within a transaction — cpliu338, May 16 '19 at 03:21
If you are not in a transaction, the datasource hands out a different connection on each get connection request, and the connection should be released back to the pool immediately after the query. You have no way of knowing the connection EclipseLink tried to use. It could be a stale connection is in your pool that somehow got closed underneath it before the query. Turn on your logging at the driver and pool level if it happens repeatedly. — Chris, May 16 '19 at 15:22
OK, just changed the persistence unit to use non-jta-datasource and turned on sql logging. Saw error below. Despite complain, read from mysql succeeded — cpliu338, May 16 '19 at 22:43
Call: SELECT XXX Query: ReportQuery(name="Member.XXX" referenceClass=Member1 sql="SELECT XXX"). [EL Info]: 2019-05-16 21:58:46.727--UnitOfWork(487705049)--Communication failure detected when attempting to perform read query outside of a transaction. Attempting to retry query. Error was: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.v20180807-4be1041): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed. — cpliu338, May 16 '19 at 22:43

score 0 · Answer 1 · answered May 19 '19 at 14:58

The problem is with Eclipselink 2.7. I have added to the datasource these parameters:

testOnBorrow = true
testWhileIdle = true
timeBetweenEvictionRuns = 60000 millisecond
testOnReturn = true
validationQuery = SELECT 1
validationInterval = 30000

This failed with closed connection with apache-tomee-plume but OK for apache-tomee-plus. Both are ver 7.1.0, only that "plus" uses openjpa but plume uses eclipselink.

Persistence unit complains datasource is closed even when it is not

1 Answers1