{"record":{"id":"34a8bb87c0d452f8","repo":"MyCATApache/Mycat-Server","slug":"sharding-column-can-t-be-updated","errorCode":null,"errorMessage":"Sharding column can't be updated:  -> ","messagePattern":"Sharding column can't be updated:  -> ","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 changing a sharding column via INSERT ... ON DUPLICATE KEY UPDATE, because updating the partition key could require moving the row to a different data node, which the router does not do. parserSingleInsert inspects the duplicate-key update list and throws SQLNonTransientException if any assignment targets the partitionColumn.","triggerScenarios":"INSERT ... ON DUPLICATE KEY UPDATE whose update assignments include the table's partitionColumn (e.g. ... ON DUPLICATE KEY UPDATE customer_id = VALUES(customer_id)).","commonSituations":"Generic upsert templates that update every inserted column, including the sharding key; MySQL-tolerant code reused against sharded Mycat tables.","solutions":["Remove the sharding column from the ON DUPLICATE KEY UPDATE assignment list; update only non-sharding columns.","Delete + re-insert with the new sharding value via two statements if the key genuinely must change.","Restructure the upsert so the sharding key is part of the uniqueness condition, not something updated."],"exampleFix":"// before\nINSERT INTO orders (id, name, customer_id) VALUES (1,'x',100)\n  ON DUPLICATE KEY UPDATE customer_id = VALUES(customer_id);\n// after\nINSERT INTO orders (id, name, customer_id) VALUES (1,'x',100)\n  ON DUPLICATE KEY UPDATE name = VALUES(name);","handlingStrategy":"validation","validationCode":"// Strip the sharding column from upsert update lists\nfunction validateUpsert(updateColumns, partitionColumn) {\n  const bad = updateColumns.filter(c => c.toUpperCase() === partitionColumn.toUpperCase());\n  if (bad.length) {\n    throw new Error(\"ON DUPLICATE KEY UPDATE must not touch sharding column: \" + bad.join(','));\n  }\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Never include the sharding key in ON DUPLICATE KEY UPDATE assignments.","Review generic upsert templates that update all inserted columns."],"tags":["upsert","sharding-column","on-duplicate-key"],"backgroundTag":"unsupported-operation","analyzedSha":"65f8d8beb752f935752f2a0eec0ab017facab9ef","analyzedAt":"2026-09-11T00:12:21.696Z","contentChangedAt":"2026-09-11T00:12:21.696Z","schemaVersion":2},"datasetVersion":"2026-09-23T08:17:48.524Z"}