No Hub or QMC access after upgrade to Sense November 2017 or higher - recursive cleanup script
Trying to access Qlik Sense Hub or Management Console (QMC) leads to the getting "refused to connect" on the web browser.
Investigating the issue with the command netstat | findstr :443 confirms there is not service listening on port 443 (or port QMC/Hub proxy is set to listen on).
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'
The Repository Service to update fails on a table that iolates 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
In November 2017, February 2018, April 2018 and June 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.
This script can be obtained from the official Qlik site: Failed to remove soft deleted records. If logged on to our support site, it can be directly downloaded from the attachment. The attachment is unavailable for anonymous users.
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.
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