apache/shardingsphere · error · UnsupportedUpdatingShardingValueException

31

31

Error message

Can not update sharding value for table '%s'.

What it means

UnsupportedUpdatingShardingValueException is thrown by ShardingInsertRouteContextChecker during INSERT ... ON DUPLICATE KEY UPDATE. The checker builds a second route context from sharding conditions extracted out of the ON DUPLICATE KEY assignment columns; if that route context differs from the original one (ShardingRouteContextCheckUtils.isSameRouteContext false), the UPDATE would move the row to a different shard than the INSERT, which ShardingSphere cannot execute atomically, so it fails with 'Can not update sharding value for table'.

Solutions

1. Remove the sharding column from the ON DUPLICATE KEY UPDATE assignment list — sharding keys must be immutable.
2. If the sharding key must change, split into DELETE (by old key) + INSERT (with new key) inside one application transaction.
3. Verify the INSERT values themselves produce a consistent route (all rows in one multi-row INSERT target the same shard).

Example fix

-- before: updating the sharding column
INSERT INTO t_order(order_id,cust_id,status) VALUES(?,?,?)
ON DUPLICATE KEY UPDATE cust_id=VALUES(cust_id), status=VALUES(status);
-- cust_id is the sharding key -> error when VALUES differ

-- after: sharding key excluded from the update
INSERT INTO t_order(order_id,cust_id,status) VALUES(?,?,?)
ON DUPLICATE KEY UPDATE status=VALUES(status);

// Strip sharding columns from ON DUPLICATE KEY UPDATE before executing
String shardingColumn = "cust_id";
String insertSql = "INSERT INTO t_order(order_id,cust_id,status) VALUES(?,?,?) ON DUPLICATE KEY UPDATE status=VALUES(status)";
// ensure shardingColumn never appears after 'ON DUPLICATE KEY UPDATE'
String tail = insertSql.substring(insertSql.toUpperCase(Locale.ROOT).indexOf("ON DUPLICATE KEY UPDATE") + 22);
if (tail.toUpperCase(Locale.ROOT).contains(shardingColumn.toUpperCase(Locale.ROOT))) {
    throw new IllegalArgumentException("Sharding key must not be updated: " + shardingColumn);
}

try {
    ps.executeUpdate();
} catch (final UnsupportedUpdatingShardingValueException ex) {
    // rewrite to DELETE by old key + INSERT with new key inside one transaction
}

When it happens

Trigger: INSERT INTO t (...) VALUES (...) ON DUPLICATE KEY UPDATE <sharding_column> = <new_value> where the new sharding value routes to a different data node/table than the INSERT values. The checker re-routes using conditions derived from the assignments and compares.

Common situations: Upsert code that also 'corrects' the sharding key (e.g. ON DUPLICATE KEY UPDATE tenant_id = VALUES(tenant_id)); changing sharding-column values as part of dedupe logic; migrating single-DB upserts unchanged into a sharded schema.

Related errors

• 0
• 1
• 1
• 1
• 2

AI-assisted analysis of apache/shardingsphere@e952770a21 (2026-08-14). Data as JSON: /api/errors/e5f8f6ceea60a03c. Report an issue: GitHub.

Appendix: source

Thrown at features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/checker/dml/ShardingInsertRouteContextChecker.java:69

    
    @Override
    public void check(final ShardingRule shardingRule, final QueryContext queryContext,
                      final ShardingSphereDatabase database, final ConfigurationProperties props, final RouteContext routeContext) {
        SQLStatementContext sqlStatementContext = queryContext.getSqlStatementContext();
        InsertStatement insertStatement = (InsertStatement) sqlStatementContext.getSqlStatement();
        Optional<SubquerySegment> insertSelect = insertStatement.getInsertSelect();
        String tableName = insertStatement.getTable().map(optional -> optional.getTableName().getIdentifier().getValue()).orElse("");
        if (insertSelect.isPresent() && shardingConditions.isNeedMerge()) {
            boolean singleRoutingOrSameShardingCondition = routeContext.isSingleRouting() || shardingConditions.isSameShardingCondition();
            ShardingSpherePreconditions.checkState(singleRoutingOrSameShardingCondition, () -> new UnsupportedShardingOperationException("INSERT ... SELECT ...", tableName));
        }
        Collection<ColumnAssignmentSegment> assignments = insertStatement.getOnDuplicateKeyColumns().map(OnDuplicateKeyColumnsSegment::getColumns).orElse(Collections.emptyList());
        Optional<ShardingConditions> onDuplicateKeyShardingConditions =
                ShardingRouteContextCheckUtils.createShardingConditions(sqlStatementContext, shardingRule, assignments, queryContext.getParameters());
        Optional<RouteContext> onDuplicateKeyRouteContext = onDuplicateKeyShardingConditions
                .map(optional -> new ShardingStandardRouteEngine(tableName, optional, sqlStatementContext, queryContext.getHintValueContext(), props).route(shardingRule));
        if (onDuplicateKeyRouteContext.isPresent() && !ShardingRouteContextCheckUtils.isSameRouteContext(routeContext, onDuplicateKeyRouteContext.get())) {
            throw new UnsupportedUpdatingShardingValueException(tableName);
        }
        if (!routeContext.isSingleRouting()) {
            boolean isSingleDataNode = routeContext.getOriginalDataNodes().stream().allMatch(dataNodes -> 1 == dataNodes.size());
            ShardingSpherePreconditions.checkState(isSingleDataNode, () -> new DuplicateInsertDataRecordException(shardingConditions, tableName));
        }
    }
}

View on GitHub (pinned to e952770a21)