服务器开发之MySQL（JAVA）could not execute query

作者:oneym
本文地址：http://blog.csdn.net/oneym/article/details/53047097

一、前言

数据库是服务器开发必不可少的一个基本环节，可是数据开发遇到了问题怎么办？这里就是记一个MySQL错误的解决方案。MySQL的wait_timeout默认值是8小时，这次就错在了这。

二、问题回顾

项目写成，测试运行正常，在运行的过程中会随机时间出现如下报错:

org.springframework.dao.DataAccessResourceFailureException: could not execute query; nested exception is org.hibernate.exception.JDBCConnectionException: could not execute query    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:631) ~[spring-orm-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) ~[spring-orm-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411) ~[spring-orm-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) ~[spring-orm-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:912) ~[spring-orm-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:904) ~[spring-orm-3.2.0.M2.jar:3.2.0.M2]    at com.package.method2(method2.java:175) [method2.class:?]    at com.package.method(method.java:236) [method.class:?]    at sun.reflect.GeneratedMethodAccessor141.invoke(Unknown Source) ~[?:?]    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[?:1.6.0_24]    at java.lang.reflect.Method.invoke(Method.java:597) ~[?:1.6.0_24]    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319) [spring-aop-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) [spring-aop-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) [spring-aop-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) [spring-tx-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [spring-aop-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90) [spring-aop-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [spring-aop-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) [spring-aop-3.2.0.M2.jar:3.2.0.M2]    at $Proxy33.findMetadataProvinceIdTheLatestOneMetadataId(Unknown Source) [?:?]    at com.package.method3$1.run(method3.java:229) [method3$1.class:?]    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [?:1.6.0_24]    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [?:1.6.0_24]    at java.lang.Thread.run(Thread.java:662) [?:1.6.0_24]Caused by: org.hibernate.exception.JDBCConnectionException: could not execute query    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74) ~[hibernate3.jar:3.2.0.ga]    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) ~[hibernate3.jar:3.2.0.ga]    at org.hibernate.loader.Loader.doList(Loader.java:2147) ~[hibernate3.jar:3.2.0.ga]    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028) ~[hibernate3.jar:3.2.0.ga]    at org.hibernate.loader.Loader.list(Loader.java:2023) ~[hibernate3.jar:3.2.0.ga]    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393) ~[hibernate3.jar:3.2.0.ga]    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338) ~[hibernate3.jar:3.2.0.ga]    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) ~[hibernate3.jar:3.2.0.ga]    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) ~[hibernate3.jar:3.2.0.ga]    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) ~[hibernate3.jar:3.2.0.ga]    at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:921) ~[spring-orm-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:912) ~[spring-orm-3.2.0.M2.jar:3.2.0.M2]    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406) ~[spring-orm-3.2.0.M2.jar:3.2.0.M2]    ... 21 more

堆栈信息比较多这里不全贴出来了，可以通过将<script src="https://code.csdn.net/snippets/1968319.js"></script>放入空白的html文件中，用浏览器打开就可以看到完整的堆栈信息。

三、问题分析

1、堆栈信息跟踪
跟踪到:
org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
这行是throw convertHibernateAccessException(ex);，抛出了异常。是方法doExecute的T result = action.doInHibernate(sessionToExpose);这一行代码出现了问题。去追踪doInHibernate方法的实现代码如下：

public List doInHibernate(Session session) throws HibernateException {    Query queryObject = session.createQuery(queryString);    prepareQuery(queryObject);    if (values != null) {        for (int i = 0; i < values.length; i++) {            queryObject.setParameter(i, values[i]);        }    }    return queryObject.list();}

在return queryObject.list();这里发生了错误，是queryObject中没有数据；没有数据的原因是java.net.SocketException: Connection reset,这是一个网络异常。
根据这个网络异常去反观代码，代码的实现是没有问题的。数据库查询操作只要第一次能够执行，并且能够执行查询（代码中写的查询方法），那么代码是没有错的。那就是别的地方发生了错误，这就出现了下面的分析。

2、c3p0连接池配置
上面出现的网络异常说明连接被中断了，为什么数据库连接会中断？
通过查看MySQL的配置得知wait_timeout的默认连接超时是8小时，而当前的MySQL也是用的默认超时。
从这里可以得到修正的一个方法，就是去修改MySQL的wait_timeout参数值。
这里没有去修改数据库的参数值，而是选择去修改c3p0的配置文件。
数据库连接中断了，怎么在连接池中依然会被使用呢？这是由于缺少了一个有效的检测。那么c3p0中有这中检测么？答案是肯定的。

四、一个可行的解决方案

在c3p0配置中将testConnectionOnCheckout的值设置为true,如下:

<property name="testConnectionOnCheckout" value="true" />

但是只设置这一个是不行的，还需要设置maxIdleTime、automaticTestTable、idleConnectionTestPeriod和unreturnedConnectionTimeout,如下:

<property name="maxIdleTime" value="360" /><property name="automaticTestTable" value="c3p0Test" /><property name="idleConnectionTestPeriod" value="180" /><property name="unreturnedConnectionTimeout" value="400" />

至此就可以解决这个问题了。

作者按：idleConnectionTestPeriod和automaticTestTable需要同时被配置，尽量不要使用preferredTestQuery来替代automaticTestTable；可以用testConnectionOnCheckout来替代idleConnectionTestPeriod；这个取决于你的应用，在可以容忍存在少量could not execute query时，可以给idleConnectionTestPeriod参数一个合理的时间长度，这个会隔一段时间去检查一次连接的有效性，如果连接不可用就会被kill;反过来不能容忍could not execute query的出现可以同时配置automaticTestTable和testConnectionOnCheckout
参数，当然你也可以在配置了testConnectionOnCheckout的情况下也配置idleConnectionTestPeriod，只是你需要牺牲一点性能作为代价。

五、参考

1、MySQL 8小时问题
2、连接池配置参数
3、Configuring Connection Testing

六、结束语

如果对文章有疑问可以通过邮箱[e-mail]与我一起探讨。
也可以加入QQ群137163838,开源我们的思维，点亮整个世界！。