Error when I try migrate database from ver 4.x to 7

ku bun

unread,

Sep 4, 2021, 11:01:01 AM9/4/21

to DSpace Technical Support

1.Before run migrate database, I check info version db:

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

2.After run migrate database, I receive this errors :

Migrating database to latest version... (Check dspace logs for details)

Migration exception:

java.sql.SQLException: Flyway migration error occurred

at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:738)

at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:632)

at org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:228)

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 : 23502

Error Code : 0

Message : ERROR: null value in column "metadata_value_id" violates not-null constraint

Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài li?u t?ng h?p,.., null, 0, null, -1, 4).

Location : org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql (e:\Dspace_Data\Dspace\file:\E:\Dspace_Data\Dspace\lib\dspace-api-7.0.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql)

Line : 44

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 = 'description' and qualifier = 'abstract') AS metadata_field_id,

short_description AS text_value,

null AS text_lang,

0 AS place

FROM community where not short_description 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:729)

... 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 : 23502

Error Code : 0

Message : ERROR: null value in column "metadata_value_id" violates not-null constraint

Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài li?u t?ng h?p,.., null, 0, null, -1, 4).

Location : org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql (e:\Dspace_Data\Dspace\file:\E:\Dspace_Data\Dspace\lib\dspace-api-7.0.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql)

Line : 44

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 = 'description' and qualifier = 'abstract') AS metadata_field_id,

short_description AS text_value,

null AS text_lang,

0 AS place

FROM community where not short_description is null

at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:275)

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.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: null value in column "metadata_value_id" violates not-null constraint

Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài li?u t?ng h?p,.., null, 0, null, -1, 4).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2505)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2241)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)

at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:309)

at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:295)

at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:272)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:267)

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:208)

... 35 more

3. And check db info again:

Database Type: postgres

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

Database Schema: public

Database Username: dspace

Database Software: PostgreSQL version 11.13

Database Driver: PostgreSQL JDBC Driver version 42.2.9

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

FlywayDB Version: 6.5.5

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

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

| Versioned | 4.9.2015.10.26 | DS-2818 registry update | SQL | 2021-09-04 20:53:33 | Success |

| Versioned | 5.0.2014.08.08 | DS-1945 Helpdesk Request a Copy | SQL | 2021-09-04 20:53:33 | Success |

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

I try find value null in column "metadata_value_id" but don't see any value null, i fall into a deadlock, hope someone can help me, thank so much :)

Tim Donohue

unread,

Sep 7, 2021, 10:27:46 AM9/7/21

to DSpace Technical Support

Hello,

The INSERT that seems to be failing is this one:
https://github.com/DSpace/DSpace/blob/main/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql#L44-L52

The purpose of that statement is to migrate Community metadata from the "community" table into the "metadatavalue" table. According to the error message you shared, it's saying that it's trying to insert a "null" value for "metadata_value_id"...which is odd, since that field should be automatically filled out by a database sequence.

Could you try running the update-sequences script on your database and then try the migration again? It's possible that'll fix it. Run:

'./dspace database update-sequences'

'./dspace database migrate ignored'

In the migrate script you may need the "ignored" flag to ensure out-of-order migrations also run. See the Upgrading docs, step 9(c). https://wiki.lyrasis.org/display/DSDOC7x/Upgrading+DSpace

Let us know on this list if that helps.

Tim

ku bun

unread,

Sep 9, 2021, 10:55:25 PM9/9/21

to DSpace Technical Support

Thank you for reply , i tryed like you solution but it still same error:

First run script sql:

C:\Program Files\PostgreSQL\11\bin>psql -U dspace -f update-sequences.sql csdlkhcn

Password for user dspace:

setval

--------

80

(1 row)

setval

--------

100

(1 row)

setval

---------

2556018

(1 row)

setval

--------

958

(1 row)

setval

--------

(1 row)

setval

--------

(1 row)

setval

--------

4075

(1 row)

setval

--------

88

(1 row)

setval

--------

75

(1 row)

setval

---------

2624133

(1 row)

setval

--------

1

(1 row)

setval

--------

(1 row)

