侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 285 篇文章
  • 累计创建 125 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

java连接mysql出现CommunicationsException异常

孔子说JAVA
2022-05-16 / 0 评论 / 0 点赞 / 106 阅读 / 11,312 字 / 正在检测是否收录...

java项目使用mysql作为数据库部署在Tomcat下,在运行一段时间后,发现项目无法正常运行,如使用登录操作,会发现提示“用户名和密码错误”,而我们输入的用户名密码又确定是正确的,打开项目日志后可以发现有CommunicationsException报错,类似报错信息如下:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 54,337,996 milliseconds ago.  The last packet sent successfully to the server was 54,337,997 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

image-1652660847831

1、报错原因

mysql报CommunicationsException的原因有多种。

原因1:本地连接池维持的连接时间超过数据库的超时时间,例如数据库配置28800秒即8小时超时,druid 默认空闲连接超时时间超过了8小时,已经超过了数据库的超时时间。

原因2:java程序中在使用mysql连接后没有及时关闭,导致连接池中的连接数量已被占满,再有新的连接的时候就会报连接异常错误。

  • 针对这种情况的解决方案就是检查代码,确保每次使用完数据库连接后都要及时关闭。要注意异常情况,关闭操作要保证在异常情况下也能正常进行。

下面我们主要分析和解决原因1的情况。

2、分析步骤

在分析之前我们先了解mysql的一个超时配置参数:在我们开启mysql服务后,正常请款下,如果我们没有修改mysql的“wait_timeout”,那么它默认是28800秒即8小时,也就是说如果我们开启一个连接后,8小时之内没有人访问,超过8个小时这个连接就会自动断掉,当我们再次访问数据库的时候,就会出现上面的错误。

遇到该问题的分析步骤如下:

  1. 检查你的数据库连接地址(配置文件中的url)是否正确.

  2. 有可能是由mysql5数据库的配置引起的。mysql5将其连接的等待时间(wait_timeout)缺省为8小时。这意味着如果一个连接的空闲时间超过8个小时,MySQL将自动断开该连接,而连接池却认为该连接还是有效的(因为并未校验连接的有效性),当应用申请使用该连接时,就会导致上面的报错。在客户程序中可以这样来查看其值:

mysql > show global variables like 'wait_timeout'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

28800 seconds,也就是8小时,如果在wait_timeout秒期间内,数据库连接(java.sql.Connection)一直处于等待状态,mysql5就将该连接关闭。这时,你的Java应用的连接池仍然合法地持有该连接的引用。当用该连接来进行数据库操作时,就碰到上述错误。

3、解决办法

3.1 mysql5及之前版本的解决方法

如果是 mysql 5及前的版本,可以在jdbc连接url的配置中附加上“autoReconnect=true”属性,例如:

jdbc:mysql://localhost:3306/dbname?autoReconnect=true

3.2 mysql5.0以上版本的解决方法

如果是mysql5.0以上版本,可以修改my.ini里面的wait_timeout为最大时间(my.ini文件,windows下在mysql的安装目录下,linux下位置为/etc/my.ini),具体内容如下:

[mysqld]
wait_timeout=31536000
interactive_timeout=31536000

也可以直接用sql命令行修改

mysql> set global wait_timeout=10;
mysql> show global variables like 'wait_timeout';

最后:重启mysql服务,重启应用服务器如tomcat,让配置文件生效。

如果经过了以上的步骤,你的问题依旧没有的到解决,则建议你修改下你程序中的 mysql驱动的版本

3.3 java代码创建连接池

通过java代码创建DBCP连接池中BasicDataSource,在代码中设置相关连接参数,重点看以下几个参数的设置,代码中有详细说明:

  • dataSource.setMaxWait(3000);
  • dataSource.setTestWhileIdle(true);
  • dataSource.setTestOnBorrow(true);
  • dataSource.setValidationQuery(“select 1”);
  • dataSource.setTimeBetweenEvictionRunsMillis(3600000);
  • dataSource.setMinEvictableIdleTimeMillis(18000000);
  • dataSource.setNumTestsPerEvictionRun(maxActive);
