MyCATApache/Mycat-Server · error · SQLNonTransientException

Sharding column can't be updated ->

Error message

Sharding column can't be updated ->

What it means

Mycat forbids UPDATE statements that change a table's sharding (partition) column, since moving a row to another shard cannot be routed atomically. If confirmShardColumnNotUpdated detects the partition column in the SET clause and the new value does not provably equal the WHERE-filtered value in the same statement, it throws this error naming the table and column.

Solutions

  1. Remove the sharding column from the SET clause if its value is not actually changing
  2. Migrate the row instead: DELETE from the old shard and INSERT into the new one (application-managed)
  3. Reshard by changing the partition key data model or re-partitioning offline, not via UPDATE
  4. If the value is identical, write the statement so WHERE value exactly matches the SET value with no OR clauses

Example fix

// before
UPDATE orders SET user_id=42 WHERE order_id=100;
// after (user_id is the sharding column)
DELETE FROM orders WHERE order_id=100;
INSERT INTO orders(order_id,user_id,...) VALUES(100,42,...);
Defensive patterns

Strategy: validation

Validate before calling

Set<String> setCols = extractSetColumns(updateStmt);
if (setCols.contains(partitionColumn)) {
    throw new IllegalArgumentException("sharding column " + partitionColumn + " must not be updated");
}

Try / catch

try { router.route(...) } catch (SQLNonTransientException e) { if (e.getMessage().startsWith("Sharding column can't be updated")) { convertToUpdateDeleteInsert(); } }

Prevention

When it happens

Trigger: UPDATE on a sharded table where a SET item targets the partitionColumn and the value is not confirmed identical to the value used in the WHERE condition (shardColCanBeUpdated returns false, including when the column appears in an OR/XOR clause).

Common situations: Changing a user_id/tenant_id/order date that happens to be the sharding key; migrating data by reassigning the shard key through UPDATE; ORMs that blindly include the key in SET.

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/af25fa45c1851bad. 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)