dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Yeah, so you need to get rid of all references is all tables to those bogus
periods, I think.

We had a lot of these issues when upgrading to 2.16, because of legacy data
import from DHIS 1.4, which seemed to have gone amiss at somepoint, but it
never seemed to matter up until now. Of course, looking back, there were
clearly some issues. I think we need some more integrity checks on the
period types, which are never explicitly defined in the database, but
coming up with some SQL to check them might be a good idea.

Regards,
Jason


On Mon, Sep 15, 2014 at 4:14 PM, Moemedi Ntunyane <
moemedi.ntunyane@xxxxxxxxxxx> wrote:

> Thanks, will eliminate all of them. So far the query returned 5 records.
> But other records are referenced by foreign keys like:
> chart_periods;reporttable_periods;completedatasetregistration
>
>
> *Moemedi NtunyaneCo-founder KeyInConsulting*
>
>
> ------------------------------
> Date: Mon, 15 Sep 2014 16:01:08 +0200
>
> Subject: Re: [Dhis2-devs] periodstructure giving errors...
> From: jason.p.pickering@xxxxxxxxx
> To: moemedi.ntunyane@xxxxxxxxxxx
> CC: dhis2-devs@xxxxxxxxxxxxxxxxxxx
>
> I suspect you have more invalid periods then. You should follow the same
> procedure for other period types, i.e. checking to be sure that all months
> are one month, all weeks are one week, etc.
>
> You may want to try something like
>
>  SELECT startdate, periodtypeid,COUNT(*) from period GROUP BY
> startdate,periodtypeid HAVING COUNT(*) > 1 ;
>
> and see which periods and period types you have multiple records for.
> Ideally, this should not happen.
>
> Regards,
> Jason
>
>
> On Mon, Sep 15, 2014 at 3:53 PM, Moemedi Ntunyane <
> moemedi.ntunyane@xxxxxxxxxxx> wrote:
>
> Hi Jason
> Thnx, I had successfully elimated duplicate records the error still
> pesist....it complaining of ISO column: is this column a date column??
>
> Process failed: PreparedStatementCallback; SQL [insert into
> _periodstructure values (?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: null value in
> column "iso" violates not-null constraint; nested exception is
> org.postgresql.util.PSQLException: ERROR: null value in column "iso"
> violates not-null constraint.
>
> Regards,
>
>
> *Moemedi NtunyaneCo-founder KeyInConsulting*
>
>
> ------------------------------
> Date: Mon, 15 Sep 2014 12:22:31 +0200
>
> Subject: Re: [Dhis2-devs] periodstructure giving errors...
> From: jason.p.pickering@xxxxxxxxx
> To: moemedi.ntunyane@xxxxxxxxxxx
> CC: dhis2-devs@xxxxxxxxxxxxxxxxxxx
>
> So, the period 1589;6;"2010-02-01";"2010-02-28 , points to a yearly
> period type, which is not a year in duration. You are going to need to get
> rid of it. You will need to do something like
>
> SELECT COUNT(*) FROM datavalue where periodid = 1589;
>
> If you have any records with this periodID, you are going to need to
> resolve these. This period looks to be monthly, so if the data is also
> monthly, you should be able to reassign it to a monthly period which starts
> in 2010-02-01 and ends in 2010-02-28, if it is already there. You could try
> "SELECT * FROM period where startdate = '2010-02-01'::date;" and see if you
> get multiple periods for Feb 2010. If you have multiple periods for Feb
> 2010, then you need to do something like
>
> UPDATE datavalue set periodid = ?????
>
> where ???? is the periodid of the "real" Feb 2010 period.
>
> Then you should get rid of the bogus Feb 2010 period
>
> with
>
> DELETE FROM period where periodid = 1589;
>
> After that, you should clear your cache from Data administration.
>
> Do not try any of this on a production database!!!
>
> Regards,
> Jason
>
>
> On Mon, Sep 15, 2014 at 12:14 PM, Moemedi Ntunyane <
> moemedi.ntunyane@xxxxxxxxxxx> wrote:
>
> Hi Jason
>
> Thanx, I just run the query you gave and no records were available and
> removed not = year(!=year) and records where available. The date format is
> available as:
>
> 1573;6;"2010-01-01";"2010-01-31"
> 1589;6;"2010-02-01";"2010-02-28"
>
> The error available shows that there is key violation for null values for
> iso column in the periodstructure table.
>
>
>
>
> *Moemedi NtunyaneCo-founder KeyInConsulting*
>
>
> ------------------------------
> Date: Mon, 15 Sep 2014 11:46:07 +0200
> Subject: Re: [Dhis2-devs] periodstructure giving errors...
> From: jason.p.pickering@xxxxxxxxx
> To: moemedi.ntunyane@xxxxxxxxxxx
> CC: dhis2-devs@xxxxxxxxxxxxxxxxxxx
>
>
> Very likely you have an invalid period in the periods table.
>
> Can you run this and see what happens?
>
> SELECT * FROM period where age(enddate,startdate) != '1 year'::interval
> and periodtypeid = (SELECT periodtypeid from periodtype where name =
> 'Yearly');
>
> Also, you should get an error which will help you to decipher which period
> is not correct , something like
>
> Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value
> violates unique constraint "in_periodstructure_iso"
>   Detail: Key (iso)=(1995) already exists.
>
> Best regards,
> Jason
>
>
> On Fri, Sep 12, 2014 at 5:03 PM, Moemedi Ntunyane <
> moemedi.ntunyane@xxxxxxxxxxx> wrote:
>
> Hi All
>
> I have this problem while generating the resource tables...all other
> tables are fine except periodstructure...
>
> Process failed: PreparedStatementCallback; SQL [insert into
> _periodstructure values (?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: null value in
> column "iso" violates not-null constraint; nested exception is
> org.postgresql.util.PSQLException: ERROR: null value in column "iso"
> violates not-null constraint
>
> Regards,
>
>
> *Moemedi NtunyaneCo-founder KeyInConsulting*
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049
>
>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049
>
>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049
>



-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049