MyCATApache/Mycat-Server · error · SQLNonTransientException

multi insert not provided

Error message

multi insert not provided

What it means

MyCat's insert router detects INSERT statements of the form INSERT INTO t (...) SELECT ... FROM ... (batch/insert-select) by checking SELECT/FROM positions relative to the closing bracket, and rejects them because multi-row/insert-select routing is not supported. It throws SQLNonTransientException('multi insert not provided'). Only single-row VALUES inserts are routable here.

Solutions

1. Replace INSERT ... SELECT with per-row INSERT ... VALUES statements, or do the copy directly on the backend MySQL nodes
2. Add the VALUES clause form: INSERT INTO t (cols) VALUES (...), (...) only if supported by your MyCat version (multi-row values may also be restricted)
3. Bypass MyCat for bulk copies (mysqldump/import directly to data nodes)

Example fix

-- before
INSERT INTO t_order (id, user_id) SELECT id, user_id FROM tmp_order;
-- after
INSERT INTO t_order (id, user_id) VALUES (1, 100);
INSERT INTO t_order (id, user_id) VALUES (2, 101);

String upper = sql.toUpperCase();
if (upper.contains("SELECT") && upper.contains("FROM") && !upper.contains("VALUES")) {
  throw new IllegalArgumentException("insert-select is not supported through sharded routing");
}

try { route(sql); } catch (SQLNonTransientException e) { if ("multi insert not provided".equals(e.getMessage())) { /* do row-by-row or direct-to-node copy */ } throw e; }

When it happens

Trigger: Executing INSERT INTO table (cols) SELECT ... FROM ... where selectIndex>0, fromIndex>0, SELECT appears after the first right bracket, and there is no VALUES keyword (valuesIndex<0). Also multi-insert style statements without VALUES hitting the check.

Common situations: Migrating data with INSERT ... SELECT across shards; ETL scripts copying rows into a sharded table; tools generating insert-select statements that MyCat cannot shard.

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

• number of columns error
• number of values and columns have to match
• Can't identify the operation of of where
• bad insert sql columnSize != valueSize:values:
• TODO:insert into .... select .... not supported!

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

Appendix: source

Thrown at src/main/java/io/mycat/route/util/RouterUtil.java:659

			int sqlType,String origSQL,String tableName,String primaryKey) throws SQLNonTransientException {

		int firstLeftBracketIndex = origSQL.indexOf("(");
		int firstRightBracketIndex = origSQL.indexOf(")");
		String upperSql = origSQL.toUpperCase();
		int valuesIndex = upperSql.indexOf("VALUES");
		int selectIndex = upperSql.indexOf("SELECT");
		int fromIndex = upperSql.indexOf("FROM");
		//屏蔽insert into table1 select * from table2语句
		if(firstLeftBracketIndex < 0) {
			String msg = "invalid sql:" + origSQL;
			LOGGER.warn(msg);
			throw new SQLNonTransientException(msg);
		}
		//屏蔽批量插入
		if(selectIndex > 0 &&fromIndex>0&&selectIndex>firstRightBracketIndex&&valuesIndex<0) {
			String msg = "multi insert not provided" ;
			LOGGER.warn(msg);
			throw new SQLNonTransientException(msg);
		}
		//插入语句必须提供列结构，因为MyCat默认对于表结构无感知
		if(valuesIndex + "VALUES".length() <= firstLeftBracketIndex) {
			throw new SQLSyntaxErrorException("insert must provide ColumnList");
		}
		Object[] vauleArrayAndSuffixStr = parseSqlValueArrayAndSuffixStr(origSQL , valuesIndex);
		List<List<String>> vauleArray = (List<List<String>>) vauleArrayAndSuffixStr[0];
		String suffixStr = null;
		if (vauleArrayAndSuffixStr.length > 1) {
			suffixStr = (String) vauleArrayAndSuffixStr[1];
		}
		//两种情况处理 1 有主键的 id ,但是值为null 进行改下
		//            2 没有主键的 需要插入 进行改写

		//如果主键不在插入语句的fields中，则需要进一步处理
		boolean processedInsert= false;
		int pkStart = isPKInFields(origSQL,primaryKey,firstLeftBracketIndex,firstRightBracketIndex);

View on GitHub (pinned to 65f8d8beb7)