You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@shardingsphere.apache.org by "shuikan95 (via GitHub)" <gi...@apache.org> on 2023/03/30 13:40:55 UTC

[GitHub] [shardingsphere] shuikan95 opened a new issue, #24913: Not work with readwrite-splitting when using Spring Data JPA

shuikan95 opened a new issue, #24913:
URL: https://github.com/apache/shardingsphere/issues/24913

   Hello:
   ```
      First of all, I create a MGR on MySQL 8.0.25 what are they one Primary and two SECONDARY.
      I have a problem which is readwrite-splitting when using Spring Data JPA. 
      It's not working well and the version is 5.3.1 in ShardingSphere Proxy or ShardingSphere JDBC. 
      I write a simple Query method and no other codes to access database, but the logs show using a write datasource and there are the same results when accessing many times. 
      I have no idea why not using readDataSourceNames. I'am not sure this is a bug. Anybody can help me to find the reason, thanks.
   ```
      Some demo files as listed below:
   
    > build.gradle
   ```groovy
      buildscript {
       ext {
           lombokVersion = '1.18.24'
           springBootVersion = '2.6.11'
           springCloudVersion = '2021.0.5'
           springCloudAlibabaVersion = '2021.0.4.0'
           colaVersion = '4.3.1'
           shardingsphere = '5.3.1'
       }
   
       repositories {
           mavenLocal()
           maven { url 'https://maven.aliyun.com/repository/public' }
           mavenCentral()
       }
   
       dependencies {
           classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
       }
   
   }
   
   plugins {
       id 'org.springframework.boot' version '2.6.11'
       id 'io.spring.dependency-management' version '1.0.13.RELEASE'
   }
   
   allprojects {
   
       apply plugin: 'java'
       apply plugin: 'java-library'
       apply plugin: 'idea'
   
       group "com.lab.demo"
       version "1.0-SNAPSHOT"
   
       sourceCompatibility = 17
       targetCompatibility = 17
   
       tasks.withType(JavaCompile).configureEach {
           options.encoding = 'UTF-8'
       }
   
       configurations.configureEach {
           resolutionStrategy.cacheChangingModulesFor 0, 'seconds'
           exclude module: 'slf4j-log4j12'
       }
   
       repositories {
           mavenLocal()
           maven { url 'https://maven.aliyun.com/repository/public' }
           mavenCentral()
       }
   
   }
   
   subprojects {
   
       jar {
           enabled = true
       }
   
       apply plugin: 'io.spring.dependency-management'
       apply plugin: 'org.springframework.boot'
   
       dependencies {
   
           implementation 'org.projectlombok:lombok'
           annotationProcessor 'org.projectlombok:lombok'
           annotationProcessor 'org.springframework.boot:spring-boot-configuration-processor'
   
           testCompileOnly 'org.projectlombok:lombok'
           testAnnotationProcessor 'org.projectlombok:lombok'
           testAnnotationProcessor 'org.springframework.boot:spring-boot-configuration-processor'
           testImplementation('org.springframework.boot:spring-boot-starter-test')
   
           implementation "com.google.guava:guava"
           implementation "org.apache.commons:commons-lang3"
           implementation "org.apache.commons:commons-collections4"
           implementation "org.apache.commons:commons-pool2"
           implementation "org.apache.commons:commons-text"
           implementation "org.apache.commons:commons-math3"
           implementation "org.apache.commons:commons-compress"
           implementation "commons-codec:commons-codec"
   
           implementation 'com.alibaba.cola:cola-component-dto'
           implementation 'com.alibaba.cola:cola-component-exception'
           implementation 'com.alibaba.cola:cola-component-domain-starter'
   
           implementation 'com.alibaba:fastjson'
           implementation 'mysql:mysql-connector-java'
   
           implementation "org.springframework.boot:spring-boot-starter-validation"
           implementation "org.springframework.boot:spring-boot-starter-web"
           implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
           implementation "org.springframework.cloud:spring-cloud-starter-openfeign"
   
   //        implementation 'com.alibaba.nacos:nacos-client:2.2.1'
   //        implementation 'io.seata:seata-spring-boot-starter:1.6.1'
   //        implementation("com.alibaba.cloud:spring-cloud-starter-alibaba-seata") {
   //            exclude group: "io.seata", module: "seata-spring-boot-starter"
   //        }
   //        implementation 'com.alibaba.cloud:spring-cloud-alibaba-commons'
   
   //        implementation "org.apache.shardingsphere:shardingsphere-jdbc-core:${shardingsphere}"
       }
   
       dependencyManagement {
           imports {
               mavenBom("org.springframework.boot:spring-boot-dependencies:${springBootVersion}")
               mavenBom("org.springframework.cloud:spring-cloud-dependencies:${springCloudVersion}")
               mavenBom "com.alibaba.cloud:spring-cloud-alibaba-dependencies:${springCloudAlibabaVersion}"
           }
           dependencies {
               dependency 'javax.annotation:javax.annotation-api:1.3.2'
               dependency "org.projectlombok:lombok:${lombokVersion}"
               dependency group: 'com.google.guava', name: 'guava', version: '30.1.1-jre'
   
               dependency group: 'org.apache.commons', name: 'commons-lang3', version: '3.12.0'
               dependency group: 'org.apache.commons', name: 'commons-collections4', version: '4.4'
               dependency group: 'org.apache.commons', name: 'commons-pool2', version: '2.9.0'
               dependency group: 'org.apache.commons', name: 'commons-text', version: '1.9'
               dependency group: 'org.apache.commons', name: 'commons-math3', version: '3.6.1'
               dependency group: 'org.apache.commons', name: 'commons-compress', version: '1.20'
               dependency group: 'commons-codec', name: 'commons-codec', version: '1.15'
   
               dependency group: 'mysql', name: 'mysql-connector-java', version: '8.0.26'
   
               dependency "com.alibaba.cola:cola-component-dto:${colaVersion}"
               dependency "com.alibaba.cola:cola-component-exception:${colaVersion}"
               dependency "com.alibaba.cola:cola-component-domain-starter:${colaVersion}"
   
               dependency 'com.alibaba:fastjson:1.2.83'
   
               dependency 'org.yaml:snakeyaml:1.33'
           }
   
       }
   
   }
   
   test {
       useJUnitPlatform()
   
       jvmArgs('--add-opens', 'java.base/java.lang=ALL-UNNAMED')
       jvmArgs('--add-opens', 'java.base/java.lang.reflect=ALL-UNNAMED')
       jvmArgs('--add-opens', 'java.base/java.util=ALL-UNNAMED')
       jvmArgs('--add-opens', 'java.base/java.util.concurrent=ALL-UNNAMED')
       jvmArgs('--add-opens', 'java.base/java.util=ALL-UNNAMED')
       jvmArgs('--add-opens', 'java.base/java.math=ALL-UNNAMED')
       jvmArgs('--add-opens', 'java.base/java.io=ALL-UNNAMED')
       jvmArgs('--add-opens', 'java.rmi/sun.rmi.transport=ALL-UNNAMED')
   }
   ```
   > application.properties
   ```yml
   spring.application.name=account-service
   server.port=8083
   spring.datasource.url=jdbc:mysql://192.168.1.168:3333/proxy_account?useSSL=false&serverTimezone=UTC
   spring.datasource.username=sharding
   spring.datasource.password=Aa123456
   spring.jpa.show-sql=true
   spring.jpa.open-in-view=false
   ```
   
   > Account
   ```java
   import lombok.Data;
   import org.hibernate.annotations.DynamicInsert;
   import org.hibernate.annotations.DynamicUpdate;
   
   import javax.persistence.Entity;
   import javax.persistence.Id;
   import javax.persistence.Table;
   import java.math.BigDecimal;
   
   @Entity
   @Table(name = "account_tbl")
   @DynamicUpdate
   @DynamicInsert
   @Data
   public class Account {
   
       @Id
       private Long id;
       private String userId;
       private BigDecimal money;
   
   }
   ```
   
   > AccountDAO
   ```java
   import com.alibaba.demo.entity.Account;
   import org.springframework.data.jpa.repository.JpaRepository;
   
   public interface AccountDAO extends JpaRepository<Account, Long> {
   
       Account findByUserId(String userId);
   
   }
   ```
   
   > AccountService
   ```java
   import com.alibaba.cola.domain.ApplicationContextHelper;
   import com.alibaba.demo.entity.Account;
   import com.alibaba.demo.repository.AccountDAO;
   import com.zaxxer.hikari.HikariDataSource;
   import lombok.extern.slf4j.Slf4j;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.stereotype.Service;
   import org.springframework.transaction.annotation.Transactional;
   
   import javax.sql.DataSource;
   import java.math.BigDecimal;
   import java.util.List;
   
   @Slf4j
   @Service
   public class AccountService {
   
       private static final String ERROR_USER_ID = "1002";
       @Autowired
       private AccountDAO accountDAO;
   
       @Transactional(rollbackFor = Exception.class)
       public void debit(String userId, BigDecimal num) {
           Account account = accountDAO.findByUserId(userId);
           account.setMoney(account.getMoney().subtract(num));
           accountDAO.save(account);
   
           if (ERROR_USER_ID.equals(userId)) {
               throw new RuntimeException("account branch exception");
           }
       }
   
       public List<Account> accountList() {
   
           List<Account> all = accountDAO.findAll();
           HikariDataSource dataSource = (HikariDataSource) ApplicationContextHelper.getBean(DataSource.class);
           String jdbcUrl = dataSource.getJdbcUrl();
   
           log.info("The current datasource: {}, jdbcUrl: {}", dataSource, jdbcUrl);
           return all;
       }
   }
   ```
   
   > AccountController
   ```java
   import com.alibaba.cola.dto.MultiResponse;
   import com.alibaba.cola.dto.Response;
   import com.alibaba.demo.entity.Account;
   import com.alibaba.demo.service.AccountService;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.web.bind.annotation.GetMapping;
   import org.springframework.web.bind.annotation.RequestMapping;
   import org.springframework.web.bind.annotation.RequestParam;
   import org.springframework.web.bind.annotation.RestController;
   
   import java.math.BigDecimal;
   import java.util.List;
   
   @RestController
   public class AccountController {
   
       @Autowired
       private AccountService accountService;
   
       @RequestMapping("/debit")
       public Boolean debit(@RequestParam("userId") String userId, @RequestParam("money") BigDecimal money) {
           accountService.debit(userId, money);
           return true;
       }
   
       @GetMapping("/accountList")
       public Response accountList() {
           List<Account> accounts = accountService.accountList();
           return MultiResponse.of(accounts);
       }
   
   }
   ```
   
   > AccountApplication
   ```java
   import org.springframework.boot.SpringApplication;
   import org.springframework.boot.autoconfigure.SpringBootApplication;
   import org.springframework.cloud.openfeign.EnableFeignClients;
   import org.springframework.context.annotation.EnableAspectJAutoProxy;
   import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
   
   @SpringBootApplication
   @EnableFeignClients
   @EnableJpaRepositories
   @EnableAspectJAutoProxy(exposeProxy = true, proxyTargetClass = true)
   //@EnableTransactionManagement
   public class AccountApplication {
   
       public static void main(String[] args) {
           SpringApplication.run(AccountApplication.class, args);
       }
   }
   ```
   
   > config-readwrite-splitting-account.yaml in ShardingSphere Proxy conf dir
   ```yml
   databaseName: proxy_account
   
   dataSources:
     write_account:
       url: jdbc:mysql://192.168.1.168:3302/account?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
     read_account_1:
       url: jdbc:mysql://192.168.1.168:3303/account?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
     read_account_2:
       url: jdbc:mysql://192.168.1.168:3304/account?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1    
   
   rules:
   - !READWRITE_SPLITTING
     dataSources:
       readwrite_ds:
         staticStrategy:
           writeDataSourceName: write_account
           readDataSourceNames:
             - read_account_1
             - read_account_2
         loadBalancerName: random
     loadBalancers:
       random:
         type: ROUND_ROBIN
   ```
   
   > server.yaml in ShardingSphere Proxy conf dir
   ```yml
   authority:
     users:
       - user: root@%
         password: Aa123456
       - user: sharding
         password: Aa123456
     privilege:
       type: ALL_PERMITTED
   props:
     sql-show: true
     check-table-metadata-enabled: true
     proxy-backend-query-fetch-size: -1
     proxy-mysql-default-version: 8.0.26 # In the absence of schema name, the default version will be used.
     proxy-default-port: 3333 # Proxy default port.
   ```
   
   That's all.
   


-- 
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