{"record":{"id":"173b4aa3c37f36c3","repo":"MyCATApache/Mycat-Server","slug":"sharding-column-can-t-be-updated-tablename","errorCode":null,"errorMessage":"\"Sharding column can't be updated: \" + tableName + \" -> \" + partitionColumn","messagePattern":"\"Sharding column can't be updated: \" \\+ tableName \\+ \" -> \" \\+ partitionColumn","errorType":"exception","errorClass":"SQLNonTransientException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/parser/druid/impl/DruidInsertParser.java","lineNumber":216,"sourceCode":"\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\n\t/**\n\t * insert into .... select .... 或insert into table() values (),(),....\n\t * @param schema\n\t * @param rrs\n\t * @param insertStmt\n\t * @throws SQLNonTransientException\n\t */\n\tprivate void parserBatchInsert(SchemaConfig schema, RouteResultset rrs, String partitionColumn, \n\t\t\tString tableName, MySqlInsertStatement insertStmt) throws SQLNonTransientException {\n\t\t//insert into table() values (),(),....\n\t\tif(insertStmt.getValuesList().size() > 1) {\n\t\t\t//字段列数\n\t\t\tint columnNum = insertStmt.getColumns().size();","sourceCodeStart":198,"sourceCodeEnd":234,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/parser/druid/impl/DruidInsertParser.java#L198-L234","documentation":"Mycat forbids updating a table's sharding column via `INSERT ... ON DUPLICATE KEY UPDATE`. Changing the partition column's value post-insert would move the row to a different datanode, which the INSERT path cannot do, so parserSingleInsert rejects it.","triggerScenarios":"`INSERT INTO sharded_table (...) VALUES (...) ON DUPLICATE KEY UPDATE <partitionColumn> = ...` — the ON DUPLICATE KEY UPDATE list contains a column equal to the configured partitionColumn.","commonSituations":"Upsert-style logic (insert-or-update counters/state) that also rewrites the shard key; generated ORM upserts including all columns in the update clause; partition column changed in config after an upsert was written.","solutions":["Remove the sharding column from the ON DUPLICATE KEY UPDATE list (update only non-shard columns)","Split into application logic: SELECT for existence, then either INSERT or UPDATE — the UPDATE of the shard key routed separately (or avoided entirely)","Redesign so the shard key is immutable; use a different column for the upsert conflict target","If shard-key change is truly required, DELETE + re-INSERT the row via Mycat"],"exampleFix":"// before\nINSERT INTO orders (id, user_id, amount) VALUES (1, 42, 99)\n  ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), amount = 99;\n// after\nINSERT INTO orders (id, user_id, amount) VALUES (1, 42, 99)\n  ON DUPLICATE KEY UPDATE amount = 99;","handlingStrategy":"try-catch","validationCode":"// strip shard key from ON DUPLICATE KEY UPDATE before sending\nList<String> updateCols = extractDuplicateKeyUpdateColumns(sql);\nif (updateCols.contains(partitionColumn.toUpperCase())) {\n    throw new IllegalArgumentException(\"ON DUPLICATE KEY UPDATE must not touch sharding column \" + partitionColumn);\n}","typeGuard":"static boolean updatesShardingColumn(InsertStatement ins, String partitionColumn) {\n    if (ins.getDuplicateKeyUpdate() == null) return false;\n    return ins.getDuplicateKeyUpdate().stream()\n        .map(e -> ((SQLBinaryOpExpr) e).getLeft().toString().toUpperCase())\n        .anyMatch(c -> c.equals(partitionColumn.toUpperCase()));\n}","tryCatchPattern":"try {\n    upsert(row);\n} catch (SQLNonTransientException e) {\n    if (e.getMessage().startsWith(\"Sharding column can't be updated\")) {\n        throw new IllegalArgumentException(\"Remove the shard key from ON DUPLICATE KEY UPDATE\", e);\n    }\n    throw e;\n}","preventionTips":["Treat the sharding column as immutable in upsert logic","Configure ORMs to exclude id/shard columns from ON DUPLICATE KEY UPDATE clauses","Review upsert templates whenever the sharding rule changes"],"tags":["sql","mycat","sharding","upsert"],"backgroundTag":"unsupported-operation","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"}