terrymanu commented on issue #35268:
URL:
https://github.com/apache/shardingsphere/issues/35268#issuecomment-3505567457
Problem Understanding
The issue occurs when ShardingSphere transforms a working SQL Server
INSERT statement:
-- Original SQL (executes successfully)
INSERT INTO BATCH_JOB_SEQ DEFAULT VALUES;
Into a failing statement:
-- Transformed SQL (execution fails)
INSERT INTO BATCH_JOB_SEQ(ID, id) DEFAULT VALUES;
For a table with:
CREATE TABLE BATCH_JOB_SEQ (ID BIGINT IDENTITY);
Root Cause Analysis
Based on ShardingSphere's SQL processing architecture, this likely stems
from:
1. SQL Parser Issue: The AbstractSQLBuilder may be incorrectly identifying
column information for tables with IDENTITY columns
2. Metadata Handling: ShardingSphere might be retrieving incorrect column
metadata for SQL Server's IDENTITY columns
3. SQL Rewrite Logic: The SQL rewriting process may not properly handle
SQL Server's DEFAULT VALUES syntax for IDENTITY columns
Additional Information Needed
To properly diagnose and fix this issue, please provide the following
details:
1. Environment Configuration
- ShardingSphere version
- Spring Batch version
- SQL Server version
- Complete ShardingSphere configuration file (data source and sharding
rules)
2. Error Logs
- Complete exception stack trace
- ShardingSphere SQL parsing and execution logs
- Specific error message returned by SQL Server
3. Configuration Details
- Spring Batch datasource configuration
- Any specific routing rules for BATCH_* tables
- Database connection parameters
4. Minimal Reproduction Case
- Simplified test code
- Minimal configuration files
- Steps to reproduce the issue
Initial Recommendations
1. Verify ShardingSphere Configuration
Ensure BATCH_* tables are not included in sharding rules:
dataSources:
# Your datasource configuration
rules:
- !SHARDING
tables:
# Only configure business tables for sharding
business_table:
# sharding configuration
# Do not configure BATCH_* tables in sharding rules
2. Alternative Solutions
If you must handle BATCH_* tables through ShardingSphere, consider:
- Broadcast Table Configuration: Configure BATCH_* tables as broadcast
tables
- Hint Routing: Use Hint routing to force queries to specific datasources
- Separate Datasource: Use a non-sharded datasource for Spring Batch tables
3. Workaround Options
- Configure Spring Batch to use a separate, non-sharded datasource
- Use different table naming that doesn't conflict with ShardingSphere's
processing logic
Next Steps
Once you provide the additional configuration and log information, we can:
1. Determine if this is a configuration issue or a ShardingSphere
compatibility bug
2. If it's a bug, create a targeted fix for SQL Server IDENTITY column
handling
3. If it's a configuration issue, provide proper configuration guidance
The SQL Server DEFAULT VALUES syntax with IDENTITY columns requires
special handling, and this appears to be a gap in ShardingSphere's SQL Server
dialect support.
--
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]
