Error: "Process failed: Exception during execution"

Hi Everyone,

Our team is currently exploring DHIS2 for our project here in the Philippines. I have installed DHIS2 with MySql database on localhost and worked fine (entering org units, data elements, and forms). However, running export on analytics, I got this error:

Inline image 1

I have attached the log file for your reference. I hope someone could help me on this.

I am using DHIS2 version 2.20 on Tomcat on a Mac.

Cheers,

Aldren Gonzales

** **** **Please consider the environment before printing this e-mail.

IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential, privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humour or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorised (either explicitly or implicitly) and constitutes an irritating social faux pas.

catalina.out (48 KB)

Hi Aldren,

First of all, would you consider using Postgresql rather than Mysql? The developers do a lot more testing on Postgres.

That being said, we DO want to support Mysql also, and from your log, it seems the problem is here:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘timestamp) from trackedentitydatavalue where programstageinstanceid=psi.programs’ at line 1

Here is the full offending statement:

Caused by: java.util.concurrent.ExecutionException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [insert into analytics_event_temp_2015_stxg2m0jtrp (uidlevel1,uidlevel2,uidlevel3,uidlevel4,uidlevel5,daily,weekly,monthly,bimonthly,quarterly,sixmonthly,sixmonthlyapril,yearly,financialapril,financialjuly,financialoct,u38IQzVacuQ,nfoSRgCoQf0,TDocdhSALAo,GCBFAqAXfyR,tKEmyncMbdH,PE72dJJkeoZ,DETRuCV0gZ7,Ow6cIP4Rajx,R1wzxDn7TmT,W2r66LhC8Nj,mwwwMcdHTBO,HAsTCOfemQo,pBLSqYGpCUR,duvWupMn4k4,ODBa6DfdkEF,B4tK2H8nM76,KalPl9q9SBm,AKtdPzlQhtz,DDoEMctbtsS,sn1w2IWgK71,dlKbwzTIlbW,FxfH9HS7Gvr,jaXzyvZK6X5,Vf0huEgYhUb,tYEI34EUpoL,nBF4I8eU1ij,r0jGfc7gM9G,nPkRyH2AOTX,apeWaeOvw5i,EovKdIkx0vh,hQ64l82cGR1,psi,pi,ps,executiondate,longitude,latitude,ou,ouname,oucode,tei) select ous.uidlevel1,ous.uidlevel2,ous.uidlevel3,ous.uidlevel4,ous.uidlevel5,dps.daily,dps.weekly,dps.monthly,dps.bimonthly,dps.quarterly,dps.sixmonthly,dps.sixmonthlyapril,dps.yearly,dps.financialapril,dps.financialjuly,dps.financialoct,(select cast(value as decimal(26,1)) from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=25 and value regexp ‘^(-?[0-9]+)(.[0-9]+)?(E(-)?\d+)?$’) as u38IQzVacuQ,(select value from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=15) as nfoSRgCoQf0,(select value from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=23) as TDocdhSALAo,(select value from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=18) as GCBFAqAXfyR,(select value from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=19) as tKEmyncMbdH,(select value from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=13) as PE72dJJkeoZ,(select cast(value as decimal(26,1)) from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=12 and value regexp ‘^(-?[0-9]+)(.[0-9]+)?(E(-)?\d+)?$’) as DETRuCV0gZ7,(select value from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=26) as Ow6cIP4Rajx,(select cast(value as timestamp) from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=24 and value regexp ‘^\d{4}-\d{2}-\d{2}(\s|T)?(\d{2}:\d{2}:\d{2})?$’) as R1wzxDn7TmT,(select value from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=21) as W2r66LhC8Nj,(select case when value = ‘true’ then 1 when value = ‘false’ then 0 else null end from trackedentitydatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=22) as mwwwMcdHTBO,(select cast(value as timestamp) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1 and value regexp ‘^\d{4}-\d{2}-\d{2}(\s|T)?(\d{2}:\d{2}:\d{2})?$’) as HAsTCOfemQo,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=5) as pBLSqYGpCUR,(select cast(value as timestamp) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=10 and value regexp ‘^\d{4}-\d{2}-\d{2}(\s|T)?(\d{2}:\d{2}:\d{2})?$’) as duvWupMn4k4,(select cast(value as decimal(26,1)) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=19 and value regexp ‘^(-?[0-9]+)(.[0-9]+)?(E(-)?\d+)?$’) as ODBa6DfdkEF,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=8) as B4tK2H8nM76,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=11) as KalPl9q9SBm,(select cast(value as timestamp) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=18 and value regexp ‘^\d{4}-\d{2}-\d{2}(\s|T)?(\d{2}:\d{2}:\d{2})?$’) as AKtdPzlQhtz,(select case when value = ‘true’ then 1 when value = ‘false’ then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=16) as DDoEMctbtsS,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=15) as sn1w2IWgK71,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=26) as dlKbwzTIlbW,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=13) as FxfH9HS7Gvr,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=9) as jaXzyvZK6X5,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4) as Vf0huEgYhUb,(select cast(value as decimal(26,1)) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=6 and value regexp ‘^(-?[0-9]+)(.[0-9]+)?(E(-)?\d+)?$’) as tYEI34EUpoL,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=7) as nBF4I8eU1ij,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=12) as r0jGfc7gM9G,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=3) as nPkRyH2AOTX,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=17) as apeWaeOvw5i,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=2) as EovKdIkx0vh,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=14) as hQ64l82cGR1,psi.uid,pi.uid,ps.uid,psi.executiondate,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 _dateperiodstructure dps on psi.executiondate=dps.dateperiod where psi.executiondate >= ‘2015-01-01’ and psi.executiondate <= ‘2015-12-31’ and pr.programid=1 and psi.organisationunitid is not null and psi.executiondate is not null]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘timestamp) from trackedentitydatavalue where programstageinstanceid=psi.programs’ at line 1

image

···

On Fri, Dec 11, 2015 at 9:07 AM, Aldren Gonzales aldrengonzales@gmail.com wrote:

Hi Everyone,

Our team is currently exploring DHIS2 for our project here in the Philippines. I have installed DHIS2 with MySql database on localhost and worked fine (entering org units, data elements, and forms). However, running export on analytics, I got this error:

I have attached the log file for your reference. I hope someone could help me on this.

I am using DHIS2 version 2.20 on Tomcat on a Mac.

Cheers,

Aldren Gonzales

** **** **Please consider the environment before printing this e-mail.

IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential, privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humour or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorised (either explicitly or implicitly) and constitutes an irritating social faux pas.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Hi Aldren,

I have done some fixes for mysql-related issues in trunk and 2.21 now. A new build will be available in 20 minutes from dhis2.org. Please reinstall and let us know how it goes.

best regards,

Lars

image

···

On Fri, Dec 11, 2015 at 9:07 AM, Aldren Gonzales aldrengonzales@gmail.com wrote:

Hi Everyone,

Our team is currently exploring DHIS2 for our project here in the Philippines. I have installed DHIS2 with MySql database on localhost and worked fine (entering org units, data elements, and forms). However, running export on analytics, I got this error:

I have attached the log file for your reference. I hope someone could help me on this.

I am using DHIS2 version 2.20 on Tomcat on a Mac.

Cheers,

Aldren Gonzales

** **** **Please consider the environment before printing this e-mail.

IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential, privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humour or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorised (either explicitly or implicitly) and constitutes an irritating social faux pas.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org