java - 闲置一段时间后失去 JPA 连接

Question

每当 API 空闲一段时间时，我都会失去与数据库的连接(时间无法精确定义。它可能会在空闲时间几秒后发生，有时在几分钟后空闲时间，有时甚至在 jetty 重新启动后立即发生)。这个问题在不同的机器上都会重现，所以我认为它与mysql数据库没有直接关系。

抛出的异常可以是found here

数据库配置基于hibernate和spring:

 <!-- c3p0 of version 0.9.2.1 (current stable) -->
 <!-- spring data: 1.2.0.RELEASE -->
 <!-- mysql connector: 5.1.18 -->
 <!-- hibernate: 4.1.9.FINAL --> 
 <bean id="basisDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${jdbc.driverClassName}" />
    <property name="jdbcUrl" value="${jdbc.url}" />
    <property name="user" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
    <property name="minPoolSize" value="5" />
    <property name="maxPoolSize" value="20" />
    <property name="maxIdleTime" value="600" />
    <property name="maxConnectionAge" value="86400" />
</bean>

<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan" value="de.yourdelivery.data.*" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="generateDdl" value="false" />
            <property name="showSql" value="true" />
            <property name="database" value="MYSQL" />
        </bean>
    </property
</bean>

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

数据库连接和驱动程序如下:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://xxx:3306/xxx?zeroDateTimeBehavior=convertToNull

它甚至无法扩展 jdbc.url

&autoReconnect=true&autoReconnectForPools=true

我使用 OpenSessionInView 模式在推土机映射期间延迟加载所有需要的关系(我知道它被认为是一种反模式，但到目前为止它工作得很好，所以目前不会将其归咎于该模式)

Mysql wait_timeout 设置为 28800

C3PO 的启动日志如下所示

13:34:54,687 INFO PropertySourcesPlaceholderConfigurer:177 - Loading properties file from file [/opt/jetty/jetty-distribution-8.1.8.v20121106/webapps/rest/WEB-INF/classes/properties/customer.fidelity.points.properties] 13:34:54,687 INFO PropertySourcesPlaceholderConfigurer:177 - Loading properties file from file [/opt/jetty/jetty-distribution-8.1.8.v20121106/webapps/rest/WEB-INF/classes/properties/application.properties] 13:34:54,687 INFO PropertySourcesPlaceholderConfigurer:177 - Loading properties file from file [/opt/jetty/jetty-distribution-8.1.8.v20121106/webapps/rest/WEB-INF/classes/properties/restapi.properties] 13:34:54,688 INFO PropertySourcesPlaceholderConfigurer:177 - Loading properties file from file [/opt/jetty/jetty-distribution-8.1.8.v20121106/webapps/rest/WEB-INF/classes/properties/database.properties] 13:34:54,823 INFO MLog:80 - MLog clients using log4j logging. 13:34:54,883 INFO C3P0Registry:204 - Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10] 13:34:55,111 INFO XmlWebApplicationContext:1350 - Bean 'deDataSource' of type [class com.mchange.v2.c3p0.ComboPooledDataSource] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying) 13:34:55,148 INFO XmlWebApplicationContext:1350 - Bean 'plDataSource' of type [class com.mchange.v2.c3p0.ComboPooledDataSource] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying) 13:34:55,150 INFO XmlWebApplicationContext:1350 - Bean 'dataSource' of type [class de.yourdelivery.data.routing.DomainRouting] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying) 13:34:55,167 INFO XmlWebApplicationContext:1350 - Bean 'org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter#24bb6086' of type [class org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying) 13:34:55,213 INFO LocalContainerEntityManagerFactoryBean:264 - Building JPA container EntityManagerFactory for persistence unit 'default' 13:34:55,332 INFO Version:37 - HCANN000001: Hibernate Commons Annotations {4.0.1.Final} 13:34:55,338 INFO Version:41 - HHH000412: Hibernate Core {4.1.9.Final} 13:34:55,341 INFO Environment:239 - HHH000206: hibernate.properties not found 13:34:55,342 INFO Environment:342 - HHH000021: Bytecode provider name : javassist 13:34:55,361 INFO Ejb3Configuration:527 - HHH000204: Processing PersistenceUnitInfo [ name: default ...] 13:34:55,795 INFO ConnectionProviderInitiator:188 - HHH000130: Instantiating explicit connection provider: org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider 13:34:55,880 INFO AbstractPoolBackedDataSource:462 - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge17b8s1e4h6uz10tbcor|3efe0ce9, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge17b8s1e4h6uz10tbcor|3efe0ce9, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.1.10:3306/xxxx?zeroDateTimeBehavior=convertToNull, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 86400, maxIdleTime -> 600, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=*, password=*}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ] 13:34:56,086 INFO Dialect:123 - HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect 13:34:56,095 INFO LobCreatorBuilder:120 - HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException 13:34:56,108 INFO TransactionFactoryInitiator:73 - HHH000268: Transaction strategy: org.hibernate.engine.transaction.internal.jdbc.JdbcTransactionFactory 13:34:56,112 INFO ASTQueryTranslatorFactory:48 - HHH000397: Using ASTQueryTranslatorFactory 13:34:56,149 INFO Version:24 - Hibernate Validator 4.2.0.Final 13:34:56,916 INFO XmlWebApplicationContext:1350 - Bean 'entityManagerFactory' of type [class org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)

那么为什么应用程序会在空闲时间失去连接呢？

Answer 1

有趣的问题

发生这种情况的原因可能有多种。您的应用程序服务器正在失去与数据库的连接。从日志中很难看出只有空闲的连接才是唯一被杀死的连接。您的数据库服务器可能没有分配连接。我希望您已检查数据库日志是否有错误。您如何处理服务中的交易？您使用 Spring 事务吗？

如果使用外部连接池提供程序，最好始终指定 destroy-method，以便在 Spring 应用程序关闭时立即关闭数据源

<bean id="basisDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">

在您的 c3p0 池设置中更改以下内容

采取两种方法删除 maxConnectionAge 和 maxIdleTime

通过更改以下两个参数进行测试，它们在性能影响方面代价高昂

testConnectionOnCheckout =true 
preferredTestQuery =SELECT 1

如果您能够使用上述设置解决该问题

将上述设置更改为以下设置，因为以下设置在性能影响方面成本较低

idleConnectionTestPeriod – 30 
preferredTestQuery =SELECT 1

发表你的发现