apache/shardingsphere · error · UnsupportedPrepareRouteToSameDataSourceException

33

33

Error message

PREPARE statement can not support sharding tables route to same data sources.

What it means

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.

Source

Thrown at features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/checker/ddl/ShardingPrepareRouteContextChecker.java:43

import org.apache.shardingsphere.sharding.exception.connection.EmptyShardingRouteResultException;
import org.apache.shardingsphere.sharding.exception.syntax.UnsupportedPrepareRouteToSameDataSourceException;
import org.apache.shardingsphere.sharding.route.engine.checker.ShardingRouteContextChecker;
import org.apache.shardingsphere.sharding.rule.ShardingRule;

import java.util.stream.Collectors;

/**
 * Sharding prepare route context checker.
 */
public final class ShardingPrepareRouteContextChecker implements ShardingRouteContextChecker {
    
    @Override
    public void check(final ShardingRule shardingRule, final QueryContext queryContext, final ShardingSphereDatabase database, final ConfigurationProperties props, final RouteContext routeContext) {
        if (routeContext.getRouteUnits().isEmpty()) {
            throw new EmptyShardingRouteResultException();
        }
        if (routeContext.getRouteUnits().stream().collect(Collectors.groupingBy(RouteUnit::getDataSourceMapper)).entrySet().stream().anyMatch(each -> each.getValue().size() > 1)) {
            throw new UnsupportedPrepareRouteToSameDataSourceException();
        }
    }
}

View on GitHub (pinned to e952770a21)

Solutions

1. 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.
2. 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.
3. If the tables are binding tables, ensure the binding-table configuration is correct so the join routes as one unit per data source.
4. Execute the query through Proxy with a driver that does not force PREPARE for this statement.

Example fix

# before: server-side prepare fans multiple tables onto one data source
jdbc:mysql://proxy:3307/db?useServerPrepStmts=true
PREPARE p AS SELECT * FROM t_order o JOIN t_order_item i ON o.id=i.order_id;

# after: client-side prepare lets ShardingSphere route the parsed SQL
jdbc:mysql://proxy:3307/db?useServerPrepStmts=false

// Detect multi-table statements before PREPARE and route them as text queries
String sql = "SELECT * FROM t_order o JOIN t_order_item i ON o.id = i.order_id";
boolean 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();
if (multiTable) { useClientSidePrepare(connection); }

try {
    conn.serverPrepare(sql);
} catch (final UnsupportedPrepareRouteToSameDataSourceException ex) {
    // retry with client-side prepared statement (plain prepareStatement in JDBC mode)
    try (PreparedStatement ps = conn.prepareStatement(sql)) { /* execute */ }
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• 41
• 40
• 36
• 57
• 1

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