MyCATApache/Mycat-Server · error · SQLNonTransientException

"bad insert sql (sharding column:"+ partitionColumn + " not…

Error message

"bad insert sql (sharding column:"+ partitionColumn + " not provided," + insertStmt

What it means

For a sharded (non-child) table, Mycat needs the sharding/partition column's value from an INSERT to compute the target datanode. parserSingleInsert scans the statement's column list for the partition column; if it is absent (isFound == false), routing is impossible and it throws SQLNonTransientException.

Solutions

  1. Always include the sharding column and a concrete value in the INSERT column list and VALUES
  2. Check schema.xml partitionColumn name matches the actual column exactly (it is compared uppercased)
  3. If the column has a default/auto-increment, still pass an explicit value in the INSERT
  4. Change the sharding rule/column if the application inserts without that column

Example fix

// before
INSERT INTO orders (id, amount) VALUES (1, 99);
// after
INSERT INTO orders (id, user_id, amount) VALUES (1, 42, 99); -- user_id is partitionColumn
Defensive patterns

Strategy: validation

Validate before calling

// validate shard key presence before single insert
Set<String> cols = extractInsertColumns(sql); // uppercased
String pc = tableConfig.getRule().getRuleAlgorithm() != null ? getPartitionColumn(tableConfig) : null;
if (pc != null && !cols.contains(pc.toUpperCase())) {
    throw new IllegalArgumentException("Insert must include sharding column: " + pc);
}

Type guard

static boolean containsShardingColumn(InsertStatement ins, TableConfig tc) {
    String pc = tc.getPartitionColumn();
    return pc == null || ins.getColumns().stream()
        .map(c -> c.toString().toUpperCase())
        .anyMatch(c -> c.equals(pc.toUpperCase()));
}

Try / catch

try {
    executeInsert(sql);
} catch (SQLNonTransientException e) {
    if (e.getMessage().startsWith("bad insert sql (sharding column:")) {
        throw new IllegalArgumentException("Include the sharding column in every INSERT", e);
    }
    throw e;
}

Prevention

When it happens

Trigger: Single-row `INSERT INTO sharded_table (cols...) VALUES (...)` where the partition column configured in schema.xml rule (partitionColumn) is not among cols. Also triggered when column names use backquotes/case that defeat the uppercase compare after removeBackquote is skipped.

Common situations: Insert with an abbreviated column list omitting the shard key; relying on DB default/auto-increment for the shard key; partitionColumn typo in schema.xml (e.g. wrong case or name); table rule configured after data model change.

Understand the failure class

Background: "missing required argument" and "the following required arguments were not provided": what required-argument errors mean and how to fix them — this error's family across 20 libraries.

Related errors

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

Appendix: source

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

			if(partitionColumn.equalsIgnoreCase(StringUtil.removeBackquote(insertStmt.getColumns().get(i).toString()))) {//找到分片字段
				isFound = true;
				String column = StringUtil.removeBackquote(insertStmt.getColumns().get(i).toString());

				String shardingValue = StringUtil.removeBackquote(getShardingValue(insertStmt.getValues().getValues().get(i)));
				insertStmt.getValues().getValues().set(i,new SQLCharExpr(shardingValue));
				ctx.setSql(insertStmt.toString());

				RouteCalculateUnit routeCalculateUnit = new RouteCalculateUnit();
				routeCalculateUnit.addShardingExpr(tableName, column, shardingValue);
				ctx.addRouteCalculateUnit(routeCalculateUnit);
				//mycat是单分片键，找到了就返回
				break;
			}
		}
		if(!isFound) {//分片表的
			String msg = "bad insert sql (sharding column:"+ partitionColumn + " not provided," + insertStmt;
			LOGGER.warn(msg);
			throw new SQLNonTransientException(msg);
		}
		// insert into .... on duplicateKey 
		//such as :INSERT INTO TABLEName (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE b=VALUES(b); 
		//INSERT INTO TABLEName (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
		if(insertStmt.getDuplicateKeyUpdate() != null) {
			List<SQLExpr> updateList = insertStmt.getDuplicateKeyUpdate();
			for(SQLExpr expr : updateList) {
				SQLBinaryOpExpr opExpr = (SQLBinaryOpExpr)expr;
				String column = StringUtil.removeBackquote(opExpr.getLeft().toString().toUpperCase());
				if(column.equals(partitionColumn)) {
					String msg = "Sharding column can't be updated: " + tableName + " -> " + partitionColumn;
					LOGGER.warn(msg);
					throw new SQLNonTransientException(msg);
				}
			}
		}
	}

View on GitHub (pinned to 65f8d8beb7)