setval

--------

(1 row)

setval

--------

434

(1 row)

setval

--------

372

(1 row)

psql:update-sequences.sql:63: ERROR: relation "handle_id_seq" does not exist

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

^

setval

--------

352309

(1 row)

After Run migrate:

e:\Dspace_Data\Dspace\bin>dspace database migrate ignored

Using DSpace installation in: e:\Dspace_Data\Dspace

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

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:673)

at org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:187)

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:229)

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

Caused by: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:

Migration V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql failed

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

SQL State : 23502

Error Code : 0

Message : ERROR: null value in column "metadata_value_id" violates not-null constraint

Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài li?u t?ng h?p,.., null, 0, null, -1, 4).

Location : org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql (e:\Dspace_Data\Dspace\file:\E:\Dspace_Data\Dspace\lib\dspace-api-6.3.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql)

Line : 44

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 = 'description' and qualifier = 'abstract') AS metadata_field_id,

short_description AS text_value,

null AS text_lang,

0 AS place

FROM community where not short_description is null

at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)

at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:71)

at org.flywaydb.core.internal.command.DbMigrate.doMigrate(DbMigrate.java:352)

at org.flywaydb.core.internal.command.DbMigrate.access$1100(DbMigrate.java:47)

at org.flywaydb.core.internal.command.DbMigrate$4.doInTransaction(DbMigrate.java:308)

at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)

at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:305)

at org.flywaydb.core.internal.command.DbMigrate.access$1000(DbMigrate.java:47)

at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:230)

at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:173)

at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)

at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:173)

at org.flywaydb.core.Flyway$1.execute(Flyway.java:959)

at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)

at org.flywaydb.core.Flyway.execute(Flyway.java:1373)

at org.flywaydb.core.Flyway.migrate(Flyway.java:917)

at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:662)

... 7 more

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "metadata_value_id" violates not-null constraint

Detail: Failing row contains (null, 1, 27, Các b? s?u t?p sách và tài li?u t?ng h?p,.., null, 0, null, -1, 4).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)

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 org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)

at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:238)

at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:114)

... 23 more

I think the column metadata_value_id is not auto increment when insert to table metadatavalue, i try change type this column from integer to serial but it impossible, my sequences like this:

How to set metadata_value_id is auto increment, I think this can solve it. I use postgresql version 11 on window server 2016.

ku bun

unread,

Sep 10, 2021, 5:53:42 AM9/10/21

to DSpace Technical Support

I fixed it by modify file V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql

like this :

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

SELECT

nextval('metadatavalue_seq') AS metadata_value_id,

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 = 'description' and qualifier is null) AS metadata_field_id,

introductory_text AS text_value,

null AS text_lang,

0 AS place

FROM community where not introductory_text is null;

.....

my file in attachment, hope someone needs

V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql

Night Librarian

unread,

Feb 24, 2022, 7:36:52 PM2/24/22

to DSpace Technical Support

I have the exact same problem trying to migrate from 4.0 to 7.2. I would like to try what he suggested, using the attached V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql file. My question is, where should I use this file? I can locate it in 3 subdirectories in both [dspace-source] and [dspace-target] directories:

/temp/DSpace-dspace-7.2/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/h2/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql

/temp/DSpace-dspace-7.2/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/oracle/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql

/temp/DSpace-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

/temp/DSpace-dspace-7.2/dspace-api/target/classes/org/dspace/storage/rdbms/sqlmigration/h2/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql

/temp/DSpace-dspace-7.2/dspace-api/target/classes/org/dspace/storage/rdbms/sqlmigration/oracle/V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql

/temp/DSpace-dspace-7.2/dspace-api/target/classes/org/dspace/storage/rdbms/sqlmigration/postgres/V5.0_2014.09.26__DS-158

I understand that I need to replace the original V5.0_2014.09.26__DS-1582_Metadata_For_All_Objects.sql file in a /postgres/ subdirectory, but in which one, source or target? Also, if I do that, I should then run "mvn package" and "ant fresh install", right? Is there anything else I need to do?

Reply all

Reply to author

Forward