MyCATApache/Mycat-Server · error · SQLSyntaxErrorException

insert must provide ColumnList

Error message

insert must provide ColumnList

What it means

The INSERT must contain a column list right after the table name because MyCat does not know the backend table structure and needs the column list to locate the sharding key. If the VALUES keyword does not come after the position of the first '(' (valuesIndex + 6 <= firstLeftBracketIndex), the insert has no column structure and SQLSyntaxErrorException('insert must provide ColumnList') is thrown.

Solutions

1. Add an explicit column list: INSERT INTO t (col1, col2, ...) VALUES (...)
2. Always enumerate columns in generated SQL/ORM settings for sharded tables
3. Route such inserts to a single non-sharded node if column lists are impossible

Example fix

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

String upper = sql.toUpperCase();
int valuesIdx = upper.indexOf("VALUES");
int lbIdx = sql.indexOf('(');
if (valuesIdx < 0 || valuesIdx + 6 <= lbIdx) {
  throw new IllegalArgumentException("insert must include a column list before VALUES");
}

try { route(sql); } catch (SQLSyntaxErrorException e) { if (e.getMessage().contains("ColumnList")) { /* rewrite SQL with column list */ } throw e; }

When it happens

Trigger: Executing INSERT INTO t VALUES (...) without an explicit column list while the table is sharded and the router needs to parse columns to find the partition key; also statements where VALUES appears before any column-list bracket.

Common situations: Convenient shorthand inserts (no column list) written by hand or legacy code hitting a sharded table; ORM configured to omit column lists; table newly sharded so previously working inserts now fail.

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

• bad insert sql columnSize != valueSize:values:
• "bad insert sql (sharding column:"+ partitionColumn + " not…
• number of columns error
• number of values and columns have to match
• In subQuery,the or condition is not supported.

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

Appendix: source

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

		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);


		if(pkStart == -1){
			processedInsert = true;
			handleBatchInsert(sc, schema, sqlType,origSQL, valuesIndex, tableName, primaryKey, vauleArray, suffixStr);

View on GitHub (pinned to 65f8d8beb7)