mybatis插入mysql数据库PersistenceException 数据库连接超时

X-Dragon烟雨任平生

已于 2023-01-10 19:54:21 修改

阅读量1.1k

点赞数

分类专栏：数据库 JAVA 文章标签： mybatis 数据库 mysql

于 2023-01-10 19:24:12 首次发布

本文链接：https://blog.csdn.net/xielong0509/article/details/128498687

版权

JAVA 同时被 2 个专栏收录

14 篇文章 1 订阅

订阅专栏

数据库

10 篇文章 0 订阅

订阅专栏

mybatis插入mysql部分数据失败

mybatis插入mysql数据库PersistenceException

mybatis插入mysql数据库PersistenceException

1、异常堆栈信息：

2022-12-30 16:55:51.448 [ERROR] [pool-8-thread-7] [DataImportExportBizImpl.addSingleDirectCityIndex(830)] - 插入数据 productPriceVersionExtendSYDC :ProductPriceVersionExtends{id='null'versionId='1608748689566781442'hugCate='3'modifyShangQuanNum='197'risePriceShangQuanNum='28'risePriceShangQuanAvg='440.5%'risePriceShangQuanAbs='5.77'cutPriceShangQuanNum='172'cutPriceShangQuanAvg='256.3%'cutPriceShangQuanAbs='6.12'modifyCities='265'priceErrorNum='0'missingDataNum='195'},异常原因:{}
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.sql.SQLException: Could not retrieve transation read-only status server
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO `product_price_version_extends`         (          `version_id`,                                                                   `hugCate`,                                                                   `modify_shangQuan_num`,                                                                   `risePrice_shangQuan_num`,                                                                   `risePrice_shangQuan_avg`,                                                                   `risePrice_shangQuan_abs`,                                                                   `cutPrice_shangQuan_num`,                                                                   `cutPrice_shangQuan_avg`,                                                                   `cutPrice_shangQuan_abs`,                                                                   `modify_cities`,                                                                   `price_error_num`,                                                                   `missing_data_num`          )         VALUES         (          ?,                                                       ?,                                                       ?,                                                       ?,                                                       ?,                                                       ?,                                                       ?,                                                       ?,                                                       ?,                                                       ?,                                                       ?,                                                       ?          )
### Cause: java.sql.SQLException: Could not retrieve transation read-only status server
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
        at com.sun.proxy.$Proxy51.insertProductPriceVersionExtends(Unknown Source)
        at com.bj58.lego.ssp.sspmanager.smartprice.biz.impl.DataImportExportBizImpl.addSingleDirectCityIndex(DataImportExportBizImpl.java:828)
        at com.bj58.lego.ssp.sspmanager.smartprice.biz.impl.DataImportExportBizImpl.distributeVersions4Cities(DataImportExportBizImpl.java:543)
        at com.bj58.lego.ssp.sspmanager.smartprice.biz.impl.DataImportExportBizImpl.distributeVersions(DataImportExportBizImpl.java:503)
        at com.bj58.lego.ssp.sspmanager.smartprice.biz.impl.DataImportExportBizImpl.access$600(DataImportExportBizImpl.java:46)
        at com.bj58.lego.ssp.sspmanager.smartprice.biz.impl.DataImportExportBizImpl$1.run$original$7FZkwEcB(DataImportExportBizImpl.java:232)
        at com.bj58.lego.ssp.sspmanager.smartprice.biz.impl.DataImportExportBizImpl$1.run$original$7FZkwEcB$accessor$0fP62Vg7(DataImportExportBizImpl.java)
        at com.bj58.lego.ssp.sspmanager.smartprice.biz.impl.DataImportExportBizImpl$1$auxiliary$6U7l5gMn.call(Unknown Source)
        at org.apache.skywalking.apm.plugin.jdk.threading.ThreadingMethodInterceptor_internal.intercept(InstanceMethodInterTemplate.java:93)
        at com.bj58.lego.ssp.sspmanager.smartprice.biz.impl.DataImportExportBizImpl$1.run(DataImportExportBizImpl.java)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:902)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:892)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3607)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3576)
        at com.mysql.jdbc.PreparedStatement.checkReadOnlySafeStatement(PreparedStatement.java:1114)
        at com.mysql.jdbc.PreparedStatement.execute$original$yu3qsIFY(PreparedStatement.java:1134)
        at com.mysql.jdbc.PreparedStatement.execute$original$yu3qsIFY$accessor$Kzwe0bUf(PreparedStatement.java)
        at com.mysql.jdbc.PreparedStatement$auxiliary$4ocyMlSE.call(Unknown Source)
        at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:97)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
        ... 16 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 91,292,434 milliseconds ago.  The last packet sent successfully to the server was 91,292,435 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.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3661)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
        at com.mysql.jdbc.StatementImpl.executeQuery$original$ZHocNkSm(StatementImpl.java:1446)
        at com.mysql.jdbc.StatementImpl.executeQuery$original$ZHocNkSm$accessor$McGAG9h4(StatementImpl.java)
        at com.mysql.jdbc.StatementImpl$auxiliary$vzbyM8ID.call(Unknown Source)
        at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:97)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3601)
        ... 29 more
