Upgrading from 2.30 to 2.31

Support - Assistance technique
1

Has anyone experience this hitch on upgrading to version 2.31:

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException:
Migration V2_31_1__Migrations_for_release_v31.sql failed

SQL State : 42501
Error Code : 0
Message : ERROR: must be owner of function generate_uid
Location : org/hisp/dhis/db/migration/2.31/V2_31_1__Migrations_for_release_v31.sql (/home/dhis_test/file:/home/dhis_test/tomcat-dhis/webapps/ROOT/WEB-INF/lib/dhis-support-db-migration-2.31.7.jar!/org/hisp/dhis/db/migration/2.31/V2_31_1__Migrations_for_release_v31.sql)
Line : 1
Statement : --Function for creating uids.
create or replace function generate_uid() returns text as
$$
declare
chars text [] := ‘{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z}’;
result text := chars [11 + random() * (array_length(chars, 1) - 11)];
begin
for i in 1…10 loop
result := result || chars [1 + random() * (array_length(chars, 1) - 1)];
end loop;
return result;
end;
$$
language plpgsql

    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:274)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:220)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:127)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:70)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:366)
    ... 90 more

Caused by: org.postgresql.util.PSQLException: ERROR: must be owner of function generate_uid
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:244)
at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:111)
at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:208)

2 Likes
2

Hello Mike Bitz,

Make sure the user assigned to that database has proper permissions.

2 Likes
3

mike,

Ibrahim is correct in a sense, but this looks more specifically like your database has one or more objects with ownership different to the rest. Do the following to find out:

  1. access the database via the commandline, pgAdmin, or similar.

  2. Run the following script:
    SELECT * FROM pg_tables t WHERE schemaname = ‘public’;
    It will list all the tables with a column called “tableowner” showing the dominant owner name (which must be the same as the username you are using to access the database). For the next steps, I will assume that the owner or most or all tables is “dhis”. If you see ANY tables that is NOT owned by this user, then run the following for each of those tables:
    ALTER TABLE public. OWNER to dhis;
    The most common discrepant owner will probably be “postgres”, but it could be anything, depending on which user that created that table in the past.

It’s not that straightforward to check ownerships of functions, but the following will list ALL procedures in the database - including the around 700 functions (mostly Postgis) that were added to DHIS2 databases in 2.31
SELECT * FROM pg_proc
Look at the field “proowner”, and see what the owner id numbers are - in my dhis2 databases, there are only two different owner ids

Then you can check if any functions deviate from those two
SELECT * FROM pg_proc where proowner not in (10, 16393); (or whatever other numbers that dominate).
I bet you will find that the uid function has a different number (owner), as a minimum - but there might be others.

If you think that the uid function is the only one with a “wrong” owner, then simply execute the following:
ALTER FUNCTION public.uid() OWNER TO dhis; (or whatever the correct owner is)

After doing either 3-5 or 6, you can try to upgrade again.

Regards
Calle

2 Likes
4

Thank you, I updated ownership to ‘dhis’ for 7 tables (spatial_ref_sys,relationshipconstraint, relationshipitem,messageattachments, keyjsonvalueuseraccesses, keyjsonvalueusergroupaccesses and chart_yearlyseries). I also ran the processes 3 through 6. However, the error still persists.

Regards,
Mike

2 Likes
5

@mykbitz

If you get exactly the same error, then try to DELETE the generate_uid() function before upgrading. If it does not exist, it will be created in any case.

If that does not work either, then consider contacting @Ameen directly - he’s the Flyaway guru in the core dev team…

Regards
Calle

2 Likes
6

Thanks @Calle_Hedberg ,pardon my late response.Patch release 2.31.8 solved the issue.

1 Like
7

@mykbitz

I think you had multiple issues, actually - what you described initially was different to the two significant bugs that has been fixed in 2.31.8 (I encounter and reported both of them, including making temporary workarounds) - but together it sorted out your blocks.

So all good, then :slight_smile:

Regards
calle

2 Likes