Approval analytics fails on H2

Bug #1332829 reported by Lars Helge Øverland on 2014-06-21
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
DHIS
Low
Lars Helge Øverland

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 where 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.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 ) [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.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.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 ) [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.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 ) [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

Changed in dhis2:
milestone: none → 2.16
assignee: nobody → Lars Helge Øverland (larshelge)
importance: Undecided → Low
Changed in dhis2:
milestone: 2.16 → 2.18
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers