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 2021/09/27 02:33:10 UTC
[GitHub] [shardingsphere] yunqian-c9 opened a new issue #12759: Failed to switch schema, please terminate current transaction.
yunqian-c9 opened a new issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759
使用**python sqlalchemy** ，在创建连接中，必须添加**autocommit**参数，否则在进行连接中便会报错：
**报错提示（4.1.0）：**
这个问题在 **sharding-porxy 4.1.1** or **sharding-porxy 5.0.0** 版本都会出现
另外，即使添加了这个参数，不用报错了，但是在 commit 之前使用 **session.flush()** 预提交数据时，便已经提交数据保存了，之后不管是 rollback还是 commit都无效：
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-943100711
Considering that this is an international community, I will close this non-standard issue first. Then create a new issue to track 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
[GitHub] [shardingsphere] tristaZero commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-928953601
Hi thanks for your feedback, we have fixed this bug on the master branch, which will be released next month.
--
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
[GitHub] [shardingsphere] yunqian-c9 commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
yunqian-c9 commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-938436717
您好，这次使用的是git克隆，编译后的源码：
在使用 **sqlalchemy** 的 **flush**方法预提交数据，还是直接提交到数据库中了，并不是在一个事务中，回退失效：
**code：**
**sql:**
**log:**
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-943098441
@yunqian-c9 Currently, distributed transactions do not support the use of `autocommit`. The control of transactions needs to be controlled by `begin`/`commit`. In the future, we will consider enhancing this feature.
--
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
[GitHub] [shardingsphere] strongduanmu closed issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759
--
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
[GitHub] [shardingsphere] strongduanmu edited a comment on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
strongduanmu edited a comment on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-939201512
@yunqian-c9 I have tested both jdbc and proxy command line, and the transaction is normal. Please check if there is a configuration problem with your `orm` framework.
--
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
[GitHub] [shardingsphere] yunqian-c9 commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
yunqian-c9 commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-938462550
@strongduanmu
**config-sharding.yaml：**
```yaml
schemaName: data_test
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3336/data_test?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
maintenanceIntervalMilliseconds: 30000
ds_1:
url: jdbc:mysql://127.0.0.1:3337/data_test?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
maintenanceIntervalMilliseconds: 30000
rules:
- !SHARDING
tables:
white_slice:
actualDataNodes: ds_${0..1}.white_slice_${0..3}
tableStrategy:
standard:
shardingColumn: callee
shardingAlgorithmName: white_inline
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
bindingTables:
- white_slice
defaultDatabaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${id % 2}
white_inline:
type: HASH_MOD
props:
sharding-count: 4
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
```
**server.yaml**
```yaml
scaling:
blockQueueSize: 10000
workerThread: 40
clusterAutoSwitchAlgorithm:
type: IDLE
props:
incremental-task-idle-minute-threshold: 30
dataConsistencyCheckAlgorithm:
type: DEFAULT
rules:
- !AUTHORITY
users:
- root@%:root
- sharding@:sharding
provider:
type: ALL_PRIVILEGES_PERMITTED
- !TRANSACTION
defaultType: XA
providerType: Atomikos
props:
max-connections-size-per-query: 3
kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
proxy-opentracing-enabled: false
proxy-hint-enabled: false
sql-show: true
check-table-metadata-enabled: false
lock-wait-timeout-milliseconds: 50000 # The maximum time to wait for a lock
show-process-list-enabled: false
# Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
# The default value is -1, which means set the minimum value for different JDBC drivers.
proxy-backend-query-fetch-size: -1
check-duplicate-table-enabled: false
sql-comment-parse-enabled: false
proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
# Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
# if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
proxy-backend-executor-suitable: OLAP
```
--
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
[GitHub] [shardingsphere] tristaZero edited a comment on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-928953601
Hi thanks for your feedback, we have fixed this bug on the master branch, which will be released next month. If you like, you can build and try the branch master. Thanks.
--
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
[GitHub] [shardingsphere] yunqian-c9 commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
yunqian-c9 commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-939736120
@strongduanmu 好的，辛苦了
--
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
[GitHub] [shardingsphere] yunqian-c9 commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
yunqian-c9 commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-939647924
@strongduanmu
我抓包查看了 **sqlalchemy** 在执行业务代码时，向 **mysql** 发送的语句。**sqlalchemy** 是通过设置 `autocommit = 0` 让当前会话保持事务运行的，在对接原生MYSQL时，是没问题的，全部语句都会在 **commit** 时提交，**rollback** 时回退。但是 **shardingsphere** 在碰到 **rollback** 时，之后的SQL语句即便还在同一个会话中，都会重新变为自动提交。以下为测试结果：
_I took a look at the statements that SQLAlchemy sent to mysql when executing business code. Sqlalchemy set autocommit = 0 to keep the current session running transactionally. This is not a problem when connecting to native MYSQL. All statements will be committed at COMMIT and rollback. However, when ShardingSphere encounters ROLLBACK, subsequent SQL statements will revert to auto-commit even if they are still in the same session. The test results are as follows:_
**sqlalchemy** 会在执行业务语句时，会在上下文中执行 **rollback** 语句，我尝试去找有没有相关配置可以关闭这个行为，但没有找到，除非重写相关代码：
_Sqlalchemy will execute a ROLLBACK statement in the context of a business statement. I tried to find a configuration to turn this behavior off, but could not find it unless I overwrote the code:_
**sqlalchemy 源码：**
_Sqlalchemy source_
**Mysql 测试 autocommit 结果：**
_Mysql tests autocommit results_
**shardingsphere 测试 autocommit 结果：**
_Shardingsphere tests autocommit results_
另外我这边尝试过在命令行测试 begin开启事务，但我这边必须要 commit 后才能看到插入的数据，跟您那边不一致，请问是配置问题么？
_In addition, I have tried to start the transaction with begin in the command line test, but I can only see the inserted data after COMMIT, which is inconsistent with yours. Is there a configuration problem?_
--
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
[GitHub] [shardingsphere] yunqian-c9 commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
yunqian-c9 commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-929979370
好的，谢谢~
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-938534359
@yunqian-c9 I tried to reproduce this problem, but the flush method was not found in the jdbc connection interface. You can look at the logic inside the flush method (your python code) to see if the commit operation has been performed, which makes it impossible to roll back.
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-939201512
@yunqian-c9 I have tested both jdbc and proxy command line, and the transaction function is normal. Please check if there is a configuration problem with your `orm` framework.
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-938442331
Hi @yunqian-c9, thank you for your information. Firstly, can you translate this issue to english? Then can you provide detailed proxy configuration? I will try to reproduce this problem in the master branch.
--
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
[GitHub] [shardingsphere] tristaZero edited a comment on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-928953601
Hi thanks for your feedback, we have fixed this bug on the master branch, which will be released next month. If you like, you can build and try the branch master. Thanks.
--
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
[GitHub] [shardingsphere] tristaZero commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-928953601
Hi thanks for your feedback, we have fixed this bug on the master branch, which will be released next month.
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-939694298
Hi @yunqian-c9, thank you for your detailed information, I will check it later.
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-938537520
Can you try setting `autoCommit` to 0?
--
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
[GitHub] [shardingsphere] yunqian-c9 commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
yunqian-c9 commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-939198932
@strongduanmu
**设置成 0 也是不行的：**
Setting it to 0 doesn't work either
**flash 方法，是 sqlalchemy 这个ORM框架自带的，执行时会向mysql提交插入语句，但语句是在事务中执行的，只有commit的时候才会真正做持久化操作。 查看 sqlalchemy 打印的语句，有向数据库发送 BEGIN 开启事务的。我觉得是代理没有执行这个操作：**
The flash method, which is the ORM framework of SQLAlchemy, will commit an insert statement to mysql, but the statement will be executed in a transaction, and will only be persisted at commit time. Look at the statements printed by SQLAlchemy that send BEGIN to the database to start the transaction. I think the agent did not perform this operation:
--
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
[GitHub] [shardingsphere] yunqian-c9 commented on issue #12759: Failed to switch schema, please terminate current transaction.
Posted by GitBox <gi...@apache.org>.
yunqian-c9 commented on issue #12759:
URL: https://github.com/apache/shardingsphere/issues/12759#issuecomment-929979370
好的，谢谢~
--
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