All geek questions in one place

Hibernate could not connect to mysql after connection is closed by timeout

I have a java application and I want to create a connection to a pool. I am using Hibernate 4.0_final and c3p0, MySQL on Jboss AS 7.0.2

hibernate.cfg.xml:

<hibernate-configuration> <session-factory > <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property> <property name="hibernate.connection.username">root</property> <property name="hibernate.connection.password"></property> <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property> <property name="hibernate.show_sql">true</property> <property name="hibernate.c3p0.acquire_increment">1</property> <property name="hibernate.c3p0.idle_test_period">100</property> <property name="hibernate.c3p0.max_size">100</property> <property name="hibernate.c3p0.max_statements">0</property> <property name="hibernate.c3p0.min_size">5</property> <property name="hibernate.c3p0.timeout">30</property> <!-- <property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property> --> <mapping class="com.test.Test"/>

c3p0.properties:

 c3p0.testConnectionOnCheckout=true c3p0.acquireRetryDelay=1000 c3p0.acquireRetryAttempts=1

My class from which I get Session to work with the database

 public class HibernateSessionFactory { private static String CONFIG_FILE_LOCATION = "/hibernate.cfg.xml"; private static final ThreadLocal<Session> threadLocal = new ThreadLocal<Session>(); private static org.hibernate.cfg.Configuration configuration = new org.hibernate.cfg.AnnotationConfiguration(); private static org.hibernate.SessionFactory sessionFactory; private static String configFile = CONFIG_FILE_LOCATION; static { rebuildSessionFactory(); } public static Session getSession() throws HibernateException { Session session = (Session) threadLocal.get(); if (session == null || !session.isOpen()) { if (sessionFactory == null) { rebuildSessionFactory(); } session = (sessionFactory != null) ? sessionFactory.openSession(): null; threadLocal.set(session); } return session; } public static void rebuildSessionFactory() { try { configuration.configure(configFile); sessionFactory = configuration.buildSessionFactory(); } catch (Exception e) { e.printStackTrace(); } } }

after creating a SessionFactory, I see 5 connections to my database in MySQL_WorkBench. After that, I sent a request and waited 50 seconds (wait_timeout in MySql_Server, which I set for the test). In Workbench, I see that I am already opening 4 connections. I sent a request and received an error, but in the workbench I already see 5 connections.

Error:

 org.hibernate.exception.JDBCConnectionException: No operations allowed after connection closed. org.hibernate.exception.internal.SQLStateConverter.convert(SQLStateConverter.java:107) org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:146) org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) $Proxy18.prepareStatement(Unknown Source) org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147) org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166) org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145) org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1739) org.hibernate.loader.Loader.doQuery(Loader.java:828) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) org.hibernate.loader.Loader.doList(Loader.java:2463) org.hibernate.loader.Loader.doList(Loader.java:2449) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279) org.hibernate.loader.Loader.list(Loader.java:2274) org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470) org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355) org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196) org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115) org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890) com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150) com.test.servlets.PostFilter.doFilter(PostFilter.java:75) com.test.servlets.PathFilter.doFilter(PathFilter.java:57) root cause com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) java.lang.reflect.Constructor.newInstance(Constructor.java:525) com.mysql.jdbc.Util.handleNewInstance(Util.java:411) com.mysql.jdbc.Util.getInstance(Util.java:386) com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013) com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1205) com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1197) com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4186) com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4152) com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:601) org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138) org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) $Proxy18.prepareStatement(Unknown Source) org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147) org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166) org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145) org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1739) org.hibernate.loader.Loader.doQuery(Loader.java:828) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) org.hibernate.loader.Loader.doList(Loader.java:2463) org.hibernate.loader.Loader.doList(Loader.java:2449) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279) org.hibernate.loader.Loader.list(Loader.java:2274) org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470) org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355) org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196) org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115) org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890) com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150) com.test.servlets.PostFilter.doFilter(PostFilter.java:75) com.test.servlets.PathFilter.doFilter(PathFilter.java:57) root cause com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 96 189 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago. sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) java.lang.reflect.Constructor.newInstance(Constructor.java:525) com.mysql.jdbc.Util.handleNewInstance(Util.java:411) com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116) com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102) com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532) com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002) com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163) com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624) com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127) com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293) com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:601) org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) $Proxy19.executeQuery(Unknown Source) org.hibernate.loader.Loader.getResultSet(Loader.java:1978) org.hibernate.loader.Loader.doQuery(Loader.java:829) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) org.hibernate.loader.Loader.doList(Loader.java:2463) org.hibernate.loader.Loader.doList(Loader.java:2449) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279) org.hibernate.loader.Loader.list(Loader.java:2274) org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470) org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355) org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196) org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115) org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890) com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150) com.test.servlets.PostFilter.doFilter(PostFilter.java:75) com.test.servlets.PathFilter.doFilter(PathFilter.java:57) root cause java.net.SocketException: Software caused connection abort: recv failed java.net.SocketInputStream.socketRead0(Native Method) java.net.SocketInputStream.read(SocketInputStream.java:150) java.net.SocketInputStream.read(SocketInputStream.java:121) com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114) com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161) com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189) com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2549) com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002) com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532) com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002) com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163) com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624) com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127) com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293) com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:601) org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) $Proxy19.executeQuery(Unknown Source) org.hibernate.loader.Loader.getResultSet(Loader.java:1978) org.hibernate.loader.Loader.doQuery(Loader.java:829) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) org.hibernate.loader.Loader.doList(Loader.java:2463) org.hibernate.loader.Loader.doList(Loader.java:2449) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279) org.hibernate.loader.Loader.list(Loader.java:2274) org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470) org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355) org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196) org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115) org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890) com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150) com.test.servlets.PostFilter.doFilter(PostFilter.java:75) com.test.servlets.PathFilter.doFilter(PathFilter.java:57)

How to fix it?

+4
source share
2 answers

I encountered this problem once, and I got the solution from server logs.

  com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was56936 milliseconds ago.The last packet sent successfully to the server was 56936 milliseconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Try setting the connection url:

 <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test?autoReconnect=true</property>

But they do not recommend this when using the transactional storage mechanism.

+3
source

Try increasing the timeout by adding it to my.cnf:

 [mysqld] interactive_timeout=864000 wait_timeout=864000
+1
source

Source: https://habr.com/ru/post/1395496/

More articles:


All Articles