MyCATApache/Mycat-Server · error · SQLSyntaxErrorException

sub table not exists for on

Error message

 sub table not exists for  on 

What it means

When expanding an ER-distributed (child/sharded) table in a FROM clause, Mycat rewrites the table source into concrete sub-table names taken from each route node's subTableName. If a route node lacks a sub-table name (e.g. the parent/child relation was not resolved during routing), getDisTable throws indicating the sub table does not exist for that node.

Solutions

1. Verify childTable/parent (joinKey/parentKey) ER configuration in schema.xml is complete and correct.
2. Ensure the query includes the join key so the router can resolve which sub-table each node maps to.
3. Create the missing sub-tables on the relevant datanodes (check backend MySQL for missing child tables).
4. Enable Mycat's auto ER table creation or run the schema init scripts for the ER relation.

Example fix

// before (schema.xml)
<table name="order_item" dataNode="dn1" />
// after (ER child table properly configured)
<table name="order_item" dataNode="dn1,dn2" joinKey="order_id" parentKey="id" />

// verify ER child tables exist on all datanodes before querying
for (String dn : childTable.getChildNodes()) {
    if (!backendTableExists(dn, childTable.getSubTableName(parentNode))) {
        throw new IllegalStateException("Missing sub-table on datanode " + dn);
    }
}

try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {
    if (e.getMessage().contains("sub table not exists for")) {
        log.error("ER sub-table missing; check schema.xml joinKey/parentKey and backend DDL", e);
        throw e;
    } else throw e;
}

When it happens

Trigger: Executing SQL against a child table in an ER relation where a RouteResultsetNode's getSubTableName() is null during the from-clause rewrite (getDisTable, called from from2).

Common situations: Broken ER (foreign-key) configuration in schema.xml; queries joining child tables without the parent relation key; data nodes where the child sub-table was never created by the ER distribution.

Understand the failure class

Background: 'Could not be found', 'does not exist', 'not found in database': the resource-not-found family when an ID, slug, key, or URI lookup comes back empty — this error's family across 20 libraries.

Related errors

• mycat parse error on sql:
• sub table not exists for on
• can't find table define of in schema:
• "can't find table define in schema " + tableName + "…
• not a query sql statement

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

Appendix: source

Thrown at src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java:522

		}
		rrs.setNodes(nodes);

		//分表
		/**
		 *  subTables="t_order$1-2,t_order3"
		 *目前分表 1.6 开始支持 幵丏 dataNode 在分表条件下只能配置一个，分表条件下不支持join。
		 */
		if(rrs.isDistTable()){
			return this.routeDisTable(statement,rrs);
		}
		return rrs;
	}

	private SQLExprTableSource getDisTable(SQLTableSource tableSource,RouteResultsetNode node) throws SQLSyntaxErrorException{
		if(node.getSubTableName()==null){
			String msg = " sub table not exists for " + node.getName() + " on " + tableSource;
			LOGGER.error("DruidMycatRouteStrategyError " + msg);
			throw new SQLSyntaxErrorException(msg);
		}

		SQLIdentifierExpr sqlIdentifierExpr = new SQLIdentifierExpr();
		sqlIdentifierExpr.setParent(tableSource.getParent());
		sqlIdentifierExpr.setName(node.getSubTableName());
		SQLExprTableSource from2 = new SQLExprTableSource(sqlIdentifierExpr);
		return from2;
	}

	private RouteResultset routeDisTable(SQLStatement statement, RouteResultset rrs) throws SQLSyntaxErrorException{
		SQLTableSource tableSource = null;
		if(statement instanceof SQLInsertStatement) {
			SQLInsertStatement insertStatement = (SQLInsertStatement) statement;
			tableSource = insertStatement.getTableSource();
			for (RouteResultsetNode node : rrs.getNodes()) {
				SQLExprTableSource from2 = getDisTable(tableSource, node);
				insertStatement.setTableSource(from2);
				node.setStatement(insertStatement.toString());

View on GitHub (pinned to 65f8d8beb7)