SQL error in maintenance_mode probe log - failure registering to maintenance_mode. The connection is broken and recovery is not possible.

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The maintenance_mode probe has the following error repeatedly in the probe log:

WARN  [attach_socket, com.nimsoft.monitor.probe.MaintenanceModeProbe] Failure registering to maintenance_mode. org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SELECT  mw.WINDOW_START_TIME,mw.WINDOW_END_TIME, dev_ids = STUFF((SELECT ',' + cd.dev_id  FROM CM_DEVICE cd join MAINTENANCE_SCHEDULE_MEMBERS msm on cd.cs_id =  msm.CS_ID  WHERE msm.SCHEDULE_ID = ms.SCHEDULE_ID  AND cd.probe_name not like 'app_disco%' FOR XML PATH('')), 1, 1, ''),  csids_devids= STUFF((SELECT ',' +  CONVERT(varchar(10),msm.CS_ID) + '=' + cd.dev_id  FROM CM_DEVICE cd join MAINTENANCE_SCHEDULE_MEMBERS msm on cd.cs_id =  msm.CS_ID  WHERE msm.SCHEDULE_ID = ms.SCHEDULE_ID  AND cd.probe_name not like 'app_disco%' FOR XML PATH('')), 1, 1, ''), ms.SCHEDULE_ID, ms.SCHEDULE_NAME, ms.START_TIME, ms.DURATION, ms.DESCRIPTION  , ms.ACCOUNT_ID, ms.RECURRENCE_PATTERN, ms.RECURRENCE_PERIOD, ms.RECURRENCE_DAYS_OF_THE_WEEK, ms.RECURRENCE_DAY_OF_THE_MONTH, ms.RECURRENCE_INSTANCE, ms.RECURRENCE_END_TIME, ms.TIMEZONE FROM MAINTENANCE_SCHEDULE ms  left join ( SELECT    MAX(START_TIME) WINDOW_START_TIME ,MAX(END_TIME) WINDOW_END_TIME, SCHEDULE_ID  FROM      MAINTENANCE_WINDOW group by SCHEDULE_ID ) mw ON (mw.SCHEDULE_ID = ms.SCHEDULE_ID)]; SQL state [null]; error code [0]; The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.

 

Environment

DX UIM - Any Version
maintenance_mode probe
Microsoft SQL backend database

Cause

SQL Query failure

Resolution

This error occurs if the given query fails to return a proper result (including 0 rows) - for example, if the SQL Server is down, or the query is timing out.

To troubleshoot this issue, ensure you can connect to the SQL database server that hosts the DX UIM Database and execute the following query:

SELECT  mw.WINDOW_START_TIME,mw.WINDOW_END_TIME, dev_ids =
STUFF((SELECT ',' + cd.dev_id FROM CM_DEVICE cd join MAINTENANCE_SCHEDULE_MEMBERS msm on cd.cs_id =  msm.CS_ID
      WHERE msm.SCHEDULE_ID = ms.SCHEDULE_ID FOR XML PATH('')), 1, 1, ''),
          csids_devids= STUFF((SELECT ',' +  CONVERT(varchar(10),msm.CS_ID) + '=' + cd.dev_id
      FROM CM_DEVICE cd join MAINTENANCE_SCHEDULE_MEMBERS msm on cd.cs_id =  msm.CS_ID
      WHERE msm.SCHEDULE_ID = ms.SCHEDULE_ID FOR XML PATH('')), 1, 1, ''),ms.*
FROM MAINTENANCE_SCHEDULE ms
  join (
         SELECT    MAX(START_TIME) WINDOW_START_TIME ,MAX(END_TIME) WINDOW_END_TIME, SCHEDULE_ID
         FROM      MAINTENANCE_WINDOW group by SCHEDULE_ID) mw ON (mw.SCHEDULE_ID = ms.SCHEDULE_ID);

 

If you are unable to connect or execute the query, work with your DBA to troubleshoot the connectivity issue.

If the query takes a long time to execute or does not complete, try the following resolution:

1. deactivate the maintenance_mode probe.
2. issue these SQL queries:

ALTER INDEX ALL ON MAINTENANCE_SCHEDULE REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON MAINTENANCE_SCHEDULE_MEMBERS REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON MAINTENANCE_WINDOW REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_DEVICE REBUILD WITH (ONLINE = ON);

3. activate maintenance_mode probe and check the result.