dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

** 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 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

To manage notifications about this bug go to:
https://bugs.launchpad.net/dhis2/+bug/1332829/+subscriptions