{"record":{"id":"e08a9bbc2fa5c1af","repo":"MyCATApache/Mycat-Server","slug":"bad-insert-sql-sharding-column-partitioncolum","errorCode":null,"errorMessage":"\"bad insert sql (sharding column:\"+ partitionColumn + \" not provided,\" + insertStmt","messagePattern":"\"bad insert sql \\(sharding column:\"\\+ partitionColumn \\+ \" not provided,\" \\+ insertStmt","errorType":"exception","errorClass":"SQLNonTransientException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/parser/druid/impl/DruidInsertParser.java","lineNumber":203,"sourceCode":"\t\t\tif(partitionColumn.equalsIgnoreCase(StringUtil.removeBackquote(insertStmt.getColumns().get(i).toString()))) {//找到分片字段\n\t\t\t\tisFound = true;\n\t\t\t\tString column = StringUtil.removeBackquote(insertStmt.getColumns().get(i).toString());\n\n\t\t\t\tString shardingValue = StringUtil.removeBackquote(getShardingValue(insertStmt.getValues().getValues().get(i)));\n\t\t\t\tinsertStmt.getValues().getValues().set(i,new SQLCharExpr(shardingValue));\n\t\t\t\tctx.setSql(insertStmt.toString());\n\n\t\t\t\tRouteCalculateUnit routeCalculateUnit = new RouteCalculateUnit();\n\t\t\t\trouteCalculateUnit.addShardingExpr(tableName, column, shardingValue);\n\t\t\t\tctx.addRouteCalculateUnit(routeCalculateUnit);\n\t\t\t\t//mycat是单分片键，找到了就返回\n\t\t\t\tbreak;\n\t\t\t}\n\t\t}\n\t\tif(!isFound) {//分片表的\n\t\t\tString msg = \"bad insert sql (sharding column:\"+ partitionColumn + \" not provided,\" + insertStmt;\n\t\t\tLOGGER.warn(msg);\n\t\t\tthrow new SQLNonTransientException(msg);\n\t\t}\n\t\t// insert into .... on duplicateKey \n\t\t//such as :INSERT INTO TABLEName (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE b=VALUES(b); \n\t\t//INSERT INTO TABLEName (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;\n\t\tif(insertStmt.getDuplicateKeyUpdate() != null) {\n\t\t\tList<SQLExpr> updateList = insertStmt.getDuplicateKeyUpdate();\n\t\t\tfor(SQLExpr expr : updateList) {\n\t\t\t\tSQLBinaryOpExpr opExpr = (SQLBinaryOpExpr)expr;\n\t\t\t\tString column = StringUtil.removeBackquote(opExpr.getLeft().toString().toUpperCase());\n\t\t\t\tif(column.equals(partitionColumn)) {\n\t\t\t\t\tString msg = \"Sharding column can't be updated: \" + tableName + \" -> \" + partitionColumn;\n\t\t\t\t\tLOGGER.warn(msg);\n\t\t\t\t\tthrow new SQLNonTransientException(msg);\n\t\t\t\t}\n\t\t\t}\n\t\t}\n\t}\n\t","sourceCodeStart":185,"sourceCodeEnd":221,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/parser/druid/impl/DruidInsertParser.java#L185-L221","documentation":"For a sharded (non-child) table, Mycat needs the sharding/partition column's value from an INSERT to compute the target datanode. parserSingleInsert scans the statement's column list for the partition column; if it is absent (isFound == false), routing is impossible and it throws SQLNonTransientException.","triggerScenarios":"Single-row `INSERT INTO sharded_table (cols...) VALUES (...)` where the partition column configured in schema.xml rule (partitionColumn) is not among cols. Also triggered when column names use backquotes/case that defeat the uppercase compare after removeBackquote is skipped.","commonSituations":"Insert with an abbreviated column list omitting the shard key; relying on DB default/auto-increment for the shard key; partitionColumn typo in schema.xml (e.g. wrong case or name); table rule configured after data model change.","solutions":["Always include the sharding column and a concrete value in the INSERT column list and VALUES","Check schema.xml partitionColumn name matches the actual column exactly (it is compared uppercased)","If the column has a default/auto-increment, still pass an explicit value in the INSERT","Change the sharding rule/column if the application inserts without that column"],"exampleFix":"// before\nINSERT INTO orders (id, amount) VALUES (1, 99);\n// after\nINSERT INTO orders (id, user_id, amount) VALUES (1, 42, 99); -- user_id is partitionColumn","handlingStrategy":"validation","validationCode":"// validate shard key presence before single insert\nSet<String> cols = extractInsertColumns(sql); // uppercased\nString pc = tableConfig.getRule().getRuleAlgorithm() != null ? getPartitionColumn(tableConfig) : null;\nif (pc != null && !cols.contains(pc.toUpperCase())) {\n    throw new IllegalArgumentException(\"Insert must include sharding column: \" + pc);\n}","typeGuard":"static boolean containsShardingColumn(InsertStatement ins, TableConfig tc) {\n    String pc = tc.getPartitionColumn();\n    return pc == null || ins.getColumns().stream()\n        .map(c -> c.toString().toUpperCase())\n        .anyMatch(c -> c.equals(pc.toUpperCase()));\n}","tryCatchPattern":"try {\n    executeInsert(sql);\n} catch (SQLNonTransientException e) {\n    if (e.getMessage().startsWith(\"bad insert sql (sharding column:\")) {\n        throw new IllegalArgumentException(\"Include the sharding column in every INSERT\", e);\n    }\n    throw e;\n}","preventionTips":["Always specify full column lists including the shard key in INSERTs","Never rely on defaults/auto-increment for a sharding column","Cross-check partitionColumn in schema.xml against the real column name during deployments"],"tags":["sql","mycat","sharding","insert"],"backgroundTag":"missing-required-argument","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"}