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/08/24 09:12:42 UTC
[GitHub] [shardingsphere] wsm12138 opened a new issue, #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
wsm12138 opened a new issue, #20491:
URL: https://github.com/apache/shardingsphere/issues/20491
## Bug Report
### Which version of ShardingSphere did you use?
```
/bin/java
we find java version: java8, full_version=1.8.0_312, full_path=/bin/java
ShardingSphere-5.1.3-SNAPSHOT
Commit ID: d2f8159f0329a6fc88bcae8ba383cce8e11c44cf
Commit Message: Basic support migration to different database type (#20479)
Branch: d2f8159f0329a6fc88bcae8ba383cce8e11c44cf
Build time: 2022-08-24T15:26:07+0800
```
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
Proxy handle `schema` have same strategy.
### Actual behavior
Proxy handle `schema` have different strategy.
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
##### server.yaml
```
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: mig_pg
server-lists: localhost:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: true
rules:
- !AUTHORITY
users:
- root@%:root
- sharding@:sharding
provider:
type: ALL_PERMITTED
props:
proxy-frontend-database-protocol-type: PostgreSQL
```
1. start proxy
2. create database test;
3. \c test
4. create schema SYSTEM;
5. create table system.acmg_t_cust_info_base(cust_no varchar(32) not null);
6. drop schema SYSTEM;
### Example codes for reproduce this issue (such as a github link).
- step 4-5
<img width="494" alt="image" src="https://user-images.githubusercontent.com/86462784/186379200-a5496dc0-179b-46ec-8ca8-e6169de7d3a9.png">
- step 6
<img width="465" alt="image" src="https://user-images.githubusercontent.com/86462784/186379154-513abe6d-6d1a-461c-9baf-b122a0a9ec2d.png">
- stdout.log
<img width="1282" alt="image" src="https://user-images.githubusercontent.com/86462784/186379075-dc71922c-d039-41bd-94ff-accca7be82f6.png">
- zk
<img width="651" alt="image" src="https://user-images.githubusercontent.com/86462784/186379308-6a284d31-a24e-4986-9291-d19370484fac.png">
--
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] wsm12138 commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1225457990
execute `drop table SYSTEM.acmg_t_cust_info_base;` SQL sucess, but zk still have that table.
<img width="450" alt="image" src="https://user-images.githubusercontent.com/86462784/186380916-e503916b-f60a-4bc7-8ad7-0a1c9d623940.png">
<img width="1208" alt="image" src="https://user-images.githubusercontent.com/86462784/186380978-3a572cf5-a0eb-4388-b9d4-f9ebd7af92ec.png">
--
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] strongduanmu commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1226880091
```sql
postgres=> create database test;
CREATE DATABASE
postgres=> \c test;
psql (13.3 (Debian 13.3-1.pgdg100+1), server 12.3-ShardingSphere-Proxy 5.1.3-SNAPSHOT-dirty-f9a3e84)
You are now connected to database "test" as user "root".
test=> create schema SYSTEM;
ERROR: There are no resources in the database test.
test=> ADD RESOURCE ds_0 (
test(> URL="jdbc:postgresql://127.0.0.1:5432/demo_ds_0",
test(> USER="postgres",
test(> PASSWORD="123456",
test(> PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
test(> );
SUCCESS
test=> create schema SYSTEM;
CREATE SCHEMA
```
<img width="764" alt="image" src="https://user-images.githubusercontent.com/10829171/186601473-e11184b6-76b5-4ffd-9b8c-c35de000048b.png">
```sql
test=> create table system.acmg_t_cust_info_base(cust_no varchar(32) not null);
CREATE TABLE
```
<img width="755" alt="image" src="https://user-images.githubusercontent.com/10829171/186601662-aa6a83be-083a-4bc9-b4b1-88114244cdc5.png">
--
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] strongduanmu commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1226833671
Besides, `SHOW SCHEMAS` are not a valid PostgreSQL statement.
--
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] strongduanmu commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1225590298
@wsm12138 Thank you for your feedback, I will investigate this issue.
--
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] wsm12138 commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1225451234
@zjcnb @strongduanmu plz have a look ~
--
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] wsm12138 commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1226894130
> Following is the result when I modify the metadata logic on my local machine.
>
> ```sql
> postgres=> create database test;
> CREATE DATABASE
> postgres=> \c test;
> psql (13.3 (Debian 13.3-1.pgdg100+1), server 12.3-ShardingSphere-Proxy 5.1.3-SNAPSHOT-dirty-f9a3e84)
> You are now connected to database "test" as user "root".
> test=> create schema SYSTEM;
> ERROR: There are no resources in the database test.
> test=> ADD RESOURCE ds_0 (
> test(> URL="jdbc:postgresql://127.0.0.1:5432/demo_ds_0",
> test(> USER="postgres",
> test(> PASSWORD="123456",
> test(> PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
> test(> );
> SUCCESS
> test=> create schema SYSTEM;
> CREATE SCHEMA
> ```
>
> <img alt="image" width="764" src="https://user-images.githubusercontent.com/10829171/186601473-e11184b6-76b5-4ffd-9b8c-c35de000048b.png">
>
> ```sql
> test=> create table system.acmg_t_cust_info_base(cust_no varchar(32) not null);
> CREATE TABLE
> ```
>
> <img alt="image" width="755" src="https://user-images.githubusercontent.com/10829171/186601662-aa6a83be-083a-4bc9-b4b1-88114244cdc5.png">
I think you are right ,when proxy is in not case sensitive mode.
--
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] strongduanmu commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1226883778
```sql
test=> drop schema system;
DROP SCHEMA
test=> drop schema SYSTEM;
ERROR: schema "system" does not exist
```
<img width="763" alt="image" src="https://user-images.githubusercontent.com/10829171/186602819-c0c88e4c-a75c-4b32-bcbc-0c24e66e8754.png">
--
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] strongduanmu commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1226882901
```sql
test=> drop schema SYSTEM;
ERROR: cannot drop schema system because other objects depend on it
DETAIL: table system.acmg_t_cust_info_base depends on schema system
HINT: Use DROP ... CASCADE to drop the dependent objects too.
test=> drop table SYSTEM.acmg_t_cust_info_base;
DROP TABLE
```
<img width="736" alt="image" src="https://user-images.githubusercontent.com/10829171/186602575-4ced01ae-df97-4494-9e52-5224e28c8488.png">
--
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] zjcnb closed issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
zjcnb closed issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
URL: https://github.com/apache/shardingsphere/issues/20491
--
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] wsm12138 commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1225469614
1. drop schema system; —— it should't sucess,because we can find table `acm` in zk
2. drop schema SYSTEM; —— it should't ERROR,because we can find `SYSTEM` in zk
3.
```
sharding_db=> create schema system;
CREATE SCHEMA
sharding_db=> drop schema SYSTEM;
ERROR: java.lang.NullPointerException
sharding_db=>
```
Why have NPE?
<img width="398" alt="image" src="https://user-images.githubusercontent.com/86462784/186383202-e4d9ce64-14a2-4497-9c3c-d1f0d458e597.png">
<img width="1127" alt="image" src="https://user-images.githubusercontent.com/86462784/186383237-16950e95-e473-4dfd-8e7b-50d019755038.png">
--
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] strongduanmu commented on issue #20491: Sometimes `schema` in proxy case sensitive,sometimes `schema` in proxy not case sensitive？
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20491:
URL: https://github.com/apache/shardingsphere/issues/20491#issuecomment-1226819749
When I execute `create schema SYSTEM;`, following exception will occur.
--
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