You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "liulipengju (via GitHub)" <gi...@apache.org> on 2023/04/27 09:46:24 UTC
[GitHub] [shardingsphere] liulipengju opened a new issue, #25364: readwrite-splitting is not working
liulipengju opened a new issue, #25364:
URL: https://github.com/apache/shardingsphere/issues/25364
## Question
**For English only**, other languages will not accept.
Before asking a question, make sure you have:
- Googled your question.
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
my springboot config :
spring:
shardingsphere:
datasource:
names: write-common, write-common-read-0, write-common-read-1, write-run-0, write-run-0-read-0, write-run-0-read-1, write-run-1, write-run-1-read-0, write-run-1-read-1, ds-history-0,ds-history-1
write-common:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql:192.168.0.136:3310/fpc_ec_m?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true
username: root
password: 123456
max-active: 16
write-common-read-0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql:192.168.0.135:3311/fpc_ec_m?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true
username: root
password: 123456
max-active: 16
write-common-read-1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql:192.168.0.135:3312/fpc_ec_m?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true
username: root
password: 123456
max-active: 16
write-run-0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql:192.168.0.136:3410/fpc_er_m_0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true
username: root
password: 123456
max-active: 16
write-run-0-read-0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql:192.168.0.135:3411/fpc_er_m_0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true&autoReconnect=true
username: root
password: 123456
max-active: 16
write-run-0-read-1:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql:192.168.0.135:3412/fpc_er_m_0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true&autoReconnect=true
username: root
password: 123456
max-active: 16
write-run-1:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql:192.168.0.136:3510/fpc_er_m_1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true
username: root
password: 123456
max-active: 16
write-run-1-read-0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql:192.168.0.135:3511/fpc_er_m_1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true&autoReconnect=true
username: root
password: 123456
max-active: 16
write-run-1-read-1:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql:192.168.0.135:3512/fpc_er_m_1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true&autoReconnect=true
username: root
password: 123456
max-active: 16
ds-history-0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql:192.168.0.136:3610/fpc_eh_m_0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true
username: root
password: 123456
max-active: 16
ds-history-1:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql:192.168.0.136:3710/fpc_eh_m_1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true
username: root
password: 123456
max-active: 16
rules:
readwrite-splitting:
data-sources:
ds-common:
props:
read-data-source-names: write-common-read-0, write-common-read-1
write-data-source-name: write-common
type: Static
ds-run-0:
props:
read-data-source-names: write-run-0-read-0, write-run-0-read-1
write-data-source-name: write-run-0
type: Static
ds-run-1:
props:
read-data-source-names: write-run-1-read-0, write-run-1-read-1
write-data-source-name: write-run-1
type: Static
load-balancers:
round_robin:
type: ROUND_ROBIN
encrypt:
tables:
t_user:
columns:
phone:
cipher-column: phone
encryptor-name: common-encryptor
t_student:
columns:
id_card:
cipher-column: id_card
encryptor-name: common-encryptor
t_school:
columns:
school_code:
cipher-column: school_code
encryptor-name: common-encryptor
sharding:
sharding-algorithms:
database-run-inline:
props:
algorithm-expression: ds-run-$->{db_location}
type: INLINE
database-history-inline:
props:
algorithm-expression: ds-history-$->{db_location}
type: INLINE
common-log-table-inline:
props:
algorithm-expression: t_log_$->{id % 9}
type: INLINE
student-item-result-table-inline:
props:
algorithm-expression: t_student_item_result_$->{table_location}
type: INLINE
student-item-result-history-table-inline:
props:
algorithm-expression: t_student_item_result_history_$->{table_location}
type: INLINE
tables:
t_log:
actual-data-nodes: ds-common.t_log_$->{0..8}
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: common-log-table-inline
t_user:
actual-data-nodes: ds-common.t_user
t_student_item_result:
actual-data-nodes: ds-run-$->{0..1}.t_student_item_result_$->{0..9}
database-strategy:
standard:
sharding-column: db_location
sharding-algorithm-name: database-run-inline
table-strategy:
standard:
sharding-column: table_location
sharding-algorithm-name: student-item-result-table-inline
t_school:
actual-data-nodes: ds-run-$->{0..1}.t_school
database-strategy:
standard:
sharding-column: db_location
sharding-algorithm-name: database-run-inline
t_student:
actual-data-nodes: ds-run-$->{0..1}.t_student
database-strategy:
standard:
sharding-column: db_location
sharding-algorithm-name: database-run-inline
t_student_item_result_history:
actual-data-nodes: ds-history-$->{0..1}.t_student_item_result_history_$->{0..9}
database-strategy:
standard:
sharding-column: db_location
sharding-algorithm-name: database-history-inline
table-strategy:
standard:
sharding-column: table_location
sharding-algorithm-name: student-item-result-history-table-inline
run log:
![image](https://user-images.githubusercontent.com/10510950/234825308-2c374cd6-de7c-49e1-9dbb-3ef18e1aeb02.png)
log mean: read write is not working, it is only work on write db, select is not on slave;
my sharding version: 5.1.2
--
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
[GitHub] [shardingsphere] liulipengju commented on issue #25364: readwrite-splitting is not working
Posted by "liulipengju (via GitHub)" <gi...@apache.org>.
liulipengju commented on issue #25364:
URL: https://github.com/apache/shardingsphere/issues/25364#issuecomment-1527629038
Okay, thank you very much for your reply. I will read the rules
--
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] liulipengju closed issue #25364: readwrite-splitting is not working
Posted by "liulipengju (via GitHub)" <gi...@apache.org>.
liulipengju closed issue #25364: readwrite-splitting is not working
URL: https://github.com/apache/shardingsphere/issues/25364
--
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] RaigorJiang commented on issue #25364: readwrite-splitting is not working
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #25364:
URL: https://github.com/apache/shardingsphere/issues/25364#issuecomment-1527084343
> yes, call method added @transactional(rollbackFor = Exception.class)
That's why, the SQL in the transaction will be routed to the primary database to ensure consistent reading and writing.
You can refer to https://shardingsphere.apache.org/document/current/en/features/readwrite-splitting/limitations/
--
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] liulipengju commented on issue #25364: readwrite-splitting is not working
Posted by "liulipengju (via GitHub)" <gi...@apache.org>.
liulipengju commented on issue #25364:
URL: https://github.com/apache/shardingsphere/issues/25364#issuecomment-1526820663
my config images
image1
![image](https://user-images.githubusercontent.com/10510950/235025073-b20fb117-2d6c-4689-8d90-63b074c86b48.png)
image2
![image](https://user-images.githubusercontent.com/10510950/235025132-a198579b-09af-4445-9d98-9def0b701f10.png)
image3
![image](https://user-images.githubusercontent.com/10510950/235025199-8cdd511e-3d2f-4a75-84be-25f371dd979b.png)
image4
![image](https://user-images.githubusercontent.com/10510950/235025257-1055951e-386b-46c0-9ae4-848a5bf2384f.png)
![image](https://user-images.githubusercontent.com/10510950/235025292-e184d37f-0a35-48ca-a82a-9bc09da04b93.png)
I have omitted some tables and only added some representative ones. I confirm that my sub database configuration is correct because it is normal to not use read write separation
--
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] RaigorJiang commented on issue #25364: readwrite-splitting is not working
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #25364:
URL: https://github.com/apache/shardingsphere/issues/25364#issuecomment-1525471464
@liulipengju
Is your SELECT SQL in a transaction?
--
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] liulipengju commented on issue #25364: readwrite-splitting is not working
Posted by "liulipengju (via GitHub)" <gi...@apache.org>.
liulipengju commented on issue #25364:
URL: https://github.com/apache/shardingsphere/issues/25364#issuecomment-1526813594
yes, call method added @Transactional(rollbackFor = Exception.class)
--
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