Qlik Sense Scheduler Tasks keep failing - PostgreSQL error

[Zareh_T]

In Qlik Sense, there could be a situation where the server is installed and functioning, all the services are running except the fact that all the scheduler tasks are failing.

The scheduler service may fail after restarting. This indicates that the issue is related to the scheduler ExecutionSessions table data in the Repository PostGres SQL database.

In the Qlik Sense Management Console, task statuses may be listed as Reset.

 

This issue happens when users add, remove, delete too many tasks while frequently stopping and starting the scheduler service.

The data in the scheduler tasks execution sessions table gets corrupted. The startup sequence will start to fail and cause errors in the scheduler logs.

Corruption may occur due to:

1. A row that is incomplete ( blank values ) in the ExecutionSessions table data
2. ID reference to a task that does not exist anymore
3. Entries flagged as incomplete that interfere with the execution queue ( block the next tasks )
4. Entries with IDs that already exist in the table, new entries with same ID will cause an error and abort the execution

This can look like this in the repository database:

The following will be printed to the scheduler logs:

System.Scheduler.Scheduler.Core.Utility.SystemInformationProvider
An item with the same key has already been added. 
     and/or
System.Scheduler.Scheduler.Main
Startup sequence failed, shutting down to avoid any inconsistent state, please restart the scheduler

and/or

Unexpected exception in Delete for Entity ExecutionSession with Id <Task ID>

To resolve this issue, it is recommended to wipe the data from the Execution sessions table.

Please note that this will need to be a hard deletion from the table and not soft deletion.


 

Resolution:

If you are running Qlik Data Gateway, proceed with the following before wiping any data:

1. Stop the Qlik Sense Scheduler Service and Qlik Data Gateway - Direct Access.
2. Start the Qlik Sense Scheduler Service
3. Start Qlik Data Gateway - Direct Access

Otherwise, proceed as follows:

1. Change the scheduler settings in Qlik Management Console to Manager and Worker
2. Stop all the Qlik Sense Services except the Repository database service
3. Make a backup of the Repository database: How to backup PostgreSQL database in Qlik Sense 
4. Locate pgAdmin or install it. Installing and Configuring PGAdmin 4 to access the PostgreSQL database used by Qlik Sense or NPrinti...
5. Login to the database
6. Open the ExecutionSessions table
   
   
   
   
7. Select and view all entries in the ExecutionSessions table
   

   SELECT 
     "ExecutionSessions"."ID", 
     "ExecutionSessions"."AbortAt", 
     "ExecutionSessions"."Retries", 
     "ExecutionSessions"."AbortReason", 
     "ExecutionSessions"."User", 
     "ExecutionSessions"."Deleted", 
     "ExecutionSessions"."CreatedDate", 
     "ExecutionSessions"."ModifiedDate", 
     "ExecutionSessions"."SyncDate", 
     "ExecutionSessions"."ModifiedByUserName", 
     "ExecutionSessions"."App_ID", 
     "ExecutionSessions"."ExecutingNode_ID", 
     "ExecutionSessions"."ExecutionResult_ID", 
     "ExecutionSessions"."ReloadTask_ID", 
     "ExecutionSessions"."UserSyncTask_ID", 
     "ExecutionSessions"."ExternalProgramTask_ID"
   FROM 
     public."ExecutionSessions";

8.  Wipe the data from the ExecutionSessions table. See SQL statements below.
   
   Please note that this will need to be a hard deletion from the table and not soft deletion.
   

   DELETE 
   FROM 
   public."ExecutionSessions";​

9. Restart the QlikSense Server ( full reboot )

[Eduardo_Monteiro]

RESOLUTION: 

1. Stop "Qlik Data Gateway - Direct Access"

2. Start Qlik Sense Scheduler Service

3. Start "Qlik Data Gateway - Direct Access"

I was able to replicate this issue on Qlik Sense Feb 2024. "Qlik Data Gateway - Direct Access" was mounting on 5050/5151. 

If QDG starts before the scheduler, the scheduler will drop after a few seconds and keep starting/stopping. 

Please consider adding this information to the article. None of the changes in the ExecutionSessions table data had an effect in my scenario.

Regards,

[rlanard]

Thank you Eduardo!