{"record":{"id":"3ee4dc96811f5643","repo":"apache/shardingsphere","slug":"33","errorCode":"33","errorMessage":"PREPARE statement can not support sharding tables route to same data sources.","messagePattern":"PREPARE statement can not support sharding tables route to same data sources\\.","errorType":"exception","errorClass":"UnsupportedPrepareRouteToSameDataSourceException","httpStatus":null,"severity":"error","filePath":"features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/checker/ddl/ShardingPrepareRouteContextChecker.java","lineNumber":43,"sourceCode":"import org.apache.shardingsphere.sharding.exception.connection.EmptyShardingRouteResultException;\nimport org.apache.shardingsphere.sharding.exception.syntax.UnsupportedPrepareRouteToSameDataSourceException;\nimport org.apache.shardingsphere.sharding.route.engine.checker.ShardingRouteContextChecker;\nimport org.apache.shardingsphere.sharding.rule.ShardingRule;\n\nimport java.util.stream.Collectors;\n\n/**\n * Sharding prepare route context checker.\n */\npublic final class ShardingPrepareRouteContextChecker 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 (routeContext.getRouteUnits().isEmpty()) {\n            throw new EmptyShardingRouteResultException();\n        }\n        if (routeContext.getRouteUnits().stream().collect(Collectors.groupingBy(RouteUnit::getDataSourceMapper)).entrySet().stream().anyMatch(each -> each.getValue().size() > 1)) {\n            throw new UnsupportedPrepareRouteToSameDataSourceException();\n        }\n    }\n}\n","sourceCodeStart":25,"sourceCodeEnd":47,"githubUrl":"https://github.com/apache/shardingsphere/blob/e952770a215630a3659c75d64369168cd3e26b82/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/checker/ddl/ShardingPrepareRouteContextChecker.java#L25-L47","documentation":"UnsupportedPrepareRouteToSameDataSourceException is thrown by ShardingPrepareRouteContextChecker when a PREPARE statement routes multiple sharding tables into the same data source. The checker groups route units by data source mapper and rejects any group with more than one unit: a prepared statement cannot be represented by a single server-side statement when several sharded tables fan into one connection, so ShardingSphere refuses the route rather than executing it incorrectly.","triggerScenarios":"PREPARE a statement touching two or more sharding tables whose combined routing produces 2+ route units on the same data source (e.g. a join of t_order and t_order_item both landing units on ds_0), detected via groupingBy(RouteUnit::getDataSourceMapper) with any group size > 1.","commonSituations":"Server-side prepared statements or drivers with useServerPrepStmts=true issuing joins across sharding/binding tables; cross-shard queries that combine to one data source; upgrading workloads that previously used client-side prepare.","solutions":["Route the statement to a single table (add an exact sharding-key equality predicate so routing is single) or rewrite the join as separate statements per table.","Disable server-side prepare for this statement/connection (client-side prepare in the JDBC URL, e.g. useServerPrepStmts=false for MySQL Connector/J) so ShardingSphere parses and routes normally.","If the tables are binding tables, ensure the binding-table configuration is correct so the join routes as one unit per data source.","Execute the query through Proxy with a driver that does not force PREPARE for this statement."],"exampleFix":"# before: server-side prepare fans multiple tables onto one data source\njdbc:mysql://proxy:3307/db?useServerPrepStmts=true\nPREPARE p AS SELECT * FROM t_order o JOIN t_order_item i ON o.id=i.order_id;\n\n# after: client-side prepare lets ShardingSphere route the parsed SQL\njdbc:mysql://proxy:3307/db?useServerPrepStmts=false","handlingStrategy":"try-catch","validationCode":"// Detect multi-table statements before PREPARE and route them as text queries\nString sql = \"SELECT * FROM t_order o JOIN t_order_item i ON o.id = i.order_id\";\nboolean multiTable = Pattern.compile(\"\\\\b(join|,)?\\\\b(t_order\\\\b.*\\\\bt_order_item\\\\b|t_order_item\\\\b.*\\\\bt_order\\\\b)\", Pattern.CASE_INSENSITIVE).matcher(sql).find();\nif (multiTable) { useClientSidePrepare(connection); }","typeGuard":null,"tryCatchPattern":"try {\n    conn.serverPrepare(sql);\n} catch (final UnsupportedPrepareRouteToSameDataSourceException ex) {\n    // retry with client-side prepared statement (plain prepareStatement in JDBC mode)\n    try (PreparedStatement ps = conn.prepareStatement(sql)) { /* execute */ }\n}","preventionTips":["Use client-side prepare for joins across sharded tables.","Configure binding tables correctly so joined sharded tables route as one unit.","Monitor driver settings: server-side prepare is opt-in in most drivers — keep it off for cross-table SQL."],"tags":["sharding","prepare","routing","join","data-source"],"backgroundTag":null,"analyzedSha":"e952770a215630a3659c75d64369168cd3e26b82","analyzedAt":"2026-08-14T13:54:53.392Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}