当前位置: 首页 > 知识库问答 >
问题:

JDBC连接超时无法重新连接

祝昊东
2023-03-14

我的Spring Hibernate Web应用程序在MySQL上运行,这给我带来了麻烦。

我四处寻找并尝试了不同的配置,在这个网站上阅读了相当多的帖子,但它仍然会弹出微笑的头像。

错误消息是:由:com.mysql.jdbc.exceptions.jdbc4.通信异常:从服务器成功接收到的最后一个数据包是63,313,144毫秒前。最后一个成功发送到服务器的数据包是63,313,144毫秒前。比服务器配置的值“wait_timeout”更长。您应该考虑在应用程序中使用之前过期和/或测试连接有效性,增加客户端超时的服务器配置值,或者使用Connector/J连接属性“自动重新连接=true”来避免此问题。

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 63,313,144 milliseconds ago.  The last packet sent successfully to the server was 63,313,144 milliseconds ago. 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.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3965)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2578)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
    at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5359)
    at net.sf.log4jdbc.ConnectionSpy.setAutoCommit(ConnectionSpy.java:764)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:912)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162)
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1435)
    at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:61)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:159)
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)
    ... 46 more
Caused by: java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3946)
    ... 58 more

MySQLwait_timeout值为28800。

我的数据源c3p0和Hibernate配置是:

@Bean
public DataSource dataSource() throws PropertyVetoException {
    ComboPooledDataSource dataSource = new ComboPooledDataSource();
    dataSource.setDriverClass(databaseProperties.getHibernateDriverClassName());
    dataSource.setJdbcUrl(databaseProperties.getDataSourceUrl());
    dataSource.setUser(databaseProperties.getDataSourceUsername());
    dataSource.setPassword(databaseProperties.getDataSourcePassword());
    dataSource.setAcquireIncrement(5);
    dataSource.setMaxStatementsPerConnection(20);
    dataSource.setMaxStatements(100);
    dataSource.setMinPoolSize(2);
    dataSource.setMaxPoolSize(5);
    return dataSource;
}

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws PropertyVetoException {
    HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
    jpaVendorAdapter.setDatabasePlatform(databaseProperties.getHibernateDialect());
    jpaVendorAdapter.setShowSql(true);
    jpaVendorAdapter.setGenerateDdl(false);

    Map<String, String> jpaPropertiesMap = new HashMap<String, String>();
    jpaPropertiesMap.put("hibernate.dialect", databaseProperties.getHibernateDialect());
    jpaPropertiesMap.put("hibernate.show_sql", "true");
    jpaPropertiesMap.put("hibernate.format_sql", "true");
    jpaPropertiesMap.put("hibernate.hbm2ddl.auto", databaseProperties.getHibernateHbm2ddlAuto());
    jpaPropertiesMap.put("hibernate.transaction.factory_class", "org.hibernate.transaction.JDBCTransactionFactory");
    jpaPropertiesMap.put("hibernate.ejb.naming_strategy", "org.hibernate.cfg.ImprovedNamingStrategy");
    jpaPropertiesMap.put("hibernate.c3p0.min_size", "5");
    jpaPropertiesMap.put("hibernate.c3p0.max_size", "20");
    jpaPropertiesMap.put("hibernate.c3p0.timeout", "1000");
    jpaPropertiesMap.put("c3p0.maxConnectionAge", "7200");
    jpaPropertiesMap.put("c3p0.maxIdleTime", "7200");
    jpaPropertiesMap.put("c3p0.unreturnedConnectionTimeout", "60");
    jpaPropertiesMap.put("c3p0.debugUnreturnedConnectionStackTraces", "true");
    jpaPropertiesMap.put("hibernate.c3p0.max_statements", "50");
    // Prevent JPA from converting the dates to the UTC time zone
    jpaPropertiesMap.put("jadira.usertype.autoRegisterUserTypes", "true");
    jpaPropertiesMap.put("jadira.usertype.databaseZone", "jvm");
    jpaPropertiesMap.put("jadira.usertype.javaZone", "jvm");

    LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
    factoryBean.setJpaVendorAdapter(jpaVendorAdapter);
    factoryBean.setPackagesToScan("com.nsn.nitro.project.data.jpa.domain");
    factoryBean.setJpaPropertyMap(jpaPropertiesMap);
    String[] mappingsResources = new String[] {"custom/typedef.hbm.xml"};        
    factoryBean.setMappingResources(mappingsResources);
    factoryBean.setDataSource(dataSource());
    return factoryBean;
}

第二天早上我回到web应用程序时,错误就发生了,整个晚上都没有访问它。

