GuangGuo commented on issue #28408:
URL:
https://github.com/apache/shardingsphere/issues/28408#issuecomment-1713725506
Steps to reproduce the behavior:
1. a mysql cluster with a master and two slaves, work well.
mysql> show tables;
+------------------+
| Tables_in_myblog |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | zhang3 | 19 |
| 2 | li4 | 20 |
| 3 | wang5 | 21 |
+----+--------+-----+
2. use sharding-proxy to readwrite splitting
config_readwrite_splitting.yaml
databaseName: myblog_rs
dataSources:
write_ds:
url:
jdbc:mysql://172.31.194.254:3307/myblog?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
read_ds_0:
url:
jdbc:mysql://172.31.194.254:3308/myblog?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
read_ds_1:
url:
jdbc:mysql://172.31.194.254:3309/myblog?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
!READWRITE_SPLITTING
dataSources:
readwrite_ds:
writeDataSourceName: write_ds
readDataSourceNames:
- read_ds_0
- read_ds_1
loadBalancerName: random
loadBalancers:
random:
type: RANDOM
server.yaml
authority:
users:
user: root@%
password: 123456
user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
props:
sql-show: true
3. docker start the sharding-proxy container
docker run -d --name sharding-proxy
-v /home/shardingsphere-proxy/conf:/opt/shardingsphere-proxy/conf
-v /home/shardingsphere-proxy/ext-lib:/opt/shardingsphere-proxy/ext-lib
-p13307:3307 apache/shardingsphere-proxy:latest
docker logs -f sharding-proxy
we find java version: java17, full_version=17.0.8.1,
full_path=/opt/java/openjdk/bin/java
The classpath is
/opt/shardingsphere-proxy/conf:/opt/shardingsphere-proxy/conf:.:/opt/shardingsphere-proxy/lib/*:/opt/shardingsphere-proxy/ext-lib/*
main class org.apache.shardingsphere.proxy.Bootstrap -1
/opt/shardingsphere-proxy/conf 0.0.0.0 false
[INFO ] 2023-09-11 07:56:43.826 [main]
o.a.s.d.p.c.l.PipelineContextManagerLifecycleListener - mode type is not
Cluster, mode type='Standalone', ignore
[INFO ] 2023-09-11 07:56:43.849 [main] o.a.s.p.v.ShardingSphereProxyVersion
- Database name is `MySQL`, version is `8.1.0`, database name is `myblog_rs`
[INFO ] 2023-09-11 07:56:43.851 [main] o.a.s.p.frontend.ssl.ProxySSLContext
- Proxy frontend SSL/TLS is not enabled.
[INFO ] 2023-09-11 07:56:43.941 [main] o.a.s.p.frontend.ShardingSphereProxy
- ShardingSphere-Proxy Standalone mode started successfully
4. use mysql to login sharding-proxy
mysql -uroot -h IP address -P13307 -p
before everything, execute `show tables;`, you'll find the sql really
executed in the mysql servers
[INFO ] 2023-09-11 08:04:53.736 [ShardingSphere-Command-2]
ShardingSphere-SQL - Logic SQL: show tables
[INFO ] 2023-09-11 08:04:53.736 [ShardingSphere-Command-2]
ShardingSphere-SQL - Actual SQL: write_ds ::: show tables
[INFO ] 2023-09-11 08:04:53.736 [ShardingSphere-Command-2]
ShardingSphere-SQL - Actual SQL: read_ds_0 ::: show tables
[INFO ] 2023-09-11 08:04:53.736 [ShardingSphere-Command-2]
ShardingSphere-SQL - Actual SQL: read_ds_1 ::: show tables
and you will see the table, it should be myblog_rs:
mysql> show tables;
+------------------+
| Tables_in_myblog |
+------------------+
| user |
+------------------+
1 row in set (0.01 sec)
and then you execute select, you got an error:
mysql> select * from myblog.user;
ERROR 10007 (42S02): Table or view `user` does not exist.
mysql> select * from myblog_rs.user;
ERROR 10007 (42S02): Table or view `user` does not exist.
there is no user table in database myblog_rs nor database myblog;
mysql> show databases;
+--------------------+
| schema_name |
+--------------------+
| information_schema |
| myblog_rs |
| mysql |
| performance_schema |
| shardingsphere |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use myblog_rs;
Database changed
mysql> show tables;
Empty set (0.01 sec)
5. but you still can see the database name, and when you execute select 1,
and it really been executed by the mysql servers.
mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
[INFO ] 2023-09-11 11:51:47.854 [ShardingSphere-Command-8]
ShardingSphere-SQL - Logic SQL: select 1
[INFO ] 2023-09-11 11:51:47.854 [ShardingSphere-Command-8]
ShardingSphere-SQL - Actual SQL: read_ds_1 ::: select 1
so, readwrite splitting really works, but the table just disapper, I've no
idea what happened.
thx for you watching here.
--
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]
