dhis2-users team mailing list archive
-
dhis2-users team
-
Mailing list archive
-
Message #08782
Re: [Dhis2-devs] Exception with Mysql database in version 2.21
Thanks Markus,
I have registered a bug on launchpad.
On Tue, Dec 8, 2015 at 4:01 PM, Markus Bekken <markus.bekken@xxxxxxxxx>
wrote:
> Hi Neeraj,
> always good to have a bug to link to. I am testing the fix now and will
> apply to trunk and 2.21. Struggling a but with connectivity working from a
> training venue in Palestine, but it will be ready soon.
>
> Markus
>
> 8. des. 2015 kl. 10.48 skrev Neeraj Gupta <neeraj.hisp@xxxxxxxxx>:
>
> Thanks Lars.
>
> Shall I report the bug nn launchpad for this?
>
> On Tue, Dec 8, 2015 at 1:55 PM, Lars Helge Øverland <larshelge@xxxxxxxxx>
> wrote:
>
>> I can see that table programrule has a column "condition" which appears
>> to be a reserved <https://dev.mysql.com/doc/refman/5.5/en/keywords.html>
>> word in mysql.
>>
>> Will look into it.
>>
>> Lars
>>
>> On Tue, Dec 8, 2015 at 7:05 AM, Neeraj Gupta <neeraj.hisp@xxxxxxxxx>
>> wrote:
>>
>>> 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
>>>
>>> _______________________________________________
>>> 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
>>>
>>>
>>
>>
>> --
>> Lars Helge Øverland
>> Lead developer, DHIS 2
>> University of Oslo
>> Skype: larshelgeoverland
>> http://www.dhis2.org <https://www.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
References
