MyCATApache/Mycat-Server · error · SQLNonTransientException

invalid route in sql, multi tables found but datanode has…

Error message

invalid route in sql, multi tables found but datanode has no intersection  sql:{sql}

What it means

Thrown when routing a SQL statement referencing multiple sharded tables whose calculated datanode sets have no intersection. Mycat routes multi-table statements to a single set of datanodes via retainAll(), and an empty intersection means the statement cannot be executed anywhere.

Solutions

1. Align sharding rules so related tables are co-located on the same datanodes (same algorithm/column for join keys)
2. Rewrite the SQL to route to a single datanode (add explicit equality on the partition column of each table matching the same node)
3. Use Mycat ER-division (childTable) configuration for parent-child join tables
4. Split the query into multiple single-table queries executed separately

Example fix

// before: join of two tables sharded to different nodes
SELECT * FROM orders o JOIN customer c ON o.cid = c.id WHERE c.id = 5;
// after: also constrain orders' partition column so both route to the same node
SELECT * FROM orders o JOIN customer c ON o.cid = c.id WHERE c.id = 5 AND o.cid = 5;

// Pre-check intersection of routes for tables in a join
Set<String> t1 = routeOf(tableA, condA), t2 = routeOf(tableB, condB);
t1.retainAll(t2);
if (t1.isEmpty()) throw new IllegalArgumentException("join tables not co-located");

try { rrs = route(...); } catch (SQLNonTransientException e) { if (e.getMessage().contains("no intersection")) { LOG.warn("rewrite join or align sharding rules"); } throw e; }

When it happens

Trigger: A join or multi-table statement where each table's partition-column conditions route to disjoint datanodes (retNodesSet.size() == 0 after retainAll).

Common situations: Joining two sharded tables that are sharded differently (different algorithms or column values landing on different nodes); queries joining tables co-located on different nodes; misaligned sharding configs across related tables.

Understand the failure class

Background: "Invalid state transition" errors: "status must be X, actually Y", "already rejected/charging/uninstalled", "cannot ... while running" — what they mean when a library rejects your call — this error's family across 31 libraries.

Related errors

• In case of slice table,there is no rule field in the…
• can't find any valid datanode : -> ->
• find no Route:
• can't find datanode for sharding column
• parent key can't find any valid datanode

AI-assisted analysis of MyCATApache/Mycat-Server@65f8d8beb7 (2026-09-11). Data as JSON: /api/errors/9c032f0660ee13a2. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/io/mycat/route/util/RouterUtil.java:1323

			}
		}


		boolean isFirstAdd = true;
		for(Map.Entry<String, Set<String>> entry : tablesRouteMap.entrySet()) {
			if(entry.getValue() == null || entry.getValue().size() == 0) {
				throw new SQLNonTransientException("parent key can't find any valid datanode ");
			} else {
				if(isFirstAdd) {
					retNodesSet.addAll(entry.getValue());
					isFirstAdd = false;
				} else {
					retNodesSet.retainAll(entry.getValue());
					if(retNodesSet.size() == 0) {//两个表的路由无交集
						String errMsg = "invalid route in sql, multi tables found but datanode has no intersection "
								+ " sql:" + ctx.getSql();
						LOGGER.warn(errMsg);
						throw new SQLNonTransientException(errMsg);
					}
				}
			}
		}

		if(retNodesSet != null && retNodesSet.size() > 0) {
			String tableName = tables.get(0);
			TableConfig tableConfig = schema.getTables().get(tableName.toUpperCase());
			if(tableConfig.isDistTable()){
			    routeToDistTableNode(schema, rrs, ctx.getSql(), tablesAndConditions, cachePool, isSelect, null);
				return rrs;
			}

			if(retNodesSet.size() > 1 && isAllGlobalTable(ctx, schema)) {
				// mulit routes ,not cache route result
				if (isSelect) {
					rrs.setCacheAble(false);
					ArrayList<String> retNodeList = new ArrayList<String>(retNodesSet);

View on GitHub (pinned to 65f8d8beb7)