8小时后，Spring应用失去与MySql的连接。如何正确配置？ [英] Spring app losing connection to MySql after 8 hours. How to properly configure?

问题描述

我有一个Spring应用程序，我相信它使用DBCP连接池来连接到MySql数据库。我说相信是因为这不是我强大的领域，如果一切设置正确，我都不积极。我没有问题运行应用程序，一切工作正常。问题一夜之间就发生该应用程序没有被大量使用，并在一夜之间它显然失去了它与MySql的连接。我看着它，发现MySql有一个8小时的窗口，然后断开连接或者其他任何东西。我很好，但是当用户在早上尝试登录时，他们会得到类似如下的错误：

通信链接失败。最后一个数据包在60,000,000ms前成功收到。最后一个数据包在15ms前成功设置。

这就是问题所在。我需要他们能够在早上重新连接而不会遇到这个问题。我似乎能够修复它的唯一方法是反弹Tomcat服务器。从查看它，似乎DBCP池应该能够以某种方式防止这种情况，但我无法找到有关如何配置它的可靠信息源。我希望这里有人能够提供一些见解。这是我当前的配置，全部在Spring xml文件中完成：
$ b app-data.xml

 <？xml version =1.0encoding =UTF-8？> 
< beans xmlns =http://www.springframework.org/schema/beans
 xmlns：xsi =http://www.w3.org/2001/XMLSchema-instance
 xmlns：aop =http://www.springframework.org/schema/aop
 xmlns：tx =http://www.springframework.org/schema/tx
 xmlns ：context =http://www.springframework.org/schema/context
 xsi：schemaLocation =
 http://www.springframework.org/schema/beans 
 http： //www.springframework.org/schema/beans/spring-beans-3.0.xsd 
 http://www.springframework.org/schema/tx 
 http://www.springframework.org/ schema / tx / spring-tx-3.0.xsd 
 http://www.springframework.org/schema/aop 
 http://www.springframework.org/schema/aop/spring-aop- 3.0.xsd 
 http://www.springframework.org/schema/context 
 http://www.springframework.org/schema/context/spring-context-3.0.xsd\"> 
 
< context：annotation-config /> 
< context：component-scan base-package =com.vz.sts.domain/> 
< context：component-scan base-package =com.vz.sts.persistence/> 
< context：component-scan base-package =com.vz.sts.service/> 
 
< bean class =org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor/> 
 
< bean id =entityManagerFactoryclass =org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean> 
< property name =dataSourceref =dataSource/> 
< property name =jpaVendorAdapter> 
< bean class =org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter> 
< property name =databasevalue =MYSQL/> 
< property name =showSqlvalue =true/> 
< / bean> 
< / property> 
< / bean> 
 
< bean id =dataSourceclass =org.apache.commons.dbcp.BasicDataSource> 
< property name =driverClassNamevalue =com.mysql.jdbc.Driver/> 
< property name =urlvalue =jdbc：mysql：// localhost：3306 / app/> 
< property name =usernamevalue =root/> 
< property name =passwordvalue =admin/> 
< property name =initialSizevalue =5/> 
< / bean> 
 
< bean id =transactionManagerclass =org.springframework.orm.jpa.JpaTransactionManager> 
< property name =entityManagerFactoryref =entityManagerFactory/> 
< / bean> 
 
< bean id =jdbcUserServiceclass =org.springframework.security.provisioning.JdbcUserDetailsManager> 
< property name =dataSourceref =dataSource/> 
< property name =authenticationManagerref =authenticationManager/> 
< / bean> 
 
< bean id =saltSourceclass =org.springframework.security.authentication.dao.ReflectionSaltSource> 
< property name =userPropertyToUsevalue =username/> 
< / bean> 
 
< tx：annotation-driven /> 
< / beans> 
  

我不确定我需要添加哪些特定属性才能让应用重新连接数据库。我不介意在几个小时后关闭连接，但它应该自动重新连接，而不是像这样抛出错误。我甚至没有肯定它实际上是使用连接池设置的。所以任何帮助将非常感激，谢谢。

更新

我找到了这个页面，我认为我需要做的就是添加 ValidationQuery 属性。任何人都可以验证，如果这将有所欲望的影响，而把所有其他的默认值？我相信这会利用DBCP的 testOnBorrow 方面。我并不完全明白testOnBorrow的解释是什么，但我认为这将做我想做的事情。任何人确认？谢谢。

