← Back to team overview

dhis2-devs team mailing list archive

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

 

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


Follow ups

References