Upgrading from 2.30 to 2.31dev(CI server)

Dear All,
I am experiencing this error below; upgrade note said to drop the SQL views but i don’t know how to do it. Please can someone help.

Migration V2_31_1__Migrations_for_release_v31.sql failed

SQL State : 0A000
Error Code : 0
Message : ERROR: cannot alter type of a column used by a view or rule
Detail: rule _RETURN on view _view_check_orgunit_open_close_dates_vs_data depends on column “openingdate”
Location : org/hisp/dhis/db/migration/2.31/V2_31_1__Migrations_for_release_v31.sql (/home/gerald/file:/var/lib/dhis2/hmis/webapps/hmis/WEB-INF/lib/dhis-support-db-migration-2.31.2-SNAPSHOT.jar!/org/hisp/dhis/db/migration/2.31/V2_31_1__Migrations_for_release_v31.sql)
Line : 115
Statement : alter table organisationunit
alter column openingdate TYPE date,
alter column closeddate TYPE date

at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:249)
at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:202)
at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:125)
at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:77)
at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:367)
... 91 more

Caused by: org.postgresql.util.PSQLException: ERROR: cannot alter type of a column used by a view or rule
Detail: rule _RETURN on view _view_check_orgunit_open_close_dates_vs_data depends on column “openingdate”
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:235)
at org.flywaydb.core.internal.sqlscript.StandardSqlStatement.execute(StandardSqlStatement.java:42)
at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:189)
… 94 more

1 Like

@Gerald_Thomas,

To drop SqL views, go to Data Administration -> Maintenance, then select “Drop SQL Views”

Regards
Calle

2 Likes

@Gerald_Thomas As Calle rightly pointed out, you need to drop all sql views as mentioned above before the upgrade. The views can be recreated after the upgrade is completed.

1 Like

Thanks Ameen and Calle;
The issue had been solved……

1 Like