MyCATApache/Mycat-Server · error · SQLNonTransientException

"can't find any valid datanode :" + tableName + " -> " +…

Error message

"can't find any valid datanode :" + tableName + " -> " + partitionColumn + " -> " + shardingValue

What it means

After extracting each row's sharding value, Mycat asks the partition algorithm to calculate a datanode index. When algorithm.calculate() returns null — the sharding value maps to no configured datanode (out of rule range) — the row cannot be routed, so parserBatchInsert throws SQLNonTransientException.

Solutions

Extend the sharding rule (partition ranges/count) in schema.xml/rule.xml so all occurring sharding values map to a datanode
Clamp or remap sharding values at the application side before inserting
Find the offending value from the error message (tableName -> partitionColumn -> shardingValue) and correct the data
Use an algorithm with a default node or a modulo rule that covers the full value space

Example fix

// before (rule.xml)
<function name="shard-long" class="long"><property name="count">2</property>...</function>
// after: use a range covering all IDs, e.g.
<function name="shard-long" class="long"><property name="count">4</property>...</function>
-- or switch to a mod algorithm that always maps

Defensive patterns

Strategy: validation

Validate before calling

// check every sharding value maps to a datanode before inserting
AbstractPartitionAlgorithm alg = tableConfig.getRule().getRuleAlgorithm();
for (Object v : shardValues) {
    Integer node = alg.calculate(String.valueOf(v));
    if (node == null) {
        throw new IllegalArgumentException("Sharding value out of rule range: " + pc + " -> " + v);
    }
}

Type guard

static boolean allValuesRoutable(AbstractPartitionAlgorithm alg, List<String> values) {
    return values.stream().allMatch(v -> alg.calculate(v) != null);
}

Try / catch

try {
    executeBatchInsert(sql);
} catch (SQLNonTransientException e) {
    if (e.getMessage().startsWith("can't find any valid datanode")) {
        String[] parts = e.getMessage().split(" -> ");
        throw new IllegalArgumentException("Extend sharding rule for value " + parts[2], e);
    }
    throw e;
}

Prevention

Define sharding rules to cover the full value space (use mod/hash when ranges are unknown)
Monitor auto-increment IDs against rule boundaries
Pre-calculate datanodes in the app to detect out-of-range values early
Re-validate rule coverage whenever ID generation or dates in data change

When it happens

Trigger: Batch insert into a sharded table where a value of the partition column is outside the algorithm's valid range, e.g. an integer sharding value with no matching rule entry (mod/long rule boundaries not covering it) or an unenumerated enum/day value.

Common situations: Sharding rule configured for IDs 0..N but application inserts IDs beyond range (e.g. negative or very large IDs); auto-increment IDs exceeding rule bounds; date-partition rules hit by unexpected dates; rule function's map/count config smaller than actual data space.

Understand the failure class

Background: "value must be between 0 and 1" / "out of range" / "must not be negative" errors: fixing range-validation failures across open-source libraries — this error's family across 42 libraries.

Related errors

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

Appendix: source

Thrown at src/main/java/io/mycat/route/parser/druid/impl/DruidInsertParser.java:268

					             + columnNum + " != " + valueClause.getValues().size() 
					             + "values:" + valueClause;
						LOGGER.warn(msg);
						throw new SQLNonTransientException(msg);
					}
					SQLExpr expr = valueClause.getValues().get(shardingColIndex);
					String shardingValue = StringUtil.removeBackquote(getShardingValue(expr));
					valueClause.getValues().set(shardingColIndex, new SQLCharExpr(shardingValue));

					Integer nodeIndex = algorithm.calculate(StringUtil.removeBackquote(shardingValue));
					if(algorithm instanceof SlotFunction){
						slotsMap.put(nodeIndex,((SlotFunction) algorithm).slotValue()) ;
					}
					//没找到插入的分片
					if(nodeIndex == null) {
						String msg = "can't find any valid datanode :" + tableName 
								+ " -> " + partitionColumn + " -> " + shardingValue;
						LOGGER.warn(msg);
						throw new SQLNonTransientException(msg);
					}
					if(nodeValuesMap.get(nodeIndex) == null) {
						nodeValuesMap.put(nodeIndex, new ArrayList<ValuesClause>());
					}
					nodeValuesMap.get(nodeIndex).add(valueClause);
				}


				RouteResultsetNode[] nodes = new RouteResultsetNode[nodeValuesMap.size()];
				int count = 0;
				for(Map.Entry<Integer,List<ValuesClause>> node : nodeValuesMap.entrySet()) {
					Integer nodeIndex = node.getKey();
					List<ValuesClause> valuesList = node.getValue();
					insertStmt.getValuesList().clear();
					insertStmt.getValuesList().addAll(valuesList);
					// insertStmt.setValuesList(valuesList);
					if(tableConfig.isDistTable()) {
						nodes[count] = new RouteResultsetNode(tableConfig.getDataNodes().get(0),

View on GitHub (pinned to 65f8d8beb7)