What causes ""Sharding column can't be updated " + tableName + "->" + partitionColumn" in MyCATApache/Mycat-Server?

Remove the sharding column from the SET list; update it via DELETE + INSERT into the correct shard instead

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 (partition) column: changing the shard key value could require moving the row to a different data node, which Mycat's UPDATE routing does not perform. In confirmShardColumnNotUpdated, if the SET item targets the partition column and shardColCanBeUpdated cannot prove the value stays routable, it throws SQLNonTransientException.

Solutions

Remove the sharding column from the SET list; update it via DELETE + INSERT into the correct shard instead
If the new value maps to the same data node, keep it but be aware shardColCanBeUpdated must be able to prove routability from the literal
Redesign the schema so frequently-updated fields are not used as the partition column

Example fix

-- before
UPDATE orders SET customer_id = 456 WHERE order_id = 1001;
-- after
INSERT INTO orders (order_id, customer_id, ...) SELECT ... -- insert into target shard with new key
DELETE FROM orders WHERE order_id = 1001; -- remove from old shard

Defensive patterns

Strategy: validation

Validate before calling

if (updateSetColumns.contains(tableConfig.getPartitionColumn())) {
    throw new IllegalArgumentException("cannot update sharding column " + tableConfig.getPartitionColumn());
}

Try / catch

try { executeUpdate(sql); } catch (SQLNonTransientException e) { if (e.getMessage().startsWith("Sharding column can't be updated")) { doDeleteInsertInstead(); } else throw e; }

Prevention

Treat the partition column as immutable after insert
Use delete+insert flows to change shard keys
Choose stable, rarely-modified columns as partition columns
Block shard-key columns in ORM dirty-checking/update builders

When it happens

Trigger: An UPDATE SET clause assigning to the column configured as <partitionColumn> for that table (e.g. `UPDATE orders SET customer_id=456 WHERE ...`) when the new value is not provably in the same shard.

Common situations: Data-correction scripts changing the shard key; ETL jobs backfilling the partition column; apps reassigning ownership of a row (e.g. moving an order to another customer).

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/2e5a2c57ad88e5ee. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/io/mycat/route/parser/druid/impl/DruidUpdateParser.java:234

    private void confirmShardColumnNotUpdated(SQLUpdateStatement update,SchemaConfig schema,String tableName,String partitionColumn,String joinKey,RouteResultset rrs) throws SQLNonTransientException {
        List<SQLUpdateSetItem> updateSetItem = update.getItems();
        if (updateSetItem != null && updateSetItem.size() > 0) {
            boolean hasParent = (schema.getTables().get(tableName).getParentTC() != null);
            for (SQLUpdateSetItem item : updateSetItem) {
                String column = StringUtil.removeBackquote(item.getColumn().toString().toUpperCase());
                //考虑别名，前面已经限制了update分片表的个数只能有一个，所以这里别名只能是分片表的
                if (column.contains(StringUtil.TABLE_COLUMN_SEPARATOR)) {
                    column = column.substring(column.indexOf(".") + 1).trim().toUpperCase();
                }
                if (partitionColumn != null && partitionColumn.equals(column)) {
                    boolean canUpdate;
                    canUpdate = ((update.getWhere() != null) && shardColCanBeUpdated(update.getWhere(),
                            partitionColumn, item.getValue(), false));

                    if (!canUpdate) {
                        String msg = "Sharding column can't be updated " + tableName + "->" + partitionColumn;
                        LOGGER.warn(msg);
                        throw new SQLNonTransientException(msg);
                    }
                }
                if (hasParent) {
                    if (column.equals(joinKey)) {
                        String msg = "Parent relevant column can't be updated " + tableName + "->" + joinKey;
                        LOGGER.warn(msg);
                        throw new SQLNonTransientException(msg);
                    }
                    rrs.setCacheAble(true);
                }
            }
        }
    }
}

View on GitHub (pinned to 65f8d8beb7)