Novarg
Reputation: 273
hibernate couldn't reconnect to mysql after connection closed by timeout
i have a java app and i want to create pool connection. i'm 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, for work with 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 create SessionFactory, i see 5 connection to my database in MySQL_WorkBench. After that i sent request and wait 50s (wait_timeout in MySql_Server, which i set for test). In Workbench i see that already open 4 connection. I sent request and get error, but in workbench i see already 5 connection.
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 that?
Upvotes: 4
Views: 8995
Answers (2)
Selcuk
Reputation: 855
Try increasing the timeout by adding this to my.cnf:
[mysqld]
interactive_timeout=864000
wait_timeout=864000
Upvotes: 1
tusar
Reputation: 3424
I faced a problem like this once and I got the solution from the 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 to :
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test?autoReconnect=true</property>
But they do not recommend it when using any transactional storage engine.
Upvotes: 3
Related Questions
- Java Hibernate connection to database timed out after few minutes
- Hibernate/Mysql, connection lost
- Hibernate MySQL 5.6 auto reconnect after timeout in Spring app
- Hibernate connection timeout
- hibernate c3p0 connection breaks after mysql timeout
- Hibernate MySQL connection not working
- MySQL connection and hibernate c3p0 settings, timeout after 8 hours?
- hibernate not able to reconnect to the mysql DB
- Hibernate JDBCExceptionReporter -> Connection validity, timeout, auto reconnect
- Hibernate cannot recover after socket has timed out