MyCATApache/Mycat-Server · error · SQLNonTransientException

multi table related update not supported,tables:

Error message

multi table related update not supported,tables:

What it means

Mycat throws this when an UPDATE statement touches more than one table in a sharded schema. The router only supports updating a single sharding table per statement, because multi-table routed updates cannot be consistently fanned out to backends. The message lists the tables parsed from the statement.

Solutions

1. Rewrite the UPDATE to touch only one table, doing cross-table work in separate statements or in the application
2. Add the schema/table to a noSharding (non-sharded) schema so multi-table updates are passed through
3. Split the joined update into a SELECT of keys, then per-table UPDATE statements
4. If the SQL must run multi-table, send it directly to the backing MySQL node instead of routing through Mycat

Example fix

// before
UPDATE order_item oi JOIN orders o ON oi.order_id=o.id SET oi.status='X' WHERE o.user_id=1;
// after
UPDATE order_item SET status='X' WHERE order_id IN (SELECT id FROM orders WHERE user_id=1);

Set<String> tables = druidParserCtx.getTables();
if (tables != null && tables.size() > 1 && !schema.isNoSharding()) {
    throw new SQLNonTransientException("multi-table UPDATE not supported: " + tables);
}

try { routeService.route(sysconf, schema, sqlType, stmt, charset, source) } catch (SQLNonTransientException e) { if (e.getMessage().startsWith("multi table related update")) { /* rewrite to single-table update or send direct to backend */ } }

When it happens

Trigger: Calling DruidUpdateParser.statementParse (via route()) with an UPDATE whose SQL references more than one table (e.g. UPDATE t1 JOIN t2 SET ... or multi-table UPDATE) while the schema is not configured as noSharding.

Common situations: Porting MySQL multi-table UPDATE syntax that worked directly on MySQL to a sharded Mycat schema; JOIN-based updates across sharded and non-sharded tables; schemas missing the noSharding flag for legacy global-update SQL.

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

• global table is not supported in multi table related update
• Unhandled SQL AST node type encountered:
• SQL AST nodes type mismatch!
• Sharding column can't be updated ->
• Parent relevant column can't be updated ->

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

Appendix: source

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

import io.mycat.cache.DefaultLayedCachePool;
import io.mycat.config.model.SchemaConfig;
import io.mycat.config.model.TableConfig;
import io.mycat.route.RouteResultset;
import io.mycat.route.util.RouterUtil;
import io.mycat.util.StringUtil;

import java.sql.SQLNonTransientException;
import java.util.List;
import java.util.Map;

public class DruidUpdateParser extends DefaultDruidParser {
    @Override
    public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) throws SQLNonTransientException {
        //这里限制了update分片表的个数只能有一个
        if (ctx.getTables() != null && getUpdateTableCount() > 1 && !schema.isNoSharding()) {
            String msg = "multi table related update not supported,tables:" + ctx.getTables();
            LOGGER.warn(msg);
            throw new SQLNonTransientException(msg);
        }
        MySqlUpdateStatement update = (MySqlUpdateStatement) stmt;
        String tableName = StringUtil.removeBackquote(update.getTableName().getSimpleName().toUpperCase());

        TableConfig tc = schema.getTables().get(tableName);

        if (RouterUtil.isNoSharding(schema, tableName)) {//整个schema都不分库或者该表不拆分
            RouterUtil.routeForTableMeta(rrs, schema, tableName, rrs.getStatement());
            rrs.setFinishedRoute(true);
            return;
        }

        String partitionColumn = tc.getPartitionColumn();
        String joinKey = tc.getJoinKey();
        if (tc.isGlobalTable() || (partitionColumn == null && joinKey == null)) {
            //修改全局表 update 受影响的行数
            RouterUtil.routeToMultiNode(false, rrs, tc.getDataNodes(), rrs.getStatement(), tc.isGlobalTable());
            rrs.setFinishedRoute(true);

View on GitHub (pinned to 65f8d8beb7)