dhis2-users team mailing list archive

[Knut Staring <knutst@xxxxxxxxx>]

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


On Fri, Dec 11, 2015 at 9:07 AM, Aldren Gonzales <aldrengonzales@xxxxxxxxx>
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:
>
> [image: 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.
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> 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