sharding proxy 's local transaction did't work....

zzbl1145141919 commented 8 months ago

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:

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.torder${0..2} #分表规则 tableStrategy: standard: shardingColumn: age #分片键 shardingAlgorithmName: asdf #分表算法 bindingTables:
- user shardingAlgorithms: #分表算法 asdf: type: INLINE props: algorithm-expression: torder${age % 3}