KBEC-00422 - Locating and Fixing Properties with Excessive Unwanted Change History Generated by Automated Processes

In ElectricFlow versions 6.0 through 8.5, the ectool modifyProperty, setProperty, and createProperty commands have a Boolean flag called counter, which should be set on properties that are used as counters (or other numeric output) by automated processes, in order to suppress Change Tracking from recording changes where the only change was the value of the property changing from one numerical value to another. In version 9.0, this flag was renamed to suppressValueTracking, and its behavior was expanded to suppress recording changes where the only change was the value of the property changing (regardless of whether the old or new values were numerical or not).

A common problem experienced with Change Tracking is to have a property belonging to a change-tracked non-runtime entity (such as a project, procedure, user, or even the server entity) whose value is routinely updated by running jobs, pipeline, workflows, or other automated processes. This can generate a large amount of change history for the property (and thus for everything above it on its property path), and the data generated is generally useless – a waste of database table space. This will seriously affect the performance and usability of Change Tracking for this property and everything above it on its property path by flooding the history with unimportant changes, and in extreme cases can even negatively affect the performance of Change Tracking for other entities. So we strongly recommend setting the suppressValueTracking flag on properties like this (or in 8.5 and before, if the values are all numeric, setting the counter flag).

On occasion, users might accidentally fail to do this, so it is useful to be able to track down properties that have a large amount of change history of this form, where this flag has not been set and setting it would have greatly reduced the amount of change history generated. Then you can assess whether the flag should have been set, and if so, set it to stop the problem from getting worse.

To do this, you will need “read‘ access to your database and the ability to issue SQL queries against it. The necessary login information (other than the password – consult your DBA for this) can be found in the <DATA_DIR>/conf/database.properties configuration file of your ElectricFlow server. You may find it helpful to use a GUI SQL workbench, such as "SQL Server Management Studio" for Microsoft SQL Server, or "SQL Developer" for Oracle, or "MySQL Workbench" for MySQL, but any means of issuing SQL queries will work.

Once logged in, with your Electric Flow database schema and user set as the default, issue the following SQL query.

In 9.0 or later:

SELECT count(*), ap.name, mcn.name
FROM ec_entity_change AS ec
LEFT JOIN ec_pooled_string AS ap ON ec.after_path_id = ap.id
LEFT JOIN ec_pooled_string AS mcn ON ec.modified_column_names_id = mcn.id
LEFT JOIN ec_property AS p ON ec.entity_id = p.id
WHERE ec.descendant_change_id IS NULL
AND ec.entity_type = 'property'
AND ec.before_path_id = ec.after_path_id
AND mcn.name IN ('compositeString', 'compositeString, numericValue')
AND ( p.suppress_value_tracking IS NULL OR NOT p.suppress_value_tracking )
GROUP BY ec.after_path_id, ap.name, mcn.name
ORDER BY count(*) DESC;

or in 8.5 or before:

SELECT count(*), ap.name, mcn.name
FROM ec_entity_change AS ec
LEFT JOIN ec_pooled_string AS ap ON ec.after_path_id = ap.id
LEFT JOIN ec_pooled_string AS mcn ON ec.modified_column_names_id = mcn.id
LEFT JOIN ec_property AS p ON ec.entity_id = p.id
WHERE ec.descendant_change_id IS NULL
AND ec.entity_type = 'property'
AND ec.before_path_id = ec.after_path_id
AND mcn.name IN ('compositeString, numericValue')
AND ( p.counter IS NULL OR NOT p.counter )
GROUP BY ec.after_path_id, ap.name, mcn.name
ORDER BY count(*) DESC;

Note that this query might take seconds or even minutes to run, depending on the size of your database. This only lists data for properties that don’t currently have the flag set. The three columns of output are:

1. How many change history events would have been avoided if the flag had been set from the start (in 8.5- this value is approximate)
2. The property's property path
3. Whether the change involved a numeric value or not (this can mostly be ignored, and in 8.5 will only ever be compositeString, numericValue')

Rows are returned in descending order of the first column – meaning the size of the potential problem. You should definitely address cases where this is in the thousands or hundreds and can probably safely ignore cases where this value is less than a few tens, or maybe even less than about a hundred.

For each row returned by this query, you need to ascertain whether:

1. Some automated process is repeatedly changing the value of this property (as opposed to people manually having manually changed its value this many times), generally this would happen via an automated process using an ectool modifyProperty or ectool setProperty call (or possible as a side effect of a Commander JavaScript invocation, if your server is configured to allow this), and
2. No one has any good need to see a (potentially very long) history of all the automated changes to this property's value in its change history.

If you are not familiar with the usage of the property, then answering these questions will require someone who is familiar with it (thought the property path might be suggestive, especially if it contains words like current, status, or counter). If the value is being changed in an automated way and no one needs an audit trail of this, then you should set the suppressValueTracking or counter flag for this property, to halt the further growth of its change history. In 8.5 and before this can only be done using:

ectool modifyProperty ... --counter 1

In version 9.0, this can also be done in the ElectricFlow Deploy GUI and the Automation Platform GUI; the checkbox there is called "Track Changes to Value", which is checked by default. Unchecking the checkbox in the UI sets the suppressValueTracking flag in the back end – its Boolean value is inverted. You should recheck this occasionally, to see if anyone has introduced new problem cases.