dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

Hi Fatema,

This is not a database issue, but a bug that occurs when generating the
period structure table on machines/servers using on of a few specific time
zones.

I think I commented on this issues on another email thread you started.

Morten knows the details, but my quick fix advice is to change the system
time zone to a zone that we know works, e.g. GMT.

Ola
On 20 Mar 2015 14:16, "Alex Tumwesigye" <atumwesigye@xxxxxxxxx> wrote:

> Dear Fatema,
>
> All looks good and no duplicates.
> The only odd stuff is this: Note the years (2063 and 2140)
> "811017","1","2063-03-11","2063-03-11"
> "811018","1","2063-03-12","2063-03-12"
> "811019","1","2063-03-13","2063-03-13"
> "811020","1","2063-03-14","2063-03-14"
> "811021","1","2063-03-15","2063-03-15"
> "811022","1","2063-03-16","2063-03-16"
> "811023","1","2063-03-17","2063-03-17"
> "811024","1","2063-03-18","2063-03-18"
> "811025","1","2063-03-19","2063-03-19"
> "811026","1","2063-03-20","2063-03-20"
> "811027","1","2063-03-21","2063-03-21"
> "811028","1","2063-03-22","2063-03-22"
> "811029","1","2063-03-23","2063-03-23"
> "811030","1","2063-03-24","2063-03-24"
> "811031","1","2063-03-25","2063-03-25"
> "1555","1","2140-09-03","2140-09-03"
> "1554","1","2140-09-04","2140-09-04"
> "1550","1","2140-09-05","2140-09-05"
> "1551","1","2140-09-06","2140-09-06"
> "1552","1","2140-09-07","2140-09-07"
> "1553","1","2140-09-08","2140-09-08"
>
> can you try this query on period table?
> delete from period where enddate is null  or startdate is null;
>
> After running this query, go to resource table and regenerate them.
>
> Thanks.
>
> Alex
>
> On Fri, Mar 20, 2015 at 3:52 PM, Uddin Fatema <fatemacse@xxxxxxxxx> wrote:
>
>> Dear Alex,
>>
>> I have run this query and get two result.
>>
>> select enddate,startdate,periodtypeid from period where enddate =
>> '2009-12-31' or startdate = '2009-12-31'
>>
>> Result is
>>
>> "2009-12-31";"2009-12-01";3
>> "2009-12-31";"2009-01-01";6
>>
>> here i attached the file too.
>>
>> Thanks a lot
>>
>>
>> On Fri, Mar 20, 2015 at 6:37 PM, Alex Tumwesigye <atumwesigye@xxxxxxxxx>
>> wrote:
>>
>>> Fatema,
>>>
>>> Can you dump for me the enddate, startdate and periodtype from period
>>> table for that date '2009-12-31'
>>>
>>> I need to see the output of "select enddate,startdate,periodtypeid from
>>> period where enddate like '2009-12-31' or startdate like '2009-12-31' "
>>>
>>> Thanks.
>>>
>>> Alex
>>>
>>> On Fri, Mar 20, 2015 at 3:20 PM, Calle Hedberg <calle.hedberg@xxxxxxxxx>
>>> wrote:
>>>
>>>> Did you verify that all three constraints are in place on the period
>>>> table?
>>>>
>>>> If they are and you are 100% sure there are no duplicates, then I don't
>>>> know - I've had similar error messages a couple of times, but removing the
>>>> duplicates and re-inserting the constraint have always sorted it out.
>>>>
>>>> Clear cache (DHIS and browser), empty resource tables, re-start tomcat
>>>> - I'm not aware of any other tricks....
>>>>
>>>> Regards
>>>> Calle
>>>>
>>>> On 20 March 2015 at 14:15, Uddin Fatema <fatemacse@xxxxxxxxx> wrote:
>>>>
>>>>> Thanks Calle,
>>>>>
>>>>> I have already checked all part and this period is daily, so startdate
>>>>> and enddate is same 2009-12-31
>>>>>
>>>>> However, my problem is remains same
>>>>>
>>>>> What can i do.
>>>>>
>>>>>
>>>>> On Fri, Mar 20, 2015 at 6:11 PM, Calle Hedberg <
>>>>> calle.hedberg@xxxxxxxxx> wrote:
>>>>>
>>>>>> Fatema,
>>>>>>
>>>>>> note that 2009-12-31 looks like an ENDdate, not a startdate.
>>>>>>
>>>>>> Regards
>>>>>> Calle
>>>>>>
>>>>>> On 20 March 2015 at 14:09, Uddin Fatema <fatemacse@xxxxxxxxx> wrote:
>>>>>>
>>>>>>> Thanks Bharath Anna and Alex.
>>>>>>>
>>>>>>> Actually i checked period table with this( 2009-12-31) startdate and
>>>>>>> there is no duplicate period with same startdate.
>>>>>>>
>>>>>>> Additionally, for test purpose i deleted this period from period
>>>>>>> table and from other related table with this periodid.
>>>>>>>
>>>>>>> Several time restart the tomcat and clear the cash.
>>>>>>>
>>>>>>> Although, problem remain same.
>>>>>>>
>>>>>>> "Process failed: PreparedStatementCallback; SQL [insert into
>>>>>>> _dateperiodstructure values (?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate
>>>>>>> key value violates unique constraint "_dateperiodstructure_pkey" Detail:
>>>>>>> Key (dateperiod)=(2009-12-31) already exists.; nested exception is
>>>>>>> org.postgresql.util.PSQLException: ERROR: duplicate key value violates
>>>>>>> unique constraint "_dateperiodstructure_pkey" Detail: Key
>>>>>>> (dateperiod)=(2009-12-31) already exists.
>>>>>>> 2015-03-20 12:03:45 Generating resource tables  "
>>>>>>>
>>>>>>> What can i do now?
>>>>>>>
>>>>>>> On Fri, Mar 20, 2015 at 5:22 PM, Alex Tumwesigye <
>>>>>>> atumwesigye@xxxxxxxxx> wrote:
>>>>>>>
>>>>>>>> Dear Fatema,
>>>>>>>>
>>>>>>>> If I remember correctly, this used to be an issue in 2.16. I guess,
>>>>>>>> it has been carried to 2.18.
>>>>>>>>
>>>>>>>> The problem was that, two similar dates were created and when you
>>>>>>>> try to run the resource table, that range is already created.
>>>>>>>> Query the period table directly for this date "2009-12-31", two
>>>>>>>> duplicates may exist.Please try to attempt to compare ranges for different
>>>>>>>> period types, this is where the problem is (You will encounter a range that
>>>>>>>> is meant to be for a week, yet it is assigned to daily or monthly)
>>>>>>>> If so, you may need drop it manually for the resource tables to be
>>>>>>>> generated.
>>>>>>>> Let me look for the script
>>>>>>>>
>>>>>>>> Thanks.
>>>>>>>>
>>>>>>>> Alex
>>>>>>>>
>>>>>>>>
>>>>>>>> On Fri, Mar 20, 2015 at 2:06 PM, Uddin Fatema <fatemacse@xxxxxxxxx>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Dear All,
>>>>>>>>>
>>>>>>>>> I have upgrade our application from 2.16 to 2.18.
>>>>>>>>> However, when we generate Resource table,we have get error. the
>>>>>>>>> error short note given in bellow.
>>>>>>>>>
>>>>>>>>>  ERROR, category: RESOURCETABLE_UPDATE, t
>>>>>>>>> ime: Fri Mar 20 16:52:29 BDT 2015, message: Process failed:
>>>>>>>>> PreparedStatementCal
>>>>>>>>> lback; SQL [insert into _dateperiodstructure values
>>>>>>>>> (?,?,?,?,?,?,?,?,?,?,?,?)];
>>>>>>>>> ERROR: duplicate key value violates unique constraint
>>>>>>>>> "_dateperiodstructure_pkey
>>>>>>>>> "
>>>>>>>>>   Detail: Key (dateperiod)=(2009-12-31) already exists.; nested
>>>>>>>>> exception is org
>>>>>>>>> .postgresql.util.PSQLException: ERROR: duplicate key value
>>>>>>>>> violates unique const
>>>>>>>>> raint "_dateperiodstructure_pkey"
>>>>>>>>>   Detail: Key (dateperiod)=(2009-12-31) already exists.]
>>>>>>>>> (InMemoryNotifier.java
>>>>>>>>> [taskScheduler-1])
>>>>>>>>> * ERROR 2015-03-20 16:52:29,445 Unexpected error occurred in
>>>>>>>>> scheduled task. (Ta
>>>>>>>>> skUtils.java [taskScheduler-1])
>>>>>>>>>
>>>>>>>>> Additionally, here i attached the bug file.
>>>>>>>>>
>>>>>>>>> Could anyone please help us.
>>>>>>>>> --
>>>>>>>>> Regards,
>>>>>>>>> *Fatema Uddin*
>>>>>>>>>
>>>>>>>>> Technical Advisor (HIS)-Support to the Health Sector Program II
>>>>>>>>> Deutsche Gesellschaft für Internationale Zusammenarbeit (GIZ) GmbH
>>>>>>>>> German House
>>>>>>>>> House 10/A-2, Road 90
>>>>>>>>> Gulshan 2, Dhaka 1212, Bangladesh
>>>>>>>>>
>>>>>>>>> T          +880 9666701000
>>>>>>>>> F          +880 28823099
>>>>>>>>> M         +880 19 6206 4721
>>>>>>>>> I           www.giz.de
>>>>>>>>> <https://owa.giz.de/owa/redir.aspx?C=mPQMujasE0-yL6Aa4ocPcnkcfUThJNEIi4h1DwsYjO3Ua20UR_COt6aTH1s4JIdR4p-jCoFsP6A.&URL=http%3a%2f%2fwww.giz.de%2f>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> _______________________________________________
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Alex Tumwesigye
>>>>>>>>
>>>>>>>> Technical Advisor - DHIS2 (Consultant),
>>>>>>>> Ministry of Health/AFENET
>>>>>>>> Kampala
>>>>>>>> Uganda
>>>>>>>>
>>>>>>>> IT Consultant - BarefootPower Uganda Ltd, SmartSolar, Kenya
>>>>>>>>
>>>>>>>> IT Specialist (Servers, Networks and Security, Health Information
>>>>>>>> Systems - DHIS2 ) & Solar Consultant
>>>>>>>>
>>>>>>>> +256 774149 775, + 256 759 800161
>>>>>>>>
>>>>>>>> "I don't want to be anything other than what I have been - one tree
>>>>>>>> hill "
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Regards,
>>>>>>> *Fatema Uddin*
>>>>>>>
>>>>>>> Technical Advisor (HIS)-Support to the Health Sector Program II
>>>>>>> Deutsche Gesellschaft für Internationale Zusammenarbeit (GIZ) GmbH
>>>>>>> German House
>>>>>>> House 10/A-2, Road 90
>>>>>>> Gulshan 2, Dhaka 1212, Bangladesh
>>>>>>>
>>>>>>> T          +880 9666701000
>>>>>>> F          +880 28823099
>>>>>>> M         +880 19 6206 4721
>>>>>>> I           www.giz.de
>>>>>>> <https://owa.giz.de/owa/redir.aspx?C=mPQMujasE0-yL6Aa4ocPcnkcfUThJNEIi4h1DwsYjO3Ua20UR_COt6aTH1s4JIdR4p-jCoFsP6A.&URL=http%3a%2f%2fwww.giz.de%2f>
>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>> *******************************************
>>>>>>
>>>>>> Calle Hedberg
>>>>>>
>>>>>> 46D Alma Road, 7700 Rosebank, SOUTH AFRICA
>>>>>>
>>>>>> Tel/fax (home): +27-21-685-6472
>>>>>>
>>>>>> Cell: +27-82-853-5352
>>>>>>
>>>>>> Iridium SatPhone: +8816-315-19274
>>>>>>
>>>>>> Email: calle.hedberg@xxxxxxxxx
>>>>>>
>>>>>> Skype: calle_hedberg
>>>>>>
>>>>>> *******************************************
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Regards,
>>>>> *Fatema Uddin*
>>>>>
>>>>> Technical Advisor (HIS)-Support to the Health Sector Program II
>>>>> Deutsche Gesellschaft für Internationale Zusammenarbeit (GIZ) GmbH
>>>>> German House
>>>>> House 10/A-2, Road 90
>>>>> Gulshan 2, Dhaka 1212, Bangladesh
>>>>>
>>>>> T          +880 9666701000
>>>>> F          +880 28823099
>>>>> M         +880 19 6206 4721
>>>>> I           www.giz.de
>>>>> <https://owa.giz.de/owa/redir.aspx?C=mPQMujasE0-yL6Aa4ocPcnkcfUThJNEIi4h1DwsYjO3Ua20UR_COt6aTH1s4JIdR4p-jCoFsP6A.&URL=http%3a%2f%2fwww.giz.de%2f>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> *******************************************
>>>>
>>>> Calle Hedberg
>>>>
>>>> 46D Alma Road, 7700 Rosebank, SOUTH AFRICA
>>>>
>>>> Tel/fax (home): +27-21-685-6472
>>>>
>>>> Cell: +27-82-853-5352
>>>>
>>>> Iridium SatPhone: +8816-315-19274
>>>>
>>>> Email: calle.hedberg@xxxxxxxxx
>>>>
>>>> Skype: calle_hedberg
>>>>
>>>> *******************************************
>>>>
>>>>
>>>
>>>
>>> --
>>> Alex Tumwesigye
>>>
>>> Technical Advisor - DHIS2 (Consultant),
>>> Ministry of Health/AFENET
>>> Kampala
>>> Uganda
>>>
>>> IT Consultant - BarefootPower Uganda Ltd, SmartSolar, Kenya
>>>
>>> IT Specialist (Servers, Networks and Security, Health Information
>>> Systems - DHIS2 ) & Solar Consultant
>>>
>>> +256 774149 775, + 256 759 800161
>>>
>>> "I don't want to be anything other than what I have been - one tree hill
>>> "
>>>
>>
>>
>>
>> --
>> Regards,
>> *Fatema Uddin*
>>
>> Technical Advisor (HIS)-Support to the Health Sector Program II
>> Deutsche Gesellschaft für Internationale Zusammenarbeit (GIZ) GmbH
>> German House
>> House 10/A-2, Road 90
>> Gulshan 2, Dhaka 1212, Bangladesh
>>
>> T          +880 9666701000
>> F          +880 28823099
>> M         +880 19 6206 4721
>> I           www.giz.de
>> <https://owa.giz.de/owa/redir.aspx?C=mPQMujasE0-yL6Aa4ocPcnkcfUThJNEIi4h1DwsYjO3Ua20UR_COt6aTH1s4JIdR4p-jCoFsP6A.&URL=http%3a%2f%2fwww.giz.de%2f>
>>
>>
>
>
> --
> Alex Tumwesigye
>
> Technical Advisor - DHIS2 (Consultant),
> Ministry of Health/AFENET
> Kampala
> Uganda
>
> IT Consultant - BarefootPower Uganda Ltd, SmartSolar, Kenya
>
> IT Specialist (Servers, Networks and Security, Health Information Systems
> - DHIS2 ) & Solar Consultant
>
> +256 774149 775, + 256 759 800161
>
> "I don't want to be anything other than what I have been - one tree hill "
>
> _______________________________________________
> 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
>
>