{"record":{"id":"e63ebf15009f9682","repo":"MyCATApache/Mycat-Server","slug":"bad-insert-sql-columnsize-valuesize-column","errorCode":null,"errorMessage":"\"bad insert sql columnSize != valueSize:\" + columnNum + \" != \" + valueClause.getValues().size() + \"values:\" + valueClause","messagePattern":"\"bad insert sql columnSize != valueSize:\" \\+ columnNum \\+ \" != \" \\+ valueClause\\.getValues\\(\\)\\.size\\(\\) \\+ \"values:\" \\+ valueClause","errorType":"exception","errorClass":"SQLNonTransientException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/parser/druid/impl/DruidInsertParser.java","lineNumber":253,"sourceCode":"\t\t\tint shardingColIndex = getShardingColIndex(insertStmt, partitionColumn);\n\t\t\tif(shardingColIndex == -1) {\n\t\t\t\tString msg = \"bad insert sql (sharding column:\"+ partitionColumn + \" not provided,\" + insertStmt;\n\t\t\t\tLOGGER.warn(msg);\n\t\t\t\tthrow new SQLNonTransientException(msg);\n\t\t\t} else {\n\t\t\t\tList<ValuesClause> valueClauseList = insertStmt.getValuesList();\n\t\t\t\t\n\t\t\t\tMap<Integer,List<ValuesClause>> nodeValuesMap = new HashMap<Integer,List<ValuesClause>>();\n\t\t\t\tMap<Integer,Integer> slotsMap = new HashMap<>();\n\t\t\t\tTableConfig tableConfig = schema.getTables().get(tableName);\n\t\t\t\tAbstractPartitionAlgorithm algorithm = tableConfig.getRule().getRuleAlgorithm();\n\t\t\t\tfor(ValuesClause valueClause : valueClauseList) {\n\t\t\t\t\tif(valueClause.getValues().size() != columnNum) {\n\t\t\t\t\t\tString msg = \"bad insert sql columnSize != valueSize:\"\n\t\t\t\t\t             + columnNum + \" != \" + valueClause.getValues().size() \n\t\t\t\t\t             + \"values:\" + valueClause;\n\t\t\t\t\t\tLOGGER.warn(msg);\n\t\t\t\t\t\tthrow new SQLNonTransientException(msg);\n\t\t\t\t\t}\n\t\t\t\t\tSQLExpr expr = valueClause.getValues().get(shardingColIndex);\n\t\t\t\t\tString shardingValue = StringUtil.removeBackquote(getShardingValue(expr));\n\t\t\t\t\tvalueClause.getValues().set(shardingColIndex, new SQLCharExpr(shardingValue));\n\n\t\t\t\t\tInteger nodeIndex = algorithm.calculate(StringUtil.removeBackquote(shardingValue));\n\t\t\t\t\tif(algorithm instanceof SlotFunction){\n\t\t\t\t\t\tslotsMap.put(nodeIndex,((SlotFunction) algorithm).slotValue()) ;\n\t\t\t\t\t}\n\t\t\t\t\t//没找到插入的分片\n\t\t\t\t\tif(nodeIndex == null) {\n\t\t\t\t\t\tString msg = \"can't find any valid datanode :\" + tableName \n\t\t\t\t\t\t\t\t+ \" -> \" + partitionColumn + \" -> \" + shardingValue;\n\t\t\t\t\t\tLOGGER.warn(msg);\n\t\t\t\t\t\tthrow new SQLNonTransientException(msg);\n\t\t\t\t\t}\n\t\t\t\t\tif(nodeValuesMap.get(nodeIndex) == null) {\n\t\t\t\t\t\tnodeValuesMap.put(nodeIndex, new ArrayList<ValuesClause>());","sourceCodeStart":235,"sourceCodeEnd":271,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/parser/druid/impl/DruidInsertParser.java#L235-L271","documentation":"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.","triggerScenarios":"`INSERT INTO t (c1,c2,c3) VALUES (1,2),(3,4,5)` — any VALUES row whose size != insertStmt.getColumns().size() during batch insert routing.","commonSituations":"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.","solutions":["Fix the SQL so every VALUES tuple has exactly the same number of values as the column list","If fields are missing, add explicit NULL or defaults for those positions in each tuple","Validate generated SQL (log and inspect the full statement) before sending through Mycat","Fix the generator (foreach template) that emits tuples of inconsistent arity"],"exampleFix":"// before\nINSERT INTO t (a,b,c) VALUES (1,2),(3,4,5);\n// after\nINSERT INTO t (a,b,c) VALUES (1,2,DEFAULT),(3,4,5);","handlingStrategy":"validation","validationCode":"// every VALUES tuple must match column count\nint colCount = extractInsertColumns(sql).size();\nfor (List<Object> tuple : valuesTuples) {\n    if (tuple.size() != colCount) {\n        throw new IllegalArgumentException(\"columnSize != valueSize: \" + colCount + \" != \" + tuple.size());\n    }\n}","typeGuard":"static boolean tuplesMatchColumns(List<List<Object>> tuples, int colCount) {\n    return tuples.stream().allMatch(t -> t.size() == colCount);\n}","tryCatchPattern":"try {\n    executeBatchInsert(sql);\n} catch (SQLNonTransientException e) {\n    if (e.getMessage().startsWith(\"bad insert sql columnSize != valueSize\")) {\n        throw new IllegalArgumentException(\"Malformed batch insert: value arity mismatch\", e);\n    }\n    throw e;\n}","preventionTips":["Build batch inserts from typed row objects instead of string concatenation","Log the full SQL for generated batch inserts in dev/test","Add unit tests covering tuple arity for foreach-style SQL generators"],"tags":["sql","mycat","batch-insert","malformed-sql"],"backgroundTag":"invalid-argument-format","analyzedSha":"65f8d8beb752f935752f2a0eec0ab017facab9ef","analyzedAt":"2026-09-11T00:12:21.696Z","contentChangedAt":"2026-09-11T00:12:21.696Z","schemaVersion":2},"datasetVersion":"2026-09-16T04:17:20.429Z"}