terrymanu commented on issue #37471:
URL:
https://github.com/apache/shardingsphere/issues/37471#issuecomment-3686026937
Problem Understanding:
- ShardingSphere-JDBC 5.5.2 with three PostgreSQL data sources (main,
shard-1, shard-2); SINGLE rule (defaultDataSource=main) plus some sharded
tables. Expect Flyway to create schema db_version_control and table
flyway_schema_history on all shards.
- Actual: Flyway init fails. Logs show CREATE SCHEMA sent to all three;
creating history table throws TableExistsException, app startup aborts.
Root Cause:
- SINGLE rule enforces “a single table lives on one data source.” Once
flyway_schema_history is discovered/created as a single table, any subsequent
CREATE TABLE (without IF NOT EXISTS) to other data sources triggers the SINGLE
router’s existence check and throws
TableExistsException
(kernel/single/core/src/main/java/org/apache/shardingsphere/single/route/engine/SingleRouteEngine.java:117).
- ShardingSphere-JDBC does not broadcast Flyway migrations to all physical
databases. Creating identical single tables across multiple data sources via
the logical datasource conflicts with SINGLE semantics—this is usage
misalignment, not a product bug.
Analysis:
- Logs show CREATE SCHEMA executed on three data sources; failure occurs
during metadata validation before executing the history table DDL, matching
SINGLE router existence-check path rather than SQL execution failure.
- Config doesn’t declare Flyway metadata as a broadcast table, and DDL
lacks IF NOT EXISTS. defaultDataSource=main plus repeated CREATE TABLE to other
nodes conflicts with “table must be unique” enforcement.
- The expectation “Flyway auto-initializes across all shards” exceeds
current ShardingSphere-JDBC default behavior; recommended usage is
per-physical-db migrations or marking such common tables as broadcast before
migration.
Conclusion:
- Behavior matches SINGLE rule design; no framework defect identified.
Align with ShardingSphere usage: run Flyway per physical DB (or designate
Flyway metadata/common tables as broadcast, then migrate) to avoid single-table
conflicts.
--
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]
