zzbl1145141919 opened a new issue, #29505:
URL: https://github.com/apache/shardingsphere/issues/29505
sharding proxy 's local transaction did't work....
Bug Report
Which version of ShardingSphere did you use?
Sharding-proxy 5.4.1 , the version of mysql is 8.0.33
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
Sharding-proxy
Expected behavior
we have three basic tables: sharding key is age
CREATE TABLE `t_order_0` (
`id` int NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_1` (
`id` int NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_2` (
`id` int NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
notice that t_order_2 has UNIQUE KEY (`name`)
we input some data into there tables :
INSERT INTO `p`.`t_order_1`
(`id`,
`name`,
`age`)
VALUES
(1,
'a',
1);
INSERT INTO `p`.`t_order_2`
(`id`,
`name`,
`age`)
VALUES
(2,
'b',
2);
INSERT INTO `p`.`t_order_2`
(`id`,
`name`,
`age`)
VALUES
(3,
'bb',
5);
then we exec this sql :
update user set name = 'c' where age in (1,2,5)
Actual behavior
I'm expecting: due to the UNIQUE KEY on t_order_2, this whole update will
fail
Actually, the operation to the t_order_2 failed, but the t_order_1
succeccfully updated:
<img width="425" alt="image"
src="https://github.com/apache/shardingsphere/assets/133546527/db970cfe-860a-4c53-a746-0eeab1f2982f";>
you can see this line updated without having any rollback
you can see these updates are all executed by the same connection:
Settings:
server.yaml:
mode:
type: Standalone # 单机模式
authority:
users:
- user: sharding
password: sharding
transaction:
defaultType: LOCAL
props:
sql-show: true
max-connections-size-per-query: 1
proxy-mysql-default-version: 8.0.23
proxy-transaction-enabled: true
config-sharding.yaml:
schemaName: hmms
dataSources:
hmms:
url: jdbc:mysql://127.0.0.1:8306/p?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
user: #表名
actualDataNodes: hmms.t_order_${0..2} #分表规则
tableStrategy:
standard:
shardingColumn: age #分片键
shardingAlgorithmName: asdf #分表算法
bindingTables:
- user
shardingAlgorithms: #分表算法
asdf:
type: INLINE
props:
algorithm-expression: t_order_${age % 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.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
