{"record":{"id":"f74c2d85eb1dfc9e","repo":"apache/shardingsphere","slug":"36","errorCode":"36","errorMessage":"DELETE ... LIMIT can not support route to multiple data nodes.","messagePattern":"DELETE \\.\\.\\. LIMIT can not support route to multiple data nodes\\.","errorType":"exception","errorClass":"DMLMultipleDataNodesWithLimitException","httpStatus":null,"severity":"error","filePath":"features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/checker/dml/ShardingDeleteRouteContextChecker.java","lineNumber":37,"sourceCode":"\nimport org.apache.shardingsphere.infra.config.props.ConfigurationProperties;\nimport org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase;\nimport org.apache.shardingsphere.infra.route.context.RouteContext;\nimport org.apache.shardingsphere.infra.session.query.QueryContext;\nimport org.apache.shardingsphere.sharding.exception.syntax.DMLMultipleDataNodesWithLimitException;\nimport org.apache.shardingsphere.sharding.route.engine.checker.ShardingRouteContextChecker;\nimport org.apache.shardingsphere.sharding.rule.ShardingRule;\nimport org.apache.shardingsphere.sql.parser.statement.core.statement.type.dml.DeleteStatement;\n\n/**\n * Sharding delete route context checker.\n */\npublic final class ShardingDeleteRouteContextChecker implements ShardingRouteContextChecker {\n    \n    @Override\n    public void check(final ShardingRule shardingRule, final QueryContext queryContext, final ShardingSphereDatabase database, final ConfigurationProperties props, final RouteContext routeContext) {\n        if (((DeleteStatement) queryContext.getSqlStatementContext().getSqlStatement()).getLimit().isPresent() && routeContext.getRouteUnits().size() > 1) {\n            throw new DMLMultipleDataNodesWithLimitException(\"DELETE\");\n        }\n    }\n}\n","sourceCodeStart":19,"sourceCodeEnd":41,"githubUrl":"https://github.com/apache/shardingsphere/blob/e952770a215630a3659c75d64369168cd3e26b82/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/checker/dml/ShardingDeleteRouteContextChecker.java#L19-L41","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Add an exact-match predicate on the sharding key (e.g. WHERE order_id = ?) so the DELETE routes to a single data node.","Replace LIMIT semantics with an explicit deterministic selection: first SELECT the row, then DELETE by its primary key/sharding key.","Run the DELETE ... LIMIT directly against the target physical database, bypassing ShardingSphere."],"exampleFix":"-- before: multi-node route + LIMIT\nDELETE FROM t_order WHERE status = 'EXPIRED' LIMIT 10;\n\n-- after: pin to one shard with the sharding key\nDELETE FROM t_order WHERE order_id = ? AND status = 'EXPIRED' LIMIT 10;","handlingStrategy":"validation","validationCode":"// Reject DELETE ... LIMIT before sending when it cannot route to one node\nboolean hasLimit = sql.toUpperCase(Locale.ROOT).contains(\"LIMIT\");\nSet<String> shardingKeys = Set.of(\"order_id\"); // from rule\nboolean hasShardingKeyEquality =/* inspect WHERE for key = literal/param */ false;\nif (\"DELETE\".equals(operation) && hasLimit && !hasShardingKeyEquality) {\n    throw new IllegalArgumentException(\"DELETE ... LIMIT requires single-node routing\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    statement.executeUpdate(deleteSql);\n} catch (final DMLMultipleDataNodesWithLimitException ex) {\n    // re-issue per shard with explicit primary keys, or run on physical DB directly\n}","preventionTips":["Port DELETE ... LIMIT patterns to deterministic SELECT-then-DELETE-by-key flows.","Add a lint rule in code review / ORM interceptors that flags LIMIT on sharded DELETE/UPDATE."],"tags":["sharding","dml","delete","limit","routing"],"backgroundTag":null,"analyzedSha":"e952770a215630a3659c75d64369168cd3e26b82","analyzedAt":"2026-08-14T13:54:53.392Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}