Data Export Issue in DHIS2 Analytics [FIXED]

elmoujarrade · 5 July 2023 10:22

Dear DHIS2 Community,
I am writing to seek assistance regarding an issue I am facing while exporting data to Analytics in DHIS2. I am encountering a problem where the process gets stuck, and I receive the following error message:

"PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Batch entry 38,043 insert into _dateperiodstructure_temp values ('0100-02-29 00:00:00+00',100,'1000227','100W8','100WedW9','100ThuW9','100SatW9','100SunW8','100BiW4','10002','10001B','100Q1','100NovQ2','100S1','99AprilS2','100NovS1','100','99April','99July','99Oct','100Nov') was aborted: ERROR: date/time field value out of range: "0100-02-29 00:00:00+00" Call getNextException to see other errors in the batch.; nested exception is java.sql.BatchUpdateException: Batch entry 38,043 insert into _dateperiodstructure_temp values ('0100-02-29 00:00:00+00',100,'1000227','100W8','100WedW9','100ThuW9','100SatW9','100SunW8','100BiW4','10002','10001B','100Q1','100NovQ2','100S1','99AprilS2','100NovS1','100','99April','99July','99Oct','100Nov') was aborted: ERROR: date/time field value out of range: "0100-02-29 00:00:00+00" Call getNextException to see other errors in the batch."

I am seeking your expertise and guidance to overcome this issue. It appears that there is an error related to a date/time field value being out of range (“0100-02-29 00:00:00+00”). Could you please assist me in resolving this problem? I would greatly appreciate any insights or suggestions you can provide.

Thank you very much for your attention and support. I look forward to hearing from you soon.

Gassim · 5 July 2023 15:05

Hi @elmoujarrade

Why are there periods with the year “0100”? It’s not clear how this would happen. Were you importing data from a spreadsheet?

It wasn’t clear at first but one speculation could be that the dates got mixed up between time zone and year i.e. GMT+01:00 (“0100”) is the time zone in Morocco. This might happen maybe if the sheet was RTL and somehow the dates were inserted backward?

The issue is from the data values inserted into the database. Are there any clues you can see when running the analytics tables export that might lead to where these dates are? The way to solve this is understand where these incorrect values are from.

I hope this helps. Thanks!

elmoujarrade · 7 July 2023 12:29

Hi @Gassim
I checked the logs by the chrome dev tool, and noticed that the process stops at [9/11] when generating the DatePeriod table. The error displayed is:

[{“uid”:“THwaXM3P8tb”,“level”:“ERROR”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T16:12:54.638”,“message”:“Process failed: java.util.concurrent.CancellationException”,“completed”:true,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“ERROR”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T16:12:54.397”,“message”:“Process failed: java.util.concurrent.CancellationException”,“completed”:true,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“ERROR”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T16:12:54.315”,“message”:“processing aborted: cancelled as failing item caused request for cancellation”,“completed”:false,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“ERROR”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T16:12:54.313”,“message”:“PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Batch entry 38,043 insert into _dateperiodstructure_temp values (‘0100-02-29 00:00:00+00’,100,‘1000227’,‘100W8’,‘100WedW9’,‘100ThuW9’,‘100SatW9’,‘100SunW8’,‘100BiW4’,‘10002’,‘10001B’,‘100Q1’,‘100NovQ2’,‘100S1’,‘99AprilS2’,‘100NovS1’,‘100’,‘99April’,‘99July’,‘99Oct’,‘100Nov’) was aborted: ERROR: date/time field value out of range: "0100-02-29 00:00:00+00" Call getNextException to see other errors in the batch.; nested exception is java.sql.BatchUpdateException: Batch entry 38,043 insert into _dateperiodstructure_temp values (‘0100-02-29 00:00:00+00’,100,‘1000227’,‘100W8’,‘100WedW9’,‘100ThuW9’,‘100SatW9’,‘100SunW8’,‘100BiW4’,‘10002’,‘10001B’,‘100Q1’,‘100NovQ2’,‘100S1’,‘99AprilS2’,‘100NovS1’,‘100’,‘99April’,‘99July’,‘99Oct’,‘100Nov’) was aborted: ERROR: date/time field value out of range: "0100-02-29 00:00:00+00" Call getNextException to see other errors in the batch.”,“completed”:false,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“INFO”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T15:05:06.351”,“message”:“Generating resource tables”,“completed”:false,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“INFO”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T15:05:06.347”,“message”:“Drop SQL views”,“completed”:false,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“INFO”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T15:05:06.343”,“message”:“Analytics table update process started”,“completed”:false,“dataType”:“PARAMETERS”,“data”:{“lastYears”:null,“skipTableTypes”:[],“skipPrograms”:[],“skipResourceTables”:false},“id”:“THwaXM3P8tb”}]

