MyCATApache/Mycat-Server · error · SQLNonTransientException

"bad insert sql columnSize != valueSize:" + columnNum + "…

Error message

"bad insert sql columnSize != valueSize:" + columnNum + " != " + valueClause.getValues().size() + "values:" + valueClause

What it means

In a batch insert every VALUES tuple must supply exactly one value per declared column. parserBatchInsert compares each ValuesClause size against the column count; a mismatch means the SQL itself is malformed for routing, so it throws SQLNonTransientException.

Solutions

1. Fix the SQL so every VALUES tuple has exactly the same number of values as the column list
2. If fields are missing, add explicit NULL or defaults for those positions in each tuple
3. Validate generated SQL (log and inspect the full statement) before sending through Mycat
4. Fix the generator (foreach template) that emits tuples of inconsistent arity

Example fix

// before
INSERT INTO t (a,b,c) VALUES (1,2),(3,4,5);
// after
INSERT INTO t (a,b,c) VALUES (1,2,DEFAULT),(3,4,5);

// every VALUES tuple must match column count
int colCount = extractInsertColumns(sql).size();
for (List<Object> tuple : valuesTuples) {
    if (tuple.size() != colCount) {
        throw new IllegalArgumentException("columnSize != valueSize: " + colCount + " != " + tuple.size());
    }
}

static boolean tuplesMatchColumns(List<List<Object>> tuples, int colCount) {
    return tuples.stream().allMatch(t -> t.size() == colCount);
}

try {
    executeBatchInsert(sql);
} catch (SQLNonTransientException e) {
    if (e.getMessage().startsWith("bad insert sql columnSize != valueSize")) {
        throw new IllegalArgumentException("Malformed batch insert: value arity mismatch", e);
    }
    throw e;
}

When it happens

Trigger: `INSERT INTO t (c1,c2,c3) VALUES (1,2),(3,4,5)` — any VALUES row whose size != insertStmt.getColumns().size() during batch insert routing.

Common situations: Programmatic SQL string building where one tuple misses a value; MyBatis foreach lists with null/missing entries; hand-written bulk inserts with copy-paste errors; trailing comma / wrong arity bugs.

Understand the failure class

Background: "Invalid ... format", "must be in format X", "does not look like a ..." — invalid argument format errors across CLI tools and libraries — this error's family across 17 libraries.

Related errors

• "ChildTable multi insert not provided"
• bad insert sql columnSize != valueSize:values:
• TODO:insert into .... select .... not supported!
• "create table from other table not supported :" + stmt
• "can't find table define in schema " + tableName + "…

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

Appendix: source

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

			int shardingColIndex = getShardingColIndex(insertStmt, partitionColumn);
			if(shardingColIndex == -1) {
				String msg = "bad insert sql (sharding column:"+ partitionColumn + " not provided," + insertStmt;
				LOGGER.warn(msg);
				throw new SQLNonTransientException(msg);
			} else {
				List<ValuesClause> valueClauseList = insertStmt.getValuesList();
				
				Map<Integer,List<ValuesClause>> nodeValuesMap = new HashMap<Integer,List<ValuesClause>>();
				Map<Integer,Integer> slotsMap = new HashMap<>();
				TableConfig tableConfig = schema.getTables().get(tableName);
				AbstractPartitionAlgorithm algorithm = tableConfig.getRule().getRuleAlgorithm();
				for(ValuesClause valueClause : valueClauseList) {
					if(valueClause.getValues().size() != columnNum) {
						String msg = "bad insert sql columnSize != valueSize:"
					             + 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>());

View on GitHub (pinned to 65f8d8beb7)