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:" + ctx.getSql()

What it means

Thrown by RouterUtil when routing a SQL statement that touches multiple sharded tables: each table's sharding key resolves to a set of datanodes, and the code intersects the sets (retailAll). When the intersection is empty, no single datanode can serve all tables and the route is impossible, so a SQLNonTransientException is thrown with the original SQL attached. This fires for cross-shard joins/lookups whose sharding values point at disjoint nodes; it is deterministic (non-transient) — the SQL or data distribution must change, not be retried.

Solutions

1. Rewrite the SQL so the joined/related tables route to at least one common datanode, e.g. use the sharding column values of the child-table constraint in the WHERE clause so each table's route set overlaps
2. Check that the sharding columns used in the SQL actually restrict routes on all involved tables; add equality predicates on sharding keys for tables whose route set is unconstrained (null/empty routes also throw)
3. If the query legitimately spans unrelated shards, split it into multiple statements per shard or use a global table / different sharding strategy so routes intersect

When it happens

Trigger: Thrown at src/main/java/io/mycat/route/util/RouterUtil.java:1324 when the library encounters an invalid state.

Common situations: See trigger scenarios.

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

Appendix: source

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

		}


		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);
					Collections.shuffle(retNodeList);//by kaiz : add shuffle

View on GitHub (pinned to 65f8d8beb7)