periodstructure giving errors...

Support - Assistance technique
1

Very likely you have an invalid period in the periods table.=20
Can you run this and see what happens?
SELECT * FROM period where age(enddate=2Cstartdate) !=3D '1 year'::interval=
and periodtypeid =3D (SELECT periodtypeid from periodtype where name =3D '=
Yearly')=3B

Also=2C you should get an error which will help you to decipher which perio=
d is not correct =2C something like=20
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value vi=
olates unique constraint "in_periodstructure_iso" Detail: Key (iso)=3D(199=
5) already exists.
Best regards=2CJason

ยทยทยท

On Fri=2C Sep 12=2C 2014 at 5:03 PM=2C Moemedi Ntunyane <moemedi.ntunyane@h= otmail.com> wrote:
=0A=
=0A=
=0A=
Hi All

I have this problem while generating the resource tables...all other tables=
are fine except periodstructure...

Process=0A=
failed: PreparedStatementCallback=3B SQL [insert into _periodstructure =0A=
values (?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?)]=3B ERROR: null =
value in column "iso" =0A=
violates not-null constraint=3B nested exception is =0A=
org.postgresql.util.PSQLException: ERROR: null value in column "iso" =0A=
violates not-null constraint =20

Regards=2C

Moemedi Ntunyane
Co-founder KeyInConsulting
                 =0A=

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

--=20
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049=0A=
                 =0A=

--=20
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049=0A=
                 =0A=

--=20
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049=0A=
=0A=

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

                                  =0A=

--=20
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049=0A=
                 =

--_470fab50-8959-4c44-aa07-f573ede44425_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 12pt=3B
font-family:Calibri
}
--></style></head>
<body class=3D'hmmessage'><div dir=3D'ltr'>I cleared cache and even restart=
ed my tomcat server<br><br><font style=3D"" color=3D"#00B050" face=3D"Couri=
er New"><b><i><font style=3D"font-size:8pt" size=3D"1">Moemedi Ntunyane</fo=

<font style=3D"font-size:8pt" size=3D"1"><br></font><font style=3D"font-=

size:8pt" size=3D"1">Co-founder KeyInConsulting</font></i></b></font><br><b=

<br><div><hr id=3D"stopSpelling">Date: Tue=2C 16 Sep 2014 06:09:43 +0200<=
Subject: Re: [Dhis2-devs] periodstructure giving errors...<br>From: jaso=

n.p.pickering@gmail.com<br>To: moemedi.ntunyane@hotmail.com<br>CC: larshelg=
e@gmail.com=3B dhis2-devs@lists.launchpad.net<br><br><div dir=3D"ltr">Did y=
ou clear your server cache and or restart DHIS2?</div><div class=3D"ecxgmai=
l_extra"><br><div class=3D"ecxgmail_quote">On Tue=2C Sep 16=2C 2014 at 5:50=
AM=2C Moemedi Ntunyane <span dir=3D"ltr">&lt=3B<a href=3D"mailto:moemedi.n=
tunyane@hotmail.com" target=3D"_blank">moemedi.ntunyane@hotmail.com</a>&gt=
=3B</span> wrote:<br><blockquote class=3D"ecxgmail_quote" style=3D"border-l=
eft:1px #ccc solid=3Bpadding-left:1ex=3B">=0A=
=0A=
=0A=
<div><div dir=3D"ltr">I have eliminated all duplicates but this error still=
persist.<div><br></div><div><span>Process failed: PreparedStatementCallbac=
k=3B SQL [insert into _periodstructure values (?=2C?=2C?=2C?=2C?=2C?=2C?=2C=
?=2C?=2C?=2C?=2C?=2C?)]=3B ERROR: null value in column "iso" violates not-n=
ull constraint=3B nested exception is org.postgresql.util.PSQLException: ER=
ROR: null value in column "iso" violates not-null constraint.<br><br></span=

<span class=3D"ecxHOEnZb"><font color=3D"#888888"><font color=3D"#00B050" =

face=3D"Courier New"><b><i><font style=3D"font-size:8pt=3B" size=3D"1">Moem=
edi Ntunyane</font><font style=3D"font-size:8pt=3B" size=3D"1"><br></font><=
font style=3D"font-size:8pt=3B" size=3D"1">Co-founder KeyInConsulting</font=

