Database migrate problem while upgrading from 4.0 to 7.2

1,897 views
Skip to first unread message

Night Librarian

unread,
Feb 23, 2022, 12:20:31 AM2/23/22
to DSpace Technical Support
Sorry for the lengthy post!

I'm trying to migrate from 4.0 on Windows to 7.2 on Ubuntu 20.04.3 LTS.  I created a virtual machine and followed the instructions from https://wiki.lyrasis.org/display/DSDOC7x/Upgrading+DSpace, only to get stuck at the database update step :(

First, I imported the pg_dump from the old machine:

$psql --set ON_ERROR_STOP=on dspace < /temp/dspace.sql
====================================================================

Then:

$ ./dspace database info 

Database Type: postgres 

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

Database Schema: public 

Database Username: dspace 

Database Software: PostgreSQL version 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1) 

Database Driver: PostgreSQL JDBC Driver version 42.2.25 

PostgreSQL 'pgcrypto' extension installed/up-to-date? true (version=1.3) 

FlywayDB Version: 6.5.7 

  

+-----------+----------------+-----------------------------------------------------+------+--------------+---------+ 

| Category  | Version        | Description                                         | Type | Installed On | State   | 

+-----------+----------------+-----------------------------------------------------+------+--------------+---------+ 

| Versioned | 1.1            | Initial DSpace 1.1 database schema                  | SQL  |              | Pending | 

| Versioned | 1.2            | Upgrade to DSpace 1.2 schema                        | SQL  |              | Pending | 

| Versioned | 1.3            | Upgrade to DSpace 1.3 schema                        | SQL  |              | Pending | 

| Versioned | 1.3.9          | Drop constraint for DSpace 1 4 schema               | JDBC |              | Pending | 

| Versioned | 1.4            | Upgrade to DSpace 1.4 schema                        | SQL  |              | Pending | 

| Versioned | 1.5            | Upgrade to DSpace 1.5 schema                        | SQL  |              | Pending | 

| Versioned | 1.5.9          | Drop constraint for DSpace 1 6 schema               | JDBC |              | Pending | 

| Versioned | 1.6            | Upgrade to DSpace 1.6 schema                        | SQL  |              | Pending | 

| Versioned | 1.7            | Upgrade to DSpace 1.7 schema                        | SQL  |              | Pending | 

| Versioned | 1.8            | Upgrade to DSpace 1.8 schema                        | SQL  |              | Pending | 

| Versioned | 3.0            | Upgrade to DSpace 3.x schema                        | SQL  |              | Pending | 

| Versioned | 4.0            | Upgrade to DSpace 4.x schema                        | SQL  |              | Pending | 

| Versioned | 4.9.2015.10.26 | DS-2818 registry update                             | SQL  |              | Pending | 

| Versioned | 5.0.2014.08.08 | DS-1945 Helpdesk Request a Copy                     | SQL  |              | Pending | 

| Versioned | 5.0.2014.09.25 | DS 1582 Metadata For All Objects drop constraint    | JDBC |              | Pending | 

| Versioned | 5.0.2014.09.26 | DS-1582 Metadata For All Objects                    | SQL  |              | Pending | 

| Versioned | 5.0.2014.11.04 | Enable XMLWorkflow Migration                        | JDBC |              | Pending | 

| Versioned | 5.6.2016.08.23 | DS-3097                                             | SQL  |              | Pending | 

| Versioned | 5.7.2017.04.11 | DS-3563 Index metadatavalue resource type id column | SQL  |              | Pending | 

| Versioned | 5.7.2017.05.05 | DS 3431 Add Policies for BasicWorkflow              | JDBC |              | Pending | 

| Versioned | 6.0.2015.03.06 | DS 2701 Dso Uuid Migration                          | JDBC |              | Pending | 

| Versioned | 6.0.2015.03.07 | DS-2701 Hibernate migration                         | SQL  |              | Pending | 

| Versioned | 6.0.2015.08.31 | DS 2701 Hibernate Workflow Migration                | JDBC |              | Pending | 

| Versioned | 6.0.2015.09.01 | DS 2701 Enable XMLWorkflow Migration                | JDBC |              | Pending | 

| Versioned | 6.0.2016.01.03 | DS-3024                                             | SQL  |              | Pending | 

| Versioned | 6.0.2016.01.26 | DS 2188 Remove DBMS Browse Tables                   | JDBC |              | Pending | 

| Versioned | 6.0.2016.02.25 | DS-3004-slow-searching-as-admin                     | SQL  |              | Pending | 

| Versioned | 6.0.2016.04.01 | DS-1955 Increase embargo reason                     | SQL  |              | Pending | 

| Versioned | 6.0.2016.04.04 | DS-3086-OAI-Performance-fix                         | SQL  |              | Pending | 

| Versioned | 6.0.2016.04.14 | DS-3125-fix-bundle-bitstream-delete-rights          | SQL  |              | Pending | 

| Versioned | 6.0.2016.05.10 | DS-3168-fix-requestitem item id column              | SQL  |              | Pending | 

| Versioned | 6.0.2016.07.21 | DS-2775                                             | SQL  |              | Pending | 

| Versioned | 6.0.2016.07.26 | DS-3277 fix handle assignment                       | SQL  |              | Pending | 

| Versioned | 6.0.2016.08.23 | DS-3097                                             | SQL  |              | Pending | 

