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'.

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)

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);
Defensive patterns

Strategy: validation

Validate before calling

// 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 / catch

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

Prevention

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

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