MyCATApache/Mycat-Server · error · SQLNonTransientException

"Sharding column can't be updated: " + tableName + " -> " +…

Error message

"Sharding column can't be updated: " + tableName + " -> " + partitionColumn

What it means

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.

Solutions

  1. Remove the sharding column from the ON DUPLICATE KEY UPDATE list (update only non-shard columns)
  2. Split into application logic: SELECT for existence, then either INSERT or UPDATE — the UPDATE of the shard key routed separately (or avoided entirely)
  3. Redesign so the shard key is immutable; use a different column for the upsert conflict target
  4. If shard-key change is truly required, DELETE + re-INSERT the row via Mycat

Example fix

// before
INSERT INTO orders (id, user_id, amount) VALUES (1, 42, 99)
  ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), amount = 99;
// after
INSERT INTO orders (id, user_id, amount) VALUES (1, 42, 99)
  ON DUPLICATE KEY UPDATE amount = 99;
Defensive patterns

Strategy: try-catch

Validate before calling

// strip shard key from ON DUPLICATE KEY UPDATE before sending
List<String> updateCols = extractDuplicateKeyUpdateColumns(sql);
if (updateCols.contains(partitionColumn.toUpperCase())) {
    throw new IllegalArgumentException("ON DUPLICATE KEY UPDATE must not touch sharding column " + partitionColumn);
}

Type guard

static boolean updatesShardingColumn(InsertStatement ins, String partitionColumn) {
    if (ins.getDuplicateKeyUpdate() == null) return false;
    return ins.getDuplicateKeyUpdate().stream()
        .map(e -> ((SQLBinaryOpExpr) e).getLeft().toString().toUpperCase())
        .anyMatch(c -> c.equals(partitionColumn.toUpperCase()));
}

Try / catch

try {
    upsert(row);
} catch (SQLNonTransientException e) {
    if (e.getMessage().startsWith("Sharding column can't be updated")) {
        throw new IllegalArgumentException("Remove the shard key from ON DUPLICATE KEY UPDATE", e);
    }
    throw e;
}

Prevention

When it happens

Trigger: `INSERT INTO sharded_table (...) VALUES (...) ON DUPLICATE KEY UPDATE <partitionColumn> = ...` — the ON DUPLICATE KEY UPDATE list contains a column equal to the configured partitionColumn.

Common situations: 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.

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/173b4aa3c37f36c3. 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)