terrymanu commented on issue #35245:
URL:
https://github.com/apache/shardingsphere/issues/35245#issuecomment-3489688049
Thank you for reporting this issue! I've analyzed the problem where creating
a table with foreign key constraints fails with "Table 'customer' already
exists" error when using mixed SINGLE and SHARDING rules.
Root Cause Analysis
The issue occurs due to a bug in ShardingSphere's DDL routing logic when
handling foreign key constraints:
1. Rule Conflict: Your configuration has both SINGLE rule (*.*) and
SHARDING rule (order_item), creating overlapping table coverage
2. Foreign Key Processing Bug: When parsing CREATE TABLE with foreign
keys, ShardingSphere incorrectly includes both the main table and referenced
tables in the routing process
3. Routing Engine Error: The system attempts to execute CREATE operations
on both order_item (main table) and customer (referenced table), causing the
"already exists" error
Recommended Solutions
1. Configuration Fix (Immediate Solution)
Modify your rules configuration to avoid conflicts:
```yaml
rules:
- !SINGLE
tables:
- "customer" # Explicitly specify SINGLE tables
# Add other non-sharded tables here instead of "*.*"
- !SHARDING
tables:
order_item:
actualDataNodes: ds.order_item,ds.order_item_${0..2}
tableStrategy:
standard:
shardingColumn: company_id
shardingAlgorithmName: order_item_inline
shardingAlgorithms:
order_item_inline:
type: INLINE
props:
algorithm-expression: order_item_${company_id.intdiv(50)}
```
2. Workaround (Alternative)
Create tables in two steps to avoid foreign key routing issues:
```sql
-- Step 1: Create table without foreign key
CREATE TABLE `order_item` (
`company_id` bigint(20) NOT NULL,
`id` bigint(20) NOT NULL,
`order_id` bigint(20) DEFAULT NULL,
`customer_company_id` bigint(20) DEFAULT NULL,
`customer_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`company_id`,`id`),
KEY `idx_order_item_company_order_id` (`company_id`,`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Step 2: Add foreign key constraint separately
ALTER TABLE `order_item`
ADD CONSTRAINT `FKg11wcg7bs8o3931hl5t2n44tr`
FOREIGN KEY (`customer_company_id`, `customer_id`)
REFERENCES `customer` (`company_id`, `id`);
```
Code Fix Required
The core issue is in the DDL routing logic that needs to distinguish
between:
- Main table: The table being created (order_item)
- Referenced tables: Existing tables referenced by foreign keys (customer)
The fix should ensure that only the main table is processed for creation,
while referenced tables are validated for existence without attempting to
recreate them.
We Need Your Help!
We would greatly appreciate it if you could contribute a pull request to
fix this issue. Here's what you can do:
1. Fork the repository and create a new branch for this fix
2. Locate the issue in the DDL routing logic (likely in
CreateTableStatement and SingleRouteEngine classes)
3. Implement the fix to properly handle foreign key referenced tables
4. Add test cases to verify the fix works correctly
5. Submit a pull request with detailed description of the changes
The fix should ensure that when processing CREATE TABLE statements with
foreign keys, only the main table is routed for creation operations, while
referenced tables are properly validated without triggering recreation
attempts.
Would you be willing to contribute a pull request for this fix? The
ShardingSphere community would be very grateful for your contribution to help
resolve this issue for other users as well.
Additional Information Needed
To help us provide a complete fix, could you also share:
1. Your complete server.yaml configuration
2. ShardingSphere proxy startup logs
3. Whether this issue occurs consistently or intermittently
Thank you again for bringing this important issue to our attention!
--
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]
