[Dspace-tech] Flyway Migration Error

[Keith Jones]

Hi All,

I'm currently attempting to upgrade from dspace version 3.2 to dspace version 5.1 but I'm getting a java.sql.SQLException which is leading to a black page:

Here's what I have installed

OS : Solaris

Java: 1.7.0_11

Maven: 3.2.3

Ant: 1.8.2

Postgres: 9.4

Tomcat: 7.0.34

I'm able to both build and compile without error, I've deployed both xmlui and solr into the webapps area.

Here is the error I'm getting in the logfile:

2015-03-17 14:03:48,848 ERROR org.dspace.storage.rdbms.DatabaseManager @ SQL getDataSource Error - 

java.sql.SQLException: Flyway migration error occurred

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

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

        at org.dspace.storage.rdbms.DatabaseManager.initialize(DatabaseManager.java:1373)

        at org.dspace.storage.rdbms.DatabaseManager.getDataSource(DatabaseManager.java:650)

        at org.dspace.storage.rdbms.DatabaseManager.getConnection(DatabaseManager.java:629)

        at org.dspace.core.Context.init(Context.java:121)

        at org.dspace.core.Context.<init>(Context.java:95)

        at org.dspace.browse.IndexBrowse.main(IndexBrowse.java:637)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:601)

        at org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:225)

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

Caused by: org.flywaydb.core.api.FlywayException: Migration failed !

        at org.flywaydb.core.internal.resolver.jdbc.JdbcMigrationExecutor.execute(JdbcMigrationExecuto

r.java:47)

        at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:287)

        at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:285)

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

2)

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

        at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46)

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

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

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

2)

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

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

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

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

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

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

        ... 13 more

Caused by: org.postgresql.util.PSQLException: ERROR: constraint "metadatavalue_item_id_fkey" of relati

on "metadatavalue" does not exist

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

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

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

        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:38

8)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)

        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.jav

a:172)

        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.jav

a:172)

        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.jav

a:172)

        at org.dspace.storage.rdbms.MigrationUtils.dropDBConstraint(MigrationUtils.java:106)

        at org.dspace.storage.rdbms.migration.V5_0_2014_09_25__DS_1582_Metadata_For_All_Objects_drop_c

onstraint.migrate(V5_0_2014_09_25__DS_1582_Metadata_For_All_Objects_drop_constraint.java:61)

        at org.flywaydb.core.internal.resolver.jdbc.JdbcMigrationExecutor.execute(JdbcMigrationExecuto

r.java:45)

        ... 27 more

Thanks

Keith

[Brian Freels-Stendel]

Hi Keith,

 

You'll need to update the foreign key constraint.  My bet is that it's named "$1" right now, but it needs to be named "metadatavalue_item_id_fkey".

 

I think the SQL would be

 

"alter table metadatavalue drop constraint '$1';"

 

followed by

 

"alter table metadatavalue add constraint '$metadatavalue_item_id_fkey' foreign key ('item_id') references item ('item_id') on delete no action on update no action;"

 

But you'll really want to make a backup first!

 

I think some of us with databases dating back to the early days have auto-named constraints, and they're breaking the flyway stuff.

 

B--

[Keith Jones]

Brian, 

Thanks for the reply.

UI used both of the lines you provided in and each time postgres said the table was altered, but when I ran the migration tool again I was meet with the same error.

Keith

[Brian Freels-Stendel]

Hi,

 

I was wrong in the new name: it should not have a $ in front of it, just metadatavalue_item_id_fkey

 

In a very old version of SQLManager, this is the SQL it says it's going to run:

 

ALTER TABLE "public"."metadatavalue"

  DROP CONSTRAINT "$1" RESTRICT;

 

ALTER TABLE "public"."metadatavalue"

  ADD CONSTRAINT "metadatavalue_item_id_fkey" FOREIGN KEY ("item_id")

    REFERENCES "public"."item"("item_id")

    ON DELETE NO ACTION

    ON UPDATE NO ACTION

    NOT DEFERRABLE;

 

