hibernate не может переподключиться к MySQL после закрытия соединения по тайм-ауту
У меня есть приложение Java, и я хочу создать подключение к пулу. я использую Hibernate 4.0_final и c3p0, MySQL на 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
Мой класс, из которого я получаю Session, для работы с базой данных
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();
}
}
}
после создания SessionFactory я вижу 5 подключений к моей базе данных в MySQL_WorkBench. После этого я отправил запрос и жду 50 секунд (wait_timeout в MySql_Server, который я установил для теста). В Workbench я вижу, что уже открыто 4 соединения. Я отправил запрос и получил ошибку, но в верстаке я вижу уже 5 соединений.
Ошибка:
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)
Как это исправить?
2 ответа
Однажды я столкнулся с такой проблемой, и я получил решение из журналов сервера.
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.
Попробуйте установить URL-адрес подключения:
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test?autoReconnect=true</property>
Но они не рекомендуют его при использовании любого механизма хранения транзакций.
Попробуйте увеличить время ожидания, добавив это в my.cnf:
[mysqld]
interactive_timeout=864000
wait_timeout=864000