Hi Scott, Looking at that migration script, I see two possible ways this might be occurring...
Take a look at the full context of the changes to the `bundle2bitstream` table: https://github.com/DSpace/DSpace/blob/dspace-6_x/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql#L226-L242 First, at the very beginning, "bundle2bitstream.bitstream_id" column is moved over to a new "bitstream_legacy_id" column: https://github.com/DSpace/DSpace/blob/dspace-6_x/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql#L227 Later on, the "bitstream_id" column is recreated, pointing to the new Bitstream UUIDs (generated by pgcrypto): https://github.com/DSpace/DSpace/blob/dspace-6_x/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql#L229 Then, the "bitstream_id" column is repopulated (now with UUIDs) based on finding matches between the "bitstream_legacy_id" and the old bitstream.bitstream_id column (Both of these are the old index-based identifiers, not UUIDs): https://github.com/DSpace/DSpace/blob/dspace-6_x/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql#L231 That LAST link is the most important one, as it implies one of two things here. (1) Either pgcrypto is not generating UUIDs, and they are all coming across as "null". This is highly unlikely though, as you said you already installed/enabled pgcrypto....and the "./dspace database info" command would show a warning if 'pgcrypto' is missing or not installed correctly. (2) OR, you have an entry in your "bundle2bitstream" table that does NOT have a corresponding entry in your "bitstream" table. In this scenario, that repopulating of the "bitstream_id" column may return null as it cannot find the bitstream to link back up to this bundle. I highly suspect the issue here is #2. If so, you should be able to query the old, original database looking for an entry in bundle2bitstream where "bitstream_id" doesn't exist in the "bitstream" table. Something like this (I haven't tested this query...this is just off the top of my head, so you might need to tweak it): select * from bundle2bitstream where bitstream_id not in (select bitstream_id from bitstream); Good luck. Let us know if this turns out to be the problem, or if you discover further issues! Obviously, we appreciate it when folks report back on the list, as that may help others in the future if they encounter something similar. Tim On Tue, Sep 11, 2018 at 3:53 PM Scott Renton <rento...@gmail.com> wrote: > I should add, I do realise that the preceding lines in the script are > reassigning columns and data, so while I can check my table has no null > values prior to the run, I don't know what's changed, as I assume it is > rolling back everything that script does. My obvious course of action is to > back up the db and run the script up to that line, so I can see what it's > ACTUALLY complaining about. > > Cheers > Scott > > > On Tuesday, September 11, 2018 at 11:28:13 AM UTC+1, Scott Renton wrote: >> >> Hi folks >> >> I know there have been a few of these- I've hit upon one now. I'm >> migrating a v5.6 database to v6, and getting the following error: >> >> ./dspace database migrate >> >> >> Database URL: jdbc:postgresql://localhost:5432/dspace >> >> 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:673) >> >> at >> org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:576) >> >> at org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:221) >> >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> >> at >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) >> >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >> >> at java.lang.reflect.Method.invoke(Method.java:498) >> >> 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 V6.0_2015.03.07__DS-2701_Hibernate_migration.sql failed >> >> ----------------------------------------------------------------- >> >> SQL State : 23502 >> >> Error Code : 0 >> >> Message : ERROR: column "bitstream_id" contains null values >> >> Location : >> org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql >> (/home/lib/dspace/dspace/bin/file:/home/lib/dspace/dspace/lib/dspace-api-6.3.jar!/org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql) >> >> Line : 236 >> >> Statement : ALTER TABLE bundle2bitstream ALTER COLUMN bitstream_id SET >> NOT 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) >> >> ... 8 more >> >> Caused by: org.postgresql.util.PSQLException: ERROR: column >> "bitstream_id" contains null values >> >> 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.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) >> >> ... 24 more >> >> I definitely have no null values in the offending column: >> >> dspace=> select * from bundle2bitstream where bitstream_id is null; >> >> id | bundle_id | bitstream_id | bitstream_order >> >> ----+-----------+--------------+----------------- >> >> (0 rows) >> >> >> >> I know the processing in the script prior to this is complicated, so >> there may be something else it's not doing- I don't know if it rolls back >> to the beginning of the script, but just wondering if anyone has any ideas >> to what's actually going on here! I have installed the pgcrypto for v6 as >> per the installation instructions, so I think my database should be ready. >> >> >> It did 5.7 and the first 6 migration ok: >> >> >> >> >> +----------------+-----------------------------------------------------+---------------------+---------+ >> >> | Version | Description | >> Installed on | State | >> >> >> +----------------+-----------------------------------------------------+---------------------+---------+ >> >> | 1.1 | Initial DSpace 1.1 database schema | >> | <Baseln | >> >> | 1.2 | Upgrade to DSpace 1.2 schema | >> | <Baseln | >> >> | 1.3 | Upgrade to DSpace 1.3 schema | >> | <Baseln | >> >> | 1.3.9 | Drop constraint for DSpace 1 4 schema | >> | <Baseln | >> >> | 1.4 | Upgrade to DSpace 1.4 schema | >> | <Baseln | >> >> | 1.5 | Upgrade to DSpace 1.5 schema | >> | <Baseln | >> >> | 1.5.9 | Drop constraint for DSpace 1 6 schema | >> | <Baseln | >> >> | 1.6 | Upgrade to DSpace 1.6 schema | >> | <Baseln | >> >> | 1.7 | Upgrade to DSpace 1.7 schema | >> | <Baseln | >> >> | 1.8 | Upgrade to DSpace 1.8 schema | >> | <Baseln | >> >> | 3.0 | Upgrade to DSpace 3.x schema | >> | <Baseln | >> >> | 4.0 | Initializing from DSpace 4.0 database schema | >> 2018-01-25 16:34:33 | Success | >> >> | 4.9.2015.10.26 | DS-2818 registry update | >> | Ignored | >> >> | 5.0.2014.08.08 | DS-1945 Helpdesk Request a Copy | >> 2018-01-25 16:34:33 | Success | >> >> | 5.0.2014.09.25 | DS 1582 Metadata For All Objects drop constraint | >> 2018-01-25 16:34:34 | Success | >> >> | 5.0.2014.09.26 | DS-1582 Metadata For All Objects | >> 2018-02-01 12:23:35 | Success | >> >> | 5.6.2016.08.23 | DS-3097 | >> 2018-02-01 12:23:37 | Success | >> >> | 5.7.2017.04.11 | DS-3563 Index metadatavalue resource type id column | >> 2018-09-06 11:11:16 | Success | >> >> | 5.7.2017.05.05 | DS 3431 Add Policies for BasicWorkflow | >> 2018-09-06 11:11:16 | Success | >> >> | 6.0.2015.03.06 | DS 2701 Dso Uuid Migration | >> 2018-09-06 11:11:17 | Success | >> >> | 6.0.2015.03.07 | DS-2701 Hibernate migration | >> | Pending | >> >> | 6.0.2015.08.31 | DS 2701 Hibernate Workflow Migration | >> | Pending | >> >> | 6.0.2016.01.03 | DS-3024 | >> | Pending | >> >> | 6.0.2016.01.26 | DS 2188 Remove DBMS Browse Tables | >> | Pending | >> >> | 6.0.2016.02.25 | DS-3004-slow-searching-as-admin | >> | Pending | >> >> | 6.0.2016.04.01 | DS-1955 Increase embargo reason | >> | Pending | >> >> | 6.0.2016.04.04 | DS-3086-OAI-Performance-fix | >> | Pending | >> >> | 6.0.2016.04.14 | DS-3125-fix-bundle-bitstream-delete-rights | >> | Pending | >> >> | 6.0.2016.05.10 | DS-3168-fix-requestitem item id column | >> | Pending | >> >> | 6.0.2016.07.21 | DS-2775 | >> | Pending | >> >> | 6.0.2016.07.26 | DS-3277 fix handle assignment | >> | Pending | >> >> | 6.0.2016.08.23 | DS-3097 | >> | Pending | >> >> | 6.0.2017.02.14 | statistics-harvester | >> | Pending | >> >> | 6.1.2017.01.03 | DS 3431 Add Policies for BasicWorkflow | >> | Pending | >> >> >> +----------------+-----------------------------------------------------+---------------------+---------+ >> >> >> Cheers >> >> Scott >> > -- > All messages to this mailing list should adhere to the DuraSpace Code of > Conduct: https://duraspace.org/about/policies/code-of-conduct/ > --- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to dspace-tech@googlegroups.com. > Visit this group at https://groups.google.com/group/dspace-tech. > For more options, visit https://groups.google.com/d/optout. > -- Tim Donohue Technical Lead for DSpace & DSpaceDirect DuraSpace.org | DSpace.org | DSpaceDirect.org -- All messages to this mailing list should adhere to the DuraSpace Code of Conduct: https://duraspace.org/about/policies/code-of-conduct/ --- 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+unsubscr...@googlegroups.com. To post to this group, send email to dspace-tech@googlegroups.com. Visit this group at https://groups.google.com/group/dspace-tech. For more options, visit https://groups.google.com/d/optout.