Sorry about the typo, and here's hoping!

[Keith Jones]

Brian,

Thanks for the information, I've made some headway but I'm not getting a different error:

I ran the dspace databse info whith these results:

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

Database Schema: public

Database Software: PostgreSQL version 9.4.0

Database Driver: PostgreSQL Native Driver version PostgreSQL 9.1 JDBC4 (build 901)

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

| Version        | Description                | Installed on        | State   |

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

| 1.1            | Initial DSpace 1.1 databas |                     | PreInit |

| 1.2            | Upgrade to DSpace 1.2 sche |                     | PreInit |

| 1.3            | Upgrade to DSpace 1.3 sche |                     | PreInit |

| 1.3.9          | Drop constraint for DSpace |                     | PreInit |

| 1.4            | Upgrade to DSpace 1.4 sche |                     | PreInit |

| 1.5            | Upgrade to DSpace 1.5 sche |                     | PreInit |

| 1.5.9          | Drop constraint for DSpace |                     | PreInit |

| 1.6            | Upgrade to DSpace 1.6 sche |                     | PreInit |

| 1.7            | Upgrade to DSpace 1.7 sche |                     | PreInit |

| 1.8            | Upgrade to DSpace 1.8 sche |                     | PreInit |

| 3.0            | Initializing from DSpace 3 | 2015-03-17 13:59:38 | Success |

| 4.0            | Upgrade to DSpace 4.x sche | 2015-03-17 13:59:38 | Success |

| 5.0.2014.08.08 | DS-1945 Helpdesk Request a | 2015-03-17 13:59:39 | Success |

| 5.0.2014.09.25 | DS 1582 Metadata For All O | 2015-03-17 16:09:57 | Success |

| 5.0.2014.09.26 | DS-1582 Metadata For All O |                     | Pending |

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

And the log file is reporting the following:

2015-03-17 16:14:29,319 ERROR org.dspace.storage.rdbms.DatabaseManager @ SQL getDataSource Error - 

java.sql.SQLException: Flyway migration error occurred

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

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

        at org.dspace.storage.rdbms.DatabaseManager.initialize(DatabaseManager.java:1373)

        at org.dspace.storage.rdbms.DatabaseManager.getDataSource(DatabaseManager.java:650)

        at org.dspace.storage.rdbms.DatabaseManager.getConnection(DatabaseManager.java:629)

        at org.dspace.core.Context.init(Context.java:121)

        at org.dspace.core.Context.<init>(Context.java:95)

        at org.dspace.app.util.AbstractDSpaceWebapp.register(AbstractDSpaceWebapp.java:79)

        at org.dspace.app.util.DSpaceContextListener.contextInitialized(DSpaceContextListener.java:128

)

        at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4791)

        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5285)

        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)

        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)

        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)

        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:633)

        at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1114)

        at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1673)

        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)

        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)

        at java.util.concurrent.FutureTask.run(FutureTask.java:166)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

        at java.lang.Thread.run(Thread.java:722)

Caused by: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Error executing statement at

 line 34: INSERT INTO metadatavalue (resource_id, resource_type_id, metadata_field_id, text_value, tex

t_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 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

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

        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.j

ava:73)

        at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:287)

        at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:285)

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

2)

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

        at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46)

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

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

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

2)

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

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

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

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

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

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

        ... 22 more

Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "metadatavalue" violate

s foreign key constraint "$metadatavalue_item_id_fkey"

  Detail: Key (resource_id)=(38) is not present in table "item".

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

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

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

        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:37

4)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)

        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)

        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)

        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)

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

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

        ... 37 more

Something still seems to be off with the metadatavalues. This database goes all the back to 1.4, I think.

Any additional help will be greatly appreciated.

Thanks

Keith

[Brian Freels-Stendel]

Oh, man, that's completely my fault.  You'll need to also remove the constraint $metadatavalue_item_id_fkey.  That's just a piece of dross put in by my from-memory, incorrect attempt at the rename.  After that's deleted, the migration should be able to continue.