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

yael · April 16, 2018, 8:13am

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:

Does session/sessionFactory have a limited lifetime?
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

vlad · April 16, 2018, 8:39am

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.

yael · April 16, 2018, 9:35am

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)

vlad · April 16, 2018, 10:26am

How did you configure the DataSource?

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

yael · April 16, 2018, 10:59am

<?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!

vlad · April 16, 2018, 11:31am

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.

yael · April 17, 2018, 10:22am

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??

vlad · April 17, 2018, 11:00am

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

yael · April 17, 2018, 11:40am

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

vlad · April 17, 2018, 11:54am

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

Topic		Replies	Views
java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction Hibernate ORM	1	6350	September 15, 2022
No operations allowed after connection closed. Hibernate ORM	1	1113	September 11, 2019
Operation not allowed after ResultSet closed during an update operation in hibernate 5.4.23 Hibernate ORM	5	1766	February 20, 2024
Duplicate releases of Statement objects Hibernate ORM	4	611	January 30, 2024
This statement has been closed error hibernate spring Hibernate ORM	3	4783	July 23, 2018

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

Related Topics