OceanBase 数据库执行超长 insert all into 报错 -4002

问题现象

执行超长的 insert all into 语句前台报错。

1. 执行 SQL 脚本。

   obclient [OBTEST]> source load.sql
   ORA-00600: internal error code, arguments: -4002, Invalid argument
   

2. 共 732 行插入语句，由于篇幅限制展示 load.sql 部分内容。

   into t_insert(id,name) values (1,'AA1');
   

   into t_insert(id,name) values (2,'AA2');
   

   into t_insert(id,name) values (3,'AA3');
   

   into t_insert(id,name) values (4,'AA4');
   

   .......
   

   into t_insert(id,name) values (729,'AA729');
   

   into t_insert(id,name) values (730,'AA730');
   

   into t_insert(id,name) values (731,'AA731');
   

   into t_insert(id,name) values (732,'AA732');
   

   select * from t_insert where id=0;
   

3. 在 OceanBase 数据库 V3.2.3 版本中，observer.log 日志报错信息。

   [admin@xxx oceanbase]$ cat observer.log|grep -v " INFO "|grep "ret=-4002"
   

   输出结果如下。

   WARN  [COMMON] add_column_desc (ob_row_desc.cpp:74) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] [dc=0] failed to insert column desc(ret=-4002)
   WARN  [SQL.RESV] add_table_item (ob_dml_stmt.cpp:3010) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=116] [dc=0] set table bit index failed(ret=-4002)
   WARN  [SQL.RESV] resolve_generate_table_item (ob_dml_resolver.cpp:2607) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=7] [dc=0] add table item failed(ret=-4002)
   WARN  [SQL.RESV] resolve_multi_insert_subquey (ob_multi_table_insert_resolver.cpp:391) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] [dc=0] failed to resolve generate table item(ret=-4002)
   WARN  [SQL.RESV] resolve (ob_multi_table_insert_resolver.cpp:87) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] [dc=0] resolve single table insert failed(ret=-4002)
   WARN  [SQL.RESV] stmt_resolver_func (ob_resolver.cpp:179) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] [dc=0] execute stmt_resolver failed(ret=-4002, parse_tree.type_=4117)
   WARN  [SQL] generate_stmt (ob_sql.cpp:1945) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6] [dc=0] failed to resolve(ret=-4002)
   WARN  [SQL] generate_physical_plan (ob_sql.cpp:2058) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=9] [dc=0] Failed to generate stmt(ret=-4002, result.get_exec_context().need_disconnect()=false)
   WARN  [SQL] handle_physical_plan (ob_sql.cpp:4133) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] [dc=0] Failed to generate plan(ret=-4002, result.get_exec_context().need_disconnect()=false)
   WARN  [SQL] handle_text_query (ob_sql.cpp:1684) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] [dc=0] fail to handle physical plan(ret=-4002)
   WARN  [SERVER] test_and_save_retry_state (ob_query_retry_ctrl.cpp:541) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=115] [dc=0] do not need retry(client_ret=-4002, err=-4002, expected_stmt=true, THIS_WORKER.get_timeout_ts()=xxxxxxxxx, retry_type_=0, result.get_stmt_type()=2, result.get_exec_context().need_change_timeout_ret()=true, session->get_retry_info().get_last_query_retry_err()=0)
   WARN  [SERVER] do_process (obmp_query.cpp:685) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=121] [dc=0] run stmt_query failed, check if need retry(ret=-4002, cli_ret=-4002, retry_ctrl_.need_retry()=0, sql=insert all
   WARN  [SERVER] do_process (obmp_query.cpp:811) [16038][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=147] [dc=0] query failed(ret=-4002, retry_ctrl_.need_retry()=0)
   

4. 在 OceanBase 数据库 V3.2.4 中，无 -4002 的错误日志。

问题原因

OceanBase 数据库在 DML 处理会调用 add_column_desc 描述列信息，insert all 会把这些 insert values 展开当成一个表，所以当 into values 的行数太多时，会报列太多检查失败，经过测试验证，当 insert all into 的行数超过或等于 732 时会报 -4002。

适用版本

OceanBase 数据库 V2.x、V3.x 版本。 ​

解决方法

• 如果为不同表的插入，将 SQL 分拆，把行数拆小，或者直接使用单行的 insert into values。

• 如果为相同表的插入，可以使用 insert into values(row1),(row2),...,(rown)。