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 Ntunyane
Co-founder KeyInConsulting
Hi All
I have attached the error log. I am not sure if this error is caused the war file I have used or not. Please help.
Regards,
Moemedi Ntunyane
Co-founder KeyInConsulting
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
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 Ntunyane
Co-founder KeyInConsulting
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 11:46:07 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.net
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
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 Ntunyane
Co-founder KeyInConsulting
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 12:22:31 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.netSo, 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 11:46:07 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.net
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
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@gmail.com 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
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
On Mon, Sep 15, 2014 at 3:53 PM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 12:22:31 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.netSo, 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 11:46:07 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.net
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
Following from Lars’ question, or import data from another system?
Regards,
Busoye
On Mon, Sep 15, 2014 at 4:01 PM, Jason Pickering jason.p.pickering@gmail.com 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
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
On Mon, Sep 15, 2014 at 3:53 PM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 12:22:31 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.netSo, 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 11:46:07 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.net
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
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 Ntunyane
Co-founder KeyInConsulting
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@hotmail.com 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;completedatasetregistrationMoemedi Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 16:01:08 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.netI 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 12:22:31 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.netSo, 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 11:46:07 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.net
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
Hi Lars,
I have not done that. Im only capturing statistical data.
Regards,
Moemedi Ntunyane
Co-founder KeyInConsulting
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 Ntunyane
Co-founder KeyInConsulting
Did you clear your server cache and or restart DHIS2?
On Tue, Sep 16, 2014 at 5:50 AM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
From: moemedi.ntunyane@hotmail.com
To: larshelge@gmail.com; jason.p.pickering@gmail.com
CC: dhis2-devs@lists.launchpad.net
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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 16:10:13 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: larshelge@gmail.com
To: jason.p.pickering@gmail.com
CC: moemedi.ntunyane@hotmail.com; dhis2-devs@lists.launchpad.netHi 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@gmail.com 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 12:22:31 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.netSo, 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 11:46:07 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.net
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
I cleared cache and even restarted my tomcat server
Moemedi Ntunyane
Co-founder KeyInConsulting
Yeah, well, I can’t offer much more advice other in this case. We had similar problems as I mentioned before with 2.16, but after carefully cleaning up all of the faulty periods, things worked OK.
One thing which I would suggest would be to turn on full logging of all postgresql queries, and see exactly where things are failing.
You should also see something like
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “in_periodstructure_iso”
Detail: Key (iso)=(1995) already exists.
in the Tomcat log, which again, gives you a clue about which period may be causing the problem. I know you posted a portion of the log before, but I did not see anything like this in your log. Turning on the postgresql logs may help you to track down which period is causing this error.
Maybe one of the developers can add more information? The procedure worked for us, but required a bit of detective work.
Regards,
Jason
On Tue, Sep 16, 2014 at 9:12 AM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com wrote:
I cleared cache and even restarted my tomcat server
Moemedi Ntunyane
Co-founder KeyInConsulting
Date: Tue, 16 Sep 2014 06:09:43 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: larshelge@gmail.com; dhis2-devs@lists.launchpad.netDid you clear your server cache and or restart DHIS2?
On Tue, Sep 16, 2014 at 5:50 AM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
From: moemedi.ntunyane@hotmail.com
To: larshelge@gmail.com; jason.p.pickering@gmail.com
CC: dhis2-devs@lists.launchpad.net
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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 16:10:13 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: larshelge@gmail.com
To: jason.p.pickering@gmail.com
CC: moemedi.ntunyane@hotmail.com; dhis2-devs@lists.launchpad.netHi 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@gmail.com 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 12:22:31 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.netSo, 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 11:46:07 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.net
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
Thanks, I was upgrading from 2.0.5 to 2.14. I can only see this error its not complaining of duplication but null value error:
org.postgresql.util.PSQLException: ERROR: null value in column "iso" violates not-null constraint.
Moemedi Ntunyane
Co-founder KeyInConsulting
Yes, you will see that error, but immediately after that exception in the logs, should be another one. If you do not see it, as I said, you may need to resort to postgresql logging to figure out exactly what is causing the problem.
On Tue, Sep 16, 2014 at 9:27 AM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com wrote:
Thanks, I was upgrading from 2.0.5 to 2.14. I can only see this error its not complaining of duplication but null value error:
org.postgresql.util.PSQLException: ERROR: null value in column “iso” violates not-null constraint.
Moemedi Ntunyane
Co-founder KeyInConsulting
Date: Tue, 16 Sep 2014 09:18:20 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: larshelge@gmail.com; dhis2-devs@lists.launchpad.netYeah, well, I can’t offer much more advice other in this case. We had similar problems as I mentioned before with 2.16, but after carefully cleaning up all of the faulty periods, things worked OK.
One thing which I would suggest would be to turn on full logging of all postgresql queries, and see exactly where things are failing.
You should also see something like
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “in_periodstructure_iso”
Detail: Key (iso)=(1995) already exists.
in the Tomcat log, which again, gives you a clue about which period may be causing the problem. I know you posted a portion of the log before, but I did not see anything like this in your log. Turning on the postgresql logs may help you to track down which period is causing this error.
Maybe one of the developers can add more information? The procedure worked for us, but required a bit of detective work.
Regards,
Jason
On Tue, Sep 16, 2014 at 9:12 AM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com wrote:
I cleared cache and even restarted my tomcat server
Moemedi Ntunyane
Co-founder KeyInConsulting
Date: Tue, 16 Sep 2014 06:09:43 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: larshelge@gmail.com; dhis2-devs@lists.launchpad.net
Did you clear your server cache and or restart DHIS2?
On Tue, Sep 16, 2014 at 5:50 AM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com 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
Moemedi Ntunyane
Co-founder KeyInConsulting
From: moemedi.ntunyane@hotmail.com
To: larshelge@gmail.com; jason.p.pickering@gmail.com
CC: dhis2-devs@lists.launchpad.net
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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 16:10:13 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: larshelge@gmail.com
To: jason.p.pickering@gmail.com
CC: moemedi.ntunyane@hotmail.com; dhis2-devs@lists.launchpad.netHi 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@gmail.com 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 12:22:31 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.netSo, 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 11:46:07 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.net
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
Depending on how much of configuration you have in your database, one option could be to start afresh - doing metadata and data exports, and import to an empty database. You would lose certain things (like users), but also possibly get rid of some issues, rather than operating directly in the database.
On Tue, Sep 16, 2014 at 9:27 AM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com wrote:
Thanks, I was upgrading from 2.0.5 to 2.14. I can only see this error its not complaining of duplication but null value error:
org.postgresql.util.PSQLException: ERROR: null value in column “iso” violates not-null constraint.
Moemedi Ntunyane
Co-founder KeyInConsulting
Date: Tue, 16 Sep 2014 09:18:20 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: larshelge@gmail.com; dhis2-devs@lists.launchpad.netYeah, well, I can’t offer much more advice other in this case. We had similar problems as I mentioned before with 2.16, but after carefully cleaning up all of the faulty periods, things worked OK.
One thing which I would suggest would be to turn on full logging of all postgresql queries, and see exactly where things are failing.
You should also see something like
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “in_periodstructure_iso”
Detail: Key (iso)=(1995) already exists.
in the Tomcat log, which again, gives you a clue about which period may be causing the problem. I know you posted a portion of the log before, but I did not see anything like this in your log. Turning on the postgresql logs may help you to track down which period is causing this error.
Maybe one of the developers can add more information? The procedure worked for us, but required a bit of detective work.
Regards,
Jason
On Tue, Sep 16, 2014 at 9:12 AM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com wrote:
I cleared cache and even restarted my tomcat server
Moemedi Ntunyane
Co-founder KeyInConsulting
Date: Tue, 16 Sep 2014 06:09:43 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: larshelge@gmail.com; dhis2-devs@lists.launchpad.net
Did you clear your server cache and or restart DHIS2?
On Tue, Sep 16, 2014 at 5:50 AM, Moemedi Ntunyane moemedi.ntunyane@hotmail.com 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
Moemedi Ntunyane
Co-founder KeyInConsulting
From: moemedi.ntunyane@hotmail.com
To: larshelge@gmail.com; jason.p.pickering@gmail.com
CC: dhis2-devs@lists.launchpad.net
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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 16:10:13 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: larshelge@gmail.com
To: jason.p.pickering@gmail.com
CC: moemedi.ntunyane@hotmail.com; dhis2-devs@lists.launchpad.netHi 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@gmail.com 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 12:22:31 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.netSo, 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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
Date: Mon, 15 Sep 2014 11:46:07 +0200
Subject: Re: [Dhis2-devs] periodstructure giving errors…
From: jason.p.pickering@gmail.com
To: moemedi.ntunyane@hotmail.com
CC: dhis2-devs@lists.launchpad.net
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@hotmail.com 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 Ntunyane
Co-founder KeyInConsulting
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
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
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
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
–
Knut Staring
Dept. of Informatics, University of Oslo
+4791880522
Skype: knutstar
Thanks a lot for your assistance, I just used a fresh database and imported meta data and the data itself eventhough other info was missing but managed to fix everything. Now its working ok on my local instance.
Moemedi Ntunyane
Co-founder KeyInConsulting