SpringMVC+Mybatis 用阿里的 druid 做数据库线程池出现的莫名其妙的问题求救

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 3232 天前的主题，其中的信息可能已经有所发展或是发生改变。

环境是 SpringMVC4.2.4+MyBatis3.3,用 maven 搭建的环境，用 JDK7 和 Tomcat7 ，系统 mac

之前一直是这个环境在使用，今天突然发现启动 tomcat ， druid 曝出了

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

去搜索引擎都了解了下，很多都是上线后 8 小时或更久出现，因数据库回收了连接，系统缓存不知导致？目前我的情况是启动 Tomcat 就会出现，无法正常启动项目，尝试过：

改数据库回收线程时间
改大 druid 的线程池参数
换其他的数据库
换新版 tomcat7

都无效。

最后尝试用 jdk8 和 tomcat8 就可以顺利启动并连接数据库成功，那现在很疑问的猜测 jdk7 坏了？但是强制 rm 掉 jdk7 （ mac 当时能想到的方法就只有这个），重新去官网下载了 jdk7 的 pkg 包安装，还是报一样的问题。

那现在请问大家还有什么其他排查思路吗？或者有遇到类似情况的？

下提有报错日志(最下面)， applicationContext.xml ， spring.xml,web.xml,

spring.xml

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

    <!-- 
        spring 可以自动去扫描 base-package 下面或者子包下面的 java 文件，
        如果扫描到有 @Component @Controller @Service @Repository 等这些注解的类，则把这些类注册为 bean 
    -->
    <context:component-scan base-package="com.edu.szpt.sicrepair.*" />

    <mvc:annotation-driven />

    <!-- 静态资源问题 -->
    <mvc:default-servlet-handler />

    <!-- 
        模型解析，在请求时为模型视图名称添加前后缀 
        比如在 controller 类中需要请求 /WEB-INF/page/index.jsp 文件，直接写 index 就可以了
    -->
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver" p:prefix="/WEB-INF/jsp/" p:suffix=".jsp" />

</beans>

报错日志

[org.springframework.beans.factory.support.DefaultListableBeanFactory] - Invoking init method  'init' on bean with name 'dataSource'
  [com.alibaba.druid.pool.DruidDataSource] - init datasource error, url: jdbc:mysql://localhost:3306/sic_2015_repair?useUnicode=true&characterEncoding=utf-8
  com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    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:404)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:981)
    at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:628)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1014)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2255)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2286)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2085)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:795)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
    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:404)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
    at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:148)
    at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:211)
    at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:142)
    at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1388)
    at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1442)
    at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:632)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1706)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1645)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1574)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:351)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:108)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1481)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1226)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:543)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1192)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1116)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1014)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireByType(AbstractAutowireCapableBeanFactory.java:1292)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1199)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:543)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getTypeForFactoryBean(AbstractBeanFactory.java:1469)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getTypeForFactoryBean(AbstractAutowireCapableBeanFactory.java:808)
    at org.springframework.beans.factory.support.AbstractBeanFactory.isTypeMatch(AbstractBeanFactory.java:544)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doGetBeanNamesForType(DefaultListableBeanFactory.java:447)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanNamesForType(DefaultListableBeanFactory.java:415)
    at org.springframework.beans.factory.BeanFactoryUtils.beanNamesForTypeIncludingAncestors(BeanFactoryUtils.java:220)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1177)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1116)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1014)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireByType(AbstractAutowireCapableBeanFactory.java:1292)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1199)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:543)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getTypeForFactoryBean(AbstractBeanFactory.java:1469)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getTypeForFactoryBean(AbstractAutowireCapableBeanFactory.java:808)
    at org.springframework.beans.factory.support.AbstractBeanFactory.isTypeMatch(AbstractBeanFactory.java:544)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doGetBeanNamesForType(DefaultListableBeanFactory.java:447)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanNamesForType(DefaultListableBeanFactory.java:415)
    at org.springframework.beans.factory.BeanFactoryUtils.beanNamesForTypeIncludingAncestors(BeanFactoryUtils.java:220)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1177)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1116)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1014)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireByType(AbstractAutowireCapableBeanFactory.java:1292)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1199)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:543)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getTypeForFactoryBean(AbstractBeanFactory.java:1469)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getTypeForFactoryBean(AbstractAutowireCapableBeanFactory.java:808)
    at org.springframework.beans.factory.support.AbstractBeanFactory.isTypeMatch(AbstractBeanFactory.java:544)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doGetBeanNamesForType(DefaultListableBeanFactory.java:447)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanNamesForType(DefaultListableBeanFactory.java:415)
    at org.springframework.beans.factory.BeanFactoryUtils.beanNamesForTypeIncludingAncestors(BeanFactoryUtils.java:220)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1177)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1116)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1014)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireByType(AbstractAutowireCapableBeanFactory.java:1292)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1199)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:543)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getTypeForFactoryBean(AbstractBeanFactory.java:1469)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getTypeForFactoryBean(AbstractAutowireCapableBeanFactory.java:808)
    at org.springframework.beans.factory.support.AbstractBeanFactory.isTypeMatch(AbstractBeanFactory.java:544)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doGetBeanNamesForType(DefaultListableBeanFactory.java:447)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanNamesForType(DefaultListableBeanFactory.java:415)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanNamesForType(DefaultListableBeanFactory.java:409)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:338)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:332)
    at org.springframework.scheduling.annotation.AsyncAnnotationBeanPostProcessor.setBeanFactory(AsyncAnnotationBeanPostProcessor.java:130)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeAwareMethods(AbstractAutowireCapableBeanFactory.java:1597)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1565)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
    at org.springframework.context.support.PostProcessorRegistrationDelegate.registerBeanPostProcessors(PostProcessorRegistrationDelegate.java:228)
    at org.springframework.context.support.AbstractApplicationContext.registerBeanPostProcessors(AbstractApplicationContext.java:687)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:523)
    at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:446)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:328)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5077)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5591)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1574)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1564)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2957)
    at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:560)
    ... 132 more

好像更多的看不到了
这是我的博客其他的配置可以帮个忙看看

30 条回复 • 2016-01-29 13:47:16 +08:00

xuhaoyangx

2016-01-29 00:10:16 +08:00

##applicationContext.xml

```xml

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


<context:component-scan base-package="com.edu.szpt.sicrepair.*" />

<mvc:annotation-driven />


<mvc:default-servlet-handler />


<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver" p:prefix="/WEB-INF/jsp/" p:suffix=".jsp" />

</beans>

```

buliugu

2016-01-29 00:43:14 +08:00

mysql 的默认 wait_timeout 是 28800 ，即 8 小时。当一个连接的空闲时间超过该值，就会被收回。 druid 可能没有检测到就直接用了这个连接，当然就跪了啊，在 my.cnf 里把这个值改改大好了

xuhaoyangx

2016-01-29 08:25:42 +08:00

@buliugu 仔细看，我是启动 tomcat 就会有问题。

ppwangs

2016-01-29 08:41:15 +08:00

没用过 druid ，你可以切成 hiakricp 看看是否有同样问题。
另外其他思路：
1 、只升级 tomcat 版本
2 、找一下 druid 是否有最大空闲时间这类配置，尝试缩小该值

cnhongwei

2016-01-29 09:10:08 +08:00

以前在 oracle 数据库中有过，在虚拟机环境中，因为系统事件少，系统生成随机数太慢，登录数据库的时候需要随机数，系统没有随机数可用，等待随机数生成，而数据库认为超时。后面连接数据库的时候，使用伪随机数生成器就可以了。
但感觉在 mysql 中没有这个问题，看网上这个问题，主要的原因是 mysql 监听地址不对，或者 skip-networking 参数没有注释掉，还有就是使用 localhost 来连接,而有时会解析为 ipv6 地址，如果你的 java 设置 ipv6 使用有问题就会连接出问题。

zacard

2016-01-29 09:13:19 +08:00

把 druid 的 bean name 改成其他的，不要用 datasource ，试试。

li24361

2016-01-29 09:16:01 +08:00

贴一下连接池的配置参数吧

xuhaoyangx

2016-01-29 09:19:54 +08:00

@ppwangs 恩，谢谢。正想找个替换 druid 的，这货有 bug ，官方还没怎么维护。

@zacard 恩，尝试下。
@cnhongwei ipv6 mac 至少我是默认禁用的，我用的是 127 连接的， mysql 的配置我就如下
最下面三行是为了减少 mysql 内存占用， sqlmode 是 5.6 默认。
port = 3306
bind-address=0.0.0.0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
performance_schema_max_table_instances=200
table_definition_cache=200
table_open_cache=128
应该不存在这样的问题

xuhaoyangx

2016-01-29 09:22:56 +08:00

@zacard 测试过，不是名字的问题

ljcarsenal

2016-01-29 09:25:30 +08:00

这名字德鲁伊。。

li24361

2016-01-29 09:25:33 +08:00

我原来遇到过一种，是 vpn 的问题，导致连接数据库的时候，返回这个问题，楼主是通过 vpn 连接测试环境了吗

xuhaoyangx

2016-01-29 09:26:06 +08:00

@li24361 不好意思，我以为放出来了。
上面那个 applicationContext 给错了。
连接配置我是放在 applicationContext 的
<?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:util="http://www.springframework.org/schema/util"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:task="http://www.springframework.org/schema/task"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd
http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.0.xsd">

<description>Spring 公共配置</description>


<task:annotation-driven />


<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />

<property name="mapperLocations" value="classpath*:/mybatis/*Mapper.xml" />

<property name="configLocation" value="classpath:/mybatis/config.xml" />
</bean>

<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg index="0" ref="sqlSessionFactory" />


</bean>


<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

<property name="basePackage" value="com.xhy.asp.one.dao" />
</bean>


<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"
proxy-target-class="true" />



<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">

<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />


<property name="initialSize" value="${ds.initialSize}" />
<property name="minIdle" value="${ds.minIdle}" />
<property name="maxActive" value="${ds.maxActive}" />