</i></b></font><br></font></span><div class=3D"ecxhm ecxHOEnZb"><br><br></=
<div><div class=3D"ecxhm ecxHOEnZb"><hr>From: <a href=3D"mailto:moemedi=

.ntunyane@hotmail.com" target=3D"_blank">moemedi.ntunyane@hotmail.com</a><b=

To: <a href=3D"mailto:larshelge@gmail.com" target=3D"_blank">larshelge@gm=

ail.com</a>=3B <a href=3D"mailto:jason.p.pickering@gmail.com" target=3D"_bl=
ank">jason.p.pickering@gmail.com</a><br>CC: <a href=3D"mailto:dhis2-devs@li=
sts.launchpad.net" target=3D"_blank">dhis2-devs@lists.launchpad.net</a><br>=
Subject: RE: [Dhis2-devs] periodstructure giving errors...<br>Date: Mon=2C =
15 Sep 2014 16:18:14 +0200</div><div><div class=3D"h5"><br><br>=0A=
=0A=
=0A=
<div dir=3D"ltr">Hi Lars=2C<br><br>I have not done that. Im only capturing =
statistical data.<br><br>Regards=2C<br><br><font color=3D"#00B050" face=3D"=
Courier New"><b><i><font style=3D"font-size:8pt=3B" size=3D"1">Moemedi Ntun=
yane</font><font style=3D"font-size:8pt=3B" size=3D"1"><br></font><font sty=
le=3D"font-size:8pt=3B" size=3D"1">Co-founder KeyInConsulting</font></i></b=

</font><br><br><br><div><hr>Date: Mon=2C 15 Sep 2014 16:10:13 +0200<br>Sub=

ject: Re: [Dhis2-devs] periodstructure giving errors...<br>From: <a href=3D=
"mailto:larshelge@gmail.com" target=3D"_blank">larshelge@gmail.com</a><br>T=
o: <a href=3D"mailto:jason.p.pickering@gmail.com" target=3D"_blank">jason.p=
.pickering@gmail.com</a><br>CC: <a href=3D"mailto:moemedi.ntunyane@hotmail.=
com" target=3D"_blank">moemedi.ntunyane@hotmail.com</a>=3B <a href=3D"mailt=
o:dhis2-devs@lists.launchpad.net" target=3D"_blank">dhis2-devs@lists.launch=
pad.net</a><br><br><div dir=3D"ltr">Hi Moemedi=2C<div><br></div><div>may I =
ask=2C did you ever run the "aggregation queries" (producing aggregate valu=
es from event data) on your database?</div><div><br></div><div>regards=2C</=

<div><br></div><div>Lars</div><div><br></div></div><div><br><div>On Mon=

=2C Sep 15=2C 2014 at 4:01 PM=2C Jason Pickering <span dir=3D"ltr">&lt=3B<a=
href=3D"mailto:jason.p.pickering@gmail.com" target=3D"_blank">jason.p.pick=
ering@gmail.com</a>&gt=3B</span> wrote:<br><blockquote style=3D"border-left=
:1px #ccc solid=3Bpadding-left:1ex=3B"><div dir=3D"ltr">I suspect you have =
more invalid periods then. You should follow the same procedure for other p=
eriod types=2C i.e. checking to be sure that all months are one month=2C al=
l weeks are one week=2C etc.&nbsp=3B<div><br></div><div>You may want to try=
something like</div><div><br></div><div>&nbsp=3BSELECT startdate=2C period=
typeid=2CCOUNT(*) from period GROUP BY startdate=2Cperiodtypeid HAVING COUN=
T(*) &gt=3B 1 =3B<br></div><div><br></div><div>and see which periods and pe=
riod types you have multiple records for. Ideally=2C this should not happen=
.&nbsp=3B</div><div><br></div><div>Regards=2C</div><div>Jason</div><div><br=

</div></div><div><div><div><br><div>On Mon=2C Sep 15=2C 2014 at 3:53 PM=2C=

Moemedi Ntunyane <span dir=3D"ltr">&lt=3B<a href=3D"mailto:moemedi.ntunyan=
e@hotmail.com" target=3D"_blank">moemedi.ntunyane@hotmail.com</a>&gt=3B</sp=

