ob_cluster_exists_index_fail_table

Description

This alert is triggered when a table with an indexing failure exists in the OceanBase cluster.

Principle

The following table describes the key parameters that are involved in the monitoring and alerting logic.

The value of the metric ob_cluster_index_fail_count indicates the number of tables with an indexing failure in the OceanBase cluster. When the value exceeds the threshold, this alert is triggered. The default threshold is 0.

Alert rule

Alert information

Alert templates

• Overview: ${alarm_target} ${alarm_name}

• Details: ${alarm_target} ${alarm_name}. The number of tables with an indexing failure is ${value}.

• Overview example: ob_cluster=C1-1000. Tables with an indexing failure exist in the OceanBase cluster.

• Details example: ob_cluster=C1-1000. The number of tables with an indexing failure in the OceanBase cluster is 1.0.

Impact on the system

The failure of index creation makes it impossible to optimize SQL execution based on the index.

Possible causes

This problem is commonly found when an error occurred while creating an index. It occurs in the following scenarios:

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

• Failure to create a unique index. OceanBase Database creates indexes asynchronously. If data in the table is not unique, an error is returned with a delay. In this case, the DBA must collaborate with the customer to verify whether the data is unique and meets the requirement of the index.

Suggested solutions

1. Find the data with an indexing failure.

   1. Run the following command to find the index that failed to be created:

      -- Applies to OceanBase Database 1.4.x
      SELECT tenant_id, database_id, table_id, `table_name` AS `index_name`,  data_table_id, index_status from __all_table where table_type=5 and index_status not in (1, 2, 3) LIMIT 10;
      
      -- Applies to OceanBase Database 2.x
      SELECT tenant_id, database_id, table_id, `table_name` AS `index_name`,  data_table_id, index_status from __all_virtual_table where table_type=5 and index_status not in (1, 2, 3) LIMIT 10;
      

      Find the index_name and data_table_id.

   2. Find the name of the table with the indexing failure based on data_table_id.

      -- Use __all_database for OceanBase Database 1.4.x and __all_virtual_database for OceanBase Database 2.x.
      
      > 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. Check whether the cluster status is normal.

3. Delete and recreate the index.

   • For more information about how to delete an index, see Drop an index in the OceanBase Database documentation of the corresponding version.

   • For more information about how to create an index, see Create an index in the OceanBase Database documentation of the corresponding version.