dhis2-users team mailing list archive

Thread
Date
Re: Fwd: Re: [Dhis2-devs] Exception with Mysql database in version 2.21

To: Knut Staring <knutst@xxxxxxxxx>
From: Neeraj Gupta <neeraj.hisp@xxxxxxxxx>
Date: Tue, 8 Dec 2015 11:35:47 +0530
Cc: DHIS 2 Users list <dhis2-users@xxxxxxxxxxxxxxxxxxx>
In-reply-to: <CAL=8=NjUtHarjB88NiCO=AdhLWpvq5VwK2O3XrqMy5qUy_bguQ@mail.gmail.com>
Yes, ran all sql scripts.

On Tue, Dec 8, 2015 at 11:34 AM, Knut Staring <knutst@xxxxxxxxx> wrote:

> But I assume you ran all the Sql update scripts?
> On 8 Dec 2015 07:02, "Knut Staring" <knutst@xxxxxxxxx> wrote:
>
>> ---------- Forwarded message ----------
>> From: "Neeraj Gupta" <neeraj.hisp@xxxxxxxxx>
>> Date: 8 Dec 2015 06:54
>> Subject: Re: [Dhis2-devs] Exception with Mysql database in version 2.21
>> To: "Knut Staring" <knutst@xxxxxxxxx>
>> Cc:
>>
>> Thanks for replying Knut.
>>
>> We upgraded directly from 2.16 to 2.21. But the errors seems more
>> database related
>>
>> For example if we see drop _orgunitstructure query, it should be dropped
>> irrespective of versions.
>>
>> On Mon, Dec 7, 2015 at 6:24 PM, Knut Staring <knutst@xxxxxxxxx> wrote:
>>
>>> You upgraded directly? Or step by step (2.17, 2.18...)
>>>
>>> On Mon, Dec 7, 2015 at 1:52 PM, Neeraj Gupta <neeraj.hisp@xxxxxxxxx>
>>> wrote:
>>>
>>>> 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
>>>>
>>>> _______________________________________________
>>>> 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
>>>>
>>>>
>>>
>>>
>>> --
>>> Knut Staring
>>> Dept. of Informatics, University of Oslo
>>> Norway: +4791880522
>>> Skype: knutstar
>>> http://dhis2.org
>>>
>>
>>
>>
>> --
>> Thanks,
>> Neeraj Gupta
>>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
Thanks,
Neeraj Gupta
Follow ups

Re: Fwd: Re: [Dhis2-devs] Exception with Mysql database in version 2.21
From: Lars Helge Øverland, 2015-12-08
References

Fwd: Re: [Dhis2-devs] Exception with Mysql database in version 2.21
From: Knut Staring, 2015-12-08
Re: Fwd: Re: [Dhis2-devs] Exception with Mysql database in version 2.21
From: Knut Staring, 2015-12-08