I would appreciate any guidance or insights you can provide to help me identify the source of the incorrect dates and resolve the error. Thank you for your support.

Best regards,

medallion · 8 July 2023 02:17

Hi @elmoujarrade,
It seems there are data values for some incorrect dates in your database. You will need to fish out the datavalues/events and delete them. Is your implementation tracker or aggregate?

idelcano · 11 July 2023 14:56

Hello, i open it as jira issue too, but maybe somebody have some workaround to solve this problems.

https://dhis2.atlassian.net/browse/DHIS2-15573

Since at least the last two build we are experiment errors with the analytics.
in the continuous analitycs we can see in the logs:
Caused by: org.postgresql.util.PSQLException: ERROR: column dps.quarterlynov does not exist
Hint: Perhaps you meant to reference the column “dps.quarterly”.

And when we execute the full analytics after remove anlaytics tables:
core_1 | Caused by: org.postgresql.util.PSQLException: ERROR: date/time field value out of range: “1100-02-29 01:14:44+01”

In other instance with diferent data:
2023-07-03 11:33:30 PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Batch entry 1,519 insert into
_dateperiodstructure_temp values (‘0200-02-29 01:14:44+01’,200,‘2000228’,‘200W9’,‘200WedW9’,‘200ThuW9’,‘200SatW8’,‘200SunW9’,‘200BiW5’,‘20002’,‘20001B’,‘200Q1’,‘200NovQ2’,‘200S1’,‘199AprilS2’,‘200NovS1’,‘200’,‘199April’,‘199July’,‘199Oct’,‘200Nov’) was aborted: ERROR: date/time field value out of range: “0200-02-29 01:14:44+01” Where: unnamed portal parameter $1 = ‘…’ Call getNextException to see other errors in the batch.; nested exception is java.sql.BatchUpdateException: Batch entry 1,519 insert into _dateperiodstructure_temp values (‘0200-02-29 01:14:44+01’,200,‘2000228’,‘200W9’,‘200WedW9’,‘200ThuW9’,‘200SatW8’,‘200SunW9’,‘200BiW5’,‘20002’,‘20001B’,‘200Q1’,‘200NovQ2’,‘200S1’,‘199AprilS2’,‘200NovS1’,‘200’,‘199April’,‘199July’,‘199Oct’,‘200Nov’) was aborted: ERROR: date/time field value out of range: “0200-02-29 01:14:44+01” Where: unnamed portal parameter $1 = ‘…’ Call getNextException to see other errors in the batch.

(This date is invalid due 02-29 not exist in the year 200, but i dont know where come from that date)

moses_mwale · 11 July 2023 19:18

Hello @idelcano

I have noticed you are facing the same exact issue as me. I took time to study the problem and we discovered that the resource tables is the one causing this issue. I assume you are also failing to complete resource table generation. Whats happening happening that _dateperiodstructure table doesnt have the column QuarterlyNov thus failing to proceed with analytics. If you check carefuly, there should be a table _dateperiodstructure_temp. Alter this table to _dateperiodstructure and alter the other _dateperiodstructure to _dateperiodstructure_temp and run the analytics by checking skip resource table. That is how I bypassed the error and can run analytics but failing resource table. This appears to be a bug big time. Have a look at my post, I have seen your Jira, I will comment and vote.

Regards
Moses

moses_mwale · 11 July 2023 18:33

Dear Team,
May I have your assistance on this matter, patch release 2.38.4.3 has brought trouble on my instance, Resource table generation cant past run _dateperiodstructure. Catalina.out is silent ans well as database logs. The only thing I can see under network is a loop

My catalina.out log is stack like mentioned

[2023-07-11 20:30:36] [info] * INFO  2023-07-11T20:30:36,170 Resource table '_categoryoptioncomboname' update done: '00:00:04.206' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:36] [info] * INFO  2023-07-11T20:30:36,283 Generating resource table: '_dataelementgroupsetstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:36] [info] * INFO  2023-07-11T20:30:36,975 Resource table '_dataelementgroupsetstructure' update done: '00:00:00.691' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:37] [info] * INFO  2023-07-11T20:30:37,006 Generating resource table: '_indicatorgroupsetstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:37] [info] * INFO  2023-07-11T20:30:37,030 Resource table '_indicatorgroupsetstructure' update done: '00:00:00.023' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:37] [info] * INFO  2023-07-11T20:30:37,064 Generating resource table: '_organisationunitgroupsetstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:38] [info] * INFO  2023-07-11T20:30:38,648 Resource table '_organisationunitgroupsetstructure' update done: '00:00:01.583' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:38] [info] * INFO  2023-07-11T20:30:38,694 Generating resource table: '_categorystructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:41] [info] * INFO  2023-07-11T20:30:41,707 Resource table '_categorystructure' update done: '00:00:03.013' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:41] [info] * INFO  2023-07-11T20:30:41,889 Generating resource table: '_dataelementstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:44] [info] * INFO  2023-07-11T20:30:44,187 Resource table '_dataelementstructure' update done: '00:00:02.296' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:44] [info] * INFO  2023-07-11T20:30:44,281 Generating resource table: '_periodstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:44] [info] * WARN  2023-07-11T20:30:44,542 Duplicate ISO date for period, ignoring: [DEFAULT | Yearly: 1995-01-01 - 1995-12-31], ISO date: 1995 (PeriodResourceTable.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:45] [info] * INFO  2023-07-11T20:30:45,385 Resource table '_periodstructure' update done: '00:00:01.103' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:45] [info] * INFO  2023-07-11T20:30:45,802 Generating resource table: '_dateperiodstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs

We can stay on this table ‘_dateperiodstructure.’ for ages.

Anyone familiar with this cause?

Gassim · 13 July 2023 06:59

Hi @elmoujarrade

It seems two other community members (@idelcano and @moses_mwale) are facing the same issue:

I have a feeling that the out of range date such as 0100 and 0200 is because the timezone is being mixed up somewhere. I’m going to ask for support so that someone from the team can investigate this further. Thanks!

jason · 13 July 2023 12:49

As noted by @medallion , it looks like this date (February 29th 100) is the problem. Note the year…100 AD, some 1900+ years in the past, would appear to be invalid. This likely results from some data entry problem with a user mistyping 100 for 2020 for instance. Look in the period table to see if you can find the periodid for this extremely old period. Once you find it, you will need to correct/remove the data which is associated with it in the datavalue table. Once you find all data which is associated with this period and have moved it to the correct period (or removed it), restart your Tomcat server and try to run analytics again. If it doesn’t work, let us know!

WaluQ · 8 November 2023 12:17

@jason, is this a new validation that came with the recent releases? We have always had data with these erroneous dates in the system, but the resource table has always been working respecting only the number of years set in the “last years” to include. Our instance currently has close to 1.5k TEIs being followed up across 10+ tracker programs since 2016, It will not be feasible to identify all the data falling out of the current range offset, and increasing this value to include the bad data would mean an entire day or 2 days of updating Analytics tables (currently at about 7 hours). Is it possible to have a more feasible solution?

Range of years allowed (based on your system settings and existing data): [2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024].
 Years out of range found: [197, 198, 199, 200, 201, 202, 1990, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2029, 2091, 2107, 2109, 2110, 2111, 2112, 2113, 2114].
	at org.hisp.dhis.resourcetable.DefaultResourceTableService.checkYearsOffset(DefaultResourceTableService.java:228) ~[dhis-service-administration-2.39.3.jar:?] at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDatePeriodTable(DefaultResourceTableService.java:187) ~[dhis-service-administration-2.39.3.jar:?] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]

jason · 9 November 2023 07:35

Tagging @maikel who may be able to advise better.

Gassim · 10 November 2023 11:50

Hi @here

@maikel has mentioned the steps for a workaround to solve the issue in the Jira bug issue, and the good news as Maikel said is that it will be fixed in the next patch release so if you wait and update the issue will stop.

Thank you!

WaluQ · 10 November 2023 14:03

Yes, we managed to resolve the issue temporarily using @maikel’s guide. Thank you once more.

Gassim · 13 November 2023 12:44

Just to confirm, if you are using 2.38.4, the update to 2.38.5 is ready: DHIS2 patch release 2.38.5 is now available

Please update to fix the issue. Thanks!

Mithilesh_Kumar_Thak · 16 November 2023 18:16

Dear Team
while generating resource table in version 2.39.3 we got this error due to this reason one of the resource tabe not created – _dateperiodstructure

resource table generation issue

Your database contains years out of the allowed offset. Range of years allowed (based on your system settings and existing data): [2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028]. Years out of range found: [1988, 1989, 1990, 1991, 1992, 1993, 2000].

please suggest whare the store and in which table contain database contains years out of the allowed offset. its shows based on your system settings and existing data)

Thanks and Regards

Mithilesh Kumar Thakur

Gassim · 17 November 2023 12:56

Hi @Mithilesh_Kumar_Thak

Welcome back to the community! This is a well known bug which as been fixed ([DHIS2-15573] - Jira). For version 2.39.3, you will need to update to 2.39.3.1 which is already released or 2.39.4 once it is released.

Thank you! I’m merging your post with the topic discussion about this issue.

Mithilesh_Kumar_Thak · 18 November 2023 19:55

@Gassim thanku very much its working