Loss of data integrity with Release between Nov 13 and Nov 20 2019

All standard software releases including our Invantive SQL engine with major versions 17.32 (production) and 17.33 (BETA) included a major bug that can lead to loss of data integrity in specific scenarios.

To avoid data loss, Invantive strongly recommends to upgrade from the affected releases as follows:

  • Production releases from 17.32.117 to 17.32.122: upgrade to 17.32.123.
  • BETA releases from 17.33.191 to 17.33.205: upgrade to 17.33.207.

The bug was introduced in version 17.32.117 and 17.33.191. Previous versions such as 17.32.116 and 17.33.190 do not include this bug and therefore are not affected.

I apologize on behalf of Invantive for any inconvenience caused.

Affected Scenarios

The bug causes loss of data integrity during retrieval of data by silently leaving out a number of records. Due to the nature of the bug, in most scenarios the bug doesn’t occur. The bug surfaces when you run a query that meets the following conditions:

  • the query retrieves data across multiple partitions within one data container such as multiple Exact Online or Visma.net companies within one subscription;
  • the connector delivers rows one-by-one instead of a batch (this holds for most high-performance connectors).

At this moment, results from queries retrieving data from one partition have not been seen to be impacted by this bug.

Typically, once all data have been retrieved for one partition, records coming in from other partitions are left out from the result set.

Background

On November 13, we have included in the BETA and production releases an enhancement to reduce the CPU-load of massive parallel processing. Over time, Invantive SQL has increased considerably in high-volume enterprise environments with hundreds and thousands of managed partitions. This enhancement reduces the CPU-load by approximately 35%, improving scalability and reducing CO2 footprint. The enhancement had been tested using a number of test cases and went through code review, but an overseen bug made it into both the BETA and production releases.

On November 19, we have received a bug report from a Belgium user of Invantive Bridge Online for Power BI that for some Exact Online companies data was missing from the results. On November 20, we received another bug report from a Dutch accountant on Get My Report uploading a large set of transactions into multiple companies that one application control query incorrectly returned a problem while the Exact Online company was correctly configured. Both incidents led to a bug report as described by affected scenarios.

At the end of November 20, we were able to reproduce the issue by extending our existing set of test cases with a new test case, explicitly combining a number of queries across multiple partitions. The single-line bug fix were included in release 17.32.117 and release 17.33.207. These releases were made available on the evening of November 20.

The release with the bug fix has been implemented during the evening of November 20 on all affected Invantive servers; products such as Get My Report and Bridge Online for Power BI have been confirmed by the affected users to no longer show the problem.

All users that upgraded to an affected release and used that release in the period November 17-20 will be informed by email with a strong suggestion to upgrade.

The affected releases have been made unavailable for download to avoid further spread.
In total, it seems that approximately five user communities have been affected.

Approach

For Data Replicator uses, we recommend to run an ‘alter persistent cache force refresh approach copy’ for the data containers using partitions.

For data exchange uses we recommend users of Invantive SQL to check for all uses with multiple active partitions (such as signaled by ‘use 123, 456, 789’) that the outcomes have matched the expectations.

Future

Looking back at the process, we have tried to find countermeasures that avoid of such an essential bug making it into the production releases again. These countermeasures should not reduce the speed of changes make it into to production and BETA releases. Invantive typically releases several times per week.

We have decided to create two production releases on the next production release:

  • One “boring” production release (such as 17.32) which solely receives technical bug fixes to known issues and no enhancements. These bug fixes typically have a low-risk of introducing new problems.
  • One “improving” production release (such as 17.34) which receives both bug fixes for both small technical issues as well as user perceived bugs. User perceived bugs typically have a risk of introducing one new bug on every seven bug fixes since they have a large code impact.
  • The BETA release remains.

Users running high-risk business process can use the “boring” production release, while users consider a specific bug fix essential for their business can use the “improving” production release. Users that need more recent additions can use the BETA releases.