apache/shardingsphere

MySQL's behavior without current schema

Open

#28 469 ouverte le 19 sept. 2023

Voir sur GitHub
 (4 commentaires) (0 réactions) (0 assignés)Java (6 902 forks)batch import
db: MySQLgood first issuestatus: volunteer wanted

Métriques du dépôt

Stars
 (20 714 stars)
Métriques de merge PR
 (Merge moyen 3j 14h) (194 PRs mergées en 30 j)

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())

     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.

Guide contributeur