public BasicDataSource createDataSource(DataSourceDto dataSourceDto) throws SQLException {
        int dsId = dataSourceDto.getDsId();
        String dbName = dataSourceDto.getDbName();
        String ip = dataSourceDto.getIp();
        int port = dataSourceDto.getPort();
        int dbType = dataSourceDto.getDbType();
        String user = dataSourceDto.getUser();
        String pwd = dataSourceDto.getPwd();
        if(dsId == 0 || !StringUtils.hasText(dbName) || !StringUtils.hasText(ip)
                || !StringUtils.hasText(user)|| !StringUtils.hasText(pwd)) {
            return null;
        }
        // 1. 创建DBCP数据源实例
        BasicDataSource dataSource = new BasicDataSource();
        // 2. 为数据源实例指定必须的属性
        String url = DataSourceConstant.getUrl(dbType, ip, port, dbName);
        String driverClassName = DataSourceConstant.getDriverClassName(dbType);
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(pwd);
        int maxActive = 20;
        // 3. 指定数据源的一些可选属性
        // 3.1 指定数据库连接池初始化连接数的个数
        dataSource.setInitialSize(10);
        // 3.2 指定最大的连接数,同一时刻可以同时向数据库申请的连接数
        dataSource.setMaxActive(maxActive);
        // 3.3 指定最小和最大空闲连接数
        dataSource.setMinIdle(5);
        dataSource.setMaxIdle(maxActive);
        // 3.4 等待连接池分配连接,最长的等待时间.单位为毫秒,超出该时间抛异常。
        dataSource.setMaxWait(3000);
        dataSource.setTestWhileIdle(true);
        // 指明是否在从池中取出连接前进行检验,如果检验失败,则从池中去除连接并尝试取出另一个.
        // 设置为true后如果要生效,validationQuery参数必须设置为非空字符串
        dataSource.setTestOnBorrow(true);
        dataSource.setValidationQuery("select 1");
        //set to something smaller than 'wait_timeout'、
        //timeBetweenEvictionRunsMillis 和 minEvictableIdleTimeMillis, 他们两个配合,可以持续更新连接池中的连接对象,
        // 当timeBetweenEvictionRunsMillis 大于0时,每过timeBetweenEvictionRunsMillis 时间,就会启动一个线程,
        // 校验连接池中闲置时间超过minEvictableIdleTimeMillis的连接对象。
        // 在空闲连接回收器线程运行期间休眠的时间值,以毫秒为单位.如果设置为非正数,则不运行空闲连接回收器线程
        dataSource.setTimeBetweenEvictionRunsMillis(3600000);
        // 连接在池中保持空闲而不被空闲连接回收器线程(如果有)回收的最小时间值,单位毫秒
        dataSource.setMinEvictableIdleTimeMillis(18000000);
        // 在每次空闲连接回收器线程(如果有)运行时检查的连接数量
        dataSource.setNumTestsPerEvictionRun(maxActive);
        // 使用这个配置的时候将会使用AbandonedObjectPool.而AbandonedObjectPool建议只在开发阶段使用,因为AbandonedObjectPool能帮你发现占用连接过长的代码.
        // <!-- 超过时间限制是否回收 -->
        dataSource.setRemoveAbandoned(true);
        // <!-- 超时时间;单位为秒。180秒=3分钟 -->
        dataSource.setRemoveAbandonedTimeout(180);
        // <!-- 关闭abanded连接时输出错误日志 -->
        dataSource.setLogAbandoned(true);
        return dataSource;
    }

3.4 spring boot中druid连接池配置

druid的yml的配置实例

spring:
  datasource:
    url: jdbc:mysql://localhost:3307/xxxxx
    username: xxxxx
    password: xxxxxx
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
# 下面为连接池的补充设置,应用到上面所有数据源中
# 初始化大小,最小,最大
      initial-size: 10
      min-idle: 10
      max-active: 20
