dhis2-devs team mailing list archive

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

Did you clear your server cache and or restart DHIS2?

On Tue, Sep 16, 2014 at 5:50 AM, Moemedi Ntunyane <
moemedi.ntunyane@xxxxxxxxxxx> wrote:

> I have eliminated all duplicates but this error still persist.
>
> 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.
>
>
> *Moemedi NtunyaneCo-founder KeyInConsulting*
>
>
> ------------------------------
> From: moemedi.ntunyane@xxxxxxxxxxx
> To: larshelge@xxxxxxxxx; jason.p.pickering@xxxxxxxxx
> CC: dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Subject: RE: [Dhis2-devs] periodstructure giving errors...
> Date: Mon, 15 Sep 2014 16:18:14 +0200
>
>
> Hi Lars,
>
> I have not done that. Im only capturing statistical data.
>
> Regards,
>
>
> *Moemedi NtunyaneCo-founder KeyInConsulting*
>
>
> ------------------------------
> Date: Mon, 15 Sep 2014 16:10:13 +0200
> Subject: Re: [Dhis2-devs] periodstructure giving errors...
> From: larshelge@xxxxxxxxx
> To: jason.p.pickering@xxxxxxxxx
> CC: moemedi.ntunyane@xxxxxxxxxxx; dhis2-devs@xxxxxxxxxxxxxxxxxxx
>
> Hi Moemedi,
>
> may I ask, did you ever run the "aggregation queries" (producing aggregate
> values from event data) on your database?
>
> regards,
>
> Lars
>
>
> On Mon, Sep 15, 2014 at 4:01 PM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
> 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
>
> _______________________________________________
> 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