我知道MySQLwait_timeout是MySQL在关闭连接之前等待再次使用连接的秒数。

这意味着我的web应用程序正在尝试使用MySQL端已过期并已关闭的连接,而我的web应用程序仍认为它是有效的连接。

我想我应该在MySQL之前让我的web应用程序连接超时。这样,web应用程序就不会重用MySQL端已经超时并关闭的任何连接,因为web应用程序端的连接已经超时。

感觉我所有的c3p0配置都没有完成未使用连接的超时工作。

我使用以下堆栈:

MySQL mysql-5.6.14
mysql-connector-java 5.1.32
Spring 4.1.0.RELEASE
spring-data-jpa 1.6.2.RELEASE
Hibernate 4.3.6.Final
hibernate-jpa-2.1-api 1.0.0.Final
C3P0 0.9.2.1

我的配置有什么问题吗?

还是我应该明确地关闭连接?

以下是我如何设置存储库:

public interface LanguageRepository extends GenericRepository<Language, Long> {
}

@Repository
@Transactional
public class GenericRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements GenericRepository<T, ID> {

    private EntityManager entityManager;

    public GenericRepositoryImpl(JpaEntityInformation<T, ID> entityMetadata, EntityManager entityManager) {
        super(entityMetadata, entityManager);

        this.entityManager = entityManager;
    }

    public GenericRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
        super(domainClass, entityManager);

        this.entityManager = entityManager;
    }

    public EntityManager getEntityManager() {
        return entityManager;
    }

    @Override
    @Transactional
    public T deleteById(ID id) throws EntityNotFoundException {
        T entity = findOne(id);
        if (entity != null) {
            delete(entity);
        } else {
            throw new EntityNotFoundException("The entity could not be found and was not deleted");
        }
        return entity;
    }

}

public class GenericRepositoryFactoryBean<R extends JpaRepository<T, I>, T, I extends Serializable> extends JpaRepositoryFactoryBean<R, T, I> {

    protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {
        return new BaseRepositoryFactory<T, I>(entityManager);
    }

    protected static class BaseRepositoryFactory<T, I extends Serializable> extends JpaRepositoryFactory {

        private EntityManager entityManager;

        public BaseRepositoryFactory(EntityManager entityManager) {
            super(entityManager);

            this.entityManager = entityManager;
        }

        @Override
        protected Object getTargetRepository(RepositoryMetadata metadata) {
            return new GenericRepositoryImpl<T, I>((JpaEntityInformation<T, I>) getEntityInformation(metadata.getDomainType()), entityManager);
        }

        @Override
        protected Class<?> getRepositoryBaseClass(RepositoryMetadata metadata) {
            return GenericRepositoryImpl.class;
        }
    }

}

@NoRepositoryBean
public interface GenericRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {

    public EntityManager getEntityManager();

    public T deleteById(ID id) throws EntityNotFoundException;

}

我看不到任何close()方法在那里被实现或调用。我的代码中缺少什么?

编辑:添加了C3P0的日志记录。以下是输出:

2014-10-17 14:29:00,464 INFO   [AbstractPoolBackedDataSource] Initializing c3p0 pool... com.mchange.
v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay
 -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false,
 checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchan
