Failed to remove soft deleted records
When upgrading Qlik Sense to November 2017 or later, the installation fails and you get the following error message: "Failed to remove soft deleted records. An exception was thrown while invoking the constructor 'Void .ctor()' on type 'DatabaseContext'".
Possible cause
The database contains soft deleted records that generate an error when upgrading to a version of Qlik Sense without soft deletes, that is, November 2017 or later.
Proposed action
Run a script to delete the soft deleted records.
Warning noteVERY IMPORTANT! Back up the whole QRS database before executing the script. If an error occurs, restore the backup, find out the data discrepancy, fix the issue and execute again, see Backup and restore Qlik Sense Enterprise on Windows.
Do the following:
-
Stop all the services, except the Qlik Sense Repository Database.
-
Save the script below to a file as recurse_cleanup.sql.
-
Move the file recurse_cleanup.sql to %ProgramFiles%\Qlik\Sense\Repository\PostgreSQL\<database version>\bin.
-
Open a command prompt with elevated privileges.
-
Navigate to %ProgramFiles%\Qlik\Sense\Repository\PostgreSQL\<database version>\bin, for example: cd C:\"Program Files"\Qlik\Sense\Repository\PostgreSQL\9.6\bin.
Information noteIf you installed PostgreSQL manually, the location where to place and run the script from will be: %ProgramFiles%\PostgreSQL\<database version>\bin. -
Run .\psql.exe -h localhost -d QSR -U postgres -p 4432 -a -f recurse_cleanup.sql
-
If prompted, enter database superuser password.
-
Restart Qlik Sense Service Dispatcher, then start the Qlik Sense Repository Service in the given order.
Information noteWhen running the script on a non-English OS, you may encounter errors during the script execution. The errors can be caused by the character set conversion between server (PostgreSQL) and client (Powershell). To enable automatic character set conversion, run the following command from the command prompt before opening Powershell and executing the script: SET PGCLIENTENCODING=UTF-8. The variable is lost the moment the command prompt is closed. For more information refer to
Character Set Support.
Script for deleting soft deleted records in the Qlik Sense Repository Database
/* ##############################################################################################################################
Script Name: Recurse cleanup
Description: the script is intended to delete all entities marked as soft deleted in the QRS database
Caution: BACKUP the whole QRS database before executing the script!
##############################################################################################################################
*/
/* Step 1. Update records according to QRS special logics
##############################################################################################################################
*/
-- Step 1.1 Update Owner to sa_repository if Owner is deleted
-- Step 1.1.1 Get all Qlik Sense Tables
CREATE OR REPLACE FUNCTION get_all_sense_tables() RETURNS SETOF information_schema.tables AS
$BODY$
BEGIN
RETURN QUERY SELECT *
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE'
AND table_catalog='QSR'
AND table_name <> '__MigrationHistory';
RETURN;
END
$BODY$
LANGUAGE plpgsql;
-- Step 1.1.2 Filter Qlik Sense Tables with name of column
CREATE OR REPLACE FUNCTION get_tables(columnname varchar)
RETURNS SETOF information_schema.columns AS $$
BEGIN
RETURN QUERY SELECT DISTINCT * FROM information_schema.columns as isc WHERE isc.column_name = columnname And isc.table_name IN (SELECT ts.table_name FROM get_all_sense_tables() as ts);
RETURN;
END
$$
LANGUAGE plpgsql;
-- Step 1.1.3 Change ownership of soft deleted users to sa_repository
CREATE OR REPLACE FUNCTION fix_orphan_owners() RETURNS void AS
$BODY$
DECLARE username character varying;
DECLARE
tables CURSOR FOR
SELECT * FROM get_tables('Owner_ID');
BEGIN
SELECT E'\'sa_repository\'' INTO username;
FOR table_record IN tables LOOP
EXECUTE 'UPDATE "' || table_record.table_name || '" SET "Owner_ID" = (SELECT "ID" FROM "Users" WHERE "UserId" = ' || username || ') WHERE "Owner_ID" IN (SELECT "ID" FROM "Users" WHERE "Deleted" = true)';
END LOOP;
END
$BODY$
LANGUAGE 'plpgsql';
SELECT * FROM fix_orphan_owners();
-- Step 1.1.4 Remove created DB functions for fixing ownership relations
DROP FUNCTION fix_orphan_owners();
DROP FUNCTION get_tables(columnname varchar);
DROP FUNCTION get_all_sense_tables();
-- Step 1.2 Unpublish App if Steam is deleted
UPDATE "Apps"
SET "Stream_ID" = null, "Published" = false
WHERE "Stream_ID" IN (SELECT "ID" FROM "Streams" where "Deleted" = true);
UPDATE "AppObjects"
SET "Approved" = false, "Published" = false
WHERE "App_ID" IN (SELECT "ID" FROM "Apps" where "Published" = false);
/* Step 2. Prepare for deletion: Alter foreign keys to Casacade Delete
##############################################################################################################################
*/
CREATE TABLE temp_foreign_key (
constraint_name VARCHAR,
table_name VARCHAR,
column_name VARCHAR,
ref_table_name VARCHAR,
ref_column_name VARCHAR
);
INSERT INTO temp_foreign_key (constraint_name, table_name, column_name, ref_table_name, ref_column_name)
SELECT fk.constraint_name, child.table_name, child.column_name, parent.table_name, parent.column_name
FROM information_schema.referential_constraints fk
JOIN information_schema.key_column_usage AS child ON fk.constraint_name = child.constraint_name
JOIN information_schema.key_column_usage AS parent ON fk.unique_constraint_name = parent.constraint_name
WHERE fk.constraint_schema = 'public'
AND child.position_in_unique_constraint = parent.ordinal_position
AND fk.delete_rule = 'NO ACTION';
-- Step 2.2 Create a function the replace foreign keys with new on DELETE option
CREATE OR REPLACE FUNCTION replace_foreign_key (new_option VARCHAR) RETURNS void AS
$BODY$
DECLARE
fks CURSOR FOR
SELECT * FROM temp_foreign_key;
BEGIN
FOR rec IN fks LOOP
EXECUTE 'alter table "' || rec.table_name || '" '
|| 'drop constraint "' || rec.constraint_name || '" ,'
|| 'add constraint "' || rec.constraint_name || '" FOREIGN KEY ("' || rec.column_name || '") REFERENCES "' || rec.ref_table_name || '" ("' || rec.ref_column_name || '") ' || new_option || ';' ;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql;
-- Step 2.3 execute the function to replace all foreign keys with CASCADE on Delete
SELECT *
FROM replace_foreign_key('on delete cascade');
/* Step 3. Delete entities marked as Soft Deleted
##############################################################################################################################
*/
-- 3.1 Create a function to delete all SoftDeleted records
CREATE OR REPLACE FUNCTION delete_softdeleted_records(keep_for_days int) RETURNS void AS
$BODY$
DECLARE
entity_tables CURSOR FOR
SELECT table_name
FROM information_schema.columns
WHERE table_schema='public'
AND column_name='Deleted';
BEGIN
FOR tbl IN entity_tables LOOP
EXECUTE 'delete from "' || tbl.table_name || '" where "Deleted" = true and "ModifiedDate" <= CURRENT_DATE - ' || keep_for_days || ';';
END LOOP;
END;
$BODY$
LANGUAGE plpgsql;
-- Step 3.2 execute the function to delete entities
SELECT *
FROM delete_softdeleted_records(3);
/* Step 4. Resume foreign keys to No Action on Delete
##############################################################################################################################
*/
SELECT *
FROM replace_foreign_key('');
/* Step 5. Drop temp objects
##############################################################################################################################
*/
DROP FUNCTION delete_softdeleted_records(keep_for_days int);
DROP FUNCTION replace_foreign_key(new_option varchar);
DROP TABLE temp_foreign_key;