{"record":{"id":"520d932e2ad779b5","repo":"MyCATApache/Mycat-Server","slug":"multi-insert-not-provided","errorCode":null,"errorMessage":"multi insert not provided","messagePattern":"multi insert not provided","errorType":"exception","errorClass":"SQLNonTransientException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/util/RouterUtil.java","lineNumber":659,"sourceCode":"\t\t\tint sqlType,String origSQL,String tableName,String primaryKey) throws SQLNonTransientException {\r\n\r\n\t\tint firstLeftBracketIndex = origSQL.indexOf(\"(\");\r\n\t\tint firstRightBracketIndex = origSQL.indexOf(\")\");\r\n\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","sourceCodeStart":641,"sourceCodeEnd":677,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/util/RouterUtil.java#L641-L677","documentation":"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.","triggerScenarios":"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.","commonSituations":"Migrating data with INSERT ... SELECT across shards; ETL scripts copying rows into a sharded table; tools generating insert-select statements that MyCat cannot shard.","solutions":["Replace INSERT ... SELECT with per-row INSERT ... VALUES statements, or do the copy directly on the backend MySQL nodes","Add the VALUES clause form: INSERT INTO t (cols) VALUES (...), (...) only if supported by your MyCat version (multi-row values may also be restricted)","Bypass MyCat for bulk copies (mysqldump/import directly to data nodes)"],"exampleFix":"-- before\nINSERT INTO t_order (id, user_id) SELECT id, user_id FROM tmp_order;\n-- after\nINSERT INTO t_order (id, user_id) VALUES (1, 100);\nINSERT INTO t_order (id, user_id) VALUES (2, 101);","handlingStrategy":"validation","validationCode":"String upper = sql.toUpperCase();\nif (upper.contains(\"SELECT\") && upper.contains(\"FROM\") && !upper.contains(\"VALUES\")) {\n  throw new IllegalArgumentException(\"insert-select is not supported through sharded routing\");\n}","typeGuard":null,"tryCatchPattern":"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; }","preventionTips":["Use per-row VALUES inserts for sharded tables","Perform bulk copies directly on backend MySQL nodes","Document insert-select restrictions for ETL authors"],"tags":["sql","insert","unsupported-operation"],"backgroundTag":"unsupported-operation","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"}