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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
