MyCATApache/Mycat-Server · error · SQLNonTransientException

can't find (root) parent sharding node for sql:

Error message

can't find (root) parent sharding node for sql:

What it means

To route a child-table insert, Mycat runs findRootTBSql (a SELECT on the root parent table using the join value) via FetchStoreNodeOfChildTableHandler against the root parent's dataNodes to learn which node holds the parent row. If that lookup returns null (parent row not found or not yet visible), the insert cannot be placed and this SQLNonTransientException is thrown.

Solutions

1. Insert the parent row (and commit it) before inserting child rows referencing it.
2. Verify the childTable's rootParent/dataNodes configuration in schema.xml points at the nodes actually holding the parent table.
3. Confirm the joinKey value in the child insert matches an existing parent primary/join key value.

Example fix

// before — parent missing
INSERT INTO order_items (item_id, order_id, qty) VALUES (9, 99999, 2);
// after — insert parent first, then child
INSERT INTO orders (id, customer_id) VALUES (99999, 7);
INSERT INTO order_items (item_id, order_id, qty) VALUES (9, 99999, 2);

// Verify the parent row exists (and is committed) before child insert
// SELECT node FROM root_parent WHERE join_value = ?  -- via Mycat
const exists = await query("SELECT 1 FROM orders WHERE id = ?", [joinValue]);
if (exists.length === 0) {
  throw new Error("Parent row missing; child insert would fail lookup");
}

try {
  execute(childInsert);
} catch (SQLNonTransientException e) {
  if (e.getMessage().startsWith("can't find (root) parent sharding node")) {
    // insert parent first, commit, then retry child insert
  } else { throw e; }
}

When it happens

Trigger: INSERT into a childTable whose joinKey value references a parent row that does not exist, was just inserted in the same uncommitted transaction, or is not visible on the root parent's dataNodes when the lookup executes.

Common situations: Referential mistakes: inserting child rows before/without the parent row; parent and child inserts in the same transaction where the lookup cannot see the uncommitted parent; rootParent dataNodes misconfigured in schema.xml so the lookup queries the wrong nodes.

Understand the failure class

Background: Record Not Found Errors: "not found", RecordNotFound, and "was not found" — what they mean and how to fix them — this error's family across 28 libraries.

Related errors

• joinKey not provided :
• ChildTable multi insert not provided
• joinKey not provided
• ChildTable multi insert not provided
• number of columns error

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

Appendix: source

Thrown at src/main/java/io/mycat/route/parser/druid/impl/DruidInsertParser.java:164

		}

		// route by sql query root parent's datanode
		String findRootTBSql = tc.getLocateRTableKeySql().toLowerCase() + joinKeyVal;
		if (LOGGER.isDebugEnabled()) {
			LOGGER.debug("find root parent's node sql "+ findRootTBSql);
		}

		String dn = null;
		if (tc.getRootParent().getFetchStoreNodeByJdbc()) {
			JDBCFetchStoreNodeOfChildTableHandler jdbcFetchHandler = new JDBCFetchStoreNodeOfChildTableHandler();
			dn = jdbcFetchHandler.execute(schema.getName(),findRootTBSql, tc.getRootParent().getDataNodes());
		} else {
			FetchStoreNodeOfChildTableHandler FetchHandler = new FetchStoreNodeOfChildTableHandler();
			FetchHandler.execute(schema.getName(),findRootTBSql, tc.getRootParent().getDataNodes());
		}

		if (dn == null) {
			throw new SQLNonTransientException("can't find (root) parent sharding node for sql:"+ sql);
		}
		if (LOGGER.isDebugEnabled()) {
			LOGGER.debug("found partion node for child table to insert "+ dn + " sql :" + sql);
		}
		return RouterUtil.routeToSingleNode(rrs, dn, sql);
	}
	
	/**
	 * 单条insert（非批量）
	 * @param schema
	 * @param rrs
	 * @param partitionColumn
	 * @param tableName
	 * @param insertStmt
	 * @throws SQLNonTransientException
	 */
	private void parserSingleInsert(SchemaConfig schema, RouteResultset rrs, String partitionColumn,
			String tableName, MySqlInsertStatement insertStmt) throws SQLNonTransientException {

View on GitHub (pinned to 65f8d8beb7)