duerwuyi opened a new issue, #35110:
URL: https://github.com/apache/shardingsphere/issues/35110
## Bug Report
### Which version of ShardingSphere did you use?
shardingsphere-proxy 5.5.2
postgres 17.2 as backend
### Expected behavior
I will provide the following steps to reproduce this bug.
global.yaml:
```yaml
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: host.docker.internal:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
authority:
users:
- user: postgres@%
password: 123abc
admin: true
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
logging:
loggers:
- loggerName: ShardingSphere-SQL
additivity: true
level: DEBUG
props:
enable: true
sqlFederation:
sqlFederationEnabled: true
allQueryUseSQLFederation: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535
props:
system-log-level: DEBUG
sql-show: true
check-table-metadata-enabled: true
proxy-frontend-database-protocol-type: PostgreSQL
```
database-sharding.yaml
```yaml
databaseName: postgres
#
dataSources:
ds_0:
url: jdbc:postgresql://host.docker.internal:5443/postgres
username: postgres
password: postgres
ds_1:
url: jdbc:postgresql://host.docker.internal:5444/postgres
username: postgres
password: postgres
ds_2:
url: jdbc:postgresql://host.docker.internal:5445/postgres
username: postgres
password: postgres
ds_3:
url: jdbc:postgresql://host.docker.internal:5446/postgres
username: postgres
password: postgres
ds_4:
url: jdbc:postgresql://host.docker.internal:5447/postgres
username: postgres
password: postgres
```
docker-compose.yml
```yml
version: '3.8'
services:
zookeeper:
image: zookeeper:3.8.4
container_name: zookeeper
restart: always
ports:
- "2181:2181"
environment:
ZOO_MY_ID: 1
shardingsphere-proxy:
image: apache/shardingsphere-proxy:5.5.2
container_name: shardingsphere-proxy
environment:
- PORT=5432
ports:
- "5440:5432"
volumes:
- ./conf:/opt/shardingsphere-proxy/conf
- ./logs:/opt/shardingsphere-proxy/logs
- ./ext-lib:/opt/shardingsphere-proxy/ext-lib
depends_on:
- zookeeper
- pg1
- pg2
- pg3
- pg4
- pg5
pg1:
image: postgres:17
container_name: pg1
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5443:5432"
volumes:
- pg1_data:/var/lib/postgresql/data
pg2:
image: postgres:17
container_name: pg2
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5444:5432"
volumes:
- pg2_data:/var/lib/postgresql/data
pg3:
image: postgres:17
container_name: pg3
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5445:5432"
volumes:
- pg3_data:/var/lib/postgresql/data
pg4:
image: postgres:17
container_name: pg4
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5446:5432"
volumes:
- pg4_data:/var/lib/postgresql/data
pg5:
image: postgres:17
container_name: pg5
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5447:5432"
volumes:
- pg5_data:/var/lib/postgresql/data
volumes:
pg1_data:
pg2_data:
pg3_data:
pg4_data:
pg5_data:
```
after starting the cluster, create a new database named `testdb` on all
postgres-deatabases and sharding-sphere proxy, then try:
```sql
REGISTER STORAGE UNIT ds_0
(HOST="host.docker.internal",PORT=5443,DB="testdb",USER="postgres",PASSWORD="123abc");
REGISTER STORAGE UNIT ds_2
(HOST="host.docker.internal",PORT=5445,DB="testdb",USER="postgres",PASSWORD="123abc");
REGISTER STORAGE UNIT ds_4
(HOST="host.docker.internal",PORT=5447,DB="testdb",USER="postgres",PASSWORD="123abc");
REGISTER STORAGE UNIT ds_3
(HOST="host.docker.internal",PORT=5446,DB="testdb",USER="postgres",PASSWORD="123abc");
CREATE SHARDING TABLE RULE IF NOT EXISTS t0 (STORAGE_UNITS(ds_0, ds_2, ds_3,
ds_4), SHARDING_COLUMN=c3, TYPE(NAME="mod",PROPERTIES("sharding-count"="5")),
KEY_GENERATE_STRATEGY(COLUMN=pkey,TYPE(NAME="UUID")), AUDIT_STRATEGY
(TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true));
DROP TABLE IF EXISTS t0;
create table t0 (
vkey int4 ,
pkey int4 ,
c0 text ,
c1 text ,
c2 numeric ,
c3 numeric ,
c4 int4 ,
c5 timestamp ,
c6 numeric ,
c7 numeric ,
c8 int4
);
ERROR: Rule and storage meta data mismatched, reason is: Can not get
uniformed table structure for logic table 't0', it has different meta data of
actual tables are as follows:
CREATE SHARDING TABLE RULE IF NOT EXISTS t1 (STORAGE_UNITS(ds_0, ds_4),
SHARDING_COLUMN=pkey, TYPE(NAME="mod",PROPERTIES("sharding-count"="7")),
KEY_GENERATE_STRATEGY(COLUMN=pkey,TYPE(NAME="SNOWFLAKE")), AUDIT_STRATEGY
(TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true));
DROP TABLE IF EXISTS t1;
create table t1 (
vkey int4 ,
pkey int4 ,
c7 text ,
c8 timestamp ,
c9 text ,
c10 timestamp ,
c11 int4
);
```
Both `t0` and `t1` should execute successfully.
### Actual behavior
`t0` got error: Rule and storage meta data mismatched, reason is: Can not
get uniformed table structure for logic table 't0', it has different meta data
of actual tables are as follows.
Besides, if we swap the creation order of t0 and t1, then t1 will fail,
while t0 will be created successfully.
### Reason analyze (If you can)
This bug is related to dist variable `check-table-metadata-enabled` with
postgres, when it is set to `false`, the table could be created successfully,
but in this case, the table is acutally sharded and could see the physical
tables in those backend postgres, I guess the bug happens in the checking stage
of creating a sharding table.
--
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]
