MyCATApache/Mycat-Server · error · SQLNonTransientException

Sharding column can't be updated: ->

Error message

Sharding column can't be updated:  ->

What it means

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.

Solutions

  1. Remove the sharding column from the ON DUPLICATE KEY UPDATE assignment list; update only non-sharding columns.
  2. Delete + re-insert with the new sharding value via two statements if the key genuinely must change.
  3. Restructure the upsert so the sharding key is part of the uniqueness condition, not something updated.

Example fix

// before
INSERT INTO orders (id, name, customer_id) VALUES (1,'x',100)
  ON DUPLICATE KEY UPDATE customer_id = VALUES(customer_id);
// after
INSERT INTO orders (id, name, customer_id) VALUES (1,'x',100)
  ON DUPLICATE KEY UPDATE name = VALUES(name);
Defensive patterns

Strategy: validation

Validate before calling

// Strip the sharding column from upsert update lists
function validateUpsert(updateColumns, partitionColumn) {
  const bad = updateColumns.filter(c => c.toUpperCase() === partitionColumn.toUpperCase());
  if (bad.length) {
    throw new Error("ON DUPLICATE KEY UPDATE must not touch sharding column: " + bad.join(','));
  }
}

Prevention

When it happens

Trigger: INSERT ... ON DUPLICATE KEY UPDATE whose update assignments include the table's partitionColumn (e.g. ... ON DUPLICATE KEY UPDATE customer_id = VALUES(customer_id)).

Common situations: Generic upsert templates that update every inserted column, including the sharding key; MySQL-tolerant code reused against sharded Mycat tables.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

AI-assisted analysis of MyCATApache/Mycat-Server@65f8d8beb7 (2026-09-11). Data as JSON: /api/errors/34a8bb87c0d452f8. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/io/mycat/route/parser/druid/impl/DruidInsertParser.java:216

			}
		}
		if(!isFound) {//分片表的
			String msg = "bad insert sql (sharding column:"+ partitionColumn + " not provided," + insertStmt;
			LOGGER.warn(msg);
			throw new SQLNonTransientException(msg);
		}
		// insert into .... on duplicateKey 
		//such as :INSERT INTO TABLEName (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE b=VALUES(b); 
		//INSERT INTO TABLEName (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
		if(insertStmt.getDuplicateKeyUpdate() != null) {
			List<SQLExpr> updateList = insertStmt.getDuplicateKeyUpdate();
			for(SQLExpr expr : updateList) {
				SQLBinaryOpExpr opExpr = (SQLBinaryOpExpr)expr;
				String column = StringUtil.removeBackquote(opExpr.getLeft().toString().toUpperCase());
				if(column.equals(partitionColumn)) {
					String msg = "Sharding column can't be updated: " + tableName + " -> " + partitionColumn;
					LOGGER.warn(msg);
					throw new SQLNonTransientException(msg);
				}
			}
		}
	}
	
	/**
	 * insert into .... select .... 或insert into table() values (),(),....
	 * @param schema
	 * @param rrs
	 * @param insertStmt
	 * @throws SQLNonTransientException
	 */
	private void parserBatchInsert(SchemaConfig schema, RouteResultset rrs, String partitionColumn, 
			String tableName, MySqlInsertStatement insertStmt) throws SQLNonTransientException {
		//insert into table() values (),(),....
		if(insertStmt.getValuesList().size() > 1) {
			//字段列数
			int columnNum = insertStmt.getColumns().size();

View on GitHub (pinned to 65f8d8beb7)