What causes "bad insert sql columnSize != valueSize:values:" in MyCATApache/Mycat-Server?

Fix the SQL so every VALUES tuple has exactly one value per column in the column list

MyCATApache/Mycat-Server · error · SQLNonTransientException

bad insert sql columnSize != valueSize:values:

Error message

bad insert sql columnSize != valueSize:values:

What it means

DruidInsertParser throws SQLNonTransientException during batch insert routing when any VALUES clause in a multi-row INSERT has a different number of values than the declared column count (columnNum). MyCat must map the sharding column by index, so a ragged VALUES list is unrouteable. The mismatch message reports both sizes and the offending clause.

Solutions

Fix the SQL so every VALUES tuple has exactly one value per column in the column list
Explicitly list all columns in the INSERT and fill missing values with NULL in each tuple
If building SQL in code, assert values.size() == columns.size() before constructing the statement
Log the offending valueClause from the message and correct that specific row

Example fix

// before
INSERT INTO t(id, name) VALUES (1,'a'), (2);
// after
INSERT INTO t(id, name) VALUES (1,'a'), (2,NULL);

Defensive patterns

Strategy: validation

Validate before calling

if (rows.stream().anyMatch(r -> r.size() != columns.size())) throw new IllegalArgumentException("VALUES arity mismatch");

Type guard

boolean isValidInsert(List<String> columns, List<List<Object>> rows) { return rows.stream().allMatch(r -> r.size() == columns.size()); }

Try / catch

try { route(sql); } catch (SQLNonTransientException e) { if (e.getMessage().startsWith("bad insert sql columnSize")) { logAndFixArity(e); } else throw e; }

Prevention

Always write explicit column lists in INSERT statements
Generate value tuples from a schema-driven mapper, not by hand
Add a unit test asserting arity per row for bulk insert builders
Run the SQL through a parser/linter before sending to MyCat

When it happens

Trigger: statementParse -> parserBatchInsert on a multi-row INSERT INTO t(cols) VALUES (...),(...) where at least one row supplies fewer or more values than the column list; the check runs per valueClause in valueClauseList.

Common situations: Hand-written bulk inserts where one row misses a column; application code building VALUES lists programmatically and skipping NULL columns; schema migrations that added a column to the INSERT column list but not all value tuples.

Understand the failure class

Background: "query failed", "%w: SQL error" — wrapped database query errors in Go libraries explained — this error's family across 3 libraries.

Related errors

AI-assisted analysis of MyCATApache/Mycat-Server@65f8d8beb7 (2026-09-11). Data as JSON: /api/errors/c49314b17707d2e9. 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)