You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/11/10 08:46:44 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #22058: DROP READWRITE_SPLITTING RULE failed
peilinqian opened a new issue, #22058:
URL: https://github.com/apache/shardingsphere/issues/22058
### Which version of ShardingSphere did you use?
we find java version: java8, full_version=1.8.0_282, full_path=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.282.b08-1.el7_9.x86_64/bin/java
ShardingSphere-5.2.1-SNAPSHOT
Commit ID: dirty-bcde6f374c4a3a025173fbc9f6d0e66ed686a042
Commit Message: Fix fetch forward all error in openGauss(https://github.com/apache/shardingsphere/issues/21421) (https://github.com/apache/shardingsphere/pull/21471)
Branch: https://github.com/apache/shardingsphere/commit/bcde6f374c4a3a025173fbc9f6d0e66ed686a042
Build time: 2022-10-11T19:13:56+0800
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
DROP READWRITE_SPLITTING RULE successfully
### Actual behavior
DROP READWRITE_SPLITTING RULE failed
```
split_db=> create database test_split
split_db-> ;
CREATE DATABASE
split_db=>
split_db=> \c test_split
Password for user sharding:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "test_split" as user "sharding".
test_split=> add resource resource_0 (
url="jdbc:opengauss://10.29.180.204:15000/test_db?batchmode=on",
user="tpccuser",
password="Test@123",
properties("maximumpoolsize"="10","idletimeout"="30000")
),resource_1 (
url="jdbc:opengauss://10.243.194.134:15000/test_db?batchmode=on",
user="tpccuser",
password="Test@123",
properties("maximumpoolsize"="10","idletimeout"="30000")
test_split(> url="jdbc:opengauss://10.29.180.204:15000/test_db?batchmode=on",
test_split(> user="tpccuser",
test_split(> password="Test@123",
test_split(> properties("maximumpoolsize"="10","idletimeout"="30000")
test_split(> ),resource_1 (
test_split(> url="jdbc:opengauss://10.243.194.134:15000/test_db?batchmode=on",
test_split(> user="tpccuser",
test_split(> password="Test@123",
test_split(> properties("maximumpoolsize"="10","idletimeout"="30000")
test_split(> ),resource_2 (
test_split(> url="jdbc:opengauss://7.212.123.28:15000/test_db?batchmode=on",
test_split(> user="tpccuser",
test_split(> password="Test@123",
test_split(> properties("maximumpoolsize"="10","idletimeout"="30000")
test_split(> );
SUCCESS
test_split=>
test_split=> CREATE READWRITE_SPLITTING RULE readwrite_ds (
test_split(> WRITE_RESOURCE=resource_0,
test_split(> READ_RESOURCES(resource_1,resource_2),
test_split(> TYPE(NAME="random"));
SUCCESS
test_split=> drop READWRITE_SPLITTING RULE readwrite_ds;
ERROR: Readwrite splitting rules `[readwrite_ds]` in database `test_split` are still in used.
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```
create database test_split；
\c test_split
add resource resource_0 (
url="jdbc:opengauss://10.29.180.204:15000/test_db?batchmode=on",
user="tpccuser",
password="Test@123",
properties("maximumpoolsize"="10","idletimeout"="30000")
),resource_1 (
url="jdbc:opengauss://10.243.194.134:15000/test_db?batchmode=on",
user="tpccuser",
password="Test@123",
properties("maximumpoolsize"="10","idletimeout"="30000")
),resource_2 (
url="jdbc:opengauss://7.212.123.28:15000/test_db?batchmode=on",
user="tpccuser",
password="Test@123",
properties("maximumpoolsize"="10","idletimeout"="30000")
);
CREATE READWRITE_SPLITTING RULE readwrite_ds (
WRITE_RESOURCE=resource_0,
READ_RESOURCES(resource_1,resource_2),
TYPE(NAME="random"));
drop READWRITE_SPLITTING RULE readwrite_ds;
```
### Example codes for reproduce this issue (such as a github link).
--
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] RaigorJiang commented on issue #22058: DROP READWRITE_SPLITTING RULE failed
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #22058:
URL: https://github.com/apache/shardingsphere/issues/22058#issuecomment-1310145360
Hi @peilinqian
Thanks for your feedback, and I have reproduced the problem from master branch.
We get this error message because a single table already exists in the resource registered, and after creating the `READWRITE_SPLITTING RULE`, the single table will use the aggregate data source `readwrite_ds`.
Therefore, if there is no table in the registered resource, this `readwrite_ds` can be dropped.
### The latest reproduction process:
```sql
REGISTER STORAGE UNIT write_ds (
HOST="127.0.0.1",
PORT=3306,
DB="demo_write_ds",
USER="root",
PASSWORD="123456"
), read_ds (
HOST="127.0.0.1",
PORT=3306,
DB="demo_read_ds",
USER="root",
PASSWORD="123456"
);
SET DEFAULT SINGLE TABLE STORAGE UNIT = write_ds;
CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`status` varchar(50) DEFAULT NULL,
PRIMARY KEY (`order_id`)
);
CREATE READWRITE_SPLITTING RULE group_0 (
WRITE_STORAGE_UNIT=write_ds,
READ_STORAGE_UNITS(read_ds),
TYPE(NAME="random")
);
DROP READWRITE_SPLITTING RULE group_0;
````
Error: Readwrite splitting rules `[group_0]` in database `xxx` are still in used.
### Analyze
I would say that this restriction is actually correct. Consider a scenario:
When `write_ds` and `read_ds` are a set of primary-replica cluster, when there is already a table, dropping the rule may cause `INSERT SQL` to be routed to `read_ds`, which will cause serious errors.
Therefore, if there is already a single table in the logic database that uses `readwrite_ds`, dropping rule is not allowed.
So, what are the scenarios where you need to drop the rules when using the primary-replica cluster?
--
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 #22058: DROP READWRITE_SPLITTING RULE failed
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #22058:
URL: https://github.com/apache/shardingsphere/issues/22058#issuecomment-1310176514
If there is another rule (like sharding table rule) using group `readwrite_ds`, it also can not be dropped.
Nothing else.
--
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] peilinqian commented on issue #22058: DROP READWRITE_SPLITTING RULE failed
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22058:
URL: https://github.com/apache/shardingsphere/issues/22058#issuecomment-1310170124
>
Thank U, I already understand the reason for my problem。
So the only scenario can't drop the rules is when there is already a table in real database ?
--
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] peilinqian commented on issue #22058: DROP READWRITE_SPLITTING RULE failed
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22058:
URL: https://github.com/apache/shardingsphere/issues/22058#issuecomment-1310219227
> sing group `readwrite_ds`, it also can not be dropped.
> Nothing else.
ok，thank u
--
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] peilinqian closed issue #22058: DROP READWRITE_SPLITTING RULE failed
Posted by GitBox <gi...@apache.org>.
peilinqian closed issue #22058: DROP READWRITE_SPLITTING RULE failed
URL: https://github.com/apache/shardingsphere/issues/22058
--
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] peilinqian commented on issue #22058: DROP READWRITE_SPLITTING RULE failed
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22058:
URL: https://github.com/apache/shardingsphere/issues/22058#issuecomment-1310170772
> Hi @peilinqian Thanks for your feedback, and I have reproduced the problem from master branch.
>
> We get this error message because a single table already exists in the resource registered, and after creating the `READWRITE_SPLITTING RULE`, the single table will use the aggregate data source `readwrite_ds`. Therefore, if there is no table in the registered resource, this `readwrite_ds` can be dropped.
>
> ### The latest reproduction process:
> ```sql
> REGISTER STORAGE UNIT write_ds (
> HOST="127.0.0.1",
> PORT=3306,
> DB="demo_write_ds",
> USER="root",
> PASSWORD="123456"
> ), read_ds (
> HOST="127.0.0.1",
> PORT=3306,
> DB="demo_read_ds",
> USER="root",
> PASSWORD="123456"
> );
>
> SET DEFAULT SINGLE TABLE STORAGE UNIT = write_ds;
>
> CREATE TABLE `t_order` (
> `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
> `user_id` int(11) NOT NULL,
> `status` varchar(50) DEFAULT NULL,
> PRIMARY KEY (`order_id`)
> );
>
> CREATE READWRITE_SPLITTING RULE group_0 (
> WRITE_STORAGE_UNIT=write_ds,
> READ_STORAGE_UNITS(read_ds),
> TYPE(NAME="random")
> );
>
> DROP READWRITE_SPLITTING RULE group_0;
> ```
>
> Error: Readwrite splitting rules `[group_0]` in database `xxx` are still in used.
>
> ### Analyze
> I would say that this restriction is actually correct. Consider a scenario: When `write_ds` and `read_ds` are a set of primary-replica cluster, when there is already a table, dropping the rule may cause `INSERT SQL` to be routed to `read_ds`, which will cause serious errors.
>
> Therefore, if there is already a single table in the logic database that uses `readwrite_ds`, dropping rule is not allowed.
>
> So, what are the scenarios where you need to drop the rules when using the primary-replica cluster?
Thank U, I already understand the reason for my problem。
So the only scenario can't drop the rules is when there is already a table in real database ?
--
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