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

Rewrite the INSERT to include an explicit column list: INSERT INTO t (col1, col2) VALUES (...)

MyCATApache/Mycat-Server · error · SQLNonTransientException

invalid sql

Error message

invalid sql:{origSQL}

What it means

During INSERT routing, RouterUtil locates the first '(' of the column list via the raw SQL; if no left bracket exists the statement cannot be parsed as an INSERT with a column list, so it throws SQLNonTransientException('invalid sql:...'). This also blocks INSERT INTO ... SELECT statements which lack the values column-list structure. The SQL is rejected as non-routable.

Solutions

Rewrite the INSERT to include an explicit column list: INSERT INTO t (col1, col2) VALUES (...)
Route INSERT ... SELECT statements to a single node instead of the sharded insert path (e.g. use hint or route to default node)
Fix SQL syntax so the statement is a well-formed INSERT with a values list

Example fix

-- before
INSERT INTO t_order SELECT * FROM tmp_order;
-- after
INSERT INTO t_order (id, user_id, amount) VALUES (1, 100, 9.99);

Defensive patterns

Strategy: validation

Validate before calling

// before routing
String upper = sql.toUpperCase();
if (!upper.matches("\\s*INSERT\\s+INTO\\s+\\S+\\s*\\(.*")) {
  throw new IllegalArgumentException("insert requires column list: " + sql);
}

Try / catch

try { route(sql); } catch (SQLNonTransientException e) { if (e.getMessage().startsWith("invalid sql:")) { /* fix or reroute to single node */ } throw e; }

Prevention

Always write INSERTs with explicit column lists
Avoid INSERT ... SELECT through MyCat sharded tables
Lint generated SQL in ORM/DAO layers

When it happens

Trigger: Executing an INSERT whose text has no '(' column list (e.g. INSERT INTO t VALUES (...) written without column list? no — specifically statements like INSERT INTO t SELECT * FROM t2 lacking '(' before firstRightBracketIndex handling), or any malformed insert where firstLeftBracketIndex < 0.

Common situations: INSERT ... SELECT statements routed through multi-node insert logic; hand-written or generated SQL with syntax mistakes; ORM emitting inserts without column lists hitting this 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/cf86e8bde6c49d17. Report an issue: GitHub.

Appendix: source

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

			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];
		}
		//两种情况处理 1 有主键的 id ,但是值为null 进行改下

View on GitHub (pinned to 65f8d8beb7)