解决方案

简短的答案应该就够了。 DBCP支持测试从连接池借用的连接（缺省值），还支持在返回和空闲时测试。

这也值得了解可能会发生什么这里错了。听起来像Tomcat服务器和数据库之间的东西在超时后（例如路由器或防火墙）丢弃空闲连接。这个问题在于Tomcat认为它仍然有一个有效的连接，试图对连接做一些工作并失败，但是保持连接处于活动状态并将其返回到池中。现在任何进一步的尝试与数据库交谈都会失败，如果它从池中断开连接。

我认为这是Michael Nygard出色的'Release It！'这本书描述了这个场景中的一个故事。

你也想看看MySQL如何清理死链接，因为Tomcat失去连接8小时后，DB也不会意识到连接失败。

最后一点，如果您使用Tomcat 7切换到其新的连接池，因为它比DBCP提供更好的性能。

I've got a Spring app that I believe uses DBCP connection pooling to connect to a MySql database. I say believe because this isn't an area I'm very strong in and I'm not positive if everything is set up correctly. I have no problems running the application and everything is working fine. The problem occurs overnight. The app is not heavily used and overnight it apparently loses it's connection to MySql. I looked into it and found out MySql has an 8 hour window and then it disconnects or whatever. I'm fine with this, but when a user attempts to log on in the morning, they get an error something like:

Communications link failure. The last packet successfully received 60,000,000ms ago. The last packet successfully setn 15ms ago.

This is the problem. I need them to be able to reconnect in the morning without running into this issue. The only way I seem to be able to fix it is by bouncing the Tomcat server. From looking into it, it seems that DBCP pooling should be able to prevent this somehow but I can't find a reliable source of info on how to configure it. I'm hoping someone here can provide me with some insight. Here is my current configuration, all done in a Spring xml file:

app-data.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
    http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/tx 
    http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
    http://www.springframework.org/schema/aop 
    http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-3.0.xsd">

<context:annotation-config />
<context:component-scan base-package="com.vz.sts.domain" />
<context:component-scan base-package="com.vz.sts.persistence" />
<context:component-scan base-package="com.vz.sts.service" />

<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="database" value="MYSQL" />
            <property name="showSql" value="true" />
        </bean>
    </property>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/app" />
    <property name="username" value="root" />
    <property name="password" value="admin" />
    <property name="initialSize" value="5" />
</bean>

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

<bean id="jdbcUserService" class="org.springframework.security.provisioning.JdbcUserDetailsManager">
    <property name="dataSource" ref="dataSource"/>
    <property name="authenticationManager" ref="authenticationManager"/>
</bean>

<bean id="saltSource" class="org.springframework.security.authentication.dao.ReflectionSaltSource">
    <property name="userPropertyToUse" value="username" />
</bean>

<tx:annotation-driven />
</beans>

I'm not sure what specific properties I need to add in order to allow the app to reconnect to the database. I don't mind if it closes the connection after a number of hours but it should automatically reconnect and not throw errors like this. Nor am I even positive it's actually set up to use connection pooling. So any help would be very much appreciated, thank you.

UPDATE

I found this page and I think that all I need to do is add the ValidationQuery property. Can anyone verify if this will have the desire affect while leaving everything else at default? I believe that will then make use of the testOnBorrow aspect of DBCP. I don't entirely understand what the explanation says testOnBorrow does, but I think this will do what I want. Anyone confirm? Thanks.

解决方案

The short answer is it should be enough. DBCP supports testing the connection on borrowing from the connection pool (the default), but also supports test on return and test while idle.

It's also worth understanding what may be going wrong here. It sounds like something between your Tomcat server and the database is dropping the idle connection after a timeout (such as a router or firewall). The problem with this is that Tomcat thinks it still has a valid connection, tries to do some work with the connection and fails, but keeps the connection alive and returns it to the pool. Now any further attempt to talk to the database will fail if it is given the same broken connection from the pool.

I think it was Michael Nygard's excellent 'Release It!' book that described this scenario in one of his from-the-trenches stories.

You will also want to look into how MySQL cleans up dead connections as when Tomcat loses the connection after 8 hours the DB will also be unaware of the failed connection.

One final point, if you are using Tomcat 7 switch to their new connection pool as it offers better performance than DBCP.

这篇关于8小时后，Spring应用失去与MySql的连接。如何正确配置？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！