apache / apache/shardingsphere
MySQL's behavior without current schema
- 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
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