OceanBase 数据库 Oracle 模式查询 user_tab_columns 报错 -5222, Incorrect value

问题描述

Oracle 模式下查询表 user_tab_columns 报错。

ORA-00600: internal error code, arguments: -5222, Incorrect value

问题复现如下。

1. 创建测试表。

   obclient [SYS]> CREATE TABLE "DAT_LOAN4" (
      "IR_FLAG" VARCHAR2(120) DEFAULT 'F',
      "DAT_PLAN_ID" VARCHAR2(100),
      "PART_TYPE" VARCHAR2(9) GENERATED ALWAYS AS ((case when (("DAT_PLAN_ID" is null) and ("IR_FLAG" = 'F')) then 'FixedRate' when (("DAT_PLAN_ID" is null) and ("IR_FLAG" = 'V')) then'FloatRate' when ("DAT_PLAN_ID" is not null) then 'Payplan' else 'FixedRate' end)) VIRTUAL,
      "ROW_NUMBER1" NUMBER(20));
   Query OK, 0 rows affected (0.276 sec)
   

2. 查询用户表上的列情况。

   obclient [SYS]> select * from user_tab_columns where table_name='DAT_LOAN4';
   ORA-00600: internal error code, arguments: -5222, Incorrect value
   

3. 显示数据库系统信息。

   obclient [SYS]> \s
   

   输出结果如下。

   obclient  Ver  Distrib 10.4.18-MariaDB, for Linux (x86_64) using readline 5.1
   
   Connection id:    2
   Current database:    SYS
   Current user:    SYS
   SSL:    Not in use
   Current pager:    stdout
   Using outfile:    ''
   Using delimiter:    ;
   Server version:    OceanBase 3.2.3.0 (r20220429172811-141f0018b07e9f8d269bb5f6dbd020cd419eb3fc) (Built Apr 29 2022 17:39:23)
   Protocol version:    10
   Connection:    127.1 via TCP/IP
   Server characterset:    utf8mb4
   Db     characterset:    utf8mb4
   Client characterset:    utf8mb4
   Conn.  characterset:    utf8mb4
   TCP port:    2883
   Active    --------------
   

对应日志信息如下。过滤出 -5222 报错信息，执行如下命令。

[root@ob xxx]# grep -i 'ret=-5222' observer.log

输出结果如下。

 WARN  [SQL.ENG] row_copy_size (ob_chunk_datum_store.h:70) [3919][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6] [dc=0] failed to eval expr datum(exprs.at(i)={type:"T_FUN_SYS_CAST", datum_meta:{type:"VARCHAR2", cs_type:"utf8mb4_bin", scale:-1, precision:2}, obj_meta:{type:"VARCHAR2", collation:"utf8mb4_bin", coercibility:"IMPLICIT"}, obj_datum_map:1, flag:2, eval_func:0xebf4dd0, eval_batch_func:0xeba8d60, inner_functions:0x7f2064e93bc0, inner_func_cnt:1, arg_cnt:2, parent_cnt:0, frame_idx:3, datum_off:1248, res_buf_off:6632, res_buf_len:128, eval_flags_off:0, pvt_skip_off:0, expr_ctx_id:4294967295, extra:3458764513820540960, batch_idx_mask:0, this:0x7f2064e89b40}, ret=-5222)
 WARN  [SQL.ENG] row_store_size (ob_chunk_datum_store.h:423) [3919][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=12] [dc=0] failed to calc store row size(ret=-5222)
 WARN  [COMMON] try_add_row (ob_scanner.cpp:193) [3919][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=6] [dc=0] fail to add_row to row store.(ret=-5222)
 WARN  [SQL.EXE] sync_send_result (ob_remote_executor_processor.cpp:340) [3919][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] [dc=0] fail add row to scanner(ret=-5222)
 WARN  [SQL.EXE] execute_remote_plan (ob_remote_executor_processor.cpp:577) [3919][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] [dc=0] sync send result failed(ret=-5222)
 WARN  [SQL.EXE] execute_with_sql (ob_remote_executor_processor.cpp:825) [3919][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] [dc=0] execute remote plan failed(ret=-5222, task={tenant_schema_version:1687918203033520, sys_schema_version:1687746003013120, runner_svr:"xxx.xxx.xxx.xxx:xxxx", ctrl_svr:"xxx.xxx.xxx.xxx:xxxx", task_id:{ob_job_id:{ob_execution_id:{server:"xxx.xxx.xxx.xxx:xxxx", execution_id:1822149, task_type:0, hash:10674127093739934131}, job_id:18446744073709551615}, task_id:0, task_cnt:0}, remote_sql_info:{use_ps:true, is_batched_stmt:false, ps_param_cnt:1, remote_sql:"select * from user_tab_columns where table_name=?", ps_params:[{obj:{"CHAR":"DAT_LOAN4", collation:"utf8mb4_bin", coercibility:"COERCIBLE"}, accuracy:{length:9, precision:2, scale:-1}, flag:1, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"CHAR", collation:"utf8mb4_bin", coercibility:"COERCIBLE"}}]}})

问题原因

Oracle 模式下 user_tab_columns 是一个视图，会去读取 column 的一些信息，读取生成列的默认值的时候，会展示生成列表达式的字符串。但是这个视图限制了字符串最大长度 128，而报错的场景下，拼出来的生成列表达式长度超过了 128，所以报错。

适用版本

OceanBase 数据库 V2.x 和 V3.x 版本。

解决方法

变更虚拟列为正常列并且创建前置触发器来完成同样的功能。

1. 若存在同名表则删除。

   obclient [SYS]> drop table DAT_LOAN4;
   Query OK, 0 rows affected (0.400 sec)
   

2. 创建测试表。

   obclient [SYS]> CREATE TABLE "DAT_LOAN4" (   "IR_FLAG" VARCHAR2(120) DEFAULT 'F',   "DAT_PLAN_ID" VARCHAR2(100),   "PART_TYPE" VARCHAR2(9) ,   "ROW_NUMBER1" NUMBER(20) );
   Query OK, 0 rows affected (0.228 sec)
   

3. 创建触发器。

   obclient [SYS]> create or replace trigger tr_test before insert on DAT_LOAN4 for each row begin :new.part_type:=(case when ((:new.DAT_PLAN_ID is null) and (:new.IR_FLAG = 'F')) then 'FixedRate' when ((:new.DAT_PLAN_ID is null) and (:new.IR_FLAG = 'V')) then 'FloatRate' when (:new.DAT_PLAN_ID is not null) then 'Payplan' else 'FixedRate' end);
   end;
   /
   Query OK, 0 rows affected (0.210 sec)
   

4. 插入测试数据。

   obclient [SYS]> insert into dat_loan4 values ('F',NULL,'test',20);
   Query OK, 1 row affected (0.273 sec)
   

5. 查询测试表内容。

   obclient [SYS]> select  * from dat_loan4;
   

   输出结果如下。

   +---------+-------------+-----------+-------------+
   | IR_FLAG | DAT_PLAN_ID | PART_TYPE | ROW_NUMBER1 |
   +---------+-------------+-----------+-------------+
   | F       | NULL        | FixedRate |          20 |
   +---------+-------------+-----------+-------------+
   1 row in set (0.067 sec)
   

6. 再次查询，结果正常。

   obclient [SYS]> select* from user_tab_columns where table_name='DAT_LOAN4';
   

   输出结果如下。

   +------------+-------------+-----------+---------------+-----------------+-------------+----------------+------------+----------+-----------+----------------+--------------+--------------+-----------+------------+---------+-----------+-------------+---------------+-------------+--------------------+----------------------+--------------+------------+-------------+-------------+-----------+---------------+---------------+-----------+
   | TABLE_NAME | COLUMN_NAME | DATA_TYPE | DATA_TYPE_MOD |     DATA_TYPE_OWNER | DATA_LENGTH | DATA_PRECISION | DATA_SCALE | NULLABLE | COLUMN_ID | DEFAULT_LENGTH | DATA_DEFAULT | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | LAST_ANALYZED | SAMPLE_SIZE | CHARACTER_SET_NAME | CHAR_COL_DECL_LENGTH | GLOBAL_STATS | USER_STATS | AVG_COL_LEN | CHAR_LENGTH | CHAR_USED | V80_FMT_IMAGE | DATA_UPGRADED | HISTOGRAM |
   +------------+-------------+-----------+---------------+-----------------+-------------+----------------+------------+----------+-----------+----------------+--------------+--------------+-----------+------------+---------+-----------+-------------+---------------+-------------+--------------------+----------------------+--------------+------------+-------------+-------------+-----------+---------------+---------------+-----------+
   | DAT_LOAN4  | ROW_NUMBER1 | NUMBER    | NULL          | NULL            |           0 |             20 |          0 | Y        |        19 |             19 | NULL         |           19 | NULL      | NULL       |      19 |        19 |          19 | NULL          |          19 | NULL               |                   19 | NULL         | NULL       |          19 |           0 | NULL      | NULL          | NULL          | NULL      |
   | DAT_LOAN4  | IR_FLAG     | VARCHAR2  | NULL          | NULL            |         120 |            120 |        120 | Y        |        16 |              3 | 'F'          |            3 | NULL      | NULL       |       3 |         3 |           3 | NULL          |           3 | CHAR_CS            |                    3 | NULL         | NULL       |           3 |         120 | B         | NULL          | NULL          | NULL      |
   | DAT_LOAN4  | DAT_PLAN_ID | VARCHAR2  | NULL          | NULL            |         100 |            100 |        100 | Y        |        17 |             17 | NULL         |           17 | NULL      | NULL       |      17 |        17 |          17 | NULL          |          17 | CHAR_CS            |                   17 | NULL         | NULL       |          17 |         100 | B         | NULL          | NULL          | NULL      |
   | DAT_LOAN4  | PART_TYPE   | VARCHAR2  | NULL          | NULL            |           9 |              9 |          9 | Y        |        18 |             18 | NULL         |           18 | NULL      | NULL       |      18 |        18 |          18 | NULL          |          18 | CHAR_CS            |                   18 | NULL         | NULL       |          18 |           9 | B         | NULL          | NULL          | NULL      |
   +------------+-------------+-----------+---------------+-----------------+-------------+----------------+------------+----------+-----------+----------------+--------------+--------------+-----------+------------+---------+-----------+-------------+---------------+-------------+--------------------+----------------------+--------------+------------+-------------+-------------+-----------+---------------+---------------+-----------+
   4 rows in set (0.018 sec)