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/01/07 09:29:01 UTC
[GitHub] [shardingsphere] xuup opened a new issue #8934: Read/write separation and sharding can write data to primary datasource, but cannot write into sharding tables in mybatis, but this is work in jdbc.
xuup opened a new issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934
#### Issue description
Read/write separation and sharding can write data to primary datasource, but cannot write into sharding tables in mybatis,
but this is work in jdbc.
I have push my code to github
https://github.com/xuup/sharding-replica-test.git (master)
#### version: sharding-jdbc master branch
#### project: ShardingSphere-JDBC
### Expected behavior
This is set in the configuration file
```properties
spring.shardingsphere.datasource.names=primary_ds_0,primary_ds_1,primary_ds_0_replica_0,primary_ds_0_replica_1,primary_ds_1_replica_0,primary_ds_1_replica_1
spring.shardingsphere.datasource.primary_ds_0.jdbc-url=jdbc:mysql://localhost:3306/demo_primary_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.primary_ds_0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.primary_ds_0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.primary_ds_0.username=root
spring.shardingsphere.datasource.primary_ds_0.password=123456
spring.shardingsphere.datasource.primary_ds_0_replica_0.jdbc-url=jdbc:mysql://localhost:3306/demo_primary_ds_0_replica_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.primary_ds_0_replica_0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.primary_ds_0_replica_0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.primary_ds_0_replica_0.username=root
spring.shardingsphere.datasource.primary_ds_0_replica_0.password=123456
spring.shardingsphere.datasource.primary_ds_0_replica_1.jdbc-url=jdbc:mysql://localhost:3306/demo_primary_ds_0_replica_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.primary_ds_0_replica_1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.primary_ds_0_replica_1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.primary_ds_0_replica_1.username=root
spring.shardingsphere.datasource.primary_ds_0_replica_1.password=123456
spring.shardingsphere.datasource.primary_ds_1.jdbc-url=jdbc:mysql://localhost:3306/demo_primary_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.primary_ds_1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.primary_ds_1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.primary_ds_1.username=root
spring.shardingsphere.datasource.primary_ds_1.password=123456
spring.shardingsphere.datasource.primary_ds_1_replica_0.jdbc-url=jdbc:mysql://localhost:3306/demo_primary_ds_1_replica_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.primary_ds_1_replica_0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.primary_ds_1_replica_0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.primary_ds_1_replica_0.username=root
spring.shardingsphere.datasource.primary_ds_1_replica_0.password=123456
spring.shardingsphere.datasource.primary_ds_1_replica_1.jdbc-url=jdbc:mysql://localhost:3306/demo_primary_ds_1_replica_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.primary_ds_1_replica_1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.primary_ds_1_replica_1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.primary_ds_1_replica_1.username=root
spring.shardingsphere.datasource.primary_ds_1_replica_1.password=123456
spring.shardingsphere.rules.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.rules.sharding.default-database-strategy.inline.algorithm-expression=ds_$->{user_id % 2}
spring.shardingsphere.rules.sharding.binding-tables=t_order,t_order_item
spring.shardingsphere.rules.sharding.broadcast-tables=t_address
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ds_$->{0..1}.t_order_$->{0..1}
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2}
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=snowflake
spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=ds_$->{0..1}.t_order_item_$->{0..1}
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.column=order_item_id
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.key-generator-name=snowflake
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=123
spring.shardingsphere.rules.replica-query.data-sources.ds_0.primary-data-source-name=primary_ds_0
spring.shardingsphere.rules.replica-query.data-sources.ds_0.replica-data-source-names=primary_ds_0_replica_0, primary_ds_0_replica_1
spring.shardingsphere.rules.replica-query.data-sources.ds_1.primary-data-source-name=primary_ds_1
spring.shardingsphere.rules.replica-query.data-sources.ds_1.replica-data-source-names=primary_ds_1_replica_0, primary_ds_1_replica_1
spring.shardingsphere.props.sql.show=true
```
Here is test code
```java
@Test
public void insert(){
Order order = new Order();
order.setOrderId(21L);
order.setUserId(12);
order.setStatus("sharding test");
orderDao.insert(order);
}
```
I expect this data could insert into ds0(primary_ds_0), insert table is t_order_1
### Actual behavior
The data insert into primary_ds_0.t_order_0,primary_ds_0.t_order_1,rimary_ds_1.t_order_0,primary_ds_1.t_order_1. I think sharding is not work .
```xml
2021-01-07 17:20:24.988 INFO 4736 --- [ main] com.xup.example.ReplicaTest : Started ReplicaTest in 8.42 seconds (JVM running for 9.859)
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2565a7d0] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@5f409872] will not be managed by Spring
==> Preparing: INSERT INTO t_order (order_id, user_id, address_id, status) VALUES (?, ?, ?,?);
==> Parameters: 21(Long), 12(Integer), 12(Integer), sharding test(String)
<== Updates: 4
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2565a7d0]
```
I use jdbc to achieve the above functions, the result is right
```java
@Test
public void testInsert() throws IOException, SQLException {
String sql = "insert into t_order values (134,2,2,'insert test')";
DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource(getFile("/application-replica.yaml"));
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
statement.executeUpdate();
statement.close();
connection.close();
}
```
excute result:
```java
17:25:42.930 [main] INFO org.apache.shardingsphere.infra.context.metadata.MetaDataContextsBuilder - Load meta data for schema logic_db finished, cost 90 milliseconds.
17:25:45.326 [main] INFO ShardingSphere-SQL - Logic SQL: insert into t_order values (135,2,2,'insert test')
17:25:45.327 [main] INFO ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
17:25:45.327 [main] INFO ShardingSphere-SQL - Actual SQL: primary_ds_0 ::: insert into t_order_1 values (135, 2, 2, 'insert test')
```
Please take a hard look at the above questions， Thx.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-756737832
Is it the same issue with ##8952?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] xuup commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
xuup commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-759151018
#### Explain the problem further
I use mybatis + sharding-jdbc to achieve sharding and replica. The application file is the same as above.
Sharding logic and read-write separation logic is:
sharding:
user_id % 2 ==0 ---> ds_0
user_id % 2 ==1 ---> ds_1
replica:
insert : primary_ds_0 primary_ds_1
read: primary_ds_0_replica_0, primary_ds_0_replica_1, primary_ds_1_replica_0, primary_ds_1_replica_1
```xml
spring.shardingsphere.rules.replica-query.data-sources.ds_0.primary-data-source-name=primary_ds_0
spring.shardingsphere.rules.replica-query.data-sources.ds_0.replica-data-source-names=primary_ds_0_replica_0, primary_ds_0_replica_1
spring.shardingsphere.rules.replica-query.data-sources.ds_1.primary-data-source-name=primary_ds_1
spring.shardingsphere.rules.replica-query.data-sources.ds_1.replica-data-source-names=primary_ds_1_replica_0, primary_ds_1_replica_1
```
I execute test case, condition column is user_id and user_id=2, sql should be route ds_0, and read in primary_ds_0_replica_0 or primary_ds_0_replica_1.
```java
@Test
public void selectByCondition(){
Order order = new Order();
order.setUserId(2); //ds0
List<Order> lst = orderDao.selectByCondition(order);
System.out.println("hello" + lst.size());
}
```
but in fact , there are two results from ds_0 and ds_1 like this
```java
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@72b6832e] will not be managed by Spring
==> Preparing: SELECT * FROM t_order where user_id = ?;
==> Parameters: 2(Integer)
<== Columns: order_id, user_id, address_id, status
<== Row: 553170939157131264, 2, 2, INSERT_TEST_ds0
<== Row: 553170939157131264, 2, 2, INSERT_TEST_ds1
<== Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4337afd]
```
After my verification，replica function is work , it can read from primary database and read from replica databases, but sharding cannot work normal, it shoule be route to ds_0 but be routed to ds_0 and ds_1.
pom.xml
```xml
<shardingsphere.version>5.0.0-alpha</shardingsphere.version>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
```
case git: https://github.com/xuup/mybatis-sharding.git (master)
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] AI1186780944 commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
AI1186780944 commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-772350379
@xuup This problem should have nothing to do with sharding not working.It causes the transaction not to take effect.The scenarios I use are read-write separation and data desensitization.I just see that your example has the same problem as mine. Let's ask if you have solved it.And I have solved some of the above problems. The container injection of transaction manager is completed.The results are as follows：
`Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@70f29b72]
2021-02-03 16:32:15.247 INFO [trading-order-extend,,,] 9800 --- [ Test worker] ShardingSphere-SQL : Logic SQL: SELECT count(id) FROM order_service_obj
2021-02-03 16:32:15.247 INFO [trading-order-extend,,,] 9800 --- [ Test worker] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty)
2021-02-03 16:32:15.248 INFO [trading-order-extend,,,] 9800 --- [ Test worker] ShardingSphere-SQL : Actual SQL: replica-ds ::: SELECT count(id) FROM order_service_obj
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@70f29b72]
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@70f29b72]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@70f29b72]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@70f29b72]`。I hope it can help you to solve our common problems.
My solution is as follows：
`private SpringManagedTransactionFactory getTransactionFactory(DataSource dataSource) {
SpringManagedTransactionFactory transactionFactory = new SpringManagedTransactionFactory();
transactionFactory.newTransaction(dataSource, TransactionIsolationLevel.REPEATABLE_READ,true);
return transactionFactory;
}`
The transaction manager generated by method ‘getTransactionFactory’ will be assigned to the properties of the mybatissqlsessionfactorybean object.This operation can solve the "JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@41f00c4b] will not be managed by Spring" problem.
And @ transactional (rollback for) to the implementation class of the service= Exception.class ）It can solve the “SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6049f52d] was not registered for synchronization because synchronization is not active” problem。
But I don't know why it keeps creating and destroying sqlsessions when executing SQL.It may be related to multiple data sources. Maybe so. Hope to get the author's solution.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-756737832
Is it the same issue with ##8952?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] AI1186780944 edited a comment on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
AI1186780944 edited a comment on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-772251882
@xuup @terrymanu Hi,man.How do you solve the problem as shown in the figure?
the problem:
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6049f52d] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@41f00c4b] will not be managed by Spring
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6049f52d]
When this problem occurs, don't you find that your transaction is invalid?
I'm using shardingsphere-jdbc-core-spring-boot-starter 5.0.0-alpha.And The ORM framework is MyBatis-Plus.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] xuup commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
xuup commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-756749843
Hi, #8952 base on mysql , and there have no relation to #8934
------------------ 原始邮件 ------------------
发件人: "apache/shardingsphere" <notifications@github.com>;
发送时间: 2021年1月8日(星期五) 晚上8:46
收件人: "apache/shardingsphere"<shardingsphere@noreply.github.com>;
抄送: "许鹏"<xupeng0727@foxmail.com>;"Author"<author@noreply.github.com>;
主题: Re: [apache/shardingsphere] sharding-jdbc with mybatis just replica , but sharding not work (#8934)
Is it the same issue with ##8952?
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub, or unsubscribe.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] AI1186780944 commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
AI1186780944 commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-772251882
@xuup @terrymanu Hi,man.How do you solve the problem as shown in the figure?
When this problem occurs, don't you find that your transaction is invalid?
I'm using shardingsphere-jdbc-core-spring-boot-starter 5.0.0-alpha.And The ORM framework is MyBatis-Plus.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-952851990
From https://github.com/xuup/mybatis-sharding, `shardingsphere.version` is `5.0.0-alpha`.
--
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] xuup commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
xuup commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-772366130
Thank you for your detailed solution, I will solved the problem that "JDBC Connection will not be managed by Spring" according your solution.
We could give developers a little time for 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] xuup commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
xuup commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-756749843
Hi, #8952 base on mysql , and there have no relation to #8934
------------------ 原始邮件 ------------------
发件人: "apache/shardingsphere" <notifications@github.com>;
发送时间: 2021年1月8日(星期五) 晚上8:46
收件人: "apache/shardingsphere"<shardingsphere@noreply.github.com>;
抄送: "许鹏"<xupeng0727@foxmail.com>;"Author"<author@noreply.github.com>;
主题: Re: [apache/shardingsphere] sharding-jdbc with mybatis just replica , but sharding not work (#8934)
Is it the same issue with ##8952?
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub, or unsubscribe.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] xuup commented on issue #8934: sharding-jdbc with mybatis just replica , but sharding not work
Posted by GitBox <gi...@apache.org>.
xuup commented on issue #8934:
URL: https://github.com/apache/shardingsphere/issues/8934#issuecomment-772328589
I have noticed the problem you mentioned before.
Do you think sharding not work has relations with it?
thanks a lot.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org