apache / apache/shardingsphere

MySQL's behavior without current schema

Open
#28,469 4 comments 0 reactions 0 assignees View on GitHub
db: MySQL type: bug
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 38m
Merged PRs (30d)
326

Description

## Question
- When using `shardingsphere-jdbc` on `MySQL`, we can give `jdbcUrl` without schema
- ex) `jdbc:mysql://localhost:3306/`
- Then `SELECT DATABASE()` returns null
- In this case, the masking feature is failed with `org.apache.shardingsphere.infra.exception.TableNotExistsException: Table or view 'actor' does not exist.`.
- Reference: #27879
- Reason: metaDataContext queries to Database with below query. (Maybe generated with `ResultSet java.sql.DatabaseMetaData.getTables()`)

```sql
SELECT TABLE_SCHEMA AS TABLE_CAT,
NULL AS TABLE_SCHEM,
TABLE_NAME,
CASE
WHEN TABLE_TYPE = 'BASE TABLE' THEN CASE
WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema'
THEN 'SYSTEM TABLE'
ELSE 'TABLE' END
WHEN TABLE_TYPE = 'TEMPORARY' THEN 'LOCAL_TEMPORARY'
ELSE TABLE_TYPE END AS TABLE_TYPE,
TABLE_COMMENT AS REMARKS,
NULL AS TYPE_CAT,
NULL AS TYPE_SCHEM,
NULL AS TYPE_NAME,
NULL AS SELF_REFERENCING_COL_NAME,
NULL AS REF_GENERATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '' -- <-- This part makes empty result
HAVING TABLE_TYPE IN ('TABLE', 'VIEW', 'SYSTEM TABLE', 'SYSTEM VIEW', null)
ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME;
```

Is this behavior is intended? I think we need to fetch all schemas in this case.
Also, even though having current schema, quering to other schema's data is being failure, we need to fetch all schemas also.

Contributor guide

Open the contributing guide

Research direction

Start at the metaDataContext code that obtains table metadata through java.sql.DatabaseMetaData.getTables(), and reproduce the case with jdbc:mysql://localhost:3306/ where SELECT DATABASE() returns null. Compare metadata discovery with and without a current schema, including references to other schemas. Done means masking no longer reports the actor table as missing when schemas should be discoverable.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, mysql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.