| Versioned | 6.1.2017.01.03 | DS 3431 Add Policies for BasicWorkflow              | JDBC |              | Pending | 

| Versioned | 7.0.2017.10.12 | DS-3542-stateless-sessions                          | SQL  |              | Pending | 

| Versioned | 7.0.2018.04.03 | Upgrade Workflow Policy                             | JDBC |              | Pending | 

| Versioned | 7.0.2018.04.16 | dspace-entities                                     | SQL  |              | Pending | 

| Versioned | 7.0.2018.06.07 | DS-3851-permission                                  | SQL  |              | Pending | 

| Versioned | 7.0.2019.05.02 | DS-4239-workflow-xml-migration                      | SQL  |              | Pending | 

| Versioned | 7.0.2019.06.14 | scripts-and-process                                 | SQL  |              | Pending | 

| Versioned | 7.0.2019.07.31 | Retrieval of name variant                           | SQL  |              | Pending | 

| Versioned | 7.0.2019.11.13 | relationship type copy left right                   | SQL  |              | Pending | 

| Versioned | 7.0.2020.01.08 | DS-626-statistics-tracker                           | SQL  |              | Pending | 

| Versioned | 7.0.2020.10.31 | CollectionCommunity Metadata Handle                 | JDBC |              | Pending | 

| Versioned | 7.0.2021.01.22 | Remove basic workflow                               | SQL  |              | Pending | 

| Versioned | 7.0.2021.02.08 | tilted rels                                         | SQL  |              | Pending | 

| Versioned | 7.0.2021.03.18 | Move entity type to dspace schema                   | SQL  |              | Pending | 

| Versioned | 7.0.2021.09.24 | Move entity type from item template to collection   | SQL  |              | Pending | 

| Versioned | 7.0.2021.10.04 | alter collection table drop workflow stem columns   | SQL  |              | Pending | 

| Versioned | 7.1.2021.10.18 | Fix MDV place after migrating from DSpace 5         | SQL  |              | Pending | 

+-----------+----------------+-----------------------------------------------------+------+--------------+---------+ 

NOTE: This database is NOT yet initialized for auto-migrations (via Flyway). 

Your database looks to be compatible with DSpace version 4.0 

All upgrades *after* version 4.0 will be run during the next migration. 

If you'd like to upgrade now, simply run 'dspace database migrate'. 

====================================================================

Then, got an error after trying to update sequences:

$psql -U dspace -f /temp/DSpace-dspace-7.2/dspace-api/target/classes/org/dspace/storage/rdbms/sqlmigration/postgres/update-sequences.sql dspace

<...>
psql:/temp/DSpace-dspace-7.2/dspace-api/target/classes/org/dspace/storage/rdbms/sqlmigration/postgres/update-sequences.sql:36: ERROR:  relation "handle_id_seq" does not exist

LINE 1: SELECT setval('handle_id_seq', max(handle_id)) FROM handle;
<...>

====================================================================

Then, tried update-sequences in a different way:

 $./dspace database update-sequences

Running org/dspace/storage/rdbms/sqlmigration/postgres/update-sequences.sql

Caught exception:

org.postgresql.util.PSQLException: ERROR: relation "handle_id_seq" does not exist

  Position: 16

        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.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:353)

        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) 

====================================================================

The last thing I did was:

$ ./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.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql failed

----------------------------------------------------------------------

SQL State  : 21000

Error Code : 0

Message    : ERROR: more than one row returned by a subquery used as an expression

Location   : org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql (/dspace/bin/file:/dspace/lib/dspace-api-7.2.jar!/org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql)

Line       : 74

Statement  : INSERT INTO metadatavalue (resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)

SELECT

community_id AS resource_id,

4 AS resource_type_id,

(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'title' and qualifier is null) AS metadata_field_id,

name AS text_value,

null AS text_lang,

0 AS place

FROM community where not name is null

 

        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.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql failed

----------------------------------------------------------------------

SQL State  : 21000

Error Code : 0

Message    : ERROR: more than one row returned by a subquery used as an expression

Location   : org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql (/dspace/bin/file:/dspace/lib/dspace-api-7.2.jar!/org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql)

Line       : 74

Statement  : INSERT INTO metadatavalue (resource_id, resource_type_id, metadata_field_id, text_value, text_lang, place)

SELECT

community_id AS resource_id,

4 AS resource_type_id,

(select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'title' and qualifier is null) AS metadata_field_id,

name AS text_value,

null AS text_lang,

0 AS place

FROM community where not name is null

 

        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: more than one row returned by a subquery used as an expression

        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

 ====================================================================

Dspace.log after migration attempt shows this:

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @ Loading Flyway DB migrations from: classpath:org/dspace/storage/rdbms/sqlmigration/postgres, classpath:org/dspace/storage/rdbms/migration, classpath:org/dspace/storage/rdbms/xmlworkflow 

INFO  unknown unknown org.flywaydb.core.internal.database.DatabaseFactory @ Database: jdbc:postgresql://localhost:5432/dspace (PostgreSQL 12.9) 

INFO  unknown unknown org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory @ Creating Schema History table "public"."schema_version" with baseline ... 

