Duplicate PERIOD records - WHY no startdate+enddate constraint on PERIOD table?

Hi

I have over the last 2-3 weeks had multiple cases with duplicated PERIOD records in DHIS2 instances. In the latest case, it looks like capturing of daily data in some cases created duplicated period records - two records with identical startdate+enddate+periodtype, and sequential periodid. With most duplicate pairs, only the first periodid had a record in the datavalue table.

I will try to track down the root cause of this duplicate generation, BUT what I don’t understand is the lack of a periodtypeid+startdate+enddate constraint on the period table.

Any periodtypeid+startdate+enddate combination would in reality be unique?

I see there’s an integrity check for it, but a constraint on the table is better, I would think.

Besides - where can I view the code/sql used for those integrity checks?

Regards

calle

···

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg


Hi Calle,

we do have such a uniqueness constraint on period table. It is called “period_periodtypeid_key”. If it is not there then it means it somehow was removed, then duplicates was inserted which prevents hibernate to put it back when starting DHIS. You can try adding it manually with this SQL:

ALTER TABLE period ADD CONSTRAINT period_periodtypeid_key UNIQUE(periodtypeid, startdate, enddate);

If that fails, please remove the duplicates and run it again.

best regards,

Lars

···

On Sat, Feb 28, 2015 at 11:39 PM, Calle Hedberg calle.hedberg@gmail.com wrote:

Hi

I have over the last 2-3 weeks had multiple cases with duplicated PERIOD records in DHIS2 instances. In the latest case, it looks like capturing of daily data in some cases created duplicated period records - two records with identical startdate+enddate+periodtype, and sequential periodid. With most duplicate pairs, only the first periodid had a record in the datavalue table.

I will try to track down the root cause of this duplicate generation, BUT what I don’t understand is the lack of a periodtypeid+startdate+enddate constraint on the period table.

Any periodtypeid+startdate+enddate combination would in reality be unique?

I see there’s an integrity check for it, but a constraint on the table is better, I would think.

Besides - where can I view the code/sql used for those integrity checks?

Regards

calle


Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg



Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars,

Thanks for the clarification - even if I then do not really understand why there’s an integrity check for duplicate periods if duplicates are directly blocked…??

As a result of your info, though, I’ve now reviewed 20-30 different DHIS2 instances and found that this constraint is missing from all instances created&populated using the DHIS14-DHIS2 conversion procedure, whereas the other “standard” instances do have the “period-periodtypeid_key”. So it looks like the conversion procedure drops that constraint during the conversion process, without re-creating it after the conversion. I will investigate and get it fixed.

Best regards

Calle

···

On 9 March 2015 at 17:52, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Calle,

we do have such a uniqueness constraint on period table. It is called “period_periodtypeid_key”. If it is not there then it means it somehow was removed, then duplicates was inserted which prevents hibernate to put it back when starting DHIS. You can try adding it manually with this SQL:

ALTER TABLE period ADD CONSTRAINT period_periodtypeid_key UNIQUE(periodtypeid, startdate, enddate);

If that fails, please remove the duplicates and run it again.

best regards,

Lars

On Sat, Feb 28, 2015 at 11:39 PM, Calle Hedberg calle.hedberg@gmail.com wrote:

Hi

I have over the last 2-3 weeks had multiple cases with duplicated PERIOD records in DHIS2 instances. In the latest case, it looks like capturing of daily data in some cases created duplicated period records - two records with identical startdate+enddate+periodtype, and sequential periodid. With most duplicate pairs, only the first periodid had a record in the datavalue table.

I will try to track down the root cause of this duplicate generation, BUT what I don’t understand is the lack of a periodtypeid+startdate+enddate constraint on the period table.

Any periodtypeid+startdate+enddate combination would in reality be unique?

I see there’s an integrity check for it, but a constraint on the table is better, I would think.

Besides - where can I view the code/sql used for those integrity checks?

Regards

calle


Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg



Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg


Lars,

Thanks for the clarification - even if I then do not really understand why
there's an integrity check for duplicate periods if duplicates are directly
blocked...??

The integrity check is actually checking duplicate period type + start date
combinations (e.g. invalid periods, such as monthly periods with start date
equal to end date). We have seen this sneak in from 3rd party
systems/aggregation query builder and this will confuse our period
generator, hence the check.

As a result of your info, though, I've now reviewed 20-30 different DHIS2
instances and found that this constraint is missing from all instances
created&populated using the DHIS14-DHIS2 conversion procedure, whereas the
other "standard" instances do have the "period-periodtypeid_key". So it
looks like the conversion procedure drops that constraint during the
conversion process, without re-creating it after the conversion. I will
investigate and get it fixed.

Okay thanks for the update.

regards,

Lars

Best regards

···

On Mon, Mar 9, 2015 at 10:10 PM, Calle Hedberg <calle.hedberg@gmail.com> wrote:

Calle

On 9 March 2015 at 17:52, Lars Helge Øverland <larshelge@gmail.com> wrote:

Hi Calle,

we do have such a uniqueness constraint on period table. It is called
"period_periodtypeid_key". If it is not there then it means it somehow was
removed, then duplicates was inserted which prevents hibernate to put it
back when starting DHIS. You can try adding it manually with this SQL:

ALTER TABLE period ADD CONSTRAINT period_periodtypeid_key
UNIQUE(periodtypeid, startdate, enddate);

If that fails, please remove the duplicates and run it again.

best regards,

Lars

On Sat, Feb 28, 2015 at 11:39 PM, Calle Hedberg <calle.hedberg@gmail.com> >> wrote:

Hi

I have over the last 2-3 weeks had multiple cases with duplicated PERIOD
records in DHIS2 instances. In the latest case, it looks like capturing of
daily data in some cases created duplicated period records - two records
with identical startdate+enddate+periodtype, and sequential periodid. With
most duplicate pairs, only the first periodid had a record in the datavalue
table.

I will try to track down the root cause of this duplicate generation,
BUT what I don't understand is the lack of a periodtypeid+startdate+enddate
constraint on the period table.

Any periodtypeid+startdate+enddate combination would in reality be
unique?

I see there's an integrity check for it, but a constraint on the table
is better, I would think.

Besides - where can I view the code/sql used for those integrity checks?

Regards
calle

*******************************************

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

*******************************************

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

--

*******************************************

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

*******************************************