shardingSphere proxy 5.4.0 transaction not rolled back (transaction type:LOCAL)

[michael-m-pro]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

environment:
shardingSphere proxy:5.4.0
spring cloud: 2022.0.0
mysql:8.0.28

server.yaml info:
authority:
users:
- user: root@%
password: root
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED

transaction:
defaultType: LOCAL

sqlParser:
sqlCommentParseEnabled: false
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
parseTreeCache:
initialCapacity: 128
maximumSize: 1024

logging:
loggers:

• loggerName: ShardingSphere-SQL
  additivity: true
  level: INFO
  props:
  enable: false

sqlFederation:
sqlFederationEnabled: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535

props:
system-log-level: INFO
max-connections-size-per-query: 1
kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
sql-show: true
proxy-opentracing-enabled:
proxy-hint-enabled: false

code:
@transactional(rollbackFor = Exception.class)
public BaseResult update(@RequestBody CustomerReqDTO customerReqDTO) throws Exception {
BaseResult result = new BaseResult<>();
CustomerDO customerDO = objectConversion.copy(customerReqDTO,CustomerDO.class);
customerDO = customerService.get(customerDO);
customerDO.setRemark("222");
customerService.update(customerDO);
if(true){
throw new Exception("test");
}
customerDO.setRemark("333");
customerService.update(customerDO);
CustomerResDTO customerResDTO =objectConversion.copy(customerDO,CustomerResDTO.class);
result.setData(customerResDTO);
return result;
}

issue:
When the sharding proxy transaction is configured as LOCAL, the spring annotation transaction (@transactional(rollbackFor = Exception.class)) is used in the project. When an exception is thrown in the transaction method, the data cannot be rolled back normally.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.4.0

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

transaction rolled back

Actual behavior

transaction not rolled back

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

[strongduanmu]

Hi @michael-m-pro, this is mysql driver 8.0.28's bug, you can refer Changes in MySQL Connector/J 8.0.29 (2022-04-26, General Availability) - https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-29.html.

A connection did not maintain the correct autocommit state when it was used in a pool with useLocalSessionState=true. (Bug #106435, Bug #33850099)

References: This issue is a regression of: Bug #33054827.

You can try the latest mysql driver.

[michael-m-pro]

Hi @strongduanmu , According to what you said in your reply, I upgraded the mysql driver version of shardingSphere-Proxy and also upgraded the mysql driver version in my project. The problem of transactions not being rolled back normally has been resolved. Thank you.