Dear team,
I upgraded my DHIS2 instance to version 2.41.4 and had trouble running analytics. A check at the catalina.out file indicated that an insert into a table (“analytics_tei_events_hx3joc2xxxx_2007_temp”) statement failed because of the error below
ERROR: value too long for type character(32);
I investigated further and discovered that the column “oucode” in the table “analytics_tei_events_hx3joc2xxxx_2007_temp” has the size 32, meanwhile the source column “code” in the organisationunit table has size 50. This means when a user enters an organization unit code of length above 32, say 50, into the code field while setting up an organizationunit, it will be accepted and stored by DHIS2, but when DHIS2 attempts to copy same value to the oucode column of the “analytics_tei_events_hx3joc2xxxx_2007_temp” table during analytics, it fails because the length of the copied value will be more than the 32 bits size set for the oucode column, this causes analytics to fail. See the screenshots below
How I resolved it in my instance
I queried the organisationunit table and fetched out all records whose value in the oucode column is longer than 32. I then edited the records to ensure the oucode values does not exceed 32 bits, saved the records, ran analytics again, and it worked.
My advise to the DHIS2 development team
If this has not already been corrected in subsequent patches, kindly increase the size of the oucode column in the “analytics_tei_events_id_year_temp” and “analytics_tei_events_id_year” tables to 50 to accommodate the values entered in the code column of the organisationunit table
The organisationunit table
The analytics_tei_events_id_year_temp table