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.

Waarschuwing: VERY 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.

Doe het volgende:

  1. Stop all the services, except the Qlik Sense Repository Database.

  2. Save the script below to a file as recurse_cleanup.sql.

  3. Move the file recurse_cleanup.sql to %ProgramFiles%\Qlik\Sense\Repository\PostgreSQL\<database version>\bin.

  4. Open a command prompt with elevated privileges.

  5. Navigate to %ProgramFiles%\Qlik\Sense\Repository\PostgreSQL\<database version>\bin, for example: cd C:\"Program Files"\Qlik\Sense\Repository\PostgreSQL\9.6\bin.

    Opmerking: If you installed PostgreSQL manually, the location where to place and run the script from will be: %ProgramFiles%\PostgreSQL\<database version>\bin.
  6. Run .\psql.exe -h localhost -d QSR -U postgres -p 4432 -a -f recurse_cleanup.sql

  7. If prompted, enter database superuser password.

  8. Restart Qlik Sense Service Dispatcher, then start the Qlik Sense Repository Service in the given order.

Opmerking: When 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;