INFO  unknown unknown org.flywaydb.core.internal.command.DbBaseline @ Successfully baselined schema with version: 4.0 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @ Pending DSpace database schema migrations: 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      4.9.2015.10.26 DS-2818 registry update SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      5.0.2014.08.08 DS-1945 Helpdesk Request a Copy SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      5.0.2014.09.25 DS 1582 Metadata For All Objects drop constraint JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      5.0.2014.09.26 DS-1582 Metadata For All Objects SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      5.0.2014.11.04 Enable XMLWorkflow Migration JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      5.6.2016.08.23 DS-3097 SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      5.7.2017.04.11 DS-3563 Index metadatavalue resource type id column SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      5.7.2017.05.05 DS 3431 Add Policies for BasicWorkflow JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2015.03.06 DS 2701 Dso Uuid Migration JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2015.03.07 DS-2701 Hibernate migration SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2015.08.31 DS 2701 Hibernate Workflow Migration JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2015.09.01 DS 2701 Enable XMLWorkflow Migration JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.01.03 DS-3024 SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.01.26 DS 2188 Remove DBMS Browse Tables JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.02.25 DS-3004-slow-searching-as-admin SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.04.01 DS-1955 Increase embargo reason SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.04.04 DS-3086-OAI-Performance-fix SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.04.14 DS-3125-fix-bundle-bitstream-delete-rights SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.05.10 DS-3168-fix-requestitem item id column SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.07.21 DS-2775 SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.07.26 DS-3277 fix handle assignment SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.0.2016.08.23 DS-3097 SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      6.1.2017.01.03 DS 3431 Add Policies for BasicWorkflow JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2017.10.12 DS-3542-stateless-sessions SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2018.04.03 Upgrade Workflow Policy JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2018.04.16 dspace-entities SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2018.06.07 DS-3851-permission SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2019.05.02 DS-4239-workflow-xml-migration SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2019.06.14 scripts-and-process SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2019.07.31 Retrieval of name variant SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2019.11.13 relationship type copy left right SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2020.01.08 DS-626-statistics-tracker SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2020.10.31 CollectionCommunity Metadata Handle JDBC PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2021.01.22 Remove basic workflow SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2021.02.08 tilted rels SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2021.03.18 Move entity type to dspace schema SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2021.09.24 Move entity type from item template to collection SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.0.2021.10.04 alter collection table drop workflow stem columns SQL PENDING 

INFO  unknown unknown org.dspace.storage.rdbms.DatabaseUtils @      7.1.2021.10.18 Fix MDV place after migrating from DSpace 5 SQL PENDING 

INFO  unknown unknown org.flywaydb.core.internal.command.DbValidate @ Successfully validated 51 migrations (execution time 00:00.119s) 

INFO  unknown unknown org.flywaydb.core.internal.command.DbMigrate @ Current version of schema "public": 4.0 

WARN  unknown unknown org.flywaydb.core.internal.command.DbMigrate @ outOfOrder mode is active. Migration of schema "public" may not be reproducible. 

INFO  unknown unknown org.flywaydb.core.internal.command.DbMigrate @ Migrating schema "public" to version 4.9.2015.10.26 - DS-2818 registry update 

INFO  unknown unknown org.flywaydb.core.internal.command.DbMigrate @ Migrating schema "public" to version 5.0.2014.08.08 - DS-1945 Helpdesk Request a Copy 

INFO  unknown unknown org.flywaydb.core.internal.command.DbMigrate @ Migrating schema "public" to version 5.0.2014.09.25 - DS 1582 Metadata For All Objects drop constraint 

INFO  unknown unknown org.flywaydb.core.internal.command.DbMigrate @ Migrating schema "public" to version 5.0.2014.09.26 - DS-1582 Metadata For All Objects 

ERROR unknown unknown org.flywaydb.core.internal.command.DbMigrate @ Migration of schema "public" to version 5.0.2014.09.26 - DS-1582 Metadata For All Objects failed! Changes successfully rolled

INFO  unknown unknown org.springframework.cache.ehcache.EhCacheManagerFactoryBean @ Shutting down EhCache CacheManager 'org.dspace.services' 

 ====================================================================
I read previous posts about both "Migration V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql failed" and "relation "handle_id_seq" does not exist" errors, but reverting to previous snapshots and retrying the database migration still doesn't work for me yet.  Your suggestions will be most welcome!

Tim Donohue

unread,
Feb 28, 2022, 11:22:13 AM2/28/22
to DSpace Technical Support
Hi,

Unfortunately, since you are on such an old version of DSpace (v4), the newer DSpace 7 "update-sequences" scripts will not work on your database until you successfully upgrade.  Instead, to update sequences on a database that's currently compatible with DSpace 4.x, you would need to manually run the *older* "update-sequences.sql" that came with DSpace 4.x.  Here it is: https://github.com/DSpace/DSpace/blob/dspace-4_x/dspace/etc/postgres/update-sequences.sql

With the migration that is failing...it looks like this is the SQL statement that is failing with "ERROR: more than one row returned by a subquery used as an expression": https://github.com/DSpace/DSpace/blob/dspace-7.2/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql#L74-L82  (As you may notice the error says it's the statement starting on line 74)

Based on the error, it appears to me that the problem is that the internal SELECT on line 78 is returning *multiple values* instead of the expected *single value*.  This would mean that somehow your DSpace 4.x database has **two different** "dc.title" fields in the "metadatafieldregistry" table.   You would need to remove one of them, as *every field must be uniquely named* in the "metadatafieldregistry".  

Once you remove the duplicate field and run the old "update-sequences.sql", hopefully the migration to DSpace 7.x will succeed.  

If you run into any other issues, let us know on this list.

