I have a version 2.31 DHIS2 instance running fine on PostgreSQL version 10. A problem seen when upgrading to 2.32 and appears coming from the new Flyaway schema upgrading engine as related to work on the organizationunit table and changes to the way coordinates are handled:
Error in logs is :
Problem section is in the .sql file at point of cursor:
@stevie Which specific version of 2.31 are you using? (Minor versions until 2.31.3 have been released so far) . Also please let me know the version of your posgresql server.
The issue looks like you have an “unexpected” value in the existing “coordinates” column of organisationunit table.
You will get the same error if you execute the following statement select ST_SetSRID(ST_GeomFromGeoJSON('{"type":"MultiPolygon", "coordinates":' || coordinates || '}'), 4326) from organisationunit where featuretype in ('POLYGON', 'MULTI_POLYGON');
My guess is, there are rows in the organisationunit table which has featuretype column value as POLYGON or MULTI_POLYGON, but the corresponding coordinates column is maybe null (or not a valid geojson format).
So I checked the database. There are currently no MULTI_POLYGON record rows, just POLYGON. Some rows are featuretype: NONE, and coordinates: [[null]] I guess then the last option is that there must be some malformed GEOJSON in the coordinates column?
Is there any easy way to assess which row might be the culprit? Is it really necessary for the upgrade script to hang on such a thing?
@stevie The upgrade script is supposed to migrate the coordinate data from the existing column into a new geometry type column. Once that migration is successful, the old (obsolete) columns are dropped from the table. If the data in “coordinate” column is corrupted (or incompatible), upgrades would naturally fail as failing is the safest option rather than facing any data loss. Is it possible for you to export and share your organisationunit table to my dhis2 email for me to have a look? If not possible, let me know and I can look into alternatives.
@stevie Based on the assumption that you have malformed coordinates in your column. From the error, it looks like one or more “,” between different coordinate points in the polygon is missing. Can you execute the following statement and see if you get some results
select * from organisationunit where coordinates like '%][%';
It should return records which has atleast one missed “coma” between polygon points.
@stevie Thanks for sharing the table. I was able to find the issue with your data. Some of the coordinate values have some alphabets in between them (Probably a result of some manual import/export).
The affected rows can be seen using the following statement
select * from organisationunit where coordinates ~* '[a-zA-Z]';
There are 15 rows which has coordinates with segments like this [32.q5hODNmn021,-9.744] Looks like a uid has been accidentally injected into some of the coordinate strings.
You will have to correct these corrupted data the way you want it to be, and then retry on the upgrade.