# 配置获取连接等待超时的时间
      max-wait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
      time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1 FROM DUAL
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
# 打开PSCache,并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
#配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat,wall
      use-global-data-source-stat: true
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 配置监控服务器
      stat-view-servlet:
        login-username: 123456
        login-password: 123456
        reset-enable: false
        url-pattern: /druid/*
# 添加IP白名单
#allow:
# 添加IP黑名单,当白名单和黑名单重复时,黑名单优先级更高
 #deny:
      web-stat-filter:
# 添加过滤规则
        url-pattern: /*
 # 忽略过滤格式
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"

druid的application.properties配置实例

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#驱动配置信息
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#基本连接信息
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.url=jdbc:mysql://192.168.153.23:3306/mytest?useUnicode=true&characterEncoding=utf-8

#连接池属性
spring.datasource.druid.initial-size=15
spring.datasource.druid.max-active=100
spring.datasource.druid.min-idle=15
spring.datasource.druid.max-wait=60000
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.validation-query=SELECT 1
spring.datasource.druid.validation-query-timeout=1000
spring.datasource.druid.keep-alive=true
spring.datasource.druid.remove-abandoned=true
spring.datasource.druid.remove-abandoned-timeout=180
spring.datasource.druid.log-abandoned=true
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
spring.datasource.druid.filters=stat,wall,slf4j
spring.datasource.druid.use-global-data-source-stat=true
spring.datasource.druid.preparedStatement=true
spring.datasource.druid.maxOpenPreparedStatements=100
spring.datasource.druid.connect-properties.mergeSql=true
spring.datasource.druid.connect-properties.slowSqlMillis=5000

3.5 spring中配置连接池

jdbc.properties中配置连接池属性

jdbc.properties:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://172.49.15.55:3306/testdb?useUnicode=true&amp;characterEncoding=utf-8
jdbc.username=test
jdbc.password=test
jdbc.filters=stat
jdbc.maxActive=300
jdbc.initialSize=2
jdbc.maxWait=60000
jdbc.minIdle=1
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.minEvictableIdleTimeMillis=300000
jdbc.validationQuery=SELECT 'x'
jdbc.testWhileIdle=true
jdbc.testOnBorrow=false
jdbc.testOnReturn=false
jdbc.poolPreparedStatements=false
jdbc.maxPoolPreparedStatementPerConnectionSize=50

springs xml中配置连接池bean

<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_user}" />
      <property name="password" value="${jdbc_password}" />
      <!-- 连接池属性 -->
      <property name="initialSize" value="100" />
      <property name="maxActive" value="1000" />
      <property name="maxWait" value="60000" />
      <property name="minEvictableIdleTimeMillis" value=300000 />
      <property name="keepAlive" value=true />
      <property name="timeBetweenEvictionRunsMillis" value=-1 />
      <property name="minIdle" value="20" />
      <property name="removeAbandoned" value="true"/>
      <property name="removeAbandonedTimeout" value="180"/>
      <property name="logAbandoned" value="true" />
      <property name="testWhileIdle" value="true" />
      <property name="validationQuery" value="SELECT 'x'" />
      <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,slf4j"/>
      <property name="connectionProperties" value="druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000" />
</bean>

4、druid连接池配置参数说明

配置 缺省值 说明
name 配置这个属性的意义在于,如果存在多个数据源,监控的时候可以通过名字来区分开来。
如果没有配置,将会生成一个名字,格式是:“DataSource-” + System.identityHashCode(this)
jdbcUrl 连接数据库的url,不同数据库不一样。例如:
mysql : jdbc:mysql://10.20.153.104:3306/druid2
oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username 连接数据库的用户名
password 连接数据库的密码。如果你不希望密码直接写在配置文件中,可以使用ConfigFilter。
详细看这里:https://github.com/alibaba/druid/wiki/使用ConfigFilter
driverClassName 根据url自动识别 这一项可配可不配,如果不配置druid会根据url自动识别dbType,
然后选择相应的driverClassName(建议配置下)
initialSize 0 初始化时建立物理连接的个数。初始化发生在显示调用init方法,或者第一次getConnection时
maxActive 8 最大连接池数量
maxIdle 8 已经不再使用,配置了也没效果
minIdle 最小连接池数量
maxWait 获取连接时最大等待时间,单位毫秒。配置了maxWait之后,缺省启用公平锁,并发效率会有所下降,如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements false 是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements -1 要启用PSCache,必须配置大于0,当大于0时,poolPreparedStatements自动触发修改为true。在Druid中,不会存在Oracle下PSCache占用内存过多的问题,可以把这个数值配置大一些,比如说100
validationQuery 用来检测连接是否有效的sql,要求是一个查询语句。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrow true
testOnReturn false 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
testWhileIdle false 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis 检查空闲连接的频率,单位毫秒, 非正整数时表示不进行检查。有两个含义:
1)Destroy线程会检测连接的间隔时间
2)testWhileIdle的判断依据,详细看testWhileIdle属性的说明
numTestsPerEvictionRun 不再使用,一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis 池中某个连接的空闲时长达到 N 毫秒后, 连接池在下次检查空闲连接时,将回收该连接,要小于防火墙超时设置
connectionInitSqls 物理连接初始化的时候执行的sql
exceptionSorter 根据dbType自动识别 当数据库抛出一些不可恢复的异常时,抛弃连接
filters 属性类型是字符串,通过别名的方式配置扩展插件,常用的插件有:
监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters 类型是List<com.alibaba.druid.filter.Filter>,如果同时配置了filters和proxyFilters,是组合关系,并非替换关系
0

评论区