Caused by: java.net.SocketException: Broken pipe
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3643)
        ... 39 more
2022-12-30 16:56:02.418 [INFO ] [pool-12-thread-1] [ConfigClientLocalCache.checkCacheUpdate(224)] - usdtconfig:start check is need update! time=1425743179514649346--45181972-10-17 15:24:09

联系DBA同事帮忙定位问题，根据数据库日志发现数据库连接状态是sleep。已经断开了。

2、问题原因：

经过阅读日志，发现报错信息如下：
应该是连接超时引起的问题。提示要在数据库连接配置里面加autoReconnect=true。

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 91,292,434 milliseconds ago.  The last packet sent successfully to the server was 91,292,435 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.

3、问题排查

3.1 查看数据库连接信息

我查看了一下数据库连接信息,发现链接信息已经加了&autoReconnect=true
经过查询发现：mysql5以上的，设置autoReconnect=true 是无效的只有4.x版本，起作用。我的数据库版本是5.7.22，所以不生效。
关于mysql自动断开的问题研究结果如下，在mysql中有相关参数设定，当数据库连接空闲一定时间后，服务器就会断开等待超时的连接：同一时间，这两个参数(interactive_timeout和wait_timeout)只有一个起作用。到底是哪个参数起作用，和用户连接时指定的连接参数相关，缺省情况下是使用 wait_timeout。我建议是将这两个参数都修改，以免引起不必要的麻烦。

private static final String URL = "jdbc:mysql://XXX?useUnicode=true&characterEncoding=utf8&autoReconnect=true";

数据库连接工具输入一下命令：

-- 查看mysql数据库版本
select version();
-- 结果是 5.7.22-log

-- 查看全局 非交互式超时时间，如jdbc
show global variables like 'interactive_timeout';
-- 结果是3600 单位是S 一小时

-- 查看数据库连接超时时间
show global variables like 'wait_timeout';
-- 结果是3600 单位是S 一小时

-- 查看当前会话非交互式超时时间，如jdbc
show session variables like 'interactive_timeout';
-- 结果是3600 单位是S 一小时

3.2 问题解决

3.2.1 Spring项目可以在配置文件里面设置

#mysql默认使用ping模式,可以通过设置系统属性System.getProperties().setProperty("druid.mysql.usePingMethod", "false")更改为sql模式
#用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。默认：SELECT 1
spring.emily.datasource.config.mysql.validation-query="SELECT 1"
#单位：秒，检测连接是否有效的超时时间。底层调用jdbc Statement对象的void setQueryTimeout(int seconds)方法，默认：-1
spring.emily.datasource.config.mysql.validation-query-timeout=-1
#申请连接时执行validationQuery检测连接是否有效，这个配置会降低性能。默认：false(如果test-on-borrow为true,那么test-while-idle无效)
spring.emily.datasource.config.mysql.test-on-borrow=false
#建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。默认：true
spring.emily.datasource.config.mysql.test-while-idle=true
#归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。默认：false
spring.emily.datasource.config.mysql.test-on-return=false

