ob_cluster_exists_index_fail_table

Alert description

This alert is triggered when the OceanBase cluster has tables with an indexing failure.

Alert principle

The following table lists the key parameters involved in the monitoring logic of this alert.

The metric ob_cluster_index_fail_count indicates the number of tables with an indexing failure in the OceanBase cluster. An alert is triggered when the value exceeds the threshold (which is 0 by default).

Alert rule

Alert information

Alert template

• Alert overview

  • Template: ${alarm_target} ${alarm_name}
  • Example: ob_cluster=obcluster-1 The OceanBase cluster has tables with an indexing failure.

• Alert details

  • Template: Cluster: ${ob_cluster_name}, Alert: ${alarm_name}. The number of tables with an indexing failure is ${value}.
  • Example: Cluster: obcluster-1, Alert: The OceanBase cluster has tables with an indexing failure. The number of tables with an indexing failure is 1.0.

• Alert recovery

  • Template: Alert: ${alarm_name}. The number of tables with abnormal indexes in the OceanBase cluster: ${value}
  • Example: Alert: The OceanBase cluster has tables with an indexing failure. The number of tables with abnormal indexes in the OceanBase cluster: 0

• Alert details

Impact on the system

Failed index creation prevents SQL execution from being optimized based on the index, thereby failing to demonstrate the execution efficiency.

Possible causes

This issue commonly occurs during the execution of index creation tasks when the index cannot be established. Common scenarios include:

• Failure to create a normal index. This error is caused by incorrect cluster status.

• Failed creation of a unique index: OceanBase creates indexes asynchronously. If the data does not meet the uniqueness constraints, errors are reported later. In this case, DBAs and business teams need to collaborate to verify data uniqueness and index requirements.

Suggested solutions

1. Find the failed index information by using the following method:

   1. Run the following command to find the failed index.

      -- For OceanBase Database versions earlier than V4.0:
      select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(100000000) */ count(*) as cnt from gv$table where table_type in (5) and index_status in (5, 6)
      
      -- For OceanBase Database V4.0 and later:
      select /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000) */ count(*) as cnt from CDB_INDEXES where status in ('ERROR','UNUSABLE')
      

      Take the values of index_name (index name) and data_table_id (ID of the table to which the index belongs).

   2. Use the data_table_id (ID of the table to which the index belongs) to find the name of the table to which the index belongs.

      -- Note that OceanBase Database V2.x uses __all_virtual_database.
      
      > SELECT t2.database_name, t1.table_name  from __all_table t1 join __all_database t2 on t1.database_id=t2.database_id where t2.tenant_id=1014 and t1.table_id=1114904790614901;
      +---------------+---------------+
      | database_name | table_name    |
      +---------------+---------------+
      | obsink        | question_dest |
      +---------------+---------------+
      1 row in set (0.05 sec)
      

   3. Run the following commands to find the index that failed to be created:

      # MySQL mode #
      obclient> SHOW INDEX FROM table_name;
      
      # Oracle mode
      obclient> SELECT * FROM USER_IND_COLUMNS WHERE table_name='table_name';
      

2. Confirm that the cluster status is normal.

3. Drop and recreate the index.

   • To drop an index, see Drop an index in the documentation of the corresponding OceanBase Database version.

   • To recreate an index, see Create an index in the documentation of the corresponding OceanBase Database version.