Hibernate throws "Cannot do an operation on a closed statement" on MariaDB


#1

Hi,

I’m working on a server (Java) that uses Hibernate for all DB actions.

I open a sessionFactory once (when the app starts) and create sessions per thread.

If there is no DB request - select/update/delete for a few hours, it seems that either the session or the sessionFactory dies/goes to sleep.

I see from the error I receive -

2018-04-16 08:11:48 DEBUG SQL:109 - SELECT * FROM FacilityEntity WHERE Role = 'Staff'
Hibernate: SELECT * FROM FacilityEntity WHERE Role = 'Staff'
2018-04-16 08:11:48 DEBUG SqlExceptionHelper:139 - could not extract ResultSet [n/a]
java.sql.SQLNonTransientConnectionException: (conn:8) Could not send query: Software caused connection abort: socket write error
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:156)
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:118)
        at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:229)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:208)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:147)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeQuery(MariaDbPreparedStatementClient.java:161)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:2065)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
        at org.hibernate.loader.Loader.doQuery(Loader.java:909)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
        at org.hibernate.loader.Loader.doList(Loader.java:2553)
        at org.hibernate.loader.Loader.doList(Loader.java:2539)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
        at org.hibernate.loader.Loader.list(Loader.java:2364)
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
        at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
        at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
        at com.pointrf.accessor.hibernate.EntityAccessorHibernateImpl.getAllStaff(EntityAccessorHibernateImpl.java:183)
        at com.pointrf.server.request.RequestManager.getAllStaff(RequestManager.java:262)
        at com.pointrf.server.request.RequestManager.<init>(RequestManager.java:102)
        at com.pointrf.server.core.Main.sortMessages(Main.java:71)
        at com.pointrf.server.mqtt.SubscribeCallback.messageArrived(SubscribeCallback.java:38)
        at org.eclipse.paho.client.mqttv3.internal.CommsCallback.deliverMessage(CommsCallback.java:475)
        at org.eclipse.paho.client.mqttv3.internal.CommsCallback.handleMessage(CommsCallback.java:379)
        at org.eclipse.paho.client.mqttv3.internal.CommsCallback.run(CommsCallback.java:183)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Could not send query: Software caused connection abort: socket write error
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.handleIoException(AbstractQueryProtocol.java:1428)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:217)
        at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:203)
        ... 25 more
Caused by: java.net.SocketException: Software caused connection abort: socket write error
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
        at org.mariadb.jdbc.internal.io.output.StandardPacketOutputStream.flushBuffer(StandardPacketOutputStream.java:101)
        at org.mariadb.jdbc.internal.io.output.AbstractPacketOutputStream.flush(AbstractPacketOutputStream.java:157)
        at org.mariadb.jdbc.internal.com.send.ComQuery.sendDirect(ComQuery.java:286)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:206)
        ... 26 more
2018-04-16 08:11:48 WARN  SqlExceptionHelper:144 - SQL Error: 0, SQLState: 08
2018-04-16 08:11:48 ERROR SqlExceptionHelper:146 - (conn:8) Could not send query: Software caused connection abort: socket write error
2018-04-16 08:11:48 ERROR SubscribeCallback:27 - Connection to MQTT broker lost!
2018-04-16 08:11:48 DEBUG JdbcCoordinatorImpl:201 - HHH000420: Closing un-released batch
2018-04-16 08:11:48 DEBUG SqlExceptionHelper:324 - could not log warnings
java.sql.SQLException: Cannot do an operation on a closed statement
        at org.mariadb.jdbc.MariaDbStatement.checkClose(MariaDbStatement.java:1372)
        at org.mariadb.jdbc.MariaDbStatement.getWarnings(MariaDbStatement.java:876)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.handleAndClearWarnings(SqlExceptionHelper.java:320)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logAndClearWarnings(SqlExceptionHelper.java:273)
        at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.close(JdbcCoordinatorImpl.java:529)
        at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.cleanup(JdbcCoordinatorImpl.java:509)
        at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.close(JdbcCoordinatorImpl.java:204)
        at org.hibernate.engine.transaction.internal.TransactionCoordinatorImpl.close(TransactionCoordinatorImpl.java:297)
        at org.hibernate.internal.SessionImpl.close(SessionImpl.java:369)
        at com.pointrf.server.mqtt.SubscribeCallback.connectionLost(SubscribeCallback.java:28)
        at org.eclipse.paho.client.mqttv3.internal.CommsCallback.connectionLost(CommsCallback.java:269)
        at org.eclipse.paho.client.mqttv3.internal.ClientComms.shutdownConnection(ClientComms.java:385)
        at org.eclipse.paho.client.mqttv3.internal.CommsCallback.run(CommsCallback.java:197)
        at java.lang.Thread.run(Thread.java:745)
2018-04-16 08:11:48 DEBUG LogicalConnectionImpl:246 - Releasing JDBC connection
2018-04-16 08:11:48 DEBUG LogicalConnectionImpl:264 - Released JDBC connection

So - my questions are:

  1. Does session/sessionFactory have a limited lifetime?
  2. If so - what is the way of changing/controlling it so that I don’t get this error again?

Thanks in advance for any help


#2

If there is no DB request - select/update/delete for a few hours, it seems that either the session or the sessionFactory dies/goes to sleep.

If you read the stack trace, you’ll see that it’s not the SF that dies, but the database connection.

The SessionFactory and the Session are completely different things, so their lifecycles differ as well. The SessionFactory scope can be bound to the application while the Session lifecycle is usually bound to the incoming HTTP request thread.

If so - what is the way of changing/controlling it so that I don’t get this error again?

The problem does not come from Hibernate, it comes from the JDBC Driver or the database server:

Cannot do an operation on a closed statement

If you’re using connection pooling, set the maxLifetime property to a lower value than the one used by the JDBC driver or the database server.

Don’t expect a pooled connection to be kept alive for hours. Therefore, you need to address this connection pooling issue at the connection pool level.


#3

Firstly, thanks a lot for your quick reply, it helped me understand this issue.

Is there a way of setting the connection pooling maxLifetime via hibernate.cfg.xml file?

that’s where is define the number of pools :

1

(for your info - my database server is : MariaDB)


#4

How did you configure the DataSource?

Better add the hibernate.cfg.xml to the post so we can better understand your config.


#5
<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-configuration PUBLIC

"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"[http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd](http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd)">

<hibernate-configuration>

<session-factory>

<property name="hibernate.archive.autodetection">class,hbm</property>

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

<property name="hibernate.show_sql">true</property>

<property name="connection.driver_class">org.mariadb.jdbc.Driver</property>

<property name="hbm2ddl.auto">update</property>

<property name="connection.pool_size">1</property>

<property name="current_session_context_class">thread</property>

<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

<mapping resource="Location.hbm.xml"></mapping>

.... (more mapping files)

</session-factory>

Thanks again!


#6

You are using the connection.driver_class and a WARN message is logged telling you that’s not meant for production.

Switch ti using hibernate-hikari instead and then you can provide thaat setting.


#7

Thanks again for your help.

It seems like I can control the idle timeout with hikari without changing the driver_class,

by adding: configuration.setProperty(“hibernate.hikari.idleTimeout”, “86400000”) to my code.

I’m I right??


#8

If you read the documentation, you will know that the default Hibernate connection properties don’t match the Hikari ones.


#9

do you by any chance have an example for a hibernte.cfg.xml with hikari example?


#10

All the properties you need to set are documented in the User Guide, as previously mentioned.