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?
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.
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?
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.
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.
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?
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?