wrote:<br><blockquote style=3D"border-left:1px #ccc solid=3Bpadding-lef=

t:1ex=3B">=0A=
=0A=
=0A=
<div><div dir=3D"ltr">Hi Jason<br>Thnx=2C I had successfully elimated dupli=
cate records the error still pesist....it complaining of ISO column: is thi=
s column a date column??<br><br>Process=0A=
failed: PreparedStatementCallback=3B SQL [insert into _periodstructure =0A=
values (?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?)]=3B ERROR: null =
value in column "iso" =0A=
violates not-null constraint=3B nested exception is =0A=
org.postgresql.util.PSQLException: ERROR: null value in column "iso" =0A=
violates not-null constraint.<span><br><br>Regards=2C<br><br><font color=3D=
"#00B050" face=3D"Courier New"><b><i><font style=3D"font-size:8pt=3B" size=
=3D"1">Moemedi Ntunyane</font><font style=3D"font-size:8pt=3B" size=3D"1"><=

</font><font style=3D"font-size:8pt=3B" size=3D"1">Co-founder KeyInConsu=

lting</font></i></b></font><br><br><br></span><div><hr>Date: Mon=2C 15 Sep =
2014 12:22:31 +0200<div><div><br>Subject: Re: [Dhis2-devs] periodstructure =
giving errors...<br>From: <a href=3D"mailto:jason.p.pickering@gmail.com" ta=
rget=3D"_blank">jason.p.pickering@gmail.com</a><br>To: <a href=3D"mailto:mo=
emedi.ntunyane@hotmail.com" target=3D"_blank">moemedi.ntunyane@hotmail.com<=
/a><br>CC: <a href=3D"mailto:dhis2-devs@lists.launchpad.net" target=3D"_bla=
nk">dhis2-devs@lists.launchpad.net</a><br><br><div dir=3D"ltr">So=2C the pe=
riod&nbsp=3B<span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=
=3B">1589=3B6=3B"2010-02-01"=3B"2010-02-</span><span style=3D"font-family:a=
rial=2Csans-serif=3Bfont-size:13px=3B">28 =2C points to a yearly period typ=
e=2C which is not a year in duration. You are going to need to get rid of i=
t. You will need to do something like</span><div><span style=3D"font-family=
:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><span style=
=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B">SELECT COUNT(*) FRO=
M datavalue where periodid =3D&nbsp=3B</span><span style=3D"font-family:ari=
al=2Csans-serif=3Bfont-size:13px=3B">1589=3B&nbsp=3B</span></div><div><span=
style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></=

<div><span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B"=
If you have any records with this periodID=2C you are going to need to res=

olve these. This period looks to be monthly=2C so if the data is also month=
ly=2C you should be able to reassign it to a monthly period which starts in=
2010-02-01 and ends in 2010-02-28=2C if it is already there. You could try=
"SELECT * FROM period where startdate =3D '2010-02-01'::date=3B" and see i=
f you get multiple periods for Feb 2010. If you have multiple periods for F=
eb 2010=2C then you need to do something like</span></div><div><span style=
=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><d=

<span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B">UPDAT=

E datavalue set periodid =3D ???&nbsp=3B</span></div><div><span style=3D"=
font-family:arial=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><=
span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B">where ???=
? is the periodid of the "real" Feb 2010 period.&nbsp=3B</span></div><div><=
span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B"><br></spa=

</div><div><span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=

=3B">Then you should get rid of the bogus Feb 2010 period&nbsp=3B</span></d=

<div><span style=3D"font-family:arial=2Csans-serif=3Bfont-size:13px=3B">=

<br></span></div><div><span style=3D"font-family:arial=2Csans-serif=3Bfont-=
size:13px=3B">with&nbsp=3B</span></div><div><span style=3D"font-family:aria=
l=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><span style=3D"fo=
nt-family:arial=2Csans-serif=3Bfont-size:13px=3B">DELETE FROM period where =
periodid =3D&nbsp=3B</span><span style=3D"font-family:arial=2Csans-serif=3B=
font-size:13px=3B">1589=3B</span></div><div><span style=3D"font-family:aria=
l=2Csans-serif=3Bfont-size:13px=3B"><br></span></div><div><font face=3D"ari=
al=2C sans-serif">After that=2C you should clear your cache from Data admin=
istration.&nbsp=3B</font></div><div><font face=3D"arial=2C sans-serif"><br>=
</font></div><div><font face=3D"arial=2C sans-serif">Do not try any of this=
on a production database!!!</font></div><div><font face=3D"arial=2C sans-s=
erif"><br></font></div><div><font face=3D"arial=2C sans-serif">Regards=2C</=

