You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/11/03 13:44:54 UTC
[GitHub] [shardingsphere] laravelshao opened a new issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
laravelshao opened a new issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015
### Which version of ShardingSphere did you use?
3.0.0.M1
### Which project did you use? Sharding-JDBC or Sharding-Proxy?
Sharding-JDBC
### Expected behavior
update operation success, transaction commit success, interface response success
### Actual behavior
production environment update operation success(data already update&insert), Sharding-jdbc transaction commit error, service global exception handle error response fail
### error detail
```
com.XXX.XXXX.XX.XXXXXX.facade.RuleUpdateFacadeService.updatePromotionRule throw Exception! globalTicket= org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:332)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:533)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.XXX.XXXX.XX.XXXXXX.strategy.RuleUpdateStrategy4Redemption$$EnhancerBySpringCGLIB$$acd37971.update(<generated>)
at com.XXX.XXXX.XX.XXXXXX.facade.RuleUpdateFacadeService.updatePromotionRule(RuleUpdateFacadeService.java:71)
at com.XXX.XXXX.XX.XXXXXX.facade.RuleUpdateFacadeService$$FastClassBySpringCGLIB$$1e0d036d.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
at com.XXX.XXXX.XX.troop.exception.aop.ValidFacadeAspect.aroundAdvice(ValidFacadeAspect.java:77)
at sun.reflect.GeneratedMethodAccessor133.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.XXX.XXXX.XX.XXXXXX.facade.RuleUpdateFacadeService$$EnhancerBySpringCGLIB$$e135e9f3.updatePromotionRule(<generated>)
at com.alibaba.dubbo.common.bytecode.Wrapper0.invokeMethod(Wrapper0.java)
at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:45)
at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:71)
at com.alibaba.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:48)
at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:52)
at com.XXX.XXXX.XX.troop.common.log.filters.AttachValidationFilter.invoke(AttachValidationFilter.java:34)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.XXX.cat.dubbo.support.CatTransactionFilter.invoke(CatTransactionFilter.java:140)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:61)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.XXX.dubbo.monitor.support.XXXMonitorFilter.invoke(XXXMonitorFilter.java:82)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:41)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.XXX.dubbo.trace.TraceContextFilter.invoke(TraceContextFilter.java:45)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.XXX.cat.dubbo.support.CatRemoteCallFilter.invoke(CatRemoteCallFilter.java:20)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:72)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:131)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:98)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:96)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:168)
at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:50)
at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:79)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException
at io.shardingsphere.core.jdbc.adapter.WrapperAdapter.throwSQLExceptionIfNecessary(WrapperAdapter.java:82)
at io.shardingsphere.core.jdbc.adapter.AbstractConnectionAdapter.commit(AbstractConnectionAdapter.java:101)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:329)
```
### Reason analyze
When under update operation, there has some error, the transaction already commit success for MySQL because update data already update and insert, but sharding-jdbc occur "org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException".
Then i copy same interface input param to development environment simulate the whole update process, which can update success and haven't transaction commit error. Also i have try exception situation by manual throw a biz exception, the update operaiton can rollback to original state.
the update operation code like above：
```
@Transactional(rollbackFor = Exception.class)
@Override
public void update(Req req) {
// 批量删除主规则层级
batchDeteleRule();
// 批量更新规则主层级
batchUpdateRule();
// 批量新增规则主层级
batchInsertRule();
// 删除该活动下全部商品
deleteAllGoodsById();
// 批量插入更新后商品列表
batchInsertGoods();
// 删除该活动下全部商品SKU
deleteAllGoodsSkuById();
// 批量插入更新后商品SKU列表
batchInsertGoodsSku();
}
```
sharding configuration
```
<?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:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.io/schema/shardingsphere/sharding
http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd">
<!-- 分库策略 -->
<sharding:inline-strategy id="idDatabaseStrategy" sharding-column="id" algorithm-expression="db$->{Integer.valueOf((id.toString().substring(id.toString().length()-2,id.toString().length()))) % 16}" />
<sharding:complex-strategy id="ruleShardingStrategy" sharding-columns="rule_id,id" algorithm-ref="ecShardingAlgorithm"/>
<sharding:none-strategy id="noneStrategy"/>
<!-- 分库数据源 -->
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="db_global,db_0,db_1,db_2,db_3,db_4,db_5,db_6,db_7,db_8,db_9,db_10,db_11,db_12,db_13,db_14,db_15">
<sharding:table-rules>
<!-- 规则库表分片策略 -->
<sharding:table-rule logic-table="table1" actual-data-nodes="db_$->{0..15}.table1" database-strategy-ref="ruleShardingStrategy"/>
<sharding:table-rule logic-table="table2" actual-data-nodes="db_$->{0..15}.table2" database-strategy-ref="idDatabaseStrategy"/>
<sharding:table-rule logic-table="table3" actual-data-nodes="db_$->{0..15}.table3" database-strategy-ref="idDatabaseStrategy"/>
<!-- 全局库表不参与分库 -->
<sharding:table-rule logic-table="id_sequence" actual-data-nodes="db_global.id_sequence" database-strategy-ref="noneStrategy"/>
</sharding:table-rules>
</sharding:sharding-rule>
<sharding:props>
<prop key="sql.show">true</prop>
</sharding:props>
</sharding:data-source>
</beans>
```
other info
- spring boot version：2.1.3.RELEASE
- spring version：5.1.5.RELEASE
- mybatis version：3.4.4
- mybatis-plus：2.3.3
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao removed a comment on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao removed a comment on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-721016146
> hi， @laravelshao 3.x&4.x is out of date.
> Can you try the master branch version for 5.0.0.alpha?
I want figure out why this happen, does this problem is known bug of version 3.x？
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] yu199195 commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
yu199195 commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-732123072
>In my druid configuration I have those params, after 180s force remove the connection, But sharding-jdbc cached physical connections, So I want to know does sharding-jdbc will be notified or not when druid remove the connection
Hi, if you physical connection may be removed , sharding-jdbc will not be notified .。 so you question is not support batch Insert？
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] cherrylzhao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
cherrylzhao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-721551903
hope this will help you, https://github.com/alibaba/druid/issues/2299
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-721016146
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-720992044
hi， @laravelshao 3.x&4.x is out of date.
Can you try the master branch version for 5.0.0.alpha?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-730822781
> AbstractConnectionAdapter.commit will commit all cached physical connections, I guest your biz have triggered some logic to make the connection invalid, we have never received this problem before
In my druid configuration I have those params, after 180s force remove the connection, But sharding-jdbc cached physical connections, So I want to know does sharding-jdbc will be notified or not when druid remove the connection. @cherrylzhao
```
jdbc.removeAbandoned = true
jdbc.removeAbandonedTimeout = 180
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao edited a comment on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao edited a comment on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-730112082
Finally found out the reason, after checking sharding-jdbc release log, from version 3.0.0.M1 [Support batch INSERT](https://github.com/sharding-sphere/sharding-sphere/issues/290), like below:
```
INSERT INTO t_table (xx, xx) VALUES
(xx,xx),
(xx,xx),
(xx,xx);
```
But for batch update operation we don't is support or not. After searching issues I found a issue about [sharding jdbc do not support batch update](https://github.com/apache/shardingsphere/issues/6665).For batch update sharding-jdbc is not support and have no plan to support because this kind of SQL may be routed to multiple data nodes, which will cause distributed transaction problems. Using mybatis foreach like below：
```
<update id="batchUpdateList" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
update table
set name = #{item.name},
where id = #{item.id}
</foreach>
</update>
```
Actual SQL
```
Actual SQL: ds_1 ::: update stock_change_detail_023
set
update_time = now()
where
id = ?
and tenant_id = ?
and warehouse_no = ?
;
update stock_change_detail
set
update_time = now()
where
id = ?
and tenant_id = ?
and warehouse_no = ? ::: [[[1, jd, 26, 2, jd, 26]]]
```
So, I change batch update operation to single update with loop. After changing, still have commit exception.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-732756787
From k8slog, I find when this exception( `TransactionSystemException` ) happend also have a mysql CommunicationsException.
> com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was `7,501,223` milliseconds ago. The last packet sent successfully to the server was 7,501,224 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.
For mysql wait_timeout time is 3600s, from above exception I find message "The last packet successfully received from the server was `7,501,223` milliseconds ago. The last packet sent successfully to the server was 7,501,224 milliseconds ago",
7,501,223ms is more than 2hours, and sharding-jdbc commit cached physical connection which already close by mysql.
```properties
SHOW VARIABLES LIKE '%timeout%';
interactive_timeout 3600 ## MySQL服务器关闭交互式连接前等待的秒数
wait_timeout 3600 ## MySQL服务器关闭非交互连接之前等待的秒数
```
I review druid DestroyTask code, from code i find three ways to remove connection:
- phyConnectTimeMillis > phyTimeoutMillis(phyConnectTimeMillis = currentTimeMillis - connection.getTimeMillis())
- checkTime && i < checkCount (here true && i < 0 always false)
- idleMillis > maxEvictableIdleTimeMillis(idleMillis = currentTimeMillis - connection.getLastActiveTimeMillis())
```java
// DruidDataSource
public class DestroyTask implements Runnable {
@Override
public void run() {
shrink(true);
if (isRemoveAbandoned()) {
removeAbandoned();
}
}
}
public void shrink(boolean checkTime) {
final List<DruidConnectionHolder> evictList = new ArrayList<DruidConnectionHolder>();
try {
lock.lockInterruptibly();
} catch (InterruptedException e) {
return;
}
try {
// 检查的数量为 = 5 - 5 = 0
final int checkCount = poolingCount - minIdle;
final long currentTimeMillis = System.currentTimeMillis();
for (int i = 0; i < poolingCount; ++i) {
DruidConnectionHolder connection = connections[i];
if (checkTime) {
if (phyTimeoutMillis > 0) {
long phyConnectTimeMillis = currentTimeMillis - connection.getTimeMillis();
if (phyConnectTimeMillis > phyTimeoutMillis) {
evictList.add(connection);
continue;
}
}
// 连接空闲时间
long idleMillis = currentTimeMillis - connection.getLastActiveTimeMillis();
// 空闲时间小于最小回收空闲时间则直接结束循环，这里有点疑惑为什么第一个小于就结束循环
// 作者回复是后进先出(https://github.com/alibaba/druid/pull/1713)
if (idleMillis < minEvictableIdleTimeMillis) {
break;
}
// 在这里有两种情况会被回收，第一种情况一直都不成立，第二种情况只有到空闲时间到7个小时才会成立
// 情况一：checkTime = true && i < 0 永远都是不成立
if (checkTime && i < checkCount) {
evictList.add(connection);
} else if (idleMillis > maxEvictableIdleTimeMillis) {
// 情况二：空闲时间大于最大回收空闲时间，我们在配置中没有配置，适用默认值(1000L * 60L * 60L * 7 ms)
evictList.add(connection);
}
} else {
if (i < checkCount) {
evictList.add(connection);
} else {
break;
}
}
}
int removeCount = evictList.size();
if (removeCount > 0) {
System.arraycopy(connections, removeCount, connections, 0, poolingCount - removeCount);
Arrays.fill(connections, poolingCount - removeCount, poolingCount, null);
poolingCount -= removeCount;
}
} finally {
lock.unlock();
}
for (DruidConnectionHolder item : evictList) {
Connection connection = item.getConnection();
JdbcUtils.close(connection);
destroyCount.incrementAndGet();
}
}
```
Current druid cofiguration:
```properties
jdbc.global.driverClassName = com.mysql.jdbc.Driver
jdbc.global.url = jdbc:mysql://......
# 配置初始化大小、最小、最大
jdbc.global.initialSize = 5
jdbc.global.minIdle = 5
jdbc.global.maxActive = 10
# 配置获取连接等待超时的时间
jdbc.global.maxWait = 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
jdbc.global.timeBetweenEvictionRunsMillis = 60000
# 配置一个连接在池中最小生存的时间，单位是毫秒 默认十分钟
jdbc.global.minEvictableIdleTimeMillis = 300000
jdbc.global.validationQuery = SELECT 'x'
jdbc.global.testWhileIdle = true
jdbc.global.testOnBorrow = false
jdbc.global.testOnReturn = false
# 打开PSCache，并且指定每个连接上PSCache的大小，mysql设置为false
jdbc.global.poolPreparedStatements = false
jdbc.global.maxPoolPreparedStatementPerConnectionSize = 20
```
Here I haven't configure "maxEvictableIdleTimeMillis", so maxEvictableIdleTimeMillis will use default value(1000L * 60L * 60L * 7 ms). More than mysql 1 hours wait_timeout time. So i add below configuration like below:
```properties
## 配置最大回收空闲时间
maxEvictableIdleTimeMillis = 1800000
```
Restart application, after 1 hours still find TransactionSystemException with CommunicationsException, And testWhileIdle is not work(also means idleMillis small than maxEvictableIdleTimeMillis which i add).
The first way to remove connection is configure phyTimeoutMillis, when phyConnectTimeMillis > phyTimeoutMillis will be removed. So i add physical connection timeout like below:
```properties
# 配置物理连接超时时间(40分钟)
phyTimeoutMillis = 2400000
```
Restart application, I haven't find this exception happend. And this problem is solved. But I'm confused why testWhileIdle is not work.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-726570823
After checking druid configuration, I haven’t find any problem. But yesterday my colleague says: Sharding-jdbc version under 3.0 has a problem which not Support batch INSERT, in my project sharding-jdbc version is 3.0.0.M1, so batch INSERT is support. But i'm wondering whether this batch update operation lead to this problem. @cherrylzhao @kimmking
```
<update id="batchUpdateList" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
update tb_rule
set
level = #{item.level},
value = #{item.value},
update_time = now()
where
id = #{item.id}
and type = #{type}
and rule_id = #{level.ruleId}
</foreach>
</update>
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-732759481
> > In my druid configuration I have those params, after 180s force remove the connection, But sharding-jdbc cached physical connections, So I want to know does sharding-jdbc will be notified or not when druid remove the connection
>
> Hi, if you physical connection may be removed , sharding-jdbc will not be notified .。 so you question is not support batch Insert？
I have solved this problem, first I thought this exception was belong to sharding-jdbc, actual was druid testWhileIdle is not work, when mysql physical connection timeout, for druid still think it's valid.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-721511600
> AbstractConnectionAdapter.commit will commit all cached physical connections, I guest your biz have triggered some logic to make the connection invalid, we have never received this problem before
@cherrylzhao Thx, after checking application k8slog, we found when this exception happened, mysql throw out CommunicationsException，so maybe some druid configuration lead to this problem。
error message
```
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 7,501,223 milliseconds ago. The last packet sent successfully to the server was 7,501,224 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.
```
druid configuration
```
jdbc.dataSourceClassName = com.alibaba.druid.pool.DruidDataSource
jdbc.driverClassName = com.mysql.jdbc.Driver
jdbc.initialSize = 5
jdbc.minIdle = 5
jdbc.maxActive = 50
jdbc.maxWait = 60000
jdbc.timeBetweenEvictionRunsMillis = 60000
jdbc.minEvictableIdleTimeMillis = 3600000
jdbc.validationQuery = SELECT 'x'
jdbc.logAbandoned = true
jdbc.removeAbandoned = true
jdbc.removeAbandonedTimeout = 180
jdbc.testWhileIdle = true
jdbc.testOnBorrow = false
jdbc.testOnReturn = false
jdbc.poolPreparedStatements = false
jdbc.maxPoolPreparedStatementPerConnectionSize = 20
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-730112082
Finally found out the reason, after checking sharding-jdbc release log, from version 3.0.0.M1 [Support batch INSERT](https://github.com/sharding-sphere/sharding-sphere/issues/290), like below:
```
INSERT INTO t_table (xx, xx) VALUES
(xx,xx),
(xx,xx),
(xx,xx);
```
I found a issues about [sharding jdbc do not support batch update](https://github.com/apache/shardingsphere/issues/6665).For batch update sharding-jdbc is not support and have no plan to support because this kind of SQL may be routed to multiple data nodes, which will cause distributed transaction problems. Using mybatis foreach like below：
```
<update id="batchUpdateList" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
update table
set name = #{item.name},
where id = #{item.id}
</foreach>
</update>
```
Actual SQL
```
Actual SQL: ds_1 ::: update stock_change_detail_023
set
update_time = now()
where
id = ?
and tenant_id = ?
and warehouse_no = ?
;
update stock_change_detail
set
update_time = now()
where
id = ?
and tenant_id = ?
and warehouse_no = ? ::: [[[1, jd, 26, 2, jd, 26]]]
```
So, I change batch update operation to single update with loop.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao edited a comment on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao edited a comment on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-726570823
After checking druid configuration, I haven’t find any problem. But yesterday my colleague says: Sharding-jdbc version under 3.0 has a problem which not Support batch INSERT, in my project sharding-jdbc version is 3.0.0.M1, so batch INSERT is support. But i'm wondering whether or not this batch update operation lead to this problem, and this version sharding-jdbc support or not. @cherrylzhao @kimmking
```
<update id="batchUpdateList" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
update tb_rule
set
level = #{item.level},
value = #{item.value},
update_time = now()
where
id = #{item.id}
and type = #{type}
and rule_id = #{level.ruleId}
</foreach>
</update>
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] yu199195 commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
yu199195 commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-732791942
@laravelshao There are no other questions, and we'll close this issue~
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao edited a comment on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao edited a comment on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-730112082
Finally found out the reason, after checking sharding-jdbc release log, from version 3.0.0.M1 [Support batch INSERT](https://github.com/sharding-sphere/sharding-sphere/issues/290), like below:
```
INSERT INTO t_table (xx, xx) VALUES
(xx,xx),
(xx,xx),
(xx,xx);
```
But for batch update operation we don't is support or not. After searching issues I found a issue about [sharding jdbc do not support batch update](https://github.com/apache/shardingsphere/issues/6665).For batch update sharding-jdbc is not support and have no plan to support because this kind of SQL may be routed to multiple data nodes, which will cause distributed transaction problems. Using mybatis foreach like below：
```
<update id="batchUpdateList" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
update table
set name = #{item.name},
where id = #{item.id}
</foreach>
</update>
```
Actual SQL
```
Actual SQL: ds_1 ::: update stock_change_detail_023
set
update_time = now()
where
id = ?
and tenant_id = ?
and warehouse_no = ?
;
update stock_change_detail
set
update_time = now()
where
id = ?
and tenant_id = ?
and warehouse_no = ? ::: [[[1, jd, 26, 2, jd, 26]]]
```
So, I change batch update operation to single update with loop.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao closed issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao closed issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] laravelshao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
laravelshao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-721576189
> hope this will help you, [alibaba/druid#2299](https://github.com/alibaba/druid/issues/2299)
@cherrylzhao thx
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] cherrylzhao commented on issue #8015: Sharding-jdbc transaction commit exception but update operation haven’t rollback
Posted by GitBox <gi...@apache.org>.
cherrylzhao commented on issue #8015:
URL: https://github.com/apache/shardingsphere/issues/8015#issuecomment-721500934
AbstractConnectionAdapter.commit will commit all cached physical connections, I guest your biz have triggered some logic to make the connection invalid, we have never received this problem before
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org