shardingsphere
shardingsphere copied to clipboard
apache
Wrong result of `information_schema.tables` when sharding a postgreSQL/mySQL table.
Bug Report
Which version of ShardingSphere did you use?
Proxy 5.5.2 with Postgres 17.2 as backend.
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy Standlone
Expected behavior
init state
CREATE SHARDING TABLE RULE t_order(
STORAGE_UNITS(ds_0,ds_1),
SHARDING_COLUMN=order_id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);
CREATE TABLE t_order (
order_id INT NOT NULL,
user_id INT,
status VARCHAR(50),
PRIMARY KEY (order_id)
);
query
select * from information_schema.tables where table_schema = 'public';
should return 4 tables, beacuse of "sharding-count"="4", and the table name should be t_order_0 to t_order_3
Actual behavior
table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------
postgres | public | t_order_0 | BASE TABLE | | | | | | YES | NO |
postgres | public | t_order_2 | BASE TABLE | | | | | | YES | NO |
(2 rows)
Reason analyze (If you can)
The query is only pushed down to 1 node(that is ds_0), so the other 2 sharding tables on ds_1 are missed.
The PREVIEW of the query:
postgres=> PREVIEW select * from information_schema.tables;
data_source_name | actual_sql
------------------+------------
(0 rows)
The EXPLAIN of the query:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=34.38..70.67 rows=37 width=608)
Hash Cond: (c.reloftype = t.oid)
-> Hash Join (cost=1.07..34.55 rows=37 width=141)
Hash Cond: (c.relnamespace = nc.oid)
-> Seq Scan on pg_class c (cost=0.00..32.67 rows=148 width=77)
Filter: ((relkind = ANY ('{r,v,f,p}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
-> Hash (cost=1.06..1.06 rows=1 width=68)
-> Seq Scan on pg_namespace nc (cost=0.00..1.06 rows=1 width=68)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'public'::name))
-> Hash (cost=25.59..25.59 rows=617 width=132)
-> Hash Join (cost=1.09..25.59 rows=617 width=132)
Hash Cond: (t.typnamespace = nt.oid)
-> Seq Scan on pg_type t (cost=0.00..21.17 rows=617 width=72)
-> Hash (cost=1.04..1.04 rows=4 width=68)
-> Seq Scan on pg_namespace nt (cost=0.00..1.04 rows=4 width=68)
(15 rows)
Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
global.yaml:
authority:
users:
- user: postgres@%
password: 123abc
admin: true
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
logging:
loggers:
- loggerName: ShardingSphere-SQL
additivity: true
level: INFO
props:
enable: true
props:
proxy-frontend-database-protocol-type: PostgreSQL
database-sharding.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.yaml(to create cluster):
version: '3.8'
services:
shardingsphere-proxy:
image: apache/shardingsphere-proxy:5.5.2
container_name: shardingsphere-proxy
environment:
- PORT=5432
ports:
- "3308:5432"
volumes:
- ./conf:/opt/shardingsphere-proxy/conf
- ./logs:/opt/shardingsphere-proxy/logs
- ./ext-lib:/opt/shardingsphere-proxy/ext-lib
depends_on:
- 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:
init state reproduction & query is shown above.
btw, I wonder if shardingsphere-proxy provides any method to query all the names of logic tables and row names they have in a database, e.g. t_order above? Thx a lot if someone could answer!
NOTE： \d and \dt also produced wrong results:
postgres=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | t_order_0 | table | postgres
public | t_order_2 | table | postgres
(2 rows)
the bug also exists in MySQL. Follow the document to create 4 shards of a table.
https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-proxy/distsql/usage/sharding-rule/
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();
+------------+
| TABLE_NAME |
+------------+
| t_order_0 |
| t_order_2 |
+------------+
2 rows in set (0.02 sec)
But SHOW TABLES is correct:
mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| t_order |
+----------------------+
1 row in set (0.00 sec)