apache/shardingsphere · error · DMLMultipleDataNodesWithLimitException

36

36

Error message

DELETE ... LIMIT can not support route to multiple data nodes.

What it means

DMLMultipleDataNodesWithLimitException is thrown by ShardingDeleteRouteContextChecker when a DELETE statement carrying a LIMIT clause routes to more than one data node. LIMIT in a distributed DELETE has no well-defined global semantics (which rows each node deletes is database-specific), so ShardingSphere rejects DELETE ... LIMIT whenever routeContext.getRouteUnits().size() > 1.

Source

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

import org.apache.shardingsphere.infra.config.props.ConfigurationProperties;
import org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase;
import org.apache.shardingsphere.infra.route.context.RouteContext;
import org.apache.shardingsphere.infra.session.query.QueryContext;
import org.apache.shardingsphere.sharding.exception.syntax.DMLMultipleDataNodesWithLimitException;
import org.apache.shardingsphere.sharding.route.engine.checker.ShardingRouteContextChecker;
import org.apache.shardingsphere.sharding.rule.ShardingRule;
import org.apache.shardingsphere.sql.parser.statement.core.statement.type.dml.DeleteStatement;

/**
 * Sharding delete route context checker.
 */
public final class ShardingDeleteRouteContextChecker implements ShardingRouteContextChecker {
    
    @Override
    public void check(final ShardingRule shardingRule, final QueryContext queryContext, final ShardingSphereDatabase database, final ConfigurationProperties props, final RouteContext routeContext) {
        if (((DeleteStatement) queryContext.getSqlStatementContext().getSqlStatement()).getLimit().isPresent() && routeContext.getRouteUnits().size() > 1) {
            throw new DMLMultipleDataNodesWithLimitException("DELETE");
        }
    }
}

View on GitHub (pinned to e952770a21)

Solutions

1. Add an exact-match predicate on the sharding key (e.g. WHERE order_id = ?) so the DELETE routes to a single data node.
2. Replace LIMIT semantics with an explicit deterministic selection: first SELECT the row, then DELETE by its primary key/sharding key.
3. Run the DELETE ... LIMIT directly against the target physical database, bypassing ShardingSphere.

Example fix

-- before: multi-node route + LIMIT
DELETE FROM t_order WHERE status = 'EXPIRED' LIMIT 10;

-- after: pin to one shard with the sharding key
DELETE FROM t_order WHERE order_id = ? AND status = 'EXPIRED' LIMIT 10;

// Reject DELETE ... LIMIT before sending when it cannot route to one node
boolean hasLimit = sql.toUpperCase(Locale.ROOT).contains("LIMIT");
Set<String> shardingKeys = Set.of("order_id"); // from rule
boolean hasShardingKeyEquality =/* inspect WHERE for key = literal/param */ false;
if ("DELETE".equals(operation) && hasLimit && !hasShardingKeyEquality) {
    throw new IllegalArgumentException("DELETE ... LIMIT requires single-node routing");
}

try {
    statement.executeUpdate(deleteSql);
} catch (final DMLMultipleDataNodesWithLimitException ex) {
    // re-issue per shard with explicit primary keys, or run on physical DB directly
}

When it happens

Trigger: Executing DELETE FROM <sharded_table> [WHERE ...] LIMIT n through ShardingSphere where the statement does not route to exactly one table data node — i.e. the WHERE lacks an exact sharding-key predicate or the key fans out across tables.

Common situations: Batch-cleanup jobs ported from a single MySQL instance to a sharded topology; running DELETE ... LIMIT 1 to remove 'one matching row'; MySQL-based queue tables migrated behind ShardingSphere.

Related errors

• 40
• 41
• 33
• 57
• 1

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