<property name="maxWait" value="${ds.maxWait}" />


<property name="timeBetweenEvictionRunsMillis" value="${ds.timeBetweenEvictionRunsMillis}" />


<property name="minEvictableIdleTimeMillis" value="${ds.minEvictableIdleTimeMillis}" />

<property name="validationQuery" value="SELECT 'x'" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />


<property name="poolPreparedStatements" value="false" />
<property name="maxPoolPreparedStatementPerConnectionSize"
value="20" />


<property name="filters" value="stat" />
</bean>



<context:property-placeholder
ignore-unresolvable="true" file-encoding="utf-8" location="classpath:jdbc.properties" />

</beans>

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/2015_repair?useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=
##DataSource Global Setting

#配置初始化大小、最小、最大
ds.initialSize=1
ds.minIdle=1
ds.maxActive=20

#配置获取连接等待超时的时间
ds.maxWait=60000

#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
ds.timeBetweenEvictionRunsMillis=60000

#配置一个连接在池中最小生存的时间，单位是毫秒
ds.minEvictableIdleTimeMillis=300000

xuhaoyangx

2016-01-29 09:26:41 +08:00

@li24361 是在本机测试的

sherlocktheplant

2016-01-29 09:33:19 +08:00

@xuhaoyangx
您手动连数据库试一下
然后看看本机有没有开 socks 代理或者 vpn 一类的东西这个坑我也踩过

li24361

2016-01-29 09:37:12 +08:00

额，看了你的配置，没看出来什么问题，建议你把代码 clone 到其他机器试试，确定是环境问题还是代码问题，我遇到的是连接 vpn 出现过这个问题，或者网络不好的时候

ghostsf

2016-01-29 09:38:17 +08:00

@xuhaoyangx druid 这货有啥 bug ？

xuhaoyangx

2016-01-29 09:38:58 +08:00

@sherlocktheplant 之前这个项目是原生写的-。-没有问题。 socket 代理有 ss ， telnet 数据库好像是没问题的。
@li24361 谢谢了。

sherlocktheplant

2016-01-29 09:40:40 +08:00

@xuhaoyangx 你看 ss 有没有吧全局代理打开我就是开了全局代理的时候遇到的

连接池建议用 BoneCP 屡试不爽

li24361

2016-01-29 09:41:48 +08:00

@sherlocktheplant 对，关了所有 vpn 试试

sherlocktheplant

2016-01-29 09:41:52 +08:00

@xuhaoyangx 哦对你先用命令行连数据库试一下

xuhaoyangx

2016-01-29 09:42:43 +08:00

@ghostsf delete id=0 删表数据，遇到过两次，无法重现 bug ，一次是大项目中出现，一个就是我现在这个做的小的。好几次内存泄露严重。

xuhaoyangx

2016-01-29 09:43:05 +08:00

@sherlocktheplant 谢谢推荐

qinxi

2016-01-29 09:48:44 +08:00

贴上我的
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
destroy-method="close">
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="maxActive" value="20" />
<property name="initialSize" value="1" />
<property name="maxWait" value="60000" />
<property name="minIdle" value="1" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="minEvictableIdleTimeMillis" value="30000" />
<property name="validationQuery" value="SELECT 'x'" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="poolPreparedStatements" value="true" />
<property name="maxPoolPreparedStatementPerConnectionSize"
value="20" />

<property name="filters" value="stat,wall" />
</bean>
从来没问题的路过

Infernalzero

2016-01-29 09:49:54 +08:00

mysql-connector 换低版本的试试，你用的最新版怕是有问题，另外你应该把 myabtis 的配置贴出来， mysql 的连接加上 autoReconnect=true

zacard

2016-01-29 10:01:40 +08:00

druid 什么版本？

jedrek

2016-01-29 10:14:17 +08:00

用 tomcat-jdbc 吧

ilaipi

2016-01-29 10:16:22 +08:00

之前也是经常碰到这个问题，不过是神经的，有时候自己会好。顺便看看有木有解决办法

ququzone

2016-01-29 10:16:51 +08:00 via iPhone

mysql 8 小时连接关闭，连接池设置自动重连就 OK

xuhaoyangx

2016-01-29 11:19:29 +08:00

@li24361
@sherlocktheplant
是 SS 的问题，虽然网络设置没有设置全局代理。但是 jdk7 的 socket 还是走了代理，然后出现了问题。
为啥-。-jdk8 没这个问题呢，迷醉。重置了网络设置就好了。

@ququzone 这不是生产环境。是一启动就有。
@Infernalzero mybatis 其实没有什么配置就开启了 cache 。
@zacard 解决了，出现问题时是用 13
@Infernalzero 解决了， thk

sherlocktheplant

2016-01-29 13:47:16 +08:00

@ilaipi 看楼上

SpringMVC+Mybatis 用阿里的 druid 做数据库线程池 出现的莫名其妙的问题 求救

spring.xml

报错日志

SpringMVC+Mybatis 用阿里的 druid 做数据库线程池出现的莫名其妙的问题求救