{"record":{"id":"e345319d17953f13","repo":"MyCATApache/Mycat-Server","slug":"insert-must-provide-columnlist","errorCode":null,"errorMessage":"insert must provide ColumnList","messagePattern":"insert must provide ColumnList","errorType":"exception","errorClass":"SQLSyntaxErrorException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/util/RouterUtil.java","lineNumber":663,"sourceCode":"\t\tString upperSql = origSQL.toUpperCase();\r\n\t\tint valuesIndex = upperSql.indexOf(\"VALUES\");\r\n\t\tint selectIndex = upperSql.indexOf(\"SELECT\");\r\n\t\tint fromIndex = upperSql.indexOf(\"FROM\");\r\n\t\t//屏蔽insert into table1 select * from table2语句\r\n\t\tif(firstLeftBracketIndex < 0) {\r\n\t\t\tString msg = \"invalid sql:\" + origSQL;\r\n\t\t\tLOGGER.warn(msg);\r\n\t\t\tthrow new SQLNonTransientException(msg);\r\n\t\t}\r\n\t\t//屏蔽批量插入\r\n\t\tif(selectIndex > 0 &&fromIndex>0&&selectIndex>firstRightBracketIndex&&valuesIndex<0) {\r\n\t\t\tString msg = \"multi insert not provided\" ;\r\n\t\t\tLOGGER.warn(msg);\r\n\t\t\tthrow new SQLNonTransientException(msg);\r\n\t\t}\r\n\t\t//插入语句必须提供列结构，因为MyCat默认对于表结构无感知\r\n\t\tif(valuesIndex + \"VALUES\".length() <= firstLeftBracketIndex) {\r\n\t\t\tthrow new SQLSyntaxErrorException(\"insert must provide ColumnList\");\r\n\t\t}\r\n\t\tObject[] vauleArrayAndSuffixStr = parseSqlValueArrayAndSuffixStr(origSQL , valuesIndex);\r\n\t\tList<List<String>> vauleArray = (List<List<String>>) vauleArrayAndSuffixStr[0];\r\n\t\tString suffixStr = null;\r\n\t\tif (vauleArrayAndSuffixStr.length > 1) {\r\n\t\t\tsuffixStr = (String) vauleArrayAndSuffixStr[1];\r\n\t\t}\r\n\t\t//两种情况处理 1 有主键的 id ,但是值为null 进行改下\r\n\t\t//            2 没有主键的 需要插入 进行改写\r\n\r\n\t\t//如果主键不在插入语句的fields中，则需要进一步处理\r\n\t\tboolean processedInsert= false;\r\n\t\tint pkStart = isPKInFields(origSQL,primaryKey,firstLeftBracketIndex,firstRightBracketIndex);\r\n\r\n\r\n\t\tif(pkStart == -1){\r\n\t\t\tprocessedInsert = true;\r\n\t\t\thandleBatchInsert(sc, schema, sqlType,origSQL, valuesIndex, tableName, primaryKey, vauleArray, suffixStr);\r","sourceCodeStart":645,"sourceCodeEnd":681,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/util/RouterUtil.java#L645-L681","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Add an explicit column list: INSERT INTO t (col1, col2, ...) VALUES (...)","Always enumerate columns in generated SQL/ORM settings for sharded tables","Route such inserts to a single non-sharded node if column lists are impossible"],"exampleFix":"-- before\nINSERT INTO t_order VALUES (1, 100, 9.99);\n-- after\nINSERT INTO t_order (id, user_id, amount) VALUES (1, 100, 9.99);","handlingStrategy":"validation","validationCode":"String upper = sql.toUpperCase();\nint valuesIdx = upper.indexOf(\"VALUES\");\nint lbIdx = sql.indexOf('(');\nif (valuesIdx < 0 || valuesIdx + 6 <= lbIdx) {\n  throw new IllegalArgumentException(\"insert must include a column list before VALUES\");\n}","typeGuard":null,"tryCatchPattern":"try { route(sql); } catch (SQLSyntaxErrorException e) { if (e.getMessage().contains(\"ColumnList\")) { /* rewrite SQL with column list */ } throw e; }","preventionTips":["Configure ORMs to always emit column lists","Code-review shorthand INSERT INTO t VALUES (...)\n","Test inserts against sharded tables after schema changes"],"tags":["sql","insert","sharding"],"backgroundTag":"missing-required-argument","analyzedSha":"65f8d8beb752f935752f2a0eec0ab017facab9ef","analyzedAt":"2026-09-11T00:12:21.696Z","contentChangedAt":"2026-09-11T00:12:21.696Z","schemaVersion":2},"datasetVersion":"2026-09-16T04:17:20.429Z"}