← Back to team overview

dhis2-devs team mailing list archive

Re: [Bug 1523882] Re: Getting exception when upgraded application from Version 2.16 to 2.21 with Mysql dataase

 

Just to add on, we got the problem while generating resource tables.
On 10-Dec-2015 5:21 pm, "Neeraj Gupta" <neeraj.hisp@xxxxxxxxx> wrote:

> Thanks Lars,
>
> Problem with Program rules is fixed and we ran DHIS 2.21 war file with
> empty MySQL database and found below problem again.
>
> *Version:**2.21**Build revision:**20959**Build date:**2015-12-10 13:44*
>
> * ERROR 2015-12-10 17:15:26,966 Unexpected error occurred in scheduled
> task. (Ta
> skUtils.java [taskScheduler-1])
> org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad
> SQL gram
> mar [CREATE TABLE _dataelementstructure_temp (dataelementid INTEGER NOT
> NULL PRI
> MARY KEY, dataelementuid CHARACTER(11), dataelementname VARCHAR(230),
> datasetid
> INTEGER, datasetuid CHARACTER(11), datasetname VARCHAR(230),
> datasetapprovalleve
> l INTEGER, periodtypeid INTEGER, periodtypename VARCHAR(230))]; nested
> exception
>  is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table
> '_dataeleme
> ntstructure_temp' already exists
>         at
> org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTra
> nslate(SQLExceptionSubclassTranslator.java:91)
>         at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat
> or.translate(AbstractFallbackSQLExceptionTranslator.java:73)
>         at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat
> or.translate(AbstractFallbackSQLExceptionTranslator.java:81)
>         at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
> 416)
>         at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
> 440)
>         at
> org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.generateResou
> rceTable(JdbcResourceTableStore.java:84)
>         at
> org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataE
> lementTable(DefaultResourceTableService.java:198)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
> java:62)
>         at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
> sorImpl.java:43)
>         at java.lang.reflect.Method.invoke(Method.java:483)
>         at
> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflecti
> on(AopUtils.java:317)
>         at
> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJo
> inpoint(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.in
> <http://org.springframework.transaction.interceptor.transactioninterceptor.in/>
> voke(TransactionInterceptor.java:96)
>         at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
> ReflectiveMethodInvocation.java:179)
>         at
> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynami
> cAopProxy.java:207)
>         at com.sun.proxy.$Proxy140.generateDataElementTable(Unknown Source)
>         at
> org.hisp.dhis.resourcetable.scheduling.ResourceTableTask.generateAll(
> ResourceTableTask.java:121)
>         at
> org.hisp.dhis.resourcetable.scheduling.ResourceTableTask.run(Resource
> TableTask.java:84)
>         at
> org.springframework.scheduling.support.DelegatingErrorHandlingRunnabl
> e.run(DelegatingErrorHandlingRunnable.java:54)
>         at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:51
> 1)
>         at java.util.concurrent.FutureTask.run(FutureTask.java:266)
>         at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.
> access$201(ScheduledThreadPoolExecutor.java:180)
>         at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.
> run(ScheduledThreadPoolExecutor.java:293)
>         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:
> Table '_da
> taelementstructure_temp' already exists
>         at sun.reflect.GeneratedConstructorAccessor82.newInstance(Unknown
> Source
> )
>         at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC
> onstructorAccessorImpl.java:45)
>         at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
>         at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
>         at com.mysql.jdbc.Util.getInstance(Util.java:360)
>         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
>         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
>         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
>         at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
>         at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
>         at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
>         at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
>         at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
>         at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
>         at
> com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.
> java:1006)
>         at
> org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.
> doInStatement(JdbcTemplate.java:432)
>         at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
> 405)
>         ... 26 more
>
>
>
> On Wed, Dec 9, 2015 at 5:49 PM, Lars Helge Øverland <larshelge@xxxxxxxxx>
> wrote:
>
>> Neeraj, I think this issue should be fixed when we renamed the
>> programrule > condition column. Can you try again with latest 2.21?
>>
>> On Wed, Dec 9, 2015 at 1:06 PM, Neeraj <neeraj.hisp@xxxxxxxxx> wrote:
>>
>>> Thanks John,
>>>
>>> Will go through these notes and try to update.
>>>
>>> On Tue, Dec 8, 2015 at 9:21 PM, John Francis Mukulu <
>>> 1523882@xxxxxxxxxxxxxxxxxx> wrote:
>>>
>>> > Hellow Neeraj,
>>> > With such a huge leap between version, I recommend, before deploying
>>> 2.21
>>> > on the 2.16 database,  you go through upgrade notes between each
>>> version
>>> > and effect all database changes step wise.
>>> >
>>> >
>>> > i.e.
>>> > >From 2.16 to 2.17 you can visit:
>>> > https://www.dhis2.org/217-upgrade
>>> >
>>> > >From 2.17 to 2.18 and above, the upgrade notes are are available on
>>> > https://www.dhis2.org/downloads
>>> > Goodluck.
>>> >
>>> > --
>>> > You received this bug notification because you are subscribed to the
>>> bug
>>> > report.
>>> > https://bugs.launchpad.net/bugs/1523882
>>> >
>>> > Title:
>>> >   Getting exception when upgraded application from Version 2.16 to 2.21
>>> >   with Mysql dataase
>>> >
>>> > Status in DHIS:
>>> >   New
>>> >
>>> > Bug description:
>>> >   Dear All,
>>> >
>>> >   We recently upgraded DHIS 2.16 to 2.21 where database is Mysql some
>>> of
>>> >   the errors are listed below.(We are using aggregated data not
>>> tracker)
>>> >
>>> >   Basically we are getting 3 errors
>>> >   - Error1: In mysql programrule table is not created automatically
>>> >
>>> >   - Error2: While generating resource tables got the error, think the
>>> >   query should be DROP TABLE IF EXISTS orgunitstructure;  Same applies
>>> >   for all resource tables.
>>> >
>>> >   - Error3: Tried to copy resource table structures from old db and
>>> >   tried generate resource table option, this time got the error:
>>> >
>>> >   The details of errors are given below.
>>> >
>>> >   While upgrading we ran all the sql update scripts.
>>> >
>>> >   Error1:
>>> >   In mysql programrule table is not created automatically. While
>>> starting
>>> > getting the below error:
>>> >
>>> >
>>> >   * ERROR 2015-12-07 17:36:56,864 Problem updating: programrule, id
>>> > column: programruleid (IdentityPopulator.java [localho
>>> >   st-startStop-1])
>>> >   org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
>>> bad
>>> > SQL grammar [SELECT * from programrule WHERE uid
>>> >    IS NULL]; nested exception is
>>> > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table
>>> > 'dhis.programrule'
>>> >    doesn't exist
>>> >           at
>>> >
>>> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTrans
>>> >   lator.java:231)
>>> >           at
>>> >
>>> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptio
>>> >   nTranslator.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.common.IdentityPopulator.execute(IdentityPopulator.java:86)
>>> >           at
>>> >
>>> org.hisp.dhis.system.startup.DefaultStartupRoutineExecutor.execute(DefaultStartupRoutineExecutor.java:117)
>>> >           at
>>> >
>>> org.hisp.dhis.system.startup.DefaultStartupRoutineExecutor.execute(DefaultStartupRoutineExecutor.java:86)
>>> >           at
>>> >
>>> org.hisp.dhis.system.startup.StartupListener.contextInitialized(StartupListener.java:71)
>>> >           at
>>> >
>>> org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4729)
>>> >           at
>>> >
>>> org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5167)
>>> >           at
>>> > org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
>>> >           at
>>> >
>>> org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
>>> >           at
>>> > org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
>>> >           at
>>> > org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
>>> >           at
>>> > org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:945)
>>> >           at
>>> >
>>> org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1768)
>>> >           at
>>> java.util.concurrent.Executors$RunnableAdapter.call(Unknown
>>> > Source)
>>> >           at java.util.concurrent.FutureTask.run(Unknown Source)
>>> >           at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown
>>> > Source)
>>> >           at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown
>>> > Source)
>>> >           at java.lang.Thread.run(Unknown Source)
>>> >   Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
>>> > Table 'dhis.programrule' doesn't exist
>>> >           at
>>> > sun.reflect.GeneratedConstructorAccessor53.newInstance(Unknown Source)
>>> >           at
>>> > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown
>>> Source)
>>> >           at java.lang.reflect.Constructor.newInstance(Unknown Source)
>>> >           at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
>>> >           at com.mysql.jdbc.Util.getInstance(Util.java:360)
>>> >           at
>>> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
>>> >           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
>>> >           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
>>> >           at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
>>> >           at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
>>> >           at
>>> > com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
>>> >           at
>>> > com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
>>> >           at
>>> > com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
>>> >           at
>>> >
>>> com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
>>> >           at
>>> >
>>> org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:455)
>>> >           at
>>> >
>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
>>> >           ... 19 more
>>> >   * INFO  2015-12-07 17:36:57,144 Executing startup routine [6 of 13,
>>> > runlevel 5]: TableCreator (DefaultStartupRoutineExec
>>> >   utor.java [localhost-startStop-1])
>>> >
>>> >
>>> >
>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>> >
>>> >
>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>> >
>>> >   Error2: While generating resource tables got the below error, think
>>> >   the query should be DROP TABLE IF EXISTS _orgunitstructure;  Same
>>> >   applies for all resource tables.
>>> >
>>> >   * INFO  2015-12-07 17:41:06,417 [Level: INFO, category:
>>> > RESOURCETABLE_UPDATE, time: Mon Dec 07 17:41:06 IST 2015, messag
>>> >   e: Generating resource tables] (InMemoryNotifier.java
>>> [taskScheduler-1])
>>> >   * INFO  2015-12-07 17:41:06,465 Create table SQL: create table
>>> > _orgunitstructure_temp (organisationunitid integer not nu
>>> >   ll primary key, organisationunituid character(11), level integer,
>>> > `idlevel1` integer, `uidlevel1` character(11), `idleve
>>> >   l2` integer, `uidlevel2` character(11), `idlevel3` integer,
>>> `uidlevel3`
>>> > character(11)); (JdbcResourceTableStore.java [ta
>>> >   skScheduler-1])
>>> >   * INFO  2015-12-07 17:41:06,495 Populate table content rows: 203
>>> > (JdbcResourceTableStore.java [taskScheduler-1])
>>> >   * INFO  2015-12-07 17:41:06,546 Create index SQL: create unique index
>>> > in_orgunitstructure_organisationunituid_VWU2G on _
>>> >   orgunitstructure_temp(organisationunituid)
>>> (JdbcResourceTableStore.java
>>> > [taskScheduler-1])
>>> >   * INFO  2015-12-07 17:41:06,582 [Level: ERROR, category:
>>> > RESOURCETABLE_UPDATE, time: Mon Dec 07 17:41:06 IST 2015, messa
>>> >   ge: Process failed: StatementCallback; bad SQL grammar [drop table
>>> > _orgunitstructure;]; nested exception is com.mysql.jd
>>> >   bc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table
>>> > 'dhis._orgunitstructure'] (InMemoryNotifier.java [tas
>>> >   kScheduler-1])
>>> >
>>> >
>>> >
>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>> >
>>> >
>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>> >
>>> >   Error3: Tried to copy resource table structures from old db and tried
>>> >   generate resource table option, this time got the below error:
>>> >
>>> >   * INFO  2015-12-07 18:05:19,296 [Level: ERROR, category:
>>> > RESOURCETABLE_UPDATE, time: Mon Dec 07 18:05:19 IST 2015, messa
>>> >   ge: Process failed: StatementCallback; bad SQL grammar [create unique
>>> > index in_dataelementstructure_dataelementuid_M86c6
>>> >    on _dataelementstructure_temp(dataelementuid);create index
>>> > in_dataelementstructure_datasetid_fVMFF on _dataelementstruc
>>> >   ture_temp(datasetid);create index
>>> > in_dataelementstructure_datasetuid_nsdEu on
>>> > _dataelementstructure_temp(datasetuid);cre
>>> >   ate index in_dataelementstructure_periodtypeid_EkjZg on
>>> > _dataelementstructure_temp(periodtypeid);]; nested exception is
>>> >   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have
>>> an
>>> > error in your SQL syntax; check the manual that c
>>> >   orresponds to your MySQL server version for the right syntax to use
>>> near
>>> > 'create index in_dataelementstructure_datasetid
>>> >   _fVMFF on _dataelementstructure_te' at line 1] (InMemoryNotifier.java
>>> > [taskScheduler-5])
>>> >   * ERROR 2015-12-07 18:05:19,319 Unexpected error occurred in
>>> scheduled
>>> > task. (TaskUtils.java [taskScheduler-5])
>>> >   org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
>>> bad
>>> > SQL grammar [create unique index in_dataelements
>>> >   tructure_dataelementuid_M86c6 on
>>> > _dataelementstructure_temp(dataelementuid);create index
>>> > in_dataelementstructure_dataset
>>> >   id_fVMFF on _dataelementstructure_temp(datasetid);create index
>>> > in_dataelementstructure_datasetuid_nsdEu on _dataelements
>>> >   tructure_temp(datasetuid);create index
>>> > in_dataelementstructure_periodtypeid_EkjZg on
>>> > _dataelementstructure_temp(periodty
>>> >   peid);]; nested exception is
>>> > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
>>> > error in your SQL sy
>>> >   ntax; check the manual that corresponds to your MySQL server version
>>> for
>>> > the right syntax to use near 'create index in_d
>>> >   ataelementstructure_datasetid_fVMFF on _dataelementstructure_te' at
>>> line
>>> > 1
>>> >           at
>>> >
>>> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTrans
>>> >   lator.java:231)
>>> >           at
>>> >
>>> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptio
>>> >   nTranslator.java:73)
>>> >           at
>>> >
>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
>>> >           at
>>> >
>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:440)
>>> >           at
>>> >
>>> org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.generateResourceTable(JdbcResourceTableStore.java:118
>>> >   )
>>> >           at
>>> >
>>> org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementTable(DefaultResourceTableService.
>>> >   java:198)
>>> >           at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
>>> Method)
>>> >           at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown
>>> Source)
>>> >           at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
>>> > Source)
>>> >           at java.lang.reflect.Method.invoke(Unknown Source)
>>> >           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(TransactionInterce
>>> >   ptor.java:99)
>>> >           at
>>> >
>>> org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspec
>>> >   tSupport.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.$Proxy143.generateDataElementTable(Unknown
>>> > Source)
>>> >           at
>>> >
>>> org.hisp.dhis.resourcetable.scheduling.ResourceTableTask.generateAll(ResourceTableTask.java:121)
>>> >           at
>>> >
>>> org.hisp.dhis.resourcetable.scheduling.ResourceTableTask.run(ResourceTableTask.java:84)
>>> >           at
>>> >
>>> org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.ja
>>> >   va:54)
>>> >           at
>>> java.util.concurrent.Executors$RunnableAdapter.call(Unknown
>>> > Source)
>>> >           at java.util.concurrent.FutureTask.run(Unknown Source)
>>> >           at
>>> >
>>> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(Unknown
>>> > Source)
>>> >           at
>>> >
>>> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown
>>> > Source)
>>> >           at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown
>>> > Source)
>>> >           at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown
>>> > Source)
>>> >           at java.lang.Thread.run(Unknown Source)
>>> >   Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
>>> > You have an error in your SQL syntax; check the ma
>>> >   nual that corresponds to your MySQL server version for the right
>>> syntax
>>> > to use near 'create index in_dataelementstructur
>>> >   e_datasetid_fVMFF on _dataelementstructure_te' at line 1
>>> >           at
>>> > sun.reflect.GeneratedConstructorAccessor53.newInstance(Unknown Source)
>>> >           at
>>> > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown
>>> Source)
>>> >           at java.lang.reflect.Constructor.newInstance(Unknown Source)
>>> >           at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
>>> >           at com.mysql.jdbc.Util.getInstance(Util.java:360)
>>> >           at
>>> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
>>> >           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
>>> >           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
>>> >           at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
>>> >           at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
>>> >           at
>>> > com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
>>> >           at
>>> > com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
>>> >           at
>>> com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
>>> >           at
>>> com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
>>> >           at
>>> >
>>> com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
>>> >           at
>>> >
>>> org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:432)
>>> >           at
>>> >
>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
>>> >           ... 26 more
>>> >
>>> > To manage notifications about this bug go to:
>>> > https://bugs.launchpad.net/dhis2/+bug/1523882/+subscriptions
>>> >
>>>
>>>
>>> --
>>> Thanks,
>>> Neeraj Gupta
>>>
>>> --
>>> You received this bug notification because you are a member of DHIS 2
>>> developers, which is subscribed to DHIS.
>>>
>>> https://bugs.launchpad.net/bugs/1523882
>>>
>>> Title:
>>>   Getting exception when upgraded application from Version 2.16 to 2.21
>>>   with Mysql dataase
>>>
>>> Status in DHIS:
>>>   New
>>>
>>> Bug description:
>>>   Dear All,
>>>
>>>   We recently upgraded DHIS 2.16 to 2.21 where database is Mysql some of
>>>   the errors are listed below.(We are using aggregated data not tracker)
>>>
>>>   Basically we are getting 3 errors
>>>   - Error1: In mysql programrule table is not created automatically
>>>
>>>   - Error2: While generating resource tables got the error, think the
>>>   query should be DROP TABLE IF EXISTS orgunitstructure;  Same applies
>>>   for all resource tables.
>>>
>>>   - Error3: Tried to copy resource table structures from old db and
>>>   tried generate resource table option, this time got the error:
>>>
>>>   The details of errors are given below.
>>>
>>>   While upgrading we ran all the sql update scripts.
>>>
>>>   Error1:
>>>   In mysql programrule table is not created automatically. While
>>> starting getting the below error:
>>>
>>>
>>>   * ERROR 2015-12-07 17:36:56,864 Problem updating: programrule, id
>>> column: programruleid (IdentityPopulator.java [localho
>>>   st-startStop-1])
>>>   org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
>>> bad SQL grammar [SELECT * from programrule WHERE uid
>>>    IS NULL]; nested exception is
>>> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table
>>> 'dhis.programrule'
>>>    doesn't exist
>>>           at
>>> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTrans
>>>   lator.java:231)
>>>           at
>>> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptio
>>>   nTranslator.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.common.IdentityPopulator.execute(IdentityPopulator.java:86)
>>>           at
>>> org.hisp.dhis.system.startup.DefaultStartupRoutineExecutor.execute(DefaultStartupRoutineExecutor.java:117)
>>>           at
>>> org.hisp.dhis.system.startup.DefaultStartupRoutineExecutor.execute(DefaultStartupRoutineExecutor.java:86)
>>>           at
>>> org.hisp.dhis.system.startup.StartupListener.contextInitialized(StartupListener.java:71)
>>>           at
>>> org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4729)
>>>           at
>>> org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5167)
>>>           at
>>> org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
>>>           at
>>> org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
>>>           at
>>> org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
>>>           at
>>> org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
>>>           at
>>> org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:945)
>>>           at
>>> org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1768)
>>>           at java.util.concurrent.Executors$RunnableAdapter.call(Unknown
>>> Source)
>>>           at java.util.concurrent.FutureTask.run(Unknown Source)
>>>           at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown
>>> Source)
>>>           at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown
>>> Source)
>>>           at java.lang.Thread.run(Unknown Source)
>>>   Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
>>> Table 'dhis.programrule' doesn't exist
>>>           at
>>> sun.reflect.GeneratedConstructorAccessor53.newInstance(Unknown Source)
>>>           at
>>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
>>>           at java.lang.reflect.Constructor.newInstance(Unknown Source)
>>>           at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
>>>           at com.mysql.jdbc.Util.getInstance(Util.java:360)
>>>           at
>>> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
>>>           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
>>>           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
>>>           at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
>>>           at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
>>>           at
>>> com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
>>>           at
>>> com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
>>>           at
>>> com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
>>>           at
>>> com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
>>>           at
>>> org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:455)
>>>           at
>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
>>>           ... 19 more
>>>   * INFO  2015-12-07 17:36:57,144 Executing startup routine [6 of 13,
>>> runlevel 5]: TableCreator (DefaultStartupRoutineExec
>>>   utor.java [localhost-startStop-1])
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>>
>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>>
>>>   Error2: While generating resource tables got the below error, think
>>>   the query should be DROP TABLE IF EXISTS _orgunitstructure;  Same
>>>   applies for all resource tables.
>>>
>>>   * INFO  2015-12-07 17:41:06,417 [Level: INFO, category:
>>> RESOURCETABLE_UPDATE, time: Mon Dec 07 17:41:06 IST 2015, messag
>>>   e: Generating resource tables] (InMemoryNotifier.java
>>> [taskScheduler-1])
>>>   * INFO  2015-12-07 17:41:06,465 Create table SQL: create table
>>> _orgunitstructure_temp (organisationunitid integer not nu
>>>   ll primary key, organisationunituid character(11), level integer,
>>> `idlevel1` integer, `uidlevel1` character(11), `idleve
>>>   l2` integer, `uidlevel2` character(11), `idlevel3` integer,
>>> `uidlevel3` character(11)); (JdbcResourceTableStore.java [ta
>>>   skScheduler-1])
>>>   * INFO  2015-12-07 17:41:06,495 Populate table content rows: 203
>>> (JdbcResourceTableStore.java [taskScheduler-1])
>>>   * INFO  2015-12-07 17:41:06,546 Create index SQL: create unique index
>>> in_orgunitstructure_organisationunituid_VWU2G on _
>>>   orgunitstructure_temp(organisationunituid)
>>> (JdbcResourceTableStore.java [taskScheduler-1])
>>>   * INFO  2015-12-07 17:41:06,582 [Level: ERROR, category:
>>> RESOURCETABLE_UPDATE, time: Mon Dec 07 17:41:06 IST 2015, messa
>>>   ge: Process failed: StatementCallback; bad SQL grammar [drop table
>>> _orgunitstructure;]; nested exception is com.mysql.jd
>>>   bc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table
>>> 'dhis._orgunitstructure'] (InMemoryNotifier.java [tas
>>>   kScheduler-1])
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>>
>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>>
>>>   Error3: Tried to copy resource table structures from old db and tried
>>>   generate resource table option, this time got the below error:
>>>
>>>   * INFO  2015-12-07 18:05:19,296 [Level: ERROR, category:
>>> RESOURCETABLE_UPDATE, time: Mon Dec 07 18:05:19 IST 2015, messa
>>>   ge: Process failed: StatementCallback; bad SQL grammar [create unique
>>> index in_dataelementstructure_dataelementuid_M86c6
>>>    on _dataelementstructure_temp(dataelementuid);create index
>>> in_dataelementstructure_datasetid_fVMFF on _dataelementstruc
>>>   ture_temp(datasetid);create index
>>> in_dataelementstructure_datasetuid_nsdEu on
>>> _dataelementstructure_temp(datasetuid);cre
>>>   ate index in_dataelementstructure_periodtypeid_EkjZg on
>>> _dataelementstructure_temp(periodtypeid);]; nested exception is
>>>   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
>>> error in your SQL syntax; check the manual that c
>>>   orresponds to your MySQL server version for the right syntax to use
>>> near 'create index in_dataelementstructure_datasetid
>>>   _fVMFF on _dataelementstructure_te' at line 1] (InMemoryNotifier.java
>>> [taskScheduler-5])
>>>   * ERROR 2015-12-07 18:05:19,319 Unexpected error occurred in scheduled
>>> task. (TaskUtils.java [taskScheduler-5])
>>>   org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
>>> bad SQL grammar [create unique index in_dataelements
>>>   tructure_dataelementuid_M86c6 on
>>> _dataelementstructure_temp(dataelementuid);create index
>>> in_dataelementstructure_dataset
>>>   id_fVMFF on _dataelementstructure_temp(datasetid);create index
>>> in_dataelementstructure_datasetuid_nsdEu on _dataelements
>>>   tructure_temp(datasetuid);create index
>>> in_dataelementstructure_periodtypeid_EkjZg on
>>> _dataelementstructure_temp(periodty
>>>   peid);]; nested exception is
>>> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
>>> error in your SQL sy
>>>   ntax; check the manual that corresponds to your MySQL server version
>>> for the right syntax to use near 'create index in_d
>>>   ataelementstructure_datasetid_fVMFF on _dataelementstructure_te' at
>>> line 1
>>>           at
>>> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTrans
>>>   lator.java:231)
>>>           at
>>> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptio
>>>   nTranslator.java:73)
>>>           at
>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
>>>           at
>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:440)
>>>           at
>>> org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.generateResourceTable(JdbcResourceTableStore.java:118
>>>   )
>>>           at
>>> org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementTable(DefaultResourceTableService.
>>>   java:198)
>>>           at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>           at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>>           at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
>>> Source)
>>>           at java.lang.reflect.Method.invoke(Unknown Source)
>>>           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(TransactionInterce
>>>   ptor.java:99)
>>>           at
>>> org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspec
>>>   tSupport.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.$Proxy143.generateDataElementTable(Unknown
>>> Source)
>>>           at
>>> org.hisp.dhis.resourcetable.scheduling.ResourceTableTask.generateAll(ResourceTableTask.java:121)
>>>           at
>>> org.hisp.dhis.resourcetable.scheduling.ResourceTableTask.run(ResourceTableTask.java:84)
>>>           at
>>> org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.ja
>>>   va:54)
>>>           at java.util.concurrent.Executors$RunnableAdapter.call(Unknown
>>> Source)
>>>           at java.util.concurrent.FutureTask.run(Unknown Source)
>>>           at
>>> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(Unknown
>>> Source)
>>>           at
>>> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown
>>> Source)
>>>           at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown
>>> Source)
>>>           at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown
>>> Source)
>>>           at java.lang.Thread.run(Unknown Source)
>>>   Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
>>> You have an error in your SQL syntax; check the ma
>>>   nual that corresponds to your MySQL server version for the right
>>> syntax to use near 'create index in_dataelementstructur
>>>   e_datasetid_fVMFF on _dataelementstructure_te' at line 1
>>>           at
>>> sun.reflect.GeneratedConstructorAccessor53.newInstance(Unknown Source)
>>>           at
>>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
>>>           at java.lang.reflect.Constructor.newInstance(Unknown Source)
>>>           at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
>>>           at com.mysql.jdbc.Util.getInstance(Util.java:360)
>>>           at
>>> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
>>>           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
>>>           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
>>>           at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
>>>           at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
>>>           at
>>> com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
>>>           at
>>> com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
>>>           at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
>>>           at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
>>>           at
>>> com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
>>>           at
>>> org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:432)
>>>           at
>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
>>>           ... 26 more
>>>
>>> To manage notifications about this bug go to:
>>> https://bugs.launchpad.net/dhis2/+bug/1523882/+subscriptions
>>>
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~dhis2-devs
>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>
>>
>>
>> --
>> Lars Helge Øverland
>> Lead developer, DHIS 2
>> University of Oslo
>> Skype: larshelgeoverland
>> http://www.dhis2.org <https://www.dhis2.org>
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
> --
> Thanks,
> Neeraj Gupta
>

References