Upgrading from 2.30 to 2.31dev(CI server)


(Gerald Thomas) #1

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


(Calle Hedberg) #2

@Gerald_Thomas,

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

Regards
Calle


(Ameen Mohamed) #3

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


(Gerald Thomas) #4

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