dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #41838
Exception with Mysql database in version 2.21
Dear All,
We recently upgraded DHIS 2.16 to 2.21 where database is Mysql some of the
errors are listed below, can someone please guide us?*(We are using
aggregated data not tracker)*
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
--
Thanks,
Neeraj Gupta
