springmvc学习指南 之---第36篇 记录一下数据库超时问题
mysql数据库,写入13w条数据的时候,报错
本地
2021-12-01 14:55:26.342 [SOFA-SEV-REST-BIZ-8341-4-T1] INFO com.sinosoft.utility.ExeSQLwithBindVariables - ExecSQLwithBV Variables: colValues7='988.8' ERROR - discard connection com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 20 milliseconds ago. The last packet sent successfully to the server was 19 milliseconds ago. 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:404) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:983) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3457) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3357) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3797) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:2843) at com.alibaba.druid.filter.FilterAdapter.preparedStatement_executeUpdate(FilterAdapter.java:1091) at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeUpdate(FilterEventAdapter.java:491) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:2841) at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeUpdate(PreparedStatementProxyImpl.java:195) at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeUpdate(DruidPooledPreparedStatement.java:254) at com.sinosoft.utility.ExeSQLwithBindVariables.execUpdateSQL(ExeSQLwithBindVariables.java:813) at com.sinosoft.utility.ExeSQL.execUpdateSQL(ExeSQL.java:1861) at com.sinosoft.lis.pubfun.PubSubmit.saveData(PubSubmit.java:401) at com.sinosoft.lis.pubfun.PubSubmit.submitData(PubSubmit.java:123) at com.sinosoft.productdef.PDImportExcelBL.pubSubmit(PDImportExcelBL.java:524) at com.sinosoft.productdef.PDImportExcelBL.submitData(PDImportExcelBL.java:123) at com.sinosoft.product.productdef.productDefi.pdcontdefi.service.impl.PDRateCashValueServiceImpl.pdImportExcelSave(PDRateCashValueServiceImpl.java:447) at com.sinosoft.product.productdef.productDefi.pdcontdefi.service.impl.PDRateCashValueServiceImpl.ImportExcel(PDRateCashValueServiceImpl.java:292) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at com.alipay.sofa.rpc.filter.ProviderInvoker.invoke(ProviderInvoker.java:100) at com.alipay.sofa.rpc.filter.PressureMarkTransformFilter.invoke(PressureMarkTransformFilter.java:68) at com.alipay.sofa.rpc.filter.FilterInvoker.invoke(FilterInvoker.java:100) at com.alipay.sofa.rpc.servcegovern.circuitbreaker.CircuitBreakerFilter.invoke(CircuitBreakerFilter.java:114) at com.alipay.sofa.rpc.filter.FilterInvoker.invoke(FilterInvoker.java:100) at com.alipay.sofa.rpc.servcegovern.faultinject.FaultInjectFilter.invoke(FaultInjectFilter.java:95) at com.alipay.sofa.rpc.filter.FilterInvoker.invoke(FilterInvoker.java:100) at com.alipay.sofa.rpc.filter.IpTransmitFilter.invoke(IpTransmitFilter.java:101) at com.alipay.sofa.rpc.filter.FilterInvoker.invoke(FilterInvoker.java:100) at com.alipay.sofa.rpc.filter.ProviderProfileFilter.invoke(ProviderProfileFilter.java:67) at com.alipay.sofa.rpc.filter.FilterInvoker.invoke(FilterInvoker.java:100) at com.alipay.sofa.rpc.filter.sofatracer.ProviderTracerFilter.invoke(ProviderTracerFilter.java:74) at com.alipay.sofa.rpc.filter.FilterInvoker.invoke(FilterInvoker.java:100) at com.alipay.sofa.rpc.filter.ProviderBaggageFilter.invoke(ProviderBaggageFilter.java:45) at com.alipay.sofa.rpc.filter.FilterInvoker.invoke(FilterInvoker.java:100) at com.alipay.sofa.rpc.filter.RpcServiceContextFilter.invoke(RpcServiceContextFilter.java:62) at com.alipay.sofa.rpc.filter.FilterInvoker.invoke(FilterInvoker.java:100) at com.alipay.sofa.rpc.filter.ProviderExceptionFilter.invoke(ProviderExceptionFilter.java:37) at com.alipay.sofa.rpc.filter.FilterInvoker.invoke(FilterInvoker.java:100) at com.alipay.sofa.rpc.filter.FilterChain.invoke(FilterChain.java:268) at com.alipay.sofa.rpc.server.ProviderProxyInvoker.invoke(ProviderProxyInvoker.java:65) at com.sinosoft.product.productdef.productDefi.pdcontdefi.service.PDRateCashValueService_proxy_22.ImportExcel(PDRateCashValueService_proxy_22.java) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:139) at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:510) at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:400) at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget$0(ResourceMethodInvoker.java:364) at org.jboss.resteasy.core.ResourceMethodInvoker$$Lambda$763/1610178591.get(Unknown Source) at org.jboss.resteasy.core.interception.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:355) at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:366) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:338) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:311) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:439) at com.alipay.sofa.rpc.server.rest.SofaSynchronousDispatcher.invoke(SofaSynchronousDispatcher.java:49) at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke$4(SynchronousDispatcher.java:229) at org.jboss.resteasy.core.SynchronousDispatcher$$Lambda$761/1163641422.run(Unknown Source) at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(SynchronousDispatcher.java:135) at org.jboss.resteasy.core.SynchronousDispatcher$$Lambda$762/2127230068.get(Unknown Source) at org.jboss.resteasy.core.interception.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:355) at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:138) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:215) at org.jboss.resteasy.plugins.server.netty.RequestDispatcher.service(RequestDispatcher.java:83) at com.alipay.sofa.rpc.server.rest.SofaRestRequestHandler.channelRead0(SofaRestRequestHandler.java:104) at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362) at io.netty.channel.AbstractChannelHandlerContext.access$600(AbstractChannelHandlerContext.java:38) at io.netty.channel.AbstractChannelHandlerContext$7.run(AbstractChannelHandlerContext.java:353) at io.netty.util.concurrent.AbstractEventExecutor.safeExecute$$$capture(AbstractEventExecutor.java:163) at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java) at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:404) at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:495) at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:905) at java.lang.Thread.run(Thread.java:745) Caused by: java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:209) at java.net.SocketInputStream.read(SocketInputStream.java:141) at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:100) at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:143) at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:173) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2946) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3367) ... 84 more 2021-12-01 14:55:26.371 [SOFA-SEV-REST-BIZ-8341-4-T1] ERROR com.sinosoft.utility.ExeSQLwithBindVariables - ### Error ExeSQL at execUpdateSQL: insert into cashvalue241801(SEX,PAYINTV,PAYENDYEAR,PAYENDYEARFLAG,CURRYEAR,AGE,CASHVALUE) values(?colValues1?,?colValues2?,?colValues3?,?colValues4?,?colValues5?,?colValues6?,?colValues7?) ERROR - create connection error, url: jdbc:mysql://10.253.76.193:3306/product?useUnicode=true&characterEncoding=utf-8, errorCode 0, state 08S01 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: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:404) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:983) 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:2254) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2285) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2084) 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: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: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:149) at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:217) at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:143) at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1466) at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1529) at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2185) Caused by: java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:209) at java.net.SocketInputStream.read(SocketInputStream.java:141) at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:100) at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:143) at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:173) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2946) at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:560)
通过
show VARIABLES like "%trx%"
show variables like '%time%';
具体不懂
那么看ob的设置为
为什么要设置事务呢?因为源码时是使用jdbc提交,通过开启收到实物
try { if (!this.mflag) { this.con = DBConnPool.getConnection(); this.con.setAutoCommit(false); } pstmt = this.con.prepareStatement(StrTool.GBKToUnicode(sqlbv.sql()), 1003, 1008); sqlbv.setParameters(pstmt); pstmt.executeUpdate(); pstmt.close(); pstmt = null; if (!this.mflag) { this.con.commit(); this.con.close(); } return true; } catch (SQLException var8) { logger.debug("### Error ExeSQL at execUpdateSQL: " + sqlbv.originalSql()); CError.buildErr(this, var8.toString(), this.mErrors); if (pstmt != null) { try { pstmt.close(); pstmt = null; } catch (SQLException var7) { var7.printStackTrace(); } } if (!this.mflag) { try { this.con.rollback(); this.con.close(); } catch (SQLException var6) { var6.printStackTrace(); } } return false; }
