** Changed in: dhis2
Milestone: 2.16 => 2.18
--
You received this bug notification because you are a member of DHIS 2
developers, which is subscribed to DHIS.
https://bugs.launchpad.net/bugs/1332829
Title:
Approval analytics fails on H2
Status in DHIS 2:
New
Bug description:
* ERROR 2014-05-16 14:59:55,504 Unexpected error occurred in scheduled task.
(TaskUtils.java [taskScheduler-1])
java.lang.RuntimeException: Exception during execution
at
org.hisp.dhis.system.util.ConcurrentUtils.waitForCompletion(ConcurrentUtils.java:55)
at
org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.populateTables(DefaultAnalyticsTableService.java:204)
at
org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.update(DefaultAnalyticsTableService.java:117)
at
org.hisp.dhis.analytics.scheduling.AnalyticsTableTask.run(AnalyticsTableTask.java:124)
at org.hisp.dhis.scheduling.ScheduledTasks.run(ScheduledTasks.java:60)
at
org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53)
at
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:178)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:292)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:724)
Caused by: java.util.concurrent.ExecutionException:
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL
grammar [insert into analytics_temp_2014
("uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","daily","weekly","monthly","bimonthly","quarterly","sixmonthly","sixmonthlyapril","yearly","financialapril","financialjuly","financialoct","de","co","level","approvallevel",daysxvalue,
daysno, value) select
ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."uidlevel5",ous."uidlevel6",ps."daily",ps."weekly",ps."monthly",ps."bimonthly",ps."quarterly",ps."sixmonthly",ps."sixmonthlyapril",ps."yearly",ps."financialapril",ps."financialjuly",ps."financialoct",de.uid,co.uid,ous.level,(select
coalesce(min(dal.level),999) from dataapproval da inner join dataapprovallevel
dal on da.dataapprovallevelid = dal.dataapprovallevelid inner join
_dataelementstructure des on da.datasetid = des.datasetid and des.dataelementid
= dv.dataelementid wh
ere da.periodid = dv.periodid and (ous.idlevel1 = da.organisationunitid or
ous.idlevel2 = da.organisationunitid or ous.idlevel3 = da.organisationunitid or
ous.idlevel4 = da.organisationunitid or ous.idlevel5 = da.organisationunitid or
ous.idlevel6 = da.organisationunitid ) ) as approvallevel,cast(dv.value as
double) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as
double) as value from datavalue dv left join _dataelementgroupsetstructure degs
on dv.dataelementid=degs.dataelementid left join
_organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid
left join _categoryoptiongroupsetstructure cogs on
dv.attributeoptioncomboid=cogs.categoryoptioncomboid left join
_categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid
left join _categorystructure acs on
dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure
ous on dv.sourceid=ous.organisationunitid left join _periodstructure ps on
dv.periodid=ps.periodid
left join dataelement de on dv.dataelementid=de.dataelementid left join
categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid
left join period pe on dv.periodid=pe.periodid where de.valuetype = 'int' and
de.domaintype = 'aggregate' and pe.startdate >= '2014-01-01' and pe.startdate
<= '2014-12-31' and dv.value is not null and dv.value regexp
'^(-?[0-9]+)(\.[0-9]+)?(E\d+)?$' and ( dv.value != '0' or de.aggregationtype =
'average' or de.zeroissignificant = true ) ]; nested exception is
org.h2.jdbc.JdbcSQLException: Column "OUS.IDLEVEL1" not found; SQL statement:
insert into analytics_temp_2014
("uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","daily","weekly","monthly","bimonthly","quarterly","sixmonthly","sixmonthlyapril","yearly","financialapril","financialjuly","financialoct","de","co","level","approvallevel",daysxvalue,
daysno, value) select
ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."uidlevel5",ous."uidlevel6",ps."daily",ps."weekly",ps."monthly",ps."bimonthly",ps."quarterly",ps."sixmonthly",ps."sixmonthlyapril",ps."yearly",ps."financialapril",ps."financialjuly",ps."financialoct",de.uid,co.uid,ous.level,(select
coalesce(min(dal.level),999) from dataapproval da inner join dataapprovallevel
dal on da.dataapprovallevelid = dal.dataapprovallevelid inner join
_dataelementstructure des on da.datasetid = des.datasetid and des.dataelementid
= dv.dataelementid where da.periodid = dv.periodid and (ous.idlevel1 =
da.organisationunitid or ous.idlevel2 = da.organisationunitid or ous.idlevel3 =
da.orga
nisationunitid or ous.idlevel4 = da.organisationunitid or ous.idlevel5 =
da.organisationunitid or ous.idlevel6 = da.organisationunitid ) ) as
approvallevel,cast(dv.value as double) * ps.daysno as daysxvalue, ps.daysno as
daysno, cast(dv.value as double) as value from datavalue dv left join
_dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left
join _organisationunitgroupsetstructure ougs on
dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure
cogs on dv.attributeoptioncomboid=cogs.categoryoptioncomboid left join
_categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid
left join _categorystructure acs on
dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure
ous on dv.sourceid=ous.organisationunitid left join _periodstructure ps on
dv.periodid=ps.periodid left join dataelement de on
dv.dataelementid=de.dataelementid left join categoryoptioncombo co on
dv.categoryoptioncomboid=co.categoryopt
ioncomboid left join period pe on dv.periodid=pe.periodid where de.valuetype =
'int' and de.domaintype = 'aggregate' and pe.startdate >= '2014-01-01' and
pe.startdate <= '2014-12-31' and dv.value is not null and dv.value regexp
'^(-?[0-9]+)(\.[0-9]+)?(E\d+)?$' and ( dv.value != '0' or de.aggregationtype =
'average' or de.zeroissignificant = true ) [42122-173]
at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:252)
at java.util.concurrent.FutureTask.get(FutureTask.java:111)
at
org.hisp.dhis.system.util.ConcurrentUtils.waitForCompletion(ConcurrentUtils.java:51)
... 13 more
Caused by: org.springframework.jdbc.BadSqlGrammarException:
StatementCallback; bad SQL grammar [insert into analytics_temp_2014
("uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","daily","weekly","monthly","bimonthly","quarterly","sixmonthly","sixmonthlyapril","yearly","financialapril","financialjuly","financialoct","de","co","level","approvallevel",daysxvalue,
daysno, value) select
ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."uidlevel5",ous."uidlevel6",ps."daily",ps."weekly",ps."monthly",ps."bimonthly",ps."quarterly",ps."sixmonthly",ps."sixmonthlyapril",ps."yearly",ps."financialapril",ps."financialjuly",ps."financialoct",de.uid,co.uid,ous.level,(select
coalesce(min(dal.level),999) from dataapproval da inner join dataapprovallevel
dal on da.dataapprovallevelid = dal.dataapprovallevelid inner join
_dataelementstructure des on da.datasetid = des.datasetid and des.dataelementid
= dv.dataelementid where da.periodid = dv.periodid and (ous.id
level1 = da.organisationunitid or ous.idlevel2 = da.organisationunitid or
ous.idlevel3 = da.organisationunitid or ous.idlevel4 = da.organisationunitid or
ous.idlevel5 = da.organisationunitid or ous.idlevel6 = da.organisationunitid )
) as approvallevel,cast(dv.value as double) * ps.daysno as daysxvalue,
ps.daysno as daysno, cast(dv.value as double) as value from datavalue dv left
join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid
left join _organisationunitgroupsetstructure ougs on
dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure
cogs on dv.attributeoptioncomboid=cogs.categoryoptioncomboid left join
_categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid
left join _categorystructure acs on
dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure
ous on dv.sourceid=ous.organisationunitid left join _periodstructure ps on
dv.periodid=ps.periodid left join dataelement de on dv.dataelemen
tid=de.dataelementid left join categoryoptioncombo co on
dv.categoryoptioncomboid=co.categoryoptioncomboid left join period pe on
dv.periodid=pe.periodid where de.valuetype = 'int' and de.domaintype =
'aggregate' and pe.startdate >= '2014-01-01' and pe.startdate <= '2014-12-31'
and dv.value is not null and dv.value regexp '^(-?[0-9]+)(\.[0-9]+)?(E\d+)?$'
and ( dv.value != '0' or de.aggregationtype = 'average' or de.zeroissignificant
= true ) ]; nested exception is org.h2.jdbc.JdbcSQLException: Column
"OUS.IDLEVEL1" not found; SQL statement:
insert into analytics_temp_2014
("uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","daily","weekly","monthly","bimonthly","quarterly","sixmonthly","sixmonthlyapril","yearly","financialapril","financialjuly","financialoct","de","co","level","approvallevel",daysxvalue,
daysno, value) select
ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."uidlevel5",ous."uidlevel6",ps."daily",ps."weekly",ps."monthly",ps."bimonthly",ps."quarterly",ps."sixmonthly",ps."sixmonthlyapril",ps."yearly",ps."financialapril",ps."financialjuly",ps."financialoct",de.uid,co.uid,ous.level,(select
coalesce(min(dal.level),999) from dataapproval da inner join dataapprovallevel
dal on da.dataapprovallevelid = dal.dataapprovallevelid inner join
_dataelementstructure des on da.datasetid = des.datasetid and des.dataelementid
= dv.dataelementid where da.periodid = dv.periodid and (ous.idlevel1 =
da.organisationunitid or ous.idlevel2 = da.organisationunitid or ous.idlevel3 =
da.orga
nisationunitid or ous.idlevel4 = da.organisationunitid or ous.idlevel5 =
da.organisationunitid or ous.idlevel6 = da.organisationunitid ) ) as
approvallevel,cast(dv.value as double) * ps.daysno as daysxvalue, ps.daysno as
daysno, cast(dv.value as double) as value from datavalue dv left join
_dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left
join _organisationunitgroupsetstructure ougs on
dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure
cogs on dv.attributeoptioncomboid=cogs.categoryoptioncomboid left join
_categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid
left join _categorystructure acs on
dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure
ous on dv.sourceid=ous.organisationunitid left join _periodstructure ps on
dv.periodid=ps.periodid left join dataelement de on
dv.dataelementid=de.dataelementid left join categoryoptioncombo co on
dv.categoryoptioncomboid=co.categoryopt
ioncomboid left join period pe on dv.periodid=pe.periodid where de.valuetype =
'int' and de.domaintype = 'aggregate' and pe.startdate >= '2014-01-01' and
pe.startdate <= '2014-12-31' and dv.value is not null and dv.value regexp
'^(-?[0-9]+)(\.[0-9]+)?(E\d+)?$' and ( dv.value != '0' or de.aggregationtype =
'average' or de.zeroissignificant = true ) [42122-173]
at
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
at
org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTable(JdbcAnalyticsTableManager.java:190)
at
org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTableAsync(JdbcAnalyticsTableManager.java:133)
at sun.reflect.GeneratedMethodAccessor666.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at
org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:95)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
... 1 more
Caused by: org.h2.jdbc.JdbcSQLException: Column "OUS.IDLEVEL1" not found; SQL
statement:
insert into analytics_temp_2014
("uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","daily","weekly","monthly","bimonthly","quarterly","sixmonthly","sixmonthlyapril","yearly","financialapril","financialjuly","financialoct","de","co","level","approvallevel",daysxvalue,
daysno, value) select
ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."uidlevel5",ous."uidlevel6",ps."daily",ps."weekly",ps."monthly",ps."bimonthly",ps."quarterly",ps."sixmonthly",ps."sixmonthlyapril",ps."yearly",ps."financialapril",ps."financialjuly",ps."financialoct",de.uid,co.uid,ous.level,(select
coalesce(min(dal.level),999) from dataapproval da inner join dataapprovallevel
dal on da.dataapprovallevelid = dal.dataapprovallevelid inner join
_dataelementstructure des on da.datasetid = des.datasetid and des.dataelementid
= dv.dataelementid where da.periodid = dv.periodid and (ous.idlevel1 =
da.organisationunitid or ous.idlevel2 = da.organisationunitid or ous.idlevel3 =
da.orga
nisationunitid or ous.idlevel4 = da.organisationunitid or ous.idlevel5 =
da.organisationunitid or ous.idlevel6 = da.organisationunitid ) ) as
approvallevel,cast(dv.value as double) * ps.daysno as daysxvalue, ps.daysno as
daysno, cast(dv.value as double) as value from datavalue dv left join
_dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left
join _organisationunitgroupsetstructure ougs on
dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure
cogs on dv.attributeoptioncomboid=cogs.categoryoptioncomboid left join
_categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid
left join _categorystructure acs on
dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure
ous on dv.sourceid=ous.organisationunitid left join _periodstructure ps on
dv.periodid=ps.periodid left join dataelement de on
dv.dataelementid=de.dataelementid left join categoryoptioncombo co on
dv.categoryoptioncomboid=co.categoryopt
ioncomboid left join period pe on dv.periodid=pe.periodid where de.valuetype =
'int' and de.domaintype = 'aggregate' and pe.startdate >= '2014-01-01' and
pe.startdate <= '2014-12-31' and dv.value is not null and dv.value regexp
'^(-?[0-9]+)(\.[0-9]+)?(E\d+)?$' and ( dv.value != '0' or de.aggregationtype =
'average' or de.zeroissignificant = true ) [42122-173]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)
at org.h2.message.DbException.get(DbException.java:171)
at org.h2.message.DbException.get(DbException.java:148)
at
org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:144)
at org.h2.expression.Comparison.optimize(Comparison.java:171)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:132)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
at org.h2.command.dml.Select.prepare(Select.java:811)
at org.h2.expression.Subquery.optimize(Subquery.java:73)
at org.h2.expression.Alias.optimize(Alias.java:52)
at org.h2.command.dml.Select.prepare(Select.java:808)
at org.h2.command.dml.Insert.prepare(Insert.java:248)
at org.h2.command.Parser.prepareCommand(Parser.java:219)
at org.h2.engine.Session.prepareLocal(Session.java:428)
at org.h2.engine.Session.prepareCommand(Session.java:377)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:168)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156)
at
com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
at
org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:421)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
... 13 more
To manage notifications about this bug go to:
https://bugs.launchpad.net/dhis2/+bug/1332829/+subscriptions
_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : [email protected]
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
