No Hub or QMC access after upgrade to Sense November 2017 or higher - recursive script

QlikSense

No Hub or QMC access after upgrade to Sense November 2017 or higher - recursive script

This article uses the recursive (recurse) cleanup sql script. After upgrading to Qlik Sense November 2017 or February 2018, the administrator is unable to access the Hub and/or QMC.  This article also fixes database issues and soft deletes, which cause numerous other problems besides being unable to access the QMC or HUB after an upgrade. This script is necessary to be run if upgrading from an older version to 11.14.3 or newer.

An inspection of the System_Repository log located in C:\ProgramData\Qlik\Sense\Log\Repository\Trace shows an error message relating to being unable to remove soft deleted records.

Sample:
Fatal exception Connection must be opened.↵↓Connection must be opened.↵↓Failed to remove soft deleted records↵↓An exception was thrown while invoking the constructor 'Void .ctor()' on type 'DatabaseContext'


On Qlik Sense November 2017 or higher

ISSUE

Till June everything is working fine.
But direct upgrade from June2017 to April2018
Fails the Repository Service to update some table which violates foreign key constraint.
C:\ProgramData\Qlik\Sense\Log\Repository\Trace\QLIKSERVER1_System_Repository
Fatal exception update or delete on table "CompositeEvents" violates foreign key constraint "FK_CompositeEventTimeConstraints_CompositeEvents_ID" on table "CompositeEventTimeConstraints"↵↓Failed to execute query: 'DELETE FROM "CompositeEvents" WHERE "Deleted" = true'. Exception: update or delete on table "CompositeEvents" violates foreign key constraint


CAUSE

In November 2017 and February 2018, when the Qlik Sense Repository Service initializes, it attempts to clean up the underlying Repository Database. If there are records which have broken referential integrity then the Repository Service will fail to initialize. This is a consequence of a change in behavior in November 2017/February 2018 where the Repository no longer does soft deletes.
The script may be found here: help.qlik.com

RESOLUTION

• Stop all services but the Qlik Sense Repository Database
• If using a stand-alone PostgreSQL server to host Qlik Sense content, then stop all Qlik Sense Services
• Take a backup of the Qlik Sense Repository Database
• Sample command using cmd.exe: cd / & mkdir QSR & "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin\pg_dump.exe" -h localhost -p 4432 -U postgres -b -F t -f "c:\QSR\QSR_backup_%date:~-4,4%%date:~-10,2%%date:~-7,2%.tar" QSR
• This assumes that:
• Qlik Sense is installed to C:\Program Files
• That the site is not using a stand-alone PostgreSQL server
• Make any necessary adjustments if neither of those assumptions are true
• Download the attached .sql file and place it into the same directory as pg_dump.exe
• Run the following command in cmd.exe (run them one at a time and hit Enter then continue with the next one):
• cd C:\"Program Files"\Qlik\Sense\Repository\PostgreSQL\9.6\bin
• SET PGCLIENTENCODING=utf-8
• chcp 65001
• psql.exe -h localhost -p 4432 -U postgres -d QSR -e -f recurse_cleanup.sql
• If Qlik Sense is installed to a non-default directory then make the necessary adjustments
• Start all Qlik Sense Services
• Validate that the error about Failed to remove soft deleted records is not present in the System_Repository log
• If this does not resolve the issue then do contact Qlik Support for a more customized query