</div><div><font face=3D"arial=2C sans-serif">Jason</font></div><div><=

font face=3D"arial=2C sans-serif"><br></font></div></div><div><br><div>On M=
on=2C Sep 15=2C 2014 at 12:14 PM=2C Moemedi Ntunyane <span dir=3D"ltr">&lt=
=3B<a href=3D"mailto:moemedi.ntunyane@hotmail.com" target=3D"_blank">moemed=
i.ntunyane@hotmail.com</a>&gt=3B</span> wrote:<br><blockquote style=3D"bord=
er-left:1px #ccc solid=3Bpadding-left:1ex=3B">=0A=
=0A=
=0A=
<div><div dir=3D"ltr">Hi Jason<br><br>Thanx=2C I just run the query you gav=
e and no records were available and removed not =3D year(!=3Dyear) and reco=
rds where available. The date format is available as:<br><br>1573=3B6=3B"20=
10-01-01"=3B"2010-01-31"<br>1589=3B6=3B"2010-02-01"=3B"2010-02-28"<br><br>T=
he error available shows that there is key violation for null values for is=
o column in the periodstructure table.<span><font color=3D"#888888"><br><br=

<br><br><font color=3D"#00B050" face=3D"Courier New"><b><i><font style=3D"=

font-size:8pt=3B" size=3D"1">Moemedi Ntunyane</font><font style=3D"font-siz=
e:8pt=3B" size=3D"1"><br></font><font style=3D"font-size:8pt=3B" size=3D"1"=

Co-founder KeyInConsulting</font></i></b></font><br></font></span><div><br=
<br></div><div><div><hr>Date: Mon=2C 15 Sep 2014 11:46:07 +0200<br>Subject=

: Re: [Dhis2-devs] periodstructure giving errors...<br>From: <a href=3D"mai=
lto:jason.p.pickering@gmail.com" target=3D"_blank">jason.p.pickering@gmail.=
com</a><br>To: <a href=3D"mailto:moemedi.ntunyane@hotmail.com" target=3D"_b=
lank">moemedi.ntunyane@hotmail.com</a><br>CC: <a href=3D"mailto:dhis2-devs@=
lists.launchpad.net" target=3D"_blank">dhis2-devs@lists.launchpad.net</a></=

<div><div><br><br><div dir=3D"ltr">Very likely you have an invalid peri=

od in the periods table.&nbsp=3B<div><br></div><div>Can you run this and se=
e what happens?</div><div><br></div><div>SELECT * FROM period where age(end=
date=2Cstartdate) !=3D '1 year'::interval and periodtypeid =3D (SELECT peri=
odtypeid from periodtype where name =3D 'Yearly')=3B<br></div><div><br></di=

<div>Also=2C you should get an error which will help you to decipher whic=

h period is not correct =2C something like&nbsp=3B</div><div><br></div><div=

<div>Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key va=

lue violates unique constraint "in_periodstructure_iso"</div><div>&nbsp=3B =
Detail: Key (iso)=3D(1995) already exists.</div></div><div><br></div><div>B=
est regards=2C</div><div>Jason</div><div><br></div></div><div><br><div>On F=
ri=2C Sep 12=2C 2014 at 5:03 PM=2C Moemedi Ntunyane <span dir=3D"ltr">&lt=
=3B<a href=3D"mailto:moemedi.ntunyane@hotmail.com" target=3D"_blank">moemed=
i.ntunyane@hotmail.com</a>&gt=3B</span> wrote:<br><blockquote style=3D"bord=
er-left:1px #ccc solid=3Bpadding-left:1ex=3B">=0A=
=0A=
=0A=
<div><div dir=3D"ltr">Hi All<br><br>I have this problem while generating th=
e resource tables...all other tables are fine except periodstructure...<br>=
<br>Process=0A=
failed: PreparedStatementCallback=3B SQL [insert into _periodstructure =0A=
values (?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?=2C?)]=3B ERROR: null =
value in column "iso" =0A=
violates not-null constraint=3B nested exception is =0A=
org.postgresql.util.PSQLException: ERROR: null value in column "iso" =0A=
violates not-null constraint &nbsp=3B<img src=3D"http&#58=3B//localhost&#58=
=3B8080/dhis2/images/error_small.png"><br><br>Regards=2C<br><br><font color=
=3D"#00B050" face=3D"Courier New"><b><i><font style=3D"font-size:8pt=3B" si=
ze=3D"1">Moemedi Ntunyane</font><font style=3D"font-size:8pt=3B" size=3D"1"=

