ob_tenant_mview_refresh_error Tenant Materialized View Automatic Refresh Exception

Alert description

This alert monitors whether there are any exceptions in the materialized view refresh of databases under a tenant. If exceptions exist, this alert is triggered.

Alert principle

1. The OCP scheduled task executes the following SQL in the sys tenant of all clusters to automatically refresh abnormal data from the corresponding materialized views and store the data in the sql_aggregate_data table of OCP MonitorDB. The scheduled task runs every 60 seconds.

   SELECT 
         D.tenant_id,
         D.tenant_name,
         A.database_name,
         B.table_name AS mview_name,
         E.start_time,
         E.result
   FROM  oceanbase.__all_virtual_database A,
         oceanbase.__all_virtual_table B,
         oceanbase.__all_virtual_mview C,
         oceanbase.DBA_OB_TENANTS D,
         (SELECT tenant_id,
                 result,
                 start_time,
                 mview_id
           FROM   (SELECT tenant_id,
                         result,
                         start_time,
                         mview_id,
                         Row_number()
                           over(
                             PARTITION BY tenant_id, mview_id
                             ORDER BY start_time DESC) rn
                   FROM   oceanbase.__all_virtual_mview_refresh_stats A)
           WHERE  rn = 1)E
   WHERE  A.tenant_id = B.tenant_id
         AND B.tenant_id = C.tenant_id
         AND C.tenant_id = D.tenant_id
         AND D.tenant_id = E.tenant_id
         AND C.mview_id = E.mview_id
         AND A.database_id = B.database_id
         AND B.table_id = C.mview_id
         AND B.table_type = 7
         AND E.result != 0
         AND D.tenant_type<>'META' AND D.tenant_name IS NOT NULL
         AND D.tenant_role = 'PRIMARY';
   

2. Use the OCP alert detection task to identify whether the alert item is of the SQL_AUDIT type. If so, run the following command to scan for abnormal data in sql_aggregate_data and generate the corresponding alert based on the configured alert detection rules.

   select * from sql_aggregate_data where tags like '%mview_stat%' order by timestamp desc
   

Rule information

Alert information

Alert template

• Alert overview

  • Template: ${alarm_target} ${alarm_name}
  • Example: alarm_template_id=0:ob_cluster=test471-11:tenant_name=mysqlTes:database=test:mview_name=test_mv_refresh_retry_mv2 Automatic refresh exception of materialized view

• Alert Details

  • Template: An exception occurred for the materialized view ${mview_name} in the database ${database} of the tenant ${tenant_name} in the OceanBase cluster ${ob_cluster_name}. The result is ${result}, and the refresh time is ${start_time}.
  • Example: The materialized view test_mv_refresh_retry_mv2 in the database test of the tenant mysqlTes in the OceanBase cluster test471 has an exception with the result -5024 and the refresh time 2025-11-14T16:13:55.026383+08:00.

Impact on the system

This may cause the materialized view to stop refreshing data.

Possible causes

Internal system exception.

Solution

1. By default, after an error occurs during materialized view refresh, only the trace ID and error code are available. You can execute the following command in the sys tenant to obtain these details:

   SELECT A.database_name, B.table_name AS mview_name, C.last_refresh_trace_id, C.last_refresh_time
   FROM oceanbase.__all_virtual_database A, oceanbase.__all_virtual_table B, oceanbase.__all_virtual_mview C
   WHERE A.tenant_id = B.tenant_id
     AND B.tenant_id = C.tenant_id
     AND A.database_id = B.database_id
     AND B.table_id = C.mview_id
     AND B.table_name = 'mview_name';
   

   Where mview_name should be filled in with the actual name of the materialized view that caused the exception.

2. Collect the relevant error logs in the cluster observer log directory based on information such as the trace ID and refresh time, and contact OCP Technical Support for assistance with troubleshooting.