3.2.2 修改conn改成局部变量

修改后的代码如下：

    private static Logger logger = LoggerFactory.getLogger(MysqlConfig.class);
    private static DataSource dataSource = null;//静态类变量
    private static Connection conn = null;
    
    //沙箱数据库
    private static final String URL =
            "jdbc:mysql://域名:端口号/数据库名?useUnicode=true&characterEncoding=utf8";
    private static final String USERNAME = "用户名";
    private static final String PASSWORD = "密码";

    public DataSource dataSource() {
        DruidDataSource dataSource = null;
        dataSource = new DruidDataSource();
        //dataSource.setDriverClassName(driverClassName);//如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName
        dataSource.setUrl(URL);
        dataSource.setUsername(USERNAME);
        dataSource.setPassword(PASSWORD);
        dataSource.setValidationQuery("SELECT 1");// 用来检测连接是否有效 防止连接断开
        dataSource.setTestOnBorrow(true);//借用连接时执行validationQuery检测连接是否有效。每次打开链接
        dataSource.setTestOnReturn(false);//归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
        //连接空闲时检测，如果连接空闲时间大于timeBetweenEvictionRunsMillis指定的毫秒，执行validationQuery指定的SQL来检测连接是否有效
        dataSource.setTestWhileIdle(true);//如果检测失败，则连接将被从池中去除
        dataSource.setTimeBetweenEvictionRunsMillis(1000 * 5);//5秒钟 TODO DBA反馈看数据库日志没生效 
        dataSource.setMaxActive(20);
        dataSource.setInitialSize(5);
        return dataSource;
    }

    /**
     * @description: 获取dataSource信息
     * @author longxie
     * @date 2023/1/3 20:02
     * @version 1.0
     */
    public  Connection getConnection() throws SQLException {
        if (conn == null) {
            dataSource = dataSource();
            conn = dataSource.getConnection();
            conn.setAutoCommit(true);//自动提交
        }
        return conn;
    }

部署版本上线以后发现还是会出现连接超时的问题。经过同事代码CR发现conn不能声明成类变量。conn超时以后会变成close状态不是null。导致无法获得新链接。详情见下图: 请添加图片描述

后面改写程序,把conn改成局部变量就OK了。后面做版本上线发现没问题。

	private static Logger logger = LoggerFactory.getLogger(MysqlConfig.class);
    private static Connection conn = null;
    private static DataSource dataSource = null;

    //沙箱数据库
    private static final String URL =
            "jdbc:mysql://域名:端口号/数据库名?useUnicode=true&characterEncoding=utf8";
    private static final String USERNAME = "用户名";
    private static final String PASSWORD = "密码";

    public static DataSource dataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        //dataSource.setDriverClassName(driverClassName);//如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName
        dataSource.setUrl(URL);
        dataSource.setUsername(USERNAME);
        dataSource.setPassword(PASSWORD);
        dataSource.setValidationQuery("SELECT 1");// 用来检测连接是否有效 防止连接断开
        dataSource.setTestOnBorrow(false);//借用连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
        dataSource.setTestOnReturn(false);//归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
        //连接空闲时检测，如果连接空闲时间大于timeBetweenEvictionRunsMillis指定的毫秒，执行validationQuery指定的SQL来检测连接是否有效
        dataSource.setTestWhileIdle(true);//如果检测失败，则连接将被从池中去除
        dataSource.setTimeBetweenEvictionRunsMillis(60000);//1分钟
        dataSource.setMaxActive(20);
        dataSource.setInitialSize(5);
        return dataSource;
    }

    /**
     * @description: 获取dataSource信息
     * @author longxie
     * @date 2023/1/3 20:02
     * @version 1.0
     */
    public static Connection getConnection() throws SQLException {
        if (conn == null) {
            dataSource = dataSource();
            conn = dataSource.getConnection();
            conn.setAutoCommit(true);//自动提交
        }
        return conn;
    }