Introduction

The Delft-FEWS client-server system consists of the Master-Controllers, Forecasting Shell Servers and Operator Clients and optionally a PiService. In rare cases there are errors that make it is necessary to remove some records from the client-server system. Removal is not always straightforward since synchronisation may cause these records to be synchronised back to the central database system. The following steps will prevent this happening. Note that for several different removal methods of records from the central database are made available within the MCRecoveryTool. Note that some types of records are uploaded by the Operator Clients (manual edits  / synchLevel 5) in which case it is necessary to remove the OC local datastores as well.

Methods for deleting records

  1. Automatically by the RollingBarrel based on expiry time
  2. Clear all records: use the McRecoveryTool -clear_database toolaction
  3. MCRecoveryTool (specialised removal actions),
  4. Sql script in sql script or via DBVisualiser or another jdbc client. See examples below
  5. ImportAmalgamate (this is for merging external historical data), see also ImportAmalgamate

Removal of records with a single master-controller

  1. Remove all forecasting shell / PI Service local datastores and cache files.
  2. Delete the records from the central database.

Removal of records with multiple Master-Controllers

  1. Suspend all MC-MC Synchronisation tasks.
  2. Remove all forecasting shell / PI Service local datastores.
  3. Remove the records from all central databases
  4. Resume the MC-MC synchronisation tasks.

Examples of custom deletions (e.g. via DBVisualizer)

Always make sure you are connected to the correct database schema first.

Before deletion, always do a select of the data to delete. If possible and relevant inquire whether a recent backup of the database is available.

Bulk deletion of workflowmappings

The MCRecoveryTool -clear_config_all option is available to clear all configuration and will also remove the workflowmappings. But in some cases, a custom delete statement can be quicker to clear all the workflowmappings for non- MC workflows without the need for uploading the configuration again.

Before deleting workflowmappings, it can be beneficial to download the current workflow mappings first via the AdminInterface.

DELETE FROM WorkflowTargetFSSs WHERE workflowId NOT LIKE 'MC:%' AND workflowId NOT LIKE 'MC_:%'

Deletion of a faulty task

When a task in the database has a malformed taskProperties configuration, this will disturb the TaskManager and the constructing of the tasks cache. In that case it is advised to first check which taskId belongs to the faulty task and then manually remove it.

E.g. taskId 'MC00:000000752_12' is a singe task that is missing a time0 object.

SELECT taskId, taskProperties FROM Tasks WHERE taskId='MC00:000000752_12'

--no longer available in 2017.02
DELETE FROM CurrentModuleInstanceRuns WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12')

DELETE FROM TimeSeries WHERE creatorTaskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12')
DELETE FROM Reports WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12')
DELETE FROM ModuleInstanceRuns WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12')
DELETE FROM ModuleRunTables WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12')
DELETE FROM TaskRunCompletions WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12')
DELETE FROM ArchiveMetaData WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12')
DELETE FROM TaskRuns WHERE taskId='MC00:000000752_12'
DELETE FROM Tasks WHERE taskId='MC00:000000752_12'

 Deletion of a obsolete TaskRuns

When a selection of TaskRuns in the database need to be removed (e.g. Taskruns older than 100 days):

First use the <basequery> for the database of your choice to find all the taskrunIds

--Oracle
--SELECT taskRunId FROM TaskRuns WHERE creationTime < SYSTIMESTAMP AT TIME ZONE 'GMT' -  NUMTODSINTERVAL(100, 'day')

--Postgres
--SELECT taskRunId FROM TaskRuns WHERE creationTime < CURRENT_TIMESTAMP AT TIME ZONE 'GMT' - interval '100 day'

--SQLServer
SELECT taskRunId FROM TaskRuns WHERE creationTime < GETUTCDATE()-100

-- DELETE FROM CurrentModuleInstanceRuns WHERE taskRunId IN (<baseQuery>) -- removed in 2017.02

--when happy with the TaskRuns to be removed do a cascade delete:
DELETE FROM TimeSeries WHERE creatorTaskRunId IN (<baseQuery>)
DELETE FROM ArchiveMetaData WHERE taskRunId IN (<baseQuery>)
DELETE FROM Reports WHERE taskRunId IN (<baseQuery>)
DELETE FROM ModuleInstanceRuns WHERE taskRunId IN (<baseQuery>)
DELETE FROM ModuleRunTables WHERE taskRunId IN (<baseQuery>)
DELETE FROM TaskRunCompletions WHERE taskRunId IN (<baseQuery>)
DELETE FROM TaskRuns WHERE taskId IN (<baseQuery>)
  • No labels

Disclaimer