Issue upgrading from 2.31 to 2.32

Support - Assistance technique
1

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 :
11
Problem section is in the .sql file at point of cursor:
19

Any suggestions?

thanks

1 Like
2

@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.

1 Like
3

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).

1 Like
4

Version: 2.31.3, Revision: 99d7b74, Build date: Fri Apr 05 14:31:15 CEST 2019

PostgreSQL Version 10.

1 Like
5

@stevie Please check my second reply above as well. Can you confirm the values in the “coordinates” column?

1 Like
6

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?
40

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?

Thanks!

1 Like
7

@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.

1 Like
8

@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.

1 Like
10

This query returned 0 rows .

1 Like
11

@stevie I have sent you an email

1 Like
12

@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.

Let us know how it goes.

2 Likes
13

Dear Ameen,

Thanks so much for your assistance. We will work to edit these rows and try to run the update script again.

Kind regards
Steven

1 Like
14

I ended up deleting all of the geo data and will reimport after the upgrade via

update organisationunit
set
featuretype=null,
coordinates=null
where
featuretype=‘POLYGON’;

Once I did this it worked, but then I ran into this issue, which I did manage to fix after tweaking the PostgreSQL server settings.

You should probably review the file I sent you via email as there was clearly still something up with the geo data .

Thanks
S

1 Like
15

@stevie I found the remaining issues in the data. There are 2 more records returned if you execute this

select * from organisationunit where coordinates NOT LIKE '%]]]]' and coordinates is not null;

It looks like the string has been “clipped” at the end and makes it invalid.

Once this is corrected, this particular part of the migration will be successful. I have verified the same.

2 Likes