【SOP系列 23】—— OB 升级到 4.2.1 版本后遇到部分分区表执行ddl报错-4109问题排查及处理方法

OceanBase 新人必读
#1

问题表现

OB 升级到4.2.1版本，部分分区表执行ddl报错-4109错误码， 并报错错误信息：Server state or role not the same as expected。

案例

给表添加字段报SQL 错误 [4109] [HY000]: Server state or role not the same as expected

OCP内部表ob_hist_trans_stat创建分区失败

排查链路

  1. obdiag gather log 收集了一下日志
  2. 通过收集的日志查看rootservice.log，找到了4109的日志，比如发现有如下日志：
__all_table_history’s row not match with __all_table’s(ret=-4109, ret=“OB_STATE_NOT_MATCH”, tenant_id=1002, table_id=501085, schema_version=1708239650768592)

上面的日志表明 __all_table_history和 __all_table 中在tenant_id=1002, table_id=501085这张表中有字段不一致。
3. 通过查询__all_table和__all_table_history表找出在不同的地方

select * from __all_table where tenant_id = xxx and table_id=xxx;

select * from __all_table_history where tenant_id = xxx and table_id=xxx order by gmt_modified desc limit 1;

如第二步中的样例，tenant_id=1002, table_id=501085这张表中有字段不一致，是空字符串和NULL的区别，OB 4.2版本加了__all_table_history和_all_table内容的校验，所以如果插入数据不一致的情况下会报错。重点关注：transition_point, b_transition_point , interval_range , b_interval_range这几个字段。

处理措施

确认__all_table和__all_table_history哪些字段不一致后，手动更新__all_table表中不一致的字段
1、sys租户登陆
2、alter system change tenant tenant_id = xxx; # 切到业务租户（一定一定要现在sys租户登陆再切换）
3、update __all_table set transition_point = NULL, b_transition_point = NULL, interval_range = NULL, b_interval_range = NULL where table_id = xxxx;（更新字段中有不一样的值）

4 个赞
给表添加字段报SQL 错误 [4109] [HY000]: Server state or role not the same as expected
OCP内部表ob_hist_trans_stat创建分区失败
#2

手动赞一个，学习了，希望多发发这种案例贴 ！

#3

另外可通过一条SQL查询出是否集群存在此问题：

select tenant_id, table_id, table_name, database_id, table_type, load_type, def_type, rowkey_column_num, index_column_num, max_used_column_id, autoinc_column_id, auto_increment, read_only, rowkey_split_pos, compress_func_name, expire_condition, is_use_bloomfilter, comment, block_size, collation_type, data_table_id, index_status, tablegroup_id, progressive_merge_num, index_type, part_level, part_func_type, part_func_expr, part_num, sub_part_func_type, sub_part_func_expr, sub_part_num, schema_version, view_definition, view_check_option, view_is_updatable, index_using_type, parser_name, index_attributes_set, tablet_size, pctfree, partition_status, partition_schema_version, session_id, pk_comment, sess_active_time, row_store_type, store_format, duplicate_scope, progressive_merge_round, storage_format_version, table_mode, encryption, tablespace_id, sub_part_template_flags, dop, character_set_client, collation_connection, auto_part_size, auto_part, association_table_id, tablet_id, max_dependency_version, define_user_id, transition_point, b_transition_point, interval_range, b_interval_range, object_status, table_flags, truncate_version, 0 as is_deleted from __all_table
EXCEPT 
select
t1.tenant_id, t1.table_id, t1.table_name, t1.database_id, t1.table_type, t1.load_type, t1.def_type, t1.rowkey_column_num, t1.index_column_num, t1.max_used_column_id, t1.autoinc_column_id, t1.auto_increment, t1.read_only, t1.rowkey_split_pos, t1.compress_func_name, t1.expire_condition, t1.is_use_bloomfilter, t1.comment, t1.block_size, t1.collation_type, t1.data_table_id, t1.index_status, t1.tablegroup_id, t1.progressive_merge_num, t1.index_type, t1.part_level, t1.part_func_type, t1.part_func_expr, t1.part_num, t1.sub_part_func_type, t1.sub_part_func_expr, t1.sub_part_num, t1.schema_version, t1.view_definition, t1.view_check_option, t1.view_is_updatable, t1.index_using_type, t1.parser_name, t1.index_attributes_set, t1.tablet_size, t1.pctfree, t1.partition_status, t1.partition_schema_version, t1.session_id, t1.pk_comment, t1.sess_active_time, t1.row_store_type, t1.store_format, t1.duplicate_scope, t1.progressive_merge_round, t1.storage_format_version, t1.table_mode, t1.encryption, t1.tablespace_id, t1.sub_part_template_flags, t1.dop, t1.character_set_client, t1.collation_connection, t1.auto_part_size, t1.auto_part, t1.association_table_id, t1.tablet_id, t1.max_dependency_version, t1.define_user_id, t1.transition_point, t1.b_transition_point, t1.interval_range, t1.b_interval_range, t1.object_status, t1.table_flags, t1.truncate_version, t1.is_deleted
from 
__all_table_history t1
inner join (select t2.table_id,max(t2.schema_version) as schema_version from __all_table_history t2 group by t2.table_id)as t3
on t1.table_id = t3.table_id and t1.schema_version = t3.schema_version and t1.is_deleted = 0;
1 个赞