Dspace 7 Database migrate ERROR

[Saul Hernandez O.]

I am wanting to migrate my database from postgresql 9.5 to 11 on Dspace 7 , but when I run the command : ./dspace database migrate ignored, I get the following error. Can anybody help me?

[dspace@localhost bin]$ ./dspace database migrate ignored

Database URL: jdbc:postgresql://localhost:5432/dspace
Migrating database to latest version AND running previously "Ignored" migrations... (Check logs for details)
Migration exception:
java.sql.SQLException: Flyway migration error occurred
        at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:775)
        at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:662)
        at org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:195)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:277)
        at org.dspace.app.launcher.ScriptLauncher.handleScript(ScriptLauncher.java:133)
        at org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:98)
Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException:
Migration V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql failed
-----------------------------------------------------------------------------------------
SQL State  : 42703
Error Code : 0
Message    : ERROR: no existe la columna «resource_type_id»
Location   : org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql (/opt/backend/bin/file:/opt/backend/lib/dspace-api-7.1.jar!/org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql)
Line       : 16
Statement  : CREATE INDEX metadatavalue_resource_type_id_idx ON metadatavalue (resource_type_id)

        at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:369)
        at org.flywaydb.core.internal.command.DbMigrate.access$200(DbMigrate.java:54)
        at org.flywaydb.core.internal.command.DbMigrate$3.call(DbMigrate.java:282)
        at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:66)
        at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:279)
        at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:244)
        at org.flywaydb.core.internal.command.DbMigrate.access$100(DbMigrate.java:54)
        at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:162)
        at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:159)
        at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:71)
        at org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:99)
        at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:140)
        at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:159)
        at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:137)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:206)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:158)
        at org.flywaydb.core.Flyway.execute(Flyway.java:527)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:158)
        at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:762)
        ... 9 more
Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException:
Migration V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql failed
-----------------------------------------------------------------------------------------
SQL State  : 42703
Error Code : 0
Message    : ERROR: no existe la columna «resource_type_id»
Location   : org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql (/opt/backend/bin/file:/opt/backend/lib/dspace-api-7.1.jar!/org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql)
Line       : 16
Statement  : CREATE INDEX metadatavalue_resource_type_id_idx ON metadatavalue (resource_type_id)

        at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:274)
        at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:219)
        at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:127)
        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:88)
        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.access$000(SqlMigrationExecutor.java:33)
        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor$1.call(SqlMigrationExecutor.java:77)
        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor$1.call(SqlMigrationExecutor.java:74)
        at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:28)
        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:74)
        at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:366)
        ... 27 more
Caused by: org.postgresql.util.PSQLException: ERROR: no existe la columna «resource_type_id»
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2565)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2297)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        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:207)
        ... 35 more

thank you!

[Tim Donohue]

We'd need some more information here.  What version of DSpace are you upgrading to DSpace 7 from?  (Keep in mind you only need to run the "./dspace migrate" script when upgrading DSpace, not when upgrade Postgres).

Also, what does the "./dspace database info" script provide for your database?  It sounds like the migrate script thinks you are migrating from DSpace 5.x -> 7.x, but it's saying that you are missing a column named "resource_type_id" in the "metadatavalue" database table.

It's difficult to say what's going on here without more information.  Please send that info to this list, and hopefully someone can provide some advice.

Tim

[Saul Hernandez O.]

I am migrating from dspace 6.1 to 7, my postgre version is 9.5, my database (backup) is dspace.dat.

this is the script that throws me ./dspace info:

[dspace@localhost bin]$ ./dspace database info

Database Type: postgres

Database URL: jdbc:postgresql://localhost:5432/dspace

Database Schema: public
Database Username: dspace
Database Software: PostgreSQL version 11.14
Database Driver: PostgreSQL JDBC Driver version 42.2.24
PostgreSQL 'pgcrypto' extension installed/up-to-date? true (version=1.3)
FlywayDB Version: 6.5.7

I attach in a PNG more information that the database info command gives me..

thank you!

[Tim Donohue]

Hi,

It appears you've somehow stumbled on the same strange migration failure that this other email thread mentioned back in October: https://groups.google.com/g/dspace-tech/c/enhTBvnQm68/m/WnEgFm2-AwAJ

The only known fix that I have at this time is what is described in that email which I linked in.  You will have to manually update that "schema_version" table column to set the "success" column to "t" (true).  Somehow (and I've not been able to figure out how this has happened for some people), this specific migration ("V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql") was ignored for so long in some database setups that it became obsolete.  It should have run on your database back when you upgraded to DSpace 6.x (from 5.x), but somehow it was missed.  Now that you are upgrading to DSpace 7.x though, it is no longer necessary & can be skipped.... unfortunately the only way to currently skip it is to modify that "schema_version" table manually.

Again, see that original thread's email for more details.  Hopefully this helps you get past this strange error.

Tim

[msp...@willamette.edu]

Hi all,

I had a similar problem upgrading from 6.3 to 7.3 (main dev branch) but the details are a little different than in the earlier thread that Tim shared above. 

In my case, the 5.7 migration had been "ignored" previously and when I checked the "schema_version" table it was not listed, so unfortunately there was no opportunity to change the success column to "t". Here's the error during migration:

SQL State : 42703
Error Code : 0

Message : ERROR: column "resource_type_id" does not exist
Location : org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql (/opt/dspace-7/file:/opt/dspace-7/lib/dspace-api-7.3-SNAPSHOT.jar!/org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql)

Line : 16
Statement : CREATE INDEX metadatavalue_resource_type_id_idx ON metadatavalue (resource_type_id)

It looked like the file that caused my problem came from the "dspace-api-7.3-SNAPSHOT.jar" so I removed the V5.7 files from the .jar and used the revise jar instead. This time running /bin/dspace database migrate ignored resulted in a successful migration. 

I don't know it this is the best solution but it did work for me. (Also, I don't know if something changed in 7.3 to address the problem that was reported earlier.)