dhis2-devs team mailing list archive

[Lars Helge Øverland <lars@xxxxxxxxx>]

Hi Pooben,

seems like you have some strange date time values in your db. See the end
of error trace. Search for values ala "0000-00-00" in the
trackedentityattributevalue and trackedentitytdatavalue tables and remove
them.

We will see if we can improve validation for such values.


best regards,

Lars


On Mon, Oct 10, 2016 at 12:20 PM, Pooben Dass <pooben@xxxxxxxx> wrote:

> Hi Devs
>
> Please can anyone assist in pointing out how to correct this issue for
> this exception:-
> I'm using dhis2 v2.24
>
> Caused by: org.springframework.dao.DataIntegrityViolationException:
> StatementCallback;
>
> SQL [insert into analytics_event_temp_2015_cskmsvrprny
> ("uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","
> BmSB4vqoDfI","CkIy9PyAAsM","nQoWiIYO3nt","YJ4TBXUBLJX","
> b3684LlRn6L","VOBfE8SbIe7","daily","weekly","monthly","
> bimonthly","quarterly","sixmonthly","sixmonthlyapril",
> "yearly","financialapril","financialjuly","financialoct",
> "ogAxuZEqzHR","c9LQl5pVwob","CTmpC9T66Xg","MJMYGf6bfAp","
> Ci27lSXGyg9","u0BSkO3zyqQ","iFNfc5mrICu","dAdvW4PqkJy","
> m4tZgSoLhOO","pCG3mE6Rd4D","u8qaP9AqGL5","DtvOF0NVDEX","
> Md18Mikm8qH","XgmLClvKywQ","LzoSKcgnUsk","RQtHZcQHvEC","
> LGO7BKcpUcB","yjZgL8A9ZFa","I78Id2CEGj9","MxxPNA4C2xZ","
> AMwD6ZTkNYJ","psi","pi","ps","enrollmentdate","incidentdate"
> ,"executiondate","duedate","completeddate","longitude","
> latitude","ou","ouname","oucode","tei")select ous."uidlevel1",ous."
> uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."
> uidlevel5",ous."uidlevel6",ougs."BmSB4vqoDfI",ougs."CkIy9PyAAsM",ougs."
> nQoWiIYO3nt",ougs."YJ4TBXUBLJX",ougs."b3684LlRn6L",ougs."
> VOBfE8SbIe7",dps."daily",dps."weekly",dps."monthly",dps."
> bimonthly",dps."quarterly",dps."sixmonthly",dps."
> sixmonthlyapril",dps."yearly",dps."financialapril",dps."
> financialjuly",dps."financialoct",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=56644) as "ogAxuZEqzHR",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7274859) as "c9LQl5pVwob",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7274933) as "CTmpC9T66Xg",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=56647) as "MJMYGf6bfAp",(select cast(value as timestamp)
> from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7872268 and value ~* '^\d{4}-\d{2}-\d{2}(\s|T)?(\d{2}:\d{2}:\d{2})?$')
> as "Ci27lSXGyg9",(select value from trackedentitydatavalue where
> programstageinstanceid=psi.programstageinstanceid and
> dataelementid=56645) as "u0BSkO3zyqQ",(select cast(value as double
> precision) from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=56643 and value ~* '^(-?[0-9]+)(\.[0-9]+)?$') as
> "iFNfc5mrICu",(select value from trackedentitydatavalue where
> programstageinstanceid=psi.programstageinstanceid and
> dataelementid=10252626) as "dAdvW4PqkJy",(
> select cast(value as double precision) from trackedentitydatavalue where
> programstageinstanceid=psi.programstageinstanceid and
> dataelementid=7872272 and value ~* '^(-?[0-9]+)(\.[0-9]+)?$') as
> "m4tZgSoLhOO",(
> select value from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7872276) as "pCG3mE6Rd4D",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=56648) as "u8qaP9AqGL5",(select cast(value as timestamp)
> from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7272791 and value ~* '^\d{4}-\d{2}-\d{2}(\s|T)?(\d{2}:\d{2}:\d{2})?$')
> as "DtvOF0NVDEX",(select value from trackedentitydatavalue where
> programstageinstanceid=psi.programstageinstanceid and
> dataelementid=10252633) as "Md18Mikm8qH",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7274887) as "XgmLClvKywQ",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7274875) as "LzoSKcgnUsk",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7872267) as "RQtHZcQHvEC",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7872278) as "LGO7BKcpUcB",(select value from
> trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7274906) as "yjZgL8A9ZFa",(select cast(value as
> timestamp) from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and dataelementid=7272801 and value ~* '^\d{4}-\d{2}-\d{2}(\s|T)?(\d{2}:\d{2}:\d{2})?$')
> as "I78Id2CEGj9",(
> select value from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid
> and
> dataelementid=56646) as "MxxPNA4C2xZ",(select value from
> trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid
> and trackedentityattributeid=56635) as "AMwD6ZTkNYJ",psi.uid,pi.uid,
> ps.uid,pi.enrollmentdate,pi.incidentdate,psi.
> executiondate,psi.duedate,psi.completeddate,psi.longitude,
> psi.latitude,ou.uid,ou.name,ou.code,tei.uid from programstageinstance psi
> inner join programinstance pi on psi.programinstanceid=pi.programinstanceid
> inner join programstage ps on psi.programstageid=ps.programstageid inner
> join program pr on pi.programid=pr.programid left join
> trackedentityinstance tei on pi.trackedentityinstanceid=tei.trackedentityinstanceid
> inner join organisationunit ou on psi.organisationunitid=ou.organisationunitid
> left join _orgunitstructure ous on psi.organisationunitid=ous.organisationunitid
> left join _organisationunitgroupsetstructure ougs on
> psi.organisationunitid=ougs.organisationunitid left join
> _categorystructure acs on psi.attributeoptioncomboid=acs.categoryoptioncomboid
> left join _dateperiodstructure dps on cast(psi.executiondate as
> date)=dps.dateperiod where psi.executiondate >= '2015-01-01' and
> psi.executiondate <= '2015-12-31' and pr.programid=47884 and
> psi.organisationunitid is not null and psi.executiondate is not null];
> ERROR: date/time field value out of range: "0000-00-00"; nested exception
> is org.postgresql.util.PSQLException: ERROR: date/time field value out of
> range: "0000-00-00"
>         at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator
> .doTranslate(SQLStateSQLExceptionTranslator.java:102)
>         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTr
> anslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
>
>
> Kind Regards
> Pooben
>
> *This message and any attachments are subject to a disclaimer published at
> http://www.hisp.org/policies.html#comms_disclaimer
> <http://www.hisp.org/policies.html#comms_disclaimer>.  Please read the
> disclaimer before opening any attachment or taking any other action in
> terms of this electronic transmission.  If you cannot access the
> disclaimer, kindly send an email to disclaimer@xxxxxxxx
> <disclaimer@xxxxxxxx> and a copy will be provided to you. By replying to
> this e-mail or opening any attachment you agree to be bound by the
> provisions of the disclaimer.*
>
> _______________________________________________
> 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
>
>


-- 
Lars Helge Øverland
Lead developer, DHIS 2
University of Oslo
Skype: larshelgeoverland
lars@xxxxxxxxx
http://www.dhis2.org <https://www.dhis2.org/>