Uploaded image for project: 'DSpace (LEGACY)'
  1. DSpace (LEGACY)
  2. DS-4540

Flyway migration failed

    XMLWordPrintable

    Details

    • Attachments:
      0
    • Comments:
      1
    • Documentation Status:
      In Description

      Description

      Hi guys (Tim Donohue, Hardy Pottinger, Ivan Masár)!

      I tried to migrate our DSpace to version 6.3 from version 5. But it ended with a FATAL error after re-deploying to tomcat:

      2020-07-19 16:02:55,932 FATAL org.dspace.core.Context @ Cannot initialize database via Flyway!
      java.sql.SQLException: Flyway migration error occurred
              at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:673)
              at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:576)
              at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:552)
              at org.dspace.core.Context.<clinit>(Context.java:103)
              at org.dspace.app.util.AbstractDSpaceWebapp.register(AbstractDSpaceWebapp.java:74)
              at org.dspace.app.util.DSpaceWebappListener.contextInitialized(DSpaceWebappListener.java:31)
              at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4797)
              at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5221)
              at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
              at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:724)
              at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:700)
              at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:714)
              at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:581)
              at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1685)
              at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
              at java.util.concurrent.FutureTask.run(FutureTask.java:266)
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
              at java.lang.Thread.run(Thread.java:745)
      
      Caused by: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
      Migration V6.0_2016.04.04__DS-3086-OAI-Performance-fix.sql failed
      -----------------------------------------------------------------
      SQL State  : 72000
      Error Code : 1408
      Message    : ORA-01408: such column list already indexed
      Location   : org/dspace/storage/rdbms/sqlmigration/oracle/V6.0_2016.04.04__DS-3086-OAI-Performance-fix.sql (/home/dspace/file:/opt/dspace63/webapps/xmlui/WEB-INF/lib/dspace-api-6.3.jar!/org/dspace/storage/rdbms/sqlmigration/oracle/V6.0_2016.04.04__DS-3086-OAI-Performance-fix.sql)
      Line       : 18
      Statement  : CREATE UNIQUE INDEX metadataschema_idx_short_id on metadataschemaregistry(short_id)
      
      Caused by: java.sql.SQLException: ORA-01408: such column list already indexed
      
              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
              at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
              at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
              at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
              at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
              at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
              at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036)
              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
              at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)
              at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)
              at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
              at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
              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)
              ... 34 more
      

       

      SQL script "V6.0_2016.04.04__DS-3086-OAI-Performance-fix.sql" will try to create an index that already exists on the given column!
      https://github.com/DSpace/DSpace/blob/dspace-6_x/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/oracle/V6.0_2016.04.04__DS-3086-OAI-Performance-fix.sql#L18

      If the database exists for many versions of DSpace, the table has a unique index over all columns.

      https://github.com/DSpace/DSpace/blob/dspace-1_4_x/dspace/etc/oracle/database_schema.sql#L230-L238
      https://github.com/DSpace/DSpace/blob/dspace-1_6_x/dspace/etc/oracle/database_schema.sql#L246-L254
      https://github.com/DSpace/DSpace/blob/dspace-1_7_x/dspace/etc/oracle/database_schema.sql#L246-L254
      https://github.com/DSpace/DSpace/blob/dspace-1_8_x/dspace/etc/oracle/database_schema.sql#L246-L254
      https://github.com/DSpace/DSpace/blob/dspace-4_x/dspace/etc/oracle/database_schema.sql#L249-L257

      In these cases oracle creates this index automatically as the SYS_C00XXXXX system index. We have a database since version 1.4 of DSpace.

      I don't understand why the unique option for the short_id column is missing in the flyway scripts. Is there a reason for that?

      https://github.com/DSpace/DSpace/blob/dspace-5_x/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/oracle/V1.4__Upgrade_to_DSpace_1.4_schema.sql#L57-L63

      I did not find that the index was ever removed. I think it's a bug when index missing in the flayway scripts.

      I think fixing the error could be easy.
      I am able to prepare PR.

       

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            ctu-developers CTU Developers
            Reviewer:
            Hardy Pottinger Hardy Pottinger
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: