{"record":{"id":"c49314b17707d2e9","repo":"MyCATApache/Mycat-Server","slug":"bad-insert-sql-columnsize-valuesize-values","errorCode":null,"errorMessage":"bad insert sql columnSize != valueSize:values:","messagePattern":"bad insert sql columnSize != valueSize:values:","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":"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.","triggerScenarios":"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.","commonSituations":"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.","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"],"exampleFix":"// before\nINSERT INTO t(id, name) VALUES (1,'a'), (2);\n// after\nINSERT INTO t(id, name) VALUES (1,'a'), (2,NULL);","handlingStrategy":"validation","validationCode":"if (rows.stream().anyMatch(r -> r.size() != columns.size())) throw new IllegalArgumentException(\"VALUES arity mismatch\");","typeGuard":"boolean isValidInsert(List<String> columns, List<List<Object>> rows) { return rows.stream().allMatch(r -> r.size() == columns.size()); }","tryCatchPattern":"try { route(sql); } catch (SQLNonTransientException e) { if (e.getMessage().startsWith(\"bad insert sql columnSize\")) { logAndFixArity(e); } else throw e; }","preventionTips":["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"],"tags":["sql","insert","sharding","mycat"],"backgroundTag":"sql-query-failed","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"}