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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
