Hi,
I’m trying to upgrade a MySQL database to 2.18. However, something goes wrong.
When I inspect the response from "getDataValues.action…" it is "an exception
occured", and in the tomcat log there is an error (copied below).
The problem seems to be this specifically:
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
'cocco.categoryoptioncomboid = 1 ) as a'
Does anyone have an advise on what could be the problem? I’m finding it
difficult to decipher the SQL..
Olav
* ERROR 2015-04-22 11:11:08,274 Error while executing action
(ExceptionInterceptor.java [http-bio-8888-exec-9])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL
grammar [select a.categoryoptioncomboid, a.organisationunitid, (select
min(coalesce(dal.level, 0)) from period p left join dataapproval da on
da.datasetid in (1) and da.periodid = p.periodid and da.attributeoptioncomboid
= a.categoryoptioncomboid and da.organisationunitid = a.organisationunitid left
join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid
where p.periodid in (25) ) as highest_approved_level, (select
substring(min(concat(100000 + coalesce(dal.level, 0), coalesce(da.accepted,
FALSE))) from 7) from period p left join dataapproval da on da.datasetid in (1)
and da.periodid = p.periodid and da.attributeoptioncomboid =
a.categoryoptioncomboid and da.organisationunitid = a.organisationunitid left
join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid
where p.periodid in (25) ) as accepted_at_highest_level, true as ready_below,
false as approved_above from ( select distinct cocco.categoryoptioncomboid,
ccoc.categorycomboid, coalesce(coo.organisationunitid, o.organisationunitid) as
organisationunitid from categoryoptioncombos_categoryoptions cocco join
categorycombos_optioncombos ccoc on ccoc.categoryoptioncomboid =
cocco.categoryoptioncomboid and ccoc.categorycomboid in (1) join
dataelementcategoryoption co on co.categoryoptionid = cocco.categoryoptionid
and (co.startdate is null or co.startdate <= '2014-12-31') and (co.enddate is
null or co.enddate >= '2014-01-01') join _orgunitstructure o on
o.organisationunitid = 117 left join categoryoption_organisationunits coo on
coo.categoryoptionid = co.categoryoptionid left join _orgunitstructure ous on
ous.idlevel3 = o.organisationunitid and ous.organisationunitid =
coo.organisationunitid left join _orgunitstructure o1 on o1.organisationunitid
= o.organisationunitid and o1.idlevel1 = coo.organisationunitid left join
_orgunitstructure o2 on o2.organisationunitid = o.organisationunitid and
o2.idlevel2 = coo.organisationunitid left join
dataelementcategoryoptionusergroupaccesses couga on couga.categoryoptionid =
cocco.categoryoptionid left join usergroupaccess uga on uga.usergroupaccessid =
couga.usergroupaccessid left join usergroupmembers ugm on ugm.usergroupid =
uga.usergroupid where ( coo.categoryoptionid is null or ous.organisationunitid
is not null or o1.organisationunitid is not null or o2.organisationunitid is
not null ) and cocco.categoryoptioncomboid = 1and cocco.categoryoptioncomboid
= 1 ) as a]; 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 'cocco.categoryoptioncomboid = 1 ) as a' at
line 1
at
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
at
org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:471)
at
org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:526)
at
org.hisp.dhis.dataapproval.hibernate.HibernateDataApprovalStore.getDataApprovals(HibernateDataApprovalStore.java:385)
at
org.hisp.dhis.dataapproval.DefaultDataApprovalService.getDataApprovalStatus(DefaultDataApprovalService.java:356)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy68.getDataApprovalStatus(Unknown Source)
at
org.hisp.dhis.dataset.DefaultDataSetService.isLocked(DefaultDataSetService.java:484)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy66.isLocked(Unknown Source)
at
org.hisp.dhis.de.action.GetDataValuesForDataSetAction.execute(GetDataValuesForDataSetAction.java:274)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at
com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:450)
at
com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:289)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:252)
at
org.hisp.dhis.webportal.interceptor.XWorkPortalUserInterceptor.intercept(XWorkPortalUserInterceptor.java:91)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.webportal.interceptor.XWorkPortalModuleInterceptor.intercept(XWorkPortalModuleInterceptor.java:92)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.webportal.interceptor.XWorkPortalMenuInterceptor.intercept(XWorkPortalMenuInterceptor.java:100)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.webportal.interceptor.XWorkPortalParamsInterceptor.intercept(XWorkPortalParamsInterceptor.java:150)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.interceptor.ContextInterceptor.intercept(ContextInterceptor.java:83)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.interceptor.UserSettingInterceptor.intercept(UserSettingInterceptor.java:84)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.security.intercept.LoginInterceptor.intercept(LoginInterceptor.java:85)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.security.intercept.XWorkSecurityInterceptor.intercept(XWorkSecurityInterceptor.java:116)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.interceptor.SystemSettingInterceptor.intercept(SystemSettingInterceptor.java:137)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:139)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:254)
at
com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:191)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.hisp.dhis.interceptor.ExceptionInterceptor.intercept(ExceptionInterceptor.java:113)
at
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at
org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54)
at
org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:562)
at
org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
at
org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:99)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.hisp.dhis.security.filter.CorsFilter.doFilter(CorsFilter.java:93)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.hisp.dhis.security.filter.CustomAuthenticationFilter.doFilter(CustomAuthenticationFilter.java:64)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.hisp.dhis.security.filter.AutomaticAccessFilter.doFilter(AutomaticAccessFilter.java:115)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:57)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
at
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
at
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)
at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at
org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: 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 'cocco.categoryoptioncomboid =
1 ) as a' at line_______________________________________________
Mailing list: https://launchpad.net/~dhis2-users
Post to : [email protected]
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
