What causes "invalid sql: + origSQL" in MyCATApache/Mycat-Server?

Rewrite the statement as a parenthesized VALUES insert: `INSERT INTO tbl (cols) VALUES (...)`; execute INSERT...SELECT directly on the backend if needed

MyCATApache/Mycat-Server · error · SQLNonTransientException

invalid sql: + origSQL

Error message

invalid sql: + origSQL

What it means

RouterUtil's batch-insert handling analyzes an INSERT statement to locate the VALUES clause for batch splitting. If no opening parenthesis is found in the SQL (firstLeftBracketIndex < 0), the statement is not in the expected `INSERT ... VALUES (...)` shape and is rejected as invalid with SQLNonTransientException.

Solutions

Rewrite the statement as a parenthesized VALUES insert: `INSERT INTO tbl (cols) VALUES (...)`; execute INSERT...SELECT directly on the backend if needed
For SET-style inserts, convert to VALUES syntax
Do a pre-check in client code that the SQL contains "(" before routing it through Mycat's batch-insert path

Example fix

-- before
INSERT INTO t_user SELECT * FROM tmp_users;
-- after
INSERT INTO t_user (id, name) VALUES (1,'a'), (2,'b');

Defensive patterns

Strategy: validation

Validate before calling

if (!sql.contains("(")) throw new IllegalArgumentException("INSERT must use VALUES (...) syntax for mycat routing");

Try / catch

try { executeInsert(sql); } catch (SQLNonTransientException e) { if (e.getMessage().startsWith("invalid sql:")) { rewriteToValuesInsert(sql); } else throw e; }

Prevention

Use only `INSERT ... VALUES (...)` syntax through Mycat
Run INSERT...SELECT / SET-style inserts directly on the backend
Lint generated SQL for supported INSERT forms
Reject non-parenthesized INSERTs at the app's SQL boundary

When it happens

Trigger: An INSERT statement routed through the batch-insert path that contains no parentheses — e.g. `INSERT INTO tbl SELECT * FROM other`, `INSERT INTO tbl SET a=1`, or a truncated/garbled statement.

Common situations: INSERT ... SELECT used to copy data (explicitly blocked here); MySQL SET-style INSERTs; ORM-generated INSERTs with unusual syntax hitting the batch-insert code path.

Understand the failure class

Background: "Invalid ... format", "must be in format X", "does not look like a ..." — invalid argument format errors across CLI tools and libraries — this error's family across 17 libraries.

Related errors

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

Appendix: source

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

		} else {
			return  -1;
		}

	}

	public static boolean processInsert(ServerConnection sc,SchemaConfig schema,
			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];
		}

View on GitHub (pinned to 65f8d8beb7)