Re: [I] When a logic database uses both `SHARDING` and `READWRITE_SPLITTING` features, CRUD operations on table throw `NoSuchTableException` [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu commented on issue #27955:
URL: https://github.com/apache/shardingsphere/issues/27955#issuecomment-3647241825

   ##  Issue Understanding
   
     - In ShardingSphere-JDBC 5.4.0, with both SHARDING and READWRITE_SPLITTING enabled and H2 (MySQL mode) for unit tests, adding DATABASE_TO_LOWER=TRUE to the JDBC URL causes CRUD tests to throw NoSuchTableException. Without that parameter, tests pass.
   
   ##  Root Cause
   
     - DATABASE_TO_LOWER=TRUE forces H2 to lowercase unquoted catalog/schema/table/column names. ShardingSphere loads and validates metadata using MySQL conventions via DatabaseMetaData#getTables; when schema/table names returned by H2 are all lowercase, they no longer match
       the expected default schema/name casing, so metadata loads empty and validateTableExist throws NoSuchTableException. This is a case-mismatch triggered by the H2 compatibility switch, not a defect in sharding or readwrite splitting logic.
   
   ##  Analysis
   
     - Your schema.sql creates t_order0/1. With check-table-metadata-enabled on by default, validation depends on the metadata loaded at startup. With DATABASE_TO_LOWER=TRUE, H2 returns lowercase catalog/schema/table names; ShardingSphere’s default schema name and table
       lookup don’t align, so metadata is empty and validation fails immediately.
     - H2 is just a convenient test database; officially supported production targets include MySQL, etc. In MySQL mode, case-insensitive behavior is the default, so DATABASE_TO_LOWER=TRUE is unnecessary. Keeping H2 aligned with MySQL defaults (remove the parameter) avoids
       the mismatch.
     - Recommended configuration practice: first define READWRITE_SPLITTING to create a logical data source, then use that logical source as the actual node for sharding, and add a SINGLE rule for non-sharded tables (as in the config suggested in the issue comments). With H2
       unchanged on case handling, this loads metadata and passes validation.
     - If you must keep DATABASE_TO_LOWER=TRUE, it would need dedicated dialect handling or relaxed validation; 5.4.0 does not include such H2 compatibility.
   
   ##  Conclusion
   
     - Remove DATABASE_TO_LOWER=TRUE from the JDBC URL to keep H2 consistent with MySQL default casing, and use the recommended readwrite-splitting + sharding + SINGLE configuration. That should avoid the exception.
     - If further diagnosis is needed, please share: the full config.yaml (including JDBC URLs), the table/catalog names returned by each data source’s information_schema.tables (to confirm casing), and whether check-table-metadata-enabled was changed (see official docs:
       https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-properties/ ).
     - At present, aligning H2 with MySQL defaults is the intended path; no code changes are required.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org