You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "zzbl1145141919 (via GitHub)" <gi...@apache.org> on 2023/12/22 08:10:50 UTC
[I] sharding proxy 's local transaction did't work.... [shardingsphere]
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] sharding proxy 's local transaction did't work.... [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #29505:
URL: https://github.com/apache/shardingsphere/issues/29505#issuecomment-1867584515
Hi @zzbl1145141919
Have you start the transaction through `begin;`?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] sharding proxy 's local transaction did't work.... [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29505:
URL: https://github.com/apache/shardingsphere/issues/29505#issuecomment-1908833625
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] sharding proxy 's local transaction did't work.... [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29505:
URL: https://github.com/apache/shardingsphere/issues/29505#issuecomment-1963045974
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] sharding proxy 's local transaction did't work.... [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #29505:
URL: https://github.com/apache/shardingsphere/issues/29505#issuecomment-1910469023
Sorry I'm late, I will check 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] sharding proxy 's local transaction did't work.... [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29505:
URL: https://github.com/apache/shardingsphere/issues/29505#issuecomment-3657967858
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] sharding proxy 's local transaction did't work.... [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #29505:
URL: https://github.com/apache/shardingsphere/issues/29505#issuecomment-3619410866
• Issue Understanding
- In your follow-up you showed START TRANSACTION; update ...; ROLLBACK; to indicate a transaction was opened, yet after rollback the update still appears. Scenario: Proxy 5.4.1 + MySQL 8.0.33, sharded table user routed by age to multiple
physical tables, expecting a unique-key conflict to rollback everything, but seeing partial success.
Root Cause
- The script alone cannot confirm the transaction actually ran in the same session/same backend connection. Many GUI/clients run each statement in a new connection or keep autocommit=1, so START TRANSACTION and subsequent statements may not
share the same physical connection, or earlier updates were auto-committed and ROLLBACK cannot undo them.
- Proxy does not auto-wrap split physical SQLs into a transaction; you must disable autocommit and explicitly start a transaction in the same session so all routed SQLs run on the same backend connection and MySQL can guarantee atomicity.
Official transaction docs: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/feature/transaction/
Analysis
- We still cannot rule out “transaction not executed in the same session” as the main possibility: the screenshots don’t show @@autocommit, nor sql-show frontend/backend connectionIds, so connection reuse is unclear.
- With autocommit=1 or multi-connection execution, the update routed to t_order_1 would be auto-committed; the later unique-key conflict on t_order_2 raises an error but cannot roll back what has already committed—matching the observed
behavior.
Conclusion
- With current info this is more likely a transaction/session usage issue than a confirmed Proxy defect. Please help confirm by providing:
1. A single-session (preferably mysql CLI) transcript with outputs: select @@autocommit; select @@tx_isolation; START TRANSACTION; update ...; ROLLBACK;
2. Matching sql-show logs showing frontend/backend connectionIds to verify they stay the same
3. Whether your client/GUI uses auto-commit or multiple connections
- If you can still reproduce with a single session, autocommit=0, and consistent connectionIds, please share the above details and we’ll continue to investigate.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] sharding proxy 's local transaction did't work.... [shardingsphere]
Posted by "zzbl1145141919 (via GitHub)" <gi...@apache.org>.
zzbl1145141919 commented on issue #29505:
URL: https://github.com/apache/shardingsphere/issues/29505#issuecomment-1868659144
> Hi @zzbl1145141919 Have you start the transaction through `begin;`?
HI i'm sure i use transaction:
this is original table 'user':
this is my script:
START TRANSACTION;
update user set name= 'yeey' where id = 1;
rollback;
since we rollback, we should't see ANY "yeey" in user table;
but....
<img width="305" alt="image" src="https://github.com/apache/shardingsphere/assets/133546527/5297cac1-cad1-4a28-8c96-f899af85ac63">
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] sharding proxy 's local transaction did't work.... [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #29505: sharding proxy 's local transaction did't work....
URL: https://github.com/apache/shardingsphere/issues/29505
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org