ge.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> agvw3s958cggbnis1syx|1acb901, debugUnretu
rnedConnectionStackTraces -> false, description -> null, driverClass -> net.sf.log4jdbc.DriverSpy, f
actoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> agvw3s958c
ggbnis1syx|1acb901, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:log4jdbc:my
sql://127.0.0.1:3306/nitroproject, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTim
e -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 5, maxStatements -> 100, maxStatementsPerC
onnection -> 20, minPoolSize -> 2, numHelperThreads -> 3, preferredTestQuery -> null, properties -> 
{user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, test
ConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, u
serOverrides -> {}, usesTraditionalReflectiveProxies -> false ] 
2014-10-17 14:29:00,479 DEBUG  [BasicResourcePool] incremented pending_acquires: 1 
2014-10-17 14:29:00,480 DEBUG  [BasicResourcePool] Starting acquisition series. Incremented pending_
acquires [1],  attempts_remaining: 30 
2014-10-17 14:29:00,480 DEBUG  [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchr
onousRunner@11dbedc: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$Scattered
AcquireTask@1dd75ae 
2014-10-17 14:29:00,481 DEBUG  [BasicResourcePool] incremented pending_acquires: 2 
2014-10-17 14:29:00,481 DEBUG  [BasicResourcePool] Starting acquisition series. Incremented pending_
acquires [2],  attempts_remaining: 30 
2014-10-17 14:29:00,482 DEBUG  [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchr
onousRunner@11dbedc: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$Scattered
AcquireTask@15083c7 
2014-10-17 14:29:00,482 DEBUG  [BasicResourcePool] incremented pending_acquires: 3 
2014-10-17 14:29:00,483 DEBUG  [BasicResourcePool] Starting acquisition series. Incremented pending_
acquires [3],  attempts_remaining: 30 
2014-10-17 14:29:00,483 DEBUG  [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchr
onousRunner@11dbedc: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$Scattered
AcquireTask@fbbf1d 
2014-10-17 14:29:00,511 DEBUG  [GooGooStatementCache] checkinAll(): com.mchange.v2.c3p0.stmt.DoubleMaxStatementCache stats -- total size: 0; checked out: 0; num connections: 0; num keys: 0 
2014-10-17 14:29:00,523 DEBUG  [C3P0PooledConnectionPool] com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@e38aca.acquireResource() returning.  
2014-10-17 14:29:00,523 DEBUG  [BasicResourcePool] trace com.mchange.v2.resourcepool.BasicResourcePool@ad2e72 [managed: 1, unused: 1, excluded: 0] 
2014-10-17 14:29:00,523 DEBUG  [BasicResourcePool] decremented pending_acquires: 2 
2014-10-17 14:29:00,523 DEBUG  [BasicResourcePool] Acquisition series terminated successfully. Decremented pending_acquires [2],  attempts_remaining: 30 
2014-10-17 14:29:00,521 DEBUG  [BasicResourcePool] com.mchange.v2.resourcepool.BasicResourcePool@ad2e72 config: [start -> 3; min -> 2; max -> 5; inc -> 5; num_acq_attempts -> 30; acq_attempt_delay -> 1000; check_idle_resources_delay -> 0; mox_resource_age -> 0; max_idle_time -> 0; excess_max_idle_time -> 0; destroy_unreturned_resc_time -> 0; expiration_enforcement_delay -> 0; break_on_acquisition_failure -> false; debug_store_checkout_exceptions -> false] 
2014-10-17 14:29:00,523 DEBUG  [C3P0PooledConnectionPoolManager] Created new pool for auth, username (masked): 'ni******'. 
2014-10-17 14:29:00,523 DEBUG  [BasicResourcePool] trace com.mchange.v2.resourcepool.BasicResourcePool@ad2e72 [managed: 1, unused: 0, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@acde7c) 
2014-10-17 14:29:00,524 DEBUG  [GooGooStatementCache] checkinAll(): com.mchange.v2.c3p0.stmt.DoubleMaxStatementCache stats -- total size: 0; checked out: 0; num connections: 0; num keys: 0 
2014-10-17 14:29:00,525 DEBUG  [C3P0PooledConnectionPool] com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@e38aca.acquireResource() returning.  
2014-10-17 14:29:00,525 DEBUG  [BasicResourcePool] trace com.mchange.v2.resourcepool.BasicResourcePool@ad2e72 [managed: 2, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@acde7c) 
2014-10-17 14:29:00,525 DEBUG  [BasicResourcePool] decremented pending_acquires: 1 
2014-10-17 14:29:00,529 DEBUG  [BasicResourcePool] Acquisition series terminated successfully. Decremented pending_acquires [1],  attempts_remaining: 30 
2014-10-17 14:29:00,525 DEBUG  [GooGooStatementCache] checkinAll(): com.mchange.v2.c3p0.stmt.DoubleMaxStatementCache stats -- total size: 0; checked out: 0; num connections: 0; num keys: 0 
2014-10-17 14:29:00,530 DEBUG  [C3P0PooledConnectionPool] com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@e38aca.acquireResource() returning.  
2014-10-17 14:29:00,530 DEBUG  [BasicResourcePool] trace com.mchange.v2.resourcepool.BasicResourcePool@ad2e72 [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@acde7c) 
2014-10-17 14:29:00,530 DEBUG  [BasicResourcePool] decremented pending_acquires: 0 
2014-10-17 14:29:00,530 DEBUG  [BasicResourcePool] Acquisition series terminated successfully. Decremented pending_acquires [0],  attempts_remaining: 30 
2014-10-17 14:29:00,562 DEBUG  [GooGooStatementCache] checkinAll(): com.mchange.v2.c3p0.stmt.DoubleMaxStatementCache stats -- total size: 0; checked out: 0; num connections: 0; num keys: 0 
2014-10-17 14:29:00,574 DEBUG  [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchronousRunner@11dbedc: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1fec09e 
2014-10-17 14:29:00,574 DEBUG  [GooGooStatementCache] checkinAll(): com.mchange.v2.c3p0.stmt.DoubleMaxStatementCache stats -- total size: 0; checked out: 0; num connections: 0; num keys: 0 
2014-10-17 14:29:00,575 DEBUG  [BasicResourcePool] trace com.mchange.v2.resourcepool.BasicResourcePool@ad2e72 [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@acde7c) 
2014-10-17 14:29:00,575 DEBUG  [NewProxyConnection] com.mchange.v2.c3p0.impl.NewProxyConnection@39069f: close() called more than once. 
2014-10-17 14:29:02,260 DEBUG  [BasicResourcePool] trace com.mchange.v2.resourcepool.BasicResourcePool@ad2e72 [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@acde7c) 
2014-10-17 14:29:03,111 DEBUG  [GooGooStatementCache] checkinAll(): com.mchange.v2.c3p0.stmt.DoubleMaxStatementCache stats -- total size: 0; checked out: 0; num connections: 0; num keys: 0 
2014-10-17 14:29:03,112 DEBUG  [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchronousRunner@11dbedc: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@146219b 
2014-10-17 14:29:03,112 DEBUG  [GooGooStatementCache] checkinAll(): com.mchange.v2.c3p0.stmt.DoubleM
axStatementCache stats -- total size: 0; checked out: 0; num connections: 0; num keys: 0 
2014-10-17 14:29:03,113 DEBUG  [BasicResourcePool] trace com.mchange.v2.resourcepool.BasicResourcePo
ol@ad2e72 [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@acde7c) 
2014-10-17 14:29:03,262 DEBUG  [DefaultListableBeanFactory] Returning cached instance of singleton bean 'org.springframework.transaction.config.internalTransactionAdvisor' 
2014-10-17 14:29:03,285 DEBUG  [DefaultListableBeanFactory] Finished creating instance of bean 'entityManagerFactory' 

然后循环输出:

2014-10-17 14:34:10,399 DEBUG  [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@70b40a -- Running DeadlockDetector[Exiting. No pending tasks.] 
2014-10-17 14:34:10,825 DEBUG  [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@15e34e2 -- Running DeadlockDetector[Exiting. No pending tasks.] 
2014-10-17 14:34:10,825 DEBUG  [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@15e34e2 -- Running DeadlockDetector[Exiting. No pending tasks.]

共有2个答案

秦承安
2023-03-14

在JDBC连接中添加以下参数可以解决您的问题:

 testOnBorrow="true" validationQuery="SELECT 1" validationInterval="60000"
孙财
2023-03-14

我认为出现这个问题是因为数据库服务器终止了连接,而应用程序连接池仍然有一个句柄。通过使应用程序连接池的超时时间短于数据库服务器端,应用程序连接池在连接被数据库服务器终止之前更新了连接,从而避免了这个问题。我的MySQL数据库服务器的超时等待时间为28800秒,我的应用程序连接池C3P0的超时等待时间为14400秒。从服务器到客户端的超时等待链必须变短,这是有道理的。

 类似资料:
  • 我编写了一些逻辑,同时表示与exchange的近200个websocet连接。我使用第三方api,它基于org.eclipse.jetty.webSocket.api。我有一个我必须重写的方法。 我在stackoverflow上找到了这个问题,但我看不到清晰的答案。请帮忙,提前谢谢。

  • 问题内容: 我是Spark的新生,并尝试完成Spark教程: 指向教程的链接 在本地计算机(Win10 64,Python 3,Spark 2.4.0)上安装它并设置所有环境变量(HADOOP_HOME,SPARK_HOME等)后,我试图通过WordCount.py文件运行一个简单的Spark作业: 从终端运行后: 我得到以下错误。我检查(逐行注释)它在崩溃 知道我应该检查些什么才能使其正常工作吗

  • 我有一个Hibernate和光数据源的Spring Boot项目。如果我使用注入的SessionFactory对象来获取会话对象,几天后,我会发现与数据库操作相关的任何方法都有这样的异常(只有重新启动才能解决这个问题): 似乎手动使用的会话使此问题。(我有类似的项目,具有相同的配置和功能,但没有注入SessionFactory和Session...我根本没有这样的问题) 应用程序. yaml: 数

  • 我们使用PgBouncer作为基于spring的应用程序连接池的中间件。在应用程序中,我们使用HikariCP进行应用程序级连接池。 我们将HikariCP的值设置为一小时,将设置为30秒。

  • 我编写了一些代码来在本地数据库中插入一个表,但我在堆栈跟踪中得到以下错误: 显然,我可能是错的,但这与我的时区或时区在MySQL或JDBC连接器中的设置方式有关。 这里是我的原始源代码: