apache / apache/shardingsphere

Drop table failed when table name doesn't match logical table name in sharding rule case 2

Open
#23,604 1 comment 0 reactions 0 assignees View on GitHub
in: kernel
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.