<br></font><font style=3D"font-size:8pt=3B" size=3D"1">Co-founder KeyInCon=

sulting</font></i></b></font><br> </div></div>=0A=
<br>_______________________________________________<br>=0A=
Mailing list: <a href=3D"https://launchpad.net/~dhis2-devs&quot; target=3D"_blan=
k">https://launchpad.net/~dhis2-devs&lt;/a&gt;&lt;br&gt;=0A=
Post to&nbsp=3B &nbsp=3B &nbsp=3B: <a href=3D"mailto:dhis2-devs@lists.launc=
hpad.net" target=3D"_blank">dhis2-devs@lists.launchpad.net</a><br>=0A=
Unsubscribe : <a href=3D"https://launchpad.net/~dhis2-devs&quot; target=3D"_blan=
k">https://launchpad.net/~dhis2-devs&lt;/a&gt;&lt;br&gt;=0A=
More help&nbsp=3B &nbsp=3B: <a href=3D"https://help.launchpad.net/ListHelp"=
target=3D"_blank">https://help.launchpad.net/ListHelp&lt;/a&gt;&lt;br&gt;=0A=
<br></blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div dir=
=3D"ltr">Jason P. Pickering<br>email: <a href=3D"mailto:jason.p.pickering@g=
mail.com" target=3D"_blank">jason.p.pickering@gmail.com</a><br>tel:<a targe=
t=3D"_blank">+46764147049</a></div>=0A=
</div></div></div></div> </div></div>=0A=
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div dir=3D"=
ltr">Jason P. Pickering<br>email: <a href=3D"mailto:jason.p.pickering@gmail=
.com" target=3D"_blank">jason.p.pickering@gmail.com</a><br>tel:<a target=3D=
"_blank">+46764147049</a></div>=0A=
</div></div></div></div> </div></div>=0A=
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div dir=3D"=
ltr">Jason P. Pickering<br>email: <a href=3D"mailto:jason.p.pickering@gmail=
.com" target=3D"_blank">jason.p.pickering@gmail.com</a><br>tel:<a target=3D=
"_blank">+46764147049</a></div>=0A=
</div>=0A=
</div></div><br>_______________________________________________<br>=0A=
Mailing list: <a href=3D"https://launchpad.net/~dhis2-devs&quot; target=3D"_blan=
k">https://launchpad.net/~dhis2-devs&lt;/a&gt;&lt;br&gt;=0A=
Post to&nbsp=3B &nbsp=3B &nbsp=3B: <a href=3D"mailto:dhis2-devs@lists.launc=
hpad.net" target=3D"_blank">dhis2-devs@lists.launchpad.net</a><br>=0A=
Unsubscribe : <a href=3D"https://launchpad.net/~dhis2-devs&quot; target=3D"_blan=
k">https://launchpad.net/~dhis2-devs&lt;/a&gt;&lt;br&gt;=0A=
More help&nbsp=3B &nbsp=3B: <a href=3D"https://help.launchpad.net/ListHelp"=
target=3D"_blank">https://help.launchpad.net/ListHelp&lt;/a&gt;&lt;br&gt;=0A=
<br></blockquote></div><br></div></div> </div></div></div></div>=
</div> </div></div>=0A=
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div dir=3D"=
ltr">Jason P. Pickering<br>email: <a href=3D"mailto:jason.p.pickering@gmail=
.com" target=3D"_blank">jason.p.pickering@gmail.com</a><br>tel:+46764147049=
</div>=0A=
</div></div> </div></body>
</html>=

--_470fab50-8959-4c44-aa07-f573ede44425_--