apache / apache/shardingsphere
Drop table failed when table name doesn't match logical table name in sharding rule case 2
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 11h 38m
- Merged PRs (30d)
- 326
Description
## Bug Report
### Which version of ShardingSphere did you use?
master, 8c58db88d879428ec80d98ae04ff4661d7dde0af
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
proxy, PostgreSQL backend
### Expected behavior
Drop table succeed
### Actual behavior
Drop table failed with `drop table T_ORDER`, but succeed with `drop table t_order`.
```
test=> CREATE SHARDING TABLE RULE t_order(
test(> STORAGE_UNITS(ds_2,ds_3,ds_4),
test(> SHARDING_COLUMN=order_id,
test(> TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
test(> KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
test(> );
SUCCESS
test=> CREATE TABLE T_ORDER (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
CREATE TABLE
test=> select * from t_order;
order_id | user_id | status
----------+---------+--------
(0 rows)
test=> select * from T_ORDER;
order_id | user_id | status
----------+---------+--------
(0 rows)
test=> drop table T_ORDER;
ERROR: Actual tables `[t_order_4, t_order_5, t_order_2, t_order_3, t_order_0, t_order_1]` are in use.
test=> drop table t_order;
DROP TABLE
```
No exception stack trace in proxy log, after debug, the exception is:
```
org.apache.shardingsphere.sharding.exception.metadata.DropInUsedTablesException: Actual tables `[t_order_4, t_order_5, t_order_2, t_order_3, t_order_0, t_order_1]` are in use.
at org.apache.shardingsphere.sharding.route.engine.validator.ddl.impl.ShardingDropTableStatementValidator.checkTableInUsed(ShardingDropTableStatementValidator.java:86)
at org.apache.shardingsphere.sharding.route.engine.validator.ddl.impl.ShardingDropTableStatementValidator.postValidate(ShardingDropTableStatementValidator.java:66)
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.lambda$createRouteContext$1(ShardingSQLRouter.java:60)
at java.util.Optional.ifPresent(Optional.java:159)
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:60)
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:46)
at org.apache.shardingsphere.infra.route.engine.impl.PartialSQLRouteExecutor.route(PartialSQLRouteExecutor.java:69)
at org.apache.shardingsphere.infra.route.engine.SQLRouteEngine.route(SQLRouteEngine.java:55)
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.route(KernelProcessor.java:59)
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:51)
at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:170)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.simple.PostgreSQLComQueryExecutor.execute(PostgreSQLComQueryExecutor.java:77)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
```
### Reason analyze (If you can)
```
Set dropTables = sqlStatement.getTables().stream().map(each -> each.getTableName().getIdentifier().getValue()).collect(Collectors.toSet());
...
Collection tableMeta = shardingRule.getTableRules().values().stream().filter(each -> !dropTables.contains(each.getLogicTable()))
...
```
`dropTables` contains `T_ORDER`
### 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: governance_ds
server-lists: localhost:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
authority:
users:
- user: root@%
password: root
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
#transaction:
# defaultType: XA
# providerType: Atomikos
sqlParser:
sqlCommentParseEnabled: false
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
parseTreeCache:
initialCapacity: 128
maximumSize: 1024
#cdc:
# enabled: false
# port: 33071
props:
max-connections-size-per-query: 1
kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
proxy-hint-enabled: false
sql-show: false
check-table-metadata-enabled: false
# Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
# The default value is -1, which means set the minimum value for different JDBC drivers.
proxy-backend-query-fetch-size: -1
proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
# Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
# and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.
proxy-backend-executor-suitable: OLAP
proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
# Available sql federation type: NONE (default), ORIGINAL, ADVANCED
sql-federation-type: NONE
proxy-mysql-default-version: 5.7.22 # In the absence of schema name, the default version will be used.
proxy-default-port: 3307 # Proxy default port.
proxy-netty-backlog: 1024 # Proxy netty backlog.
proxy-frontend-database-protocol-type: PostgreSQL
```
Execute SQLs in PostgreSQL:
```
DROP DATABASE IF EXISTS migration_ds_10;
CREATE DATABASE migration_ds_10;
DROP DATABASE IF EXISTS migration_ds_11;
CREATE DATABASE migration_ds_11;
DROP DATABASE IF EXISTS migration_ds_12;
CREATE DATABASE migration_ds_12;
```
Execute DistSQLs in proxy:
```
REGISTER STORAGE UNIT ds_2 (
URL="jdbc:postgresql://127.0.0.1:5432/migration_ds_10",
USER="postgres",
PASSWORD="root",
PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
), ds_3 (
URL="jdbc:postgresql://127.0.0.1:5432/migration_ds_11",
USER="postgres",
PASSWORD="root",
PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
), ds_4 (
URL="jdbc:postgresql://127.0.0.1:5432/migration_ds_12",
USER="postgres",
PASSWORD="root",
PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
);
CREATE SHARDING TABLE RULE t_order(
STORAGE_UNITS(ds_2,ds_3,ds_4),
SHARDING_COLUMN=order_id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);
CREATE TABLE T_ORDER (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
SELECT * FROM t_order;
DROP TABLE T_ORDER;
DROP TABLE t_order;
```
### Example codes for reproduce this issue (such as a github link).
Contributor guide
Research direction
Reproduce the PostgreSQL proxy case with DROP TABLE T_ORDER and DROP TABLE t_order, then inspect ShardingDropTableStatementValidator.java at checkTableInUsed and postValidate, as identified in the stack trace. Compare how the parsed table name is matched with the logical table name; done when both casing variants drop the sharded table without DropInUsedTablesException.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, postgresql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 45/100