Tim

Night Librarian

unread,
Feb 28, 2022, 11:59:48 PM2/28/22
to DSpace Technical Support
Thank you very much, Tim.

The first part is fixed.  I replaced the 7.2 update-sequences.sql by the dspace-4.x version and now it works great:

$ ./dspace database update-sequences 

Running org/dspace/storage/rdbms/sqlmigration/postgres/update-sequences.sql 

update-sequences complete 

==========================================

As to the $./dspace database migrate ignored part, I checked the metadatafieldregistry table and it had 2 rows for title:

metadata_field_id   metadata_schema_id   element   qualifier          scope_note
76                              1                                        title           [null]               A name given to the resource
77                              1                                        title           alternative     An alternate name given to the resource

I wasn't sure which to delete, so I tried to delete the alternative one:

delete from metadatafieldregistry 

where  metadata_field_id = 77; 

 

and got this: 

 

ERROR: update or delete on table "metadatafieldregistry" violates foreign key constraint "metadatavalue_metadata_field_id_fkey" on table "metadatavalue" DETAIL: Key (metadata_field_id)=(77) is still referenced from table "metadatavalue". SQL state: 23503 


Then I looked at the metadatavalue table and it has 1500 rows with metadata_field_id = 76 and only 28 rows with 77.  I decided to delete these 28 rows, after which I was able to delete metadata_field_id = 77 in the metadatafieldregistry.

However, when I try to run $ ./dspace database migrate ignored, I get the exact same errors as before.  The dspace.log this time collected more info, and I am attaching it here.

What else could I try?
dspace.log

Tim Donohue

unread,
Mar 1, 2022, 10:27:27 AM3/1/22
to DSpace Technical Support
Hi,

You misunderstood my second point in my previous message.  The error you got initially was saying that somehow you have two "dc.title" fields in your database (which shouldn't happen).  So, the problem wasn't the "dc.title.alternative" field, as that is a different field from "dc.title"...it's that you may have two "dc.title" fields (exact same name).

Try this query and see if it returns multiple results (this is the query that is being run by the migration to find your "dc.title" field):

select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'title' and qualifier is null;

This query won't change anything in your system, but it sounds like it might return *2 results* instead of the expected *1 result*.

If it does return *2 results*, you need to figure out how you have two different "dc.title" fields, and remove one of them.
This may require determining which is being used to store metadata (as you cannot delete a field that is being used -- this is the error you got when trying to delete "dc.title.alternative").
If both fields are in use, then you would need to migrate all existing "dc.title" values from one field to the other.

Overall, this is a strange scenario, as I've never heard of a case where a DSpace had two of the same metadata field. Hopefully it'll be easy enough to resolve, once you find the duplicate fields.

Let us know what you find on this list,
Tim

Night Librarian

unread,
Mar 1, 2022, 1:26:36 PM3/1/22
to DSpace Technical Support
I see now.  When I run select metadata_field_id from metadatafieldregistry where metadata_schema_id=(select metadata_schema_id from metadataschemaregistry where short_id='dc') and element = 'title' and qualifier is null;, it returns 64 and 76.

64;1;"title";"";"Title statement/title proper."
76;1;"title";"";"A name given to the resource"

select * from metadatavalue where metadata_field_id = '64';  shows  that it's used in 348 rows.
select * from metadatavalue where metadata_field_id = '76';  shows  that it's used in 1518 rows.

Could you suggest a way of merging these values into one?  Does it matter into which one?



Tim Donohue

unread,
Mar 1, 2022, 1:35:47 PM3/1/22
to Night Librarian, DSpace Technical Support
Unfortunately, I don't have a great suggestion here as this is an odd scenario.  DSpace shouldn't let you create two of the exact same metadata fields, so I'm not sure how this happened.

I'd recommend looking more closely at the values in each of those fields and trying to determine which one is the correct "dc.title".

As a "guess", it might be the one which says "Title statement/title proper." since that's the default description for "dc.title" in DSpace 4.x: https://github.com/DSpace/DSpace/blob/dspace-4_x/dspace/config/registries/dublin-core-types.xml#L562-L568

However, before you do anything, I'd highly recommend backing up your database.  That way, if you make a mistake and choose the wrong "dc.title" (or things break after you move your data), then you can revert and try again.

Tim
From: dspac...@googlegroups.com <dspac...@googlegroups.com> on behalf of Night Librarian <stani...@gmail.com>
Sent: Tuesday, March 1, 2022 12:26 PM
To: DSpace Technical Support <dspac...@googlegroups.com>
Subject: [dspace-tech] Re: Database migrate problem while upgrading from 4.0 to 7.2
 
--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to a topic in the Google Groups "DSpace Technical Support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/dspace-tech/UcAuyYjZiw4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to dspace-tech...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/8bfaaa8e-e3fc-4506-b38a-38e2fc50da11n%40googlegroups.com.

Night Librarian

unread,
Mar 1, 2022, 3:21:54 PM3/1/22
to DSpace Technical Support
Many thanks.

I have no problem merging 76 ("A name given to the resource") into 64 ("Title statement/title proper").  I exported the database into a sandbox virtual machine, so I can experiment with the upgrade and revert to snapshots, until I get it right.  Just wondering how to merge/combine them?



Mark H. Wood

unread,
Mar 2, 2022, 7:49:38 AM3/2/22
to dspac...@googlegroups.com
On Tue, Mar 01, 2022 at 06:35:39PM +0000, 'Tim Donohue' via DSpace Technical Support wrote:
> Unfortunately, I don't have a great suggestion here as this is an odd scenario. DSpace shouldn't let you create two of the exact same metadata fields, so I'm not sure how this happened.

Clearly, we should have a UNIQUE index over (metadata_schema_id,
element, qualifier) on metadatafieldregistry.

> I'd recommend looking more closely at the values in each of those fields and trying to determine which one is the correct "dc.title".
>
> As a "guess", it might be the one which says "Title statement/title proper." since that's the default description for "dc.title" in DSpace 4.x: https://github.com/DSpace/DSpace/blob/dspace-4_x/dspace/config/registries/dublin-core-types.xml#L562-L568

One thing I'd want to determine is whether there are objects that have
*both* fields defined. If so then you probably need to go through
them one by one and decide which title you want to keep. DELETE the
unwanted metadatavalue and (if necessary) UPDATE the other to have the
correct metadata_field_id.

You should be able to do this in the DSpace user interface, as
administrator, if that's more comfortable than hacking on the database
directly, but you'll have to copy values that must move rather than
updating their field IDs in place.

Otherwise you can just decide which field to keep and UPDATE the other
metadatavalue records to the correct metadata_field_id, then delete
the unwanted field from the registry.

> However, before you do anything, I'd highly recommend backing up your database. That way, if you make a mistake and choose the wrong "dc.title" (or things break after you move your data), then you can revert and try again.

Yes, indeed!
> To unsubscribe from this group and all its topics, send an email to dspace-tech...@googlegroups.com<mailto:dspace-tech...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/8bfaaa8e-e3fc-4506-b38a-38e2fc50da11n%40googlegroups.com<https://groups.google.com/d/msgid/dspace-tech/8bfaaa8e-e3fc-4506-b38a-38e2fc50da11n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> --
> All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
> ---
> You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/SA2PR22MB2698B8CCB81BC10F82D83610ED029%40SA2PR22MB2698.namprd22.prod.outlook.com.

--
Mark H. Wood
Lead Technology Analyst

University Library
Indiana University - Purdue University Indianapolis
755 W. Michigan Street
Indianapolis, IN 46202
317-274-0749
www.ulib.iupui.edu
signature.asc

Tim Donohue

unread,
Mar 2, 2022, 10:42:22 AM3/2/22
to Night Librarian, DSpace Technical Support
Hi,

Merging the two fields could be done in a few ways.  

If you wanted to do it at the database level all at once (make sure to backup first!), you could just move all the values in "metadatavalue" table from "metadata_field_id" 76 into field 64.  Then, remove field 76 and do a full reindex (./dspace index-discovery -b) of the site.  Finally, you'd want to likely test the site again to ensure that moving those values had no negative impact on the Items that used to use field 76.

If you don't want to do this at the database level, you might be able to use Batch Metadata Editing tools to export metadata to CSV, move it and reimport: https://wiki.lyrasis.org/display/DSDOC7x/Batch+Metadata+Editing  Though the challenge there would be differentiating field 76 from 64, as they both may appear as "dc.title".  So, it may be difficult to tell which field you are moving values into...but as long as you get all the values into the same field (whether 76 or 64), then you should be able to remove the other field.

Tim

Sent: Tuesday, March 1, 2022 2:21 PM

To: DSpace Technical Support <dspac...@googlegroups.com>
Subject: Re: [dspace-tech] Re: Database migrate problem while upgrading from 4.0 to 7.2
 
You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/c3407f74-5f6f-43f3-bbdc-f803095812f0n%40googlegroups.com.

Night Librarian

unread,
Mar 24, 2022, 8:51:13 PM3/24/22
to DSpace Technical Support
With your help, I repeated the update process and moved one step ahead, but am stuck at running the index-discovery -b.  So far,  I did this:

- installed DSpace 7.2 and imported the dump from the old machine into the new one

- replaced the 7.2 version of update-sequences.sql by the 4.x one

- in Postgres, ran:

UPDATE metadatavalue
SET metadata_field_id = 64
WHERE metadata_field_id = 76;

and then:
DELETE from metadatafieldregistry
WHERE metadata_field_id = '76';

- ran $./dspace database update-sequences

- copied assetstore from the 4.x to 7.2 machine

So far, so good, but when I run: 

$ ./dspace index-discovery -b

I have the following message:

org.apache.commons.cli.ParseException: Unable to create a new DSpace Context: null

at org.dspace.discovery.IndexClient.setup(IndexClient.java:157)

at org.dspace.scripts.DSpaceRunnable.parse(DSpaceRunnable.java:86)

at org.dspace.scripts.DSpaceRunnable.initialize(DSpaceRunnable.java:75)

at org.dspace.app.launcher.ScriptLauncher.executeScript(ScriptLauncher.java:148)

at org.dspace.app.launcher.ScriptLauncher.handleScript(ScriptLauncher.java:131)

at org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:98)


Can you see whether I missed a step somewhere which now prevents me from reindexing?

Tim Donohue

unread,
Mar 25, 2022, 10:25:32 AM3/25/22
to Night Librarian, DSpace Technical Support
Hi,

Before you can reindex, you need to make sure that DSpace starts up properly.  Make sure that both Tomcat & Solr are running, and that there are no errors in your DSpace logs.  The error you received from "./dspace index-discovery -b" implies that, either Tomcat or Solr was not started...or there was a major error in your "dspace.log" file that was blocking indexing.

Tim
Sent: Thursday, March 24, 2022 7:51 PM

To: DSpace Technical Support <dspac...@googlegroups.com>
Subject: Re: [dspace-tech] Re: Database migrate problem while upgrading from 4.0 to 7.2
--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to a topic in the Google Groups "DSpace Technical Support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/dspace-tech/UcAuyYjZiw4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to dspace-tech...@googlegroups.com.

Night Librarian

unread,
Aug 26, 2022, 11:39:52 AM8/26/22
to DSpace Technical Support
Greetings,

Last time I was attempting the migration, I got bogged down at the reindexing stage, so I am now trying from scratch.  I have the HAL browser working, SOLR is working, I also fixed the problem with duplicate "dc.title" field that I had before. Now I am stuck at the update-sequences step.  Last time, I was able to download the old v4 update-sequences.sql and run it successfully (bug ):

$ ./dspace database update-sequences 

Running org/dspace/storage/rdbms/sqlmigration/postgres/update-sequences.sql 

update-sequences complete 


This time, I downloaded the v4 of the file and tried to run it, but I don't remember where I need to put the file and how to properly run the command.  Could you refresh me?

Many thanks in advance!

Night Librarian

unread,
Aug 27, 2022, 11:16:52 PM8/27/22
to DSpace Technical Support
So, since I re-started the migration/upgrade process from the beginning, I re-downloaded the 4.x version update-sequences.sql from https://github.com/DSpace/DSpace/blob/dspace-4_x/dspace/etc/postgres/update-sequences.sql like I did in March and placed it in /temp/DSpace-dspace-7.2/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres.  What I don't understand, is that in March, after replacing the 7.x version by 4.x, it completed successfully:

$ ./dspace database update-sequences 

Running org/dspace/storage/rdbms/sqlmigration/postgres/update-sequences.sql 

update-sequences complete 


Today, the terminal shows this:

Running org/dspace/storage/rdbms/sqlmigration/postgres/update-sequences.sql 

Caught exception: 

org.postgresql.util.PSQLException: ERROR: relation "handle_id_seq" does not exist 

  Position: 16 

   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.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:353) 

   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) 


It's the exact error I got in March before I downloaded the 4.x update-sequences.sql and ran the command successfully.  This time, it doesn't work.  Am I using a wrong directory to store the file?  Could there be a problem with permissions?  What else am I missing?


Tim Donohue

unread,
Aug 29, 2022, 11:37:32 AM8/29/22
to DSpace Technical Support
Hi,

You are running the wrong "update-sequences.sql" script (as that "handle_id_seq" is in the v7 script, not the v4 one)... likely because you tried to copy the v4 one into your v7 codebase.  That won't work, because DSpace 7 will likely use the update-sequences.sql script that is embedded in dspace-api.jar.

Instead, if you need to run the v4 update-sequences.sql manually by just doing something like this:

psql -U dspace -f full/path/to/update-sequences.sql

(This is assuming your DSpace database user account is named "dspace".  If it isn't, then you need to change the "-U" param.  For more information on psql see https://www.postgresql.org/docs/current/app-psql.html)

Tim

Night Librarian

unread,
Aug 29, 2022, 6:39:05 PM8/29/22
to DSpace Technical Support
Thank you, Tim.

When I run the psql command, pointing to the v4 update-sequences file, I get the following list:

$ psql -U dspace -f /temp/update-sequences.sql  

setval  

-------- 

     79 

(1 row) 

  

setval  

-------- 

     90 

(1 row) 

  

setval  

-------- 

   5274 

(1 row) 

  

setval  

-------- 

    120 

(1 row) 

  

setval  

-------- 

    591 

(1 row) 

  

setval  

-------- 

    692 

(1 row) 

  

setval  

-------- 

162402 

(1 row) 

  

setval  

-------- 

   2702 

(1 row) 

  

setval  

-------- 

   4407 

(1 row) 

  

setval  

-------- 

   4407 

(1 row) 

  

setval  

-------- 

   5226 

(1 row) 

  

setval  

-------- 

  49969 

(1 row) 

  

setval  

-------- 

     86 

(1 row) 

  

setval  

-------- 

     51 

(1 row) 

  

setval  

-------- 

    200 

(1 row) 

  

setval  

-------- 

    200 

(1 row) 

  

setval  

-------- 

   2601 

(1 row) 

  

setval  

-------- 

132708 

(1 row) 

  

setval  

-------- 

    229 

(1 row) 

  

setval  

-------- 

   2711 

(1 row) 

  

setval  

-------- 

   2083 

(1 row) 

  

setval  

-------- 

   9488 

(1 row) 

  

setval  

-------- 

    207 

(1 row) 

  

setval  

-------- 

      8 

(1 row) 

  

setval  

-------- 

        

(1 row) 

  

setval  

-------- 

        

(1 row) 

  

setval  

-------- 

    100 

(1 row) 

  

setval  

-------- 

  49969 

(1 row) 

  

setval  

-------- 

      5 

(1 row) 

  

setval  

-------- 

        

(1 row) 

  

setval  

-------- 

        

(1 row) 

  

setval  

-------- 

    419 

(1 row) 

  

setval  

-------- 

      6 

(1 row) 

  

setval  

-------- 

   2216 

(1 row) 


==========================================================================
Then, just to make sure, I check the database status, which looks the same as it was before I run the update-sequences:

$ sudo ./dspace database status 

Database Type: postgres 

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

Database Schema: public 

Database Username: dspace 

Database Software: PostgreSQL version 12.12 (Ubuntu 12.12-0ubuntu0.20.04.1) 

==========================================================================

And then, when I run database migrate ignored, I get an error about non-existing "checksum_history" table and "Migration V6.0_2015.03.07__DS-2701_Hibernate_migration.sql failed". The dspace.log (attached) shows (if I read it correctly), no errors until migration to version 6.0.2015.03.06, and then says:

"ERROR unknown unknown org.flywaydb.core.internal.command.DbMigrate @ Migration of schema "public" to version 6.0.2015.03.07 - DS-2701 Hibernate migration failed! Changes successfully rolled back." 

$ sudo ./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 V6.0_2015.03.07__DS-2701_Hibernate_migration.sql failed 

----------------------------------------------------------------- 

SQL State  : 42P01 

Error Code : 0 

Message    : ERROR: relation "checksum_history" does not exist 

Location   : org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql (/dspace/bin/file:/dspace/lib/dspace-api-7.2.jar!/org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql) 

Line       : 455 

Statement  : ALTER TABLE checksum_history RENAME COLUMN bitstream_id to bitstream_legacy_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 V6.0_2015.03.07__DS-2701_Hibernate_migration.sql failed 

----------------------------------------------------------------- 

SQL State  : 42P01 

Error Code : 0 

Message    : ERROR: relation "checksum_history" does not exist 

Location   : org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql (/dspace/bin/file:/dspace/lib/dspace-api-7.2.jar!/org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql) 

Line       : 455 

Statement  : ALTER TABLE checksum_history RENAME COLUMN bitstream_id to bitstream_legacy_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: relation "checksum_history" does not exist 

   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 

==========================================================================



Maybe I need to try to upgrade to install DSpace 6.x first and upgrade to it, before upgrading to 7? Or is there something else I could try?
dspace.log after database migrate ignored.txt

Tim Donohue

unread,
Aug 30, 2022, 11:22:39 AM8/30/22
to Night Librarian, DSpace Technical Support
Hi,

First, it looks like the update-sequences was successful. That's what it's output looks like.

When you ran the "database migrate", it looks like that key error is the one about the non-existing "checksum_history" table.  That table SHOULD EXIST in your DSpace 4 database (it's been in DSpace since v1.4, according to the code).  

So, it seems either you somehow removed it from your DSpace 4 database, or maybe you have some sort of permissions error where that table is not being "seen" by DSpace 7?  It's hard to say, but you need to check to see if that table exists or not first.

You could also try to do an upgrade to 5.x or 6.x first...but, I think you may hit this same error eventually until you figure out what happened to your "checksum_history" table.

Tim

Sent: Monday, August 29, 2022 5:39 PM

To: DSpace Technical Support <dspac...@googlegroups.com>
Subject: Re: [dspace-tech] Re: Database migrate problem while upgrading from 4.0 to 7.2
--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to a topic in the Google Groups "DSpace Technical Support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/dspace-tech/UcAuyYjZiw4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to dspace-tech...@googlegroups.com.

Night Librarian

unread,
Aug 30, 2022, 11:57:49 AM8/30/22
to DSpace Technical Support
Thank you, Tim.

It looks like the checksum_history table is missing in my 4.x.  Not sure how that happened.  Is there something I can do at this stage?

bi_2_dis    postgres        
bi_2_dmap    postgres        
bi_4_dis    postgres        
bi_4_dmap    postgres        
bi_item    postgres        
bi_private    postgres        
bi_withdrawn    postgres        
bitstream    postgres        
bitstreamformatregistry    postgres        
bundle    postgres        
bundle2bitstream    postgres        
checksum_results    postgres        
collection    postgres        
collection2item    postgres        
collection_item_count    postgres        
communities2item    postgres        
community    postgres        
community2collection    postgres        
community2community    postgres        
community_item_count    postgres        
doi    postgres        
eperson    postgres        
epersongroup    postgres        
epersongroup2eperson    postgres        
epersongroup2workspaceitem    postgres        
fileextension    postgres        
group2group    postgres        
group2groupcache    postgres        
handle    postgres        
harvested_collection    postgres        
harvested_item    postgres        
item    postgres        
item2bundle    postgres        
metadatafieldregistry    postgres        
metadataschemaregistry    postgres        
metadatavalue    postgres        
most_recent_checksum    postgres        
registrationdata    postgres        
requestitem    postgres        
resourcepolicy    postgres        
subscription    postgres        
tasklistitem    postgres        
versionhistory    postgres        
versionitem    postgres        
webapp    postgres        
workflowitem    postgres        
workspaceitem    postgres

Tim Donohue

unread,
Aug 30, 2022, 12:29:42 PM8/30/22
to Night Librarian, DSpace Technical Support
Hi,

Hmm... that's concerning.  Hopefully that's the only table you are somehow missing.

Nonetheless, I think you should be able to just create it using the proper lines in the DSpace 1.4 upgrade script at https://github.com/DSpace/DSpace/blob/main/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V1.4__Upgrade_to_DSpace_1.4_schema.sql

Specifically, I think you'll need to run these lines (lines 165-174) on your database:

CREATE TABLE checksum_history
(
    check_id BIGSERIAL PRIMARY KEY,  
    bitstream_id INTEGER,
    process_start_date TIMESTAMP,
    process_end_date TIMESTAMP,
    checksum_expected VARCHAR,
    checksum_calculated VARCHAR,
    result VARCHAR REFERENCES checksum_results(result_code)
);

It looks like​ at a glance you have the other "checksum" related tables.  So, hopefully just creating this one table will be enough to get you upgraded successfully.  However, if you hit another missing table error, you may need to look again at your database to see whether you are missing other tables.

Tim

Sent: Tuesday, August 30, 2022 10:57 AM
You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/62fcb1b4-3aa6-4e8f-adee-466ccb856a06n%40googlegroups.com.

Night Librarian

unread,
Aug 30, 2022, 2:31:18 PM8/30/22
to DSpace Technical Support
Should I be running:

./dspace database /temp/DSpace-dspace-7.2/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V1.4_Upgrade_to_Dspace_1.4_schema.sql ?

 Or is there a psql command I would need?

Tim Donohue

unread,
Aug 30, 2022, 2:43:29 PM8/30/22
to Night Librarian, DSpace Technical Support
You won't be able to run any DSpace command to fix this, as DSpace doesn't have a way to recreate individual tables. 

You'd have to use psql (or other Postgres tools) to run that single CREATE TABLE statement. You won't be able to run that entire v1.4 upgrade script, because you are missing only one table in that entire script -- so trying to run that entire script will more than likely throw errors on all other tables.

Here's the docs for psql. You may need to use the "-c" flag or create a custom *.sql file (including that one CREATE TABLE command) and use the "-f" flag. https://www.postgresql.org/docs/current/app-psql.html

Tim
Sent: Tuesday, August 30, 2022 1:31 PM

To: DSpace Technical Support <dspac...@googlegroups.com>
Subject: Re: [dspace-tech] Re: Database migrate problem while upgrading from 4.0 to 7.2
Should I be running:

./dspace database /temp/DSpace-dspace-7.2/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V1.4_Upgrade_to_Dspace_1.4_schema.sql ?

 Or is there a psql command I would need?

--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.

Night Librarian

unread,
Aug 30, 2022, 6:35:12 PM8/30/22
to DSpace Technical Support
Thank you, Tim!

I added the checksum_history table and ran the process again.  This time, the output is much shorter and it includes the magic word "Done"!   However, it also talks about some exception in thread-6.  I am also attaching the log.  Should I be worrying about it and if not, what should be my next step?

$ sudo ./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) 

Done. 

Exception in thread "Thread-6" java.lang.IllegalStateException: EntityManagerFactory is closed 

   at org.hibernate.internal.SessionFactoryImpl.validateNotClosed(SessionFactoryImpl.java:548) 

   at org.hibernate.internal.SessionFactoryImpl.getCache(SessionFactoryImpl.java:848) 

   at org.hibernate.internal.AbstractSharedSessionContract.<init>(AbstractSharedSessionContract.java:153) 

   at org.hibernate.internal.AbstractSessionImpl.<init>(AbstractSessionImpl.java:29) 

   at org.hibernate.internal.SessionImpl.<init>(SessionImpl.java:223) 

   at org.hibernate.internal.SessionFactoryImpl$SessionBuilderImpl.openSession(SessionFactoryImpl.java:1305) 

   at org.hibernate.context.internal.ThreadLocalSessionContext.buildOrObtainSession(ThreadLocalSessionContext.java:144) 

   at org.hibernate.context.internal.ThreadLocalSessionContext.currentSession(ThreadLocalSessionContext.java:94) 

   at org.hibernate.internal.SessionFactoryImpl.getCurrentSession(SessionFactoryImpl.java:514) 

   at org.dspace.core.HibernateDBConnection.isSessionAlive(HibernateDBConnection.java:114) 

   at org.dspace.core.Context.isValid(Context.java:599) 

   at org.dspace.storage.rdbms.DatabaseUtils$ReindexerThread.run(DatabaseUtils.java:1350) 

DSpace log after database migrate ignored 2.txt

Tim Donohue

unread,
Aug 31, 2022, 1:26:43 PM8/31/22
to Night Librarian, DSpace Technical Support
Hi,

I don't see any errors in the logs (You'd see a line that has ERROR in all caps if so).  So, at a glance, it sounds like the database upgrade worked.

You'd want to continue with the upgrade process per the documentation: https://wiki.lyrasis.org/display/DSDOC7x/Upgrading+DSpace

Obviously, you've already done the database upgrade portion, but you'd need to do any other upgrade portions that you have not yet done.

Tim
Sent: Tuesday, August 30, 2022 5:35 PM

To: DSpace Technical Support <dspac...@googlegroups.com>
Subject: Re: [dspace-tech] Re: Database migrate problem while upgrading from 4.0 to 7.2
--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to a topic in the Google Groups "DSpace Technical Support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/dspace-tech/UcAuyYjZiw4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to dspace-tech...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/bd74e6bb-d6f9-48d1-97d1-2c1d5276be99n%40googlegroups.com.

Night Librarian

unread,
Aug 31, 2022, 2:02:39 PM8/31/22
to DSpace Technical Support
Million thanks for your expert help and your patience!

Despite all the problems at my end, you helped me navigate the hurdles!  I can now see my communities and items in the HAL browser, so I am going to continue with the upgrade process! 
Reply all
Reply to author
Forward
0 new messages