本文介绍 OceanBase 数据库 Oracle 租户中普通用户先设置表为只读然后再改为读写时报 ORA-00600: internal error code, arguments: -5235 的原因和解决方法。
问题现象
OceanBase 数据库 Oracle 模式下，普通用户将表转换为只读之后，再转换为读写时报错。错误信息如下。
ORA-00600: internal error code, arguments: -5235, The table 'TESTREAD.TEST_READONLY' is read only so it cannot execute this statement
问题示例如下。
使用 SYS 用户登录 Oracle 租户。
$obclient -hxx.xx.xx.xx -Pxx -usys@oracle_xxx -p*** -A Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221518721 Server version: OceanBase 3.2.3.3 (r108000062023041511-33aee97541a93ccde986373e55fd0ba188615f29) (Built Apr 15 2023 11:55:17) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient [SYS]>创建普通用户。
obclient [SYS]> create user testreadonly identified by test01; Query OK, 0 rows affected (0.075 sec)授予普通用户权限。
obclient [SYS]> grant connect,resource to testreadonly; Query OK, 0 rows affected (0.074 sec)登录普通用户 testreadonly。
$obclient -hxx.xx.xx.xx -Pxx -utestreadonly@oracle_xxx -p*** -A Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221519008 Server version: OceanBase 3.2.3.3 (r108000062023041511-33aee97541a93ccde986373e55fd0ba188615f29) (Built Apr 15 2023 11:55:17) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient [TESTREADONLY]>创建只读表。
obclient [TESTREADONLY]> create table test_readonly(id number); Query OK, 0 rows affected (0.065 sec)进行只读切换读写操作。
obclient [TESTREADONLY]> alter table test_readonly read only; Query OK, 0 rows affected (0.054 sec)obclient [TESTREADONLY]> alter table test_readonly read write; ORA-00600: internal error code, arguments: -5235, The table 'TESTREADONLY.TEST_READONLY' is read only so it cannot execute this statement查看错误日志信息。
[admin@xxxx /home/admin/xxxxxxx/oceanbase/log] $grep -E 'TESTREADONLY|ret=-5235|ORA-00600' observer.log输出结果如下:
[2023-11-24 10:03:28.147465] WARN verify_table_read_only (ob_schema_getter_guard.cpp:2841) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] [dc=0] ORA-00600: internal error code, arguments: -5235, The table 'TESTREADONLY.TEST_READONLY' is read only so it cannot execute this statement [2023-11-24 10:03:28.147484] WARN [SHARE.SCHEMA] verify_table_read_only (ob_schema_getter_guard.cpp:2843) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=13] [dc=0] table is read only, can't not execute this statment(need_priv={db: TESTREADONLY", table:"TEST_READONLY", priv_set:2, priv_level:3, is_sys_table:false}, tenant_id=1002, ret=-5235) [2023-11-24 10:03:28.147490] WARN [SHARE.SCHEMA] verify_read_only (ob_schema_getter_guard.cpp:2786) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=5] [dc=0] table is read only, can't not execute this statement(ret=-5235) [2023-11-24 10:03:28.147493] WARN [SQL.SESSION] check_privilege_new (ob_privilege_check.cpp:2259) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] [dc=0] database or table is read only, cannot execute this stmt(ret=-5235) [2023-11-24 10:03:28.147550] WARN [SQL] generate_physical_plan (ob_sql.cpp:2076) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] [dc=0] Failed to check ora privilege info(ret=-5235, *basic_stmt={ObTableStmt:{part_type:11}, stmt_type:22, alter_table_arg:{is_alter_columns:false, is_alter_indexs:false, is_alter_options:true, session_id:18446744073709551615, index_arg_list:[], alter_table_schema:{alter_type:"OB_INVALID_DDL_OP", origin_table_name:"TEST_READONLY", new_database_name:"TESTREADONLY", origin_database_name:"TESTREADONLY", split_partition_name:"", split_high_bound_val:, split_list_row_values:, alter_table_schema:{simple_table_schema:{tenant_id:1002, database_id:18446744073709551615, tablegroup_id:18446744073709551615, table_id:1101710651081591, table_name:"TEST_READONLY", replica_num:0, previous_locality_str:"", min_partition_id:0, session_id:0, index_type:0, table_type:3, table_mode:{table_mode_flag:0, pk_mode:0}, tablespace_id:18446744073709551615, data_table_id:0, name_casemode:-1, schema_version:0, part_level:0, part_option:{part_func_type:0, part_func_expr:"", part_num:1, partition_cnt_within_partition_table:-1, max_used_part_id:-1, auto_part:false, auto_part_size:-1}, sub_part_option:{part_func_type:0, part_func_expr:"", part_num:1, partition_cnt_within_partition_table:-1, max_used_part_id:-1, auto_part:false, auto_part_size:-1}, locality_str:"", zone_list:[], primary_zone:"", part_num:0, def_subpart_num:0, partition_num:0, def_subpartition_num:0, partition_array:null, def_subpartition_array:null, dropped_partition_array:null, zone_replica_attr_array:[], primary_zone_array:[], index_status:1, binding:false, duplicate_scope:0, encryption:"", encrypt_key:"", master_key_id:18446744073709551615, drop_schema_version:-1, is_sub_part_template:true}, max_used_column_id:0, max_used_constraint_id:0, sess_active_time:0, rowkey_column_num:0, index_column_num:0, rowkey_split_pos:0, block_size:16384, is_use_bloomfilter:false, progressive_merge_num:0, tablet_size:-1, pctfree:10, load_type:0, index_using_type:0, def_type:1, charset_type:0, collation_type:0, create_mem_version:0, index_status:1, partition_status:0, code_version:1, last_modified_frozen_version:0, comment:"", pk_comment:"", create_host:"", tablegroup_name:"", compress_func_name:"", row_store_type:4, store_format:0, expire_info:"", view_schema:{view_definition:"", check_option:"none", is_updatable:"false", is_materialized:"false", character_set_client:0, collation_connection:"invalid_type"}, autoinc_column_id:0, auto_increment:1, read_only:false, simple_index_infos:[], mv_tid_array:[], aux_vp_tid_array:[], base_table_ids:[], rowkey_info:{columns:null, capacity:0}, partition_key_info:{columns:null, capacity:0}, column_cnt:0, table_dop:1, constraints:null, column_array:null, aux_vp_tid_array:[]}}, is_inner:false, alter_constraint_type:1000, nls_formats:["DD-MON-RR", "DD-MON-RR HH.MI.SSXFF AM", "DD-MON-RR HH.MI.SSXFF AM TZR"]}, index_arg_list:[]}) [2023-11-24 10:03:28.147566] WARN [SQL] handle_physical_plan (ob_sql.cpp:4149) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=14] [dc=0] Failed to generate plan(ret=-5235, result.get_exec_context().need_disconnect()=false) [2023-11-24 10:03:28.147570] WARN [SQL] handle_text_query (ob_sql.cpp:1693) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] [dc=0] fail to handle physical plan(ret=-5235) [2023-11-24 10:03:28.147575] WARN [SQL] stmt_query (ob_sql.cpp:209) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] [dc=0] fail to handle text query(stmt=alter table test_readonly read write, ret=-5235) [2023-11-24 10:03:28.147581] WARN [SERVER] test_and_save_retry_state (ob_query_retry_ctrl.cpp:165) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] [dc=0] ddl, and errno is not OB_EAGAIN or OB_SNAPSHOT_DISCARDED or OB_ERR_PARALLEL_DDL_CONFLICT, do not need retry(client_ret=-5235, err=-5235, retry_type_=0) [2023-11-24 10:03:28.147589] INFO [SERVER] ob_query_retry_ctrl.cpp:579 [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=4] [dc=0] check if need retry(client_ret=-5235, err=-5235, retry_type_=0, retry_times=1, multi_stmt_item={is_part_of_multi_stmt:true, seq_num:0, sql:"alter table test_readonly read write"}) [2023-11-24 10:03:28.147595] WARN [SERVER] do_process (obmp_query.cpp:687) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=3] [dc=0] run stmt_query failed, check if need retry(ret=-5235, cli_ret=-5235, retry_ctrl_.need_retry()=0, sql=alter table test_readonly read write) [2023-11-24 10:03:28.147641] WARN [SERVER] do_process (obmp_query.cpp:803) [134302][0][xxxxx-xxxxx-xxxxx-xxxxx] [lt=40] [dc=0] query failed(ret=-5235, retry_ctrl_.need_retry()=0)
问题原因
OceanBase 数据库暂不支持普通租户将 Table 属性由 read only 改为 read write。
解决方法
通过 SYS 用户来执行将只读表改为读写表的操作。
适用版本
OceanBase 数据库 V2.x 和 V3.x 版本。
