{"record":{"id":"1cf2393b9d60ff4d","repo":"MyCATApache/Mycat-Server","slug":"multi-table-related-update-not-supported-tables","errorCode":null,"errorMessage":"multi table related update not supported,tables:","messagePattern":"multi table related update not supported,tables:","errorType":"exception","errorClass":"SQLNonTransientException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/parser/druid/impl/DruidUpdateParser.java","lineNumber":33,"sourceCode":"import io.mycat.cache.DefaultLayedCachePool;\nimport io.mycat.config.model.SchemaConfig;\nimport io.mycat.config.model.TableConfig;\nimport io.mycat.route.RouteResultset;\nimport io.mycat.route.util.RouterUtil;\nimport io.mycat.util.StringUtil;\n\nimport java.sql.SQLNonTransientException;\nimport java.util.List;\nimport java.util.Map;\n\npublic class DruidUpdateParser extends DefaultDruidParser {\n    @Override\n    public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) throws SQLNonTransientException {\n        //这里限制了update分片表的个数只能有一个\n        if (ctx.getTables() != null && getUpdateTableCount() > 1 && !schema.isNoSharding()) {\n            String msg = \"multi table related update not supported,tables:\" + ctx.getTables();\n            LOGGER.warn(msg);\n            throw new SQLNonTransientException(msg);\n        }\n        MySqlUpdateStatement update = (MySqlUpdateStatement) stmt;\n        String tableName = StringUtil.removeBackquote(update.getTableName().getSimpleName().toUpperCase());\n\n        TableConfig tc = schema.getTables().get(tableName);\n\n        if (RouterUtil.isNoSharding(schema, tableName)) {//整个schema都不分库或者该表不拆分\n            RouterUtil.routeForTableMeta(rrs, schema, tableName, rrs.getStatement());\n            rrs.setFinishedRoute(true);\n            return;\n        }\n\n        String partitionColumn = tc.getPartitionColumn();\n        String joinKey = tc.getJoinKey();\n        if (tc.isGlobalTable() || (partitionColumn == null && joinKey == null)) {\n            //修改全局表 update 受影响的行数\n            RouterUtil.routeToMultiNode(false, rrs, tc.getDataNodes(), rrs.getStatement(), tc.isGlobalTable());\n            rrs.setFinishedRoute(true);","sourceCodeStart":15,"sourceCodeEnd":51,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/parser/druid/impl/DruidUpdateParser.java#L15-L51","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Rewrite the UPDATE to touch only one table, doing cross-table work in separate statements or in the application","Add the schema/table to a noSharding (non-sharded) schema so multi-table updates are passed through","Split the joined update into a SELECT of keys, then per-table UPDATE statements","If the SQL must run multi-table, send it directly to the backing MySQL node instead of routing through Mycat"],"exampleFix":"// before\nUPDATE order_item oi JOIN orders o ON oi.order_id=o.id SET oi.status='X' WHERE o.user_id=1;\n// after\nUPDATE order_item SET status='X' WHERE order_id IN (SELECT id FROM orders WHERE user_id=1);","handlingStrategy":"validation","validationCode":"Set<String> tables = druidParserCtx.getTables();\nif (tables != null && tables.size() > 1 && !schema.isNoSharding()) {\n    throw new SQLNonTransientException(\"multi-table UPDATE not supported: \" + tables);\n}","typeGuard":null,"tryCatchPattern":"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 */ } }","preventionTips":["Restrict application SQL to single-table UPDATEs on sharded schemas","Keep JOIN updates in unsharded (noSharding) schemas only","Add SQL linting in CI to reject multi-table UPDATE syntax","Document which schemas allow multi-table DML"],"tags":["mysql","sharding","sql-routing"],"backgroundTag":"unsupported-operation","analyzedSha":"65f8d8beb752f935752f2a0eec0ab017facab9ef","analyzedAt":"2026-09-11T00:12:21.696Z","contentChangedAt":"2026-09-11T00:12:21.696Z","schemaVersion":2},"datasetVersion":"2026-09-16T04:17:20.429Z"}