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: [email protected]
