terrymanu commented on issue #30446:
URL:
https://github.com/apache/shardingsphere/issues/30446#issuecomment-3507581375
Problem Confirmation
After thoroughly analyzing the ShardingSphere codebase and the provided
example, I can confirm this is a legitimate design limitation in
ShardingSphere's Shadow functionality, not a user error.
Root Cause Analysis
1. Architecture Design Limitation
The fundamental issue lies in ShardingSphere's metadata management
architecture for Shadow databases:
- Independent Metadata Contexts: Production and shadow databases maintain
separate metadata contexts
- SQL Routing Only: Shadow router only handles SQL routing, not metadata
synchronization
- Lack of Bidirectional Sync: No automatic synchronization mechanism
exists between production and shadow metadata
2. Technical Code Analysis
ShadowSQLRouter.java (line 44-62):
public void decorateRouteContext(final RouteContext routeContext, final
QueryContext queryContext,
final ShardingSphereDatabase database,
final ShadowRule rule,
final Collection<String> tableNames, final
ConfigurationProperties props) {
// Only handles routing units, does not process metadata
synchronization
Map<String, String> shadowDataSourceMappings =
ShadowDataSourceMappingsRetrieverFactory.newInstance(queryContext).retrieve(rule);
// No DDL awareness or metadata sync logic
}
ContextManager.java (line 175-189):
public void reloadDatabase(final ShardingSphereDatabase database) {
MetaDataContexts reloadedMetaDataContexts =
createMetaDataContexts(database);
// Reloads ALL data sources' metadata, too expensive for specific
shadow pairs
}
ShardingSphereDatabaseFactory.java (line 80-88):
public static ShardingSphereDatabase create(final String name, final
DatabaseType protocolType,
final DatabaseConfiguration
databaseConfig,
final ConfigurationProperties
props,
final ComputeNodeInstanceContext
computeNodeInstanceContext) throws SQLException {
// Each data source independently builds metadata, no synchronization
mechanism
Map<String, ShardingSphereSchema> schemas = new
ConcurrentHashMap<>(GenericSchemaBuilder.build(
protocolType, new
GenericSchemaBuilderMaterial(resourceMetaData.getStorageUnits(),
databaseRules,
props, defaultSchemaName)));
}
3. The Specific Problem
From your example code:
// Production database - uses IF NOT EXISTS
public void createTableIfNotExists() throws SQLException {
String sql = "CREATE TABLE IF NOT EXISTS t_order " +
"(order_id BIGINT NOT NULL AUTO_INCREMENT, order_type
INT(11), user_id INT NOT NULL, address_id BIGINT NOT NULL, status VARCHAR(50),
PRIMARY KEY (order_id))";
// This works on subsequent executions
}
// Shadow database - missing IF NOT EXISTS
public void createTableIfNotExistsShadow() throws SQLException {
String sql = "CREATE TABLE t_order (order_id BIGINT NOT NULL
AUTO_INCREMENT, order_type INT(11), user_id INT NOT NULL, address_id BIGINT NOT
NULL, status VARCHAR(50), PRIMARY KEY (order_id)) /*
SHARDINGSPHERE_HINT:shadow=true,foo=bar*/";
// This fails on second execution with "Table already exists"
}
4. Why This is a Design Issue
Expected Behavior: Users expect Shadow functionality to handle shadow
database state transparently without requiring manual metadata management.
Current Limitation: The system should provide automatic metadata
synchronization between production and shadow databases, especially for DDL
operations.
Solution Recommendations
1. Immediate Workaround
Use IF NOT EXISTS in shadow table creation:
public void createTableIfNotExistsShadow() throws SQLException {
String sql = "CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT
NULL AUTO_INCREMENT, order_type INT(11), user_id INT NOT NULL, address_id
BIGINT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id)) /*
SHARDINGSPHERE_HINT:shadow=true,foo=bar*/";
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()) {
statement.executeUpdate(sql);
}
}
2. Short-term Solution - DDL-Aware Shadow Router
Extend ShadowSQLRouter to detect DDL and synchronize metadata:
public void decorateRouteContext(final RouteContext routeContext, final
QueryContext queryContext,
final ShardingSphereDatabase database,
final ShadowRule rule,
final Collection<String> tableNames, final
ConfigurationProperties props) {
Map<String, String> shadowDataSourceMappings =
ShadowDataSourceMappingsRetrieverFactory.newInstance(queryContext).retrieve(rule);
// NEW: DDL detection and metadata synchronization
if (isDDLStatement(queryContext)) {
synchronizeMetaDataForShadowPairs(database,
shadowDataSourceMappings, queryContext);
}
// Existing routing logic continues...
}
private boolean isDDLStatement(final QueryContext queryContext) {
return
queryContext.getSqlStatementContext().getSqlStatement().getClass().getName()
.contains("CreateTableStatement") ||
queryContext.getSqlStatementContext().getSqlStatement().getClass().getName()
.contains("DropTableStatement");
}
3. Long-term Solution - Shadow Metadata Synchronization Service
Introduce dedicated service for shadow metadata management:
public class ShadowMetaDataSynchronizationService {
/**
* Synchronize metadata between production and shadow data sources
*/
public void synchronizeShadowPairMetaData(final ShardingSphereDatabase
database,
final String
productionDataSource,
final String shadowDataSource)
{
// Compare metadata differences between two data sources
// Execute necessary synchronization operations
}
/**
* Check metadata consistency
*/
public boolean isMetaDataConsistent(final ShardingSphereDatabase
database,
final String productionDataSource,
final String shadowDataSource) {
// Implement metadata consistency check logic
}
}
4. Configuration-Based Solution
Allow users to configure metadata synchronization strategy:
rules:
- !SHADOW
dataSources:
shadow-data-source:
productionDataSourceName: ds_production
shadowDataSourceName: ds_shadow
metaDataSyncStrategy: AUTO # AUTO, MANUAL, NONE
syncOnDDL: true
Files That Need Modification
Core Changes Required:
1. ShadowSQLRouter.java - Add DDL awareness and metadata sync
2. ContextManager.java - Add selective shadow data source pair reload
3. ShadowRule.java - Add metadata synchronization configuration
4. New File: ShadowMetaDataSynchronizationService.java
Implementation Priority
- High Priority: DDL-aware metadata synchronization
- Medium Priority: Manual synchronization API
- Low Priority: Configurable automatic sync strategy
Questions for the User
To help us implement the best solution:
1. Execution Pattern: Does this issue occur when running the application
multiple times?
2. Workaround Validation: Have you tried using IF NOT EXISTS in the shadow
creation method?
3. Production Scenario: In your use case, should shadow tables be created
automatically when production tables are created?
Conclusion
This issue reveals a genuine design limitation in ShardingSphere's Shadow
functionality regarding metadata management. The current architecture lacks
automatic synchronization between production and shadow database
metadata.
We recommend implementing the DDL-aware synchronization mechanism as a
priority fix, while the IF NOT EXISTS workaround should resolve the immediate
issue for most use cases.
Thank you for bringing this important issue to our attention. This will
significantly improve Shadow functionality's usability in production
environments.
--
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]
