Hi Tim,
Apologies for not reporting back sooner. As per your suggestion, I removed
the SQL you mentioned, performed 'mvn clean package', and 'ant update'. I
did not receive any errors when running 'dspace database repair', but when
I run 'dspace database migrate ignored', the console returned a lot of
errors too long to post here. Please see the attached text file from the
output of my console. I will try to restore this repository in a local
instance with the latest 6x version and try to run 'dspace database migrate
ignored' from there. I suspect there is something wrong with this
particular instance because when I run the command 'dspace database migrate
ignored' on this repository which is running version 6.3, it returned this
error:
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:673)
at org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:187)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
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.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql
failed
-----------------------------------------------------------------------------------------
SQL State : 42703
Error Code : 0
Message : ERROR: column "resource_type_id" does not exist
Location :
org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql
(c:\DSpace\file:\C:\DSpace\lib\dspace-api-6.3.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql)
Line : 16
Statement : CREATE INDEX metadatavalue_resource_type_id_idx ON
metadatavalue (resource_type_id)
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: column
"resource_type_id" does not exist
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)
... 23 more
Please note that the original version of DSpace that I used to install in
this instance was version 6.0, so I wonder why it is complaining about
scripts from version 5.7.
Hoping this can be resolved so that I can successfully migrate it to
version 7.6
Thanks in advance and best regards,
euler
On Monday, July 11, 2022 at 11:41:35 PM UTC+8 Tim Donohue wrote:
> Hi Euler,
>
> Hmm... that's confusing to me that this issue has triggered for you when
> you started your DSpace site from 6.0.
>
> One approach that *should* work is to:
>
> 1. Delete the migration file from
>
> dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql
> 2. Rebuild DSpace backend ("mvn clean package") and redeploy ("ant
> update")
> 3. Run "./dspace database repair" (this will tell Flyway to ignore
> that deleted migration file which you removed in step 1)
> 4. Try to migrate again. Run "./dspace database migrate ignored"
>
> If this works for you, please report back. It seems that there's
> something strange going on with this particular migration file...and I'm
> still trying to determine the root cause. So, if we can at least document
> a known "workaround", that'd be very useful to everyone who encounters this
> issue.
>
> Tim
> ------------------------------
> *From:* [email protected] <[email protected]> on behalf
> of euler <[email protected]>
> *Sent:* Tuesday, July 5, 2022 10:20 PM
>
> *To:* DSpace Technical Support <[email protected]>
> *Subject:* Re: [dspace-tech] Re: Seemingly crazy catch 22 Dspace DB
> migration loop question!!!
>
> Hi Steven,
>
> Did you manage to solve your issue? I am also experiencing the same issue
> where the version used to install DSpace was 6.0. Right now it is running
> on version 6.3. Hoping you can share your steps on fixing this migration
> issue so that others who have started from version 6 can apply your fix.
>
> Thanks in advance and best regards,
> euler
> On Tuesday, October 19, 2021 at 1:40:08 AM UTC+8 [email protected] wrote:
>
> Tim, that is great advice, and sort of what we are already doing, so we
> will continue down this path, and report back. Thanks again!
>
> On Monday, October 18, 2021 at 12:26:38 PM UTC-5 Tim Donohue wrote:
>
> Hi Steven,
>
> Ok, I understand the frustration. Honestly, I've very confused how this
> migration was never triggered earlier...as there was a time (in 5.x days)
> when it was not "ignored" / out of order and it should have been triggered
> by a simple "dspace database migrate" at that time.
>
> *One other option here*, and I would recommend BACKING UP FIRST or trying
> it on a test database (as it could be dangerous & I've not tested this).
> As you may have already figured out, our entire "dspace database" script
> just runs FlywayDB.org behind the scenes. So...
>
> *You could manually modify the Flyway "schema_version" table for that
> specific migration entry, setting the "success" column to "t" (true).*
>
> In other words, after the migration failure, you should already have a row
> in the "schema_version" table for "script" named
> "V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql".
> Currently, that row's "success" column will say "f" (false). Setting it to
> true will "fool" Flyway into thinking your previous run was successful.
> Since, this migration script is obviously no longer needed in 7.x, that's
> an OK fix in this scenario.
>
> After this manual change, I *think* your "./dspace database migrate info"
> script will show it as "Out of Order" (which means successful, but not run
> in order)
>
> After that change, you can try to run "./dspace database migrate ignored"
> again to see if it gets passed that migration. If you end up with a
> warning/failure saying that Flyway's validation process failed, you can try
> first a "./dspace database repair" and then try the "migrate ignored" again.
>
> This sort of modification to the Flyway "schema_version" table is
> obviously NOT RECOMMENDED in most scenarios, as you are bypassing the
> Flyway database upgrade process. But, in this odd scenario, it's worth a
> try, since this old migration is quite obviously no longer valid...and
> there's no easier way to "skip" a migration in Flyway unfortunately.
>
> Please let us know on this list if you decide to go this route & what
> steps work for you. I've not been able to yet figure out a way to reproduce
> this scenario locally, so it's difficult to list the exact steps
> above...but I'm hoping this should at least get you passed that migration.
>
> Tim
>
> ------------------------------
> *From:* [email protected] <[email protected]> on behalf
> of S. T. <[email protected]>
> *Sent:* Monday, October 18, 2021 10:52 AM
>
> *To:* DSpace Technical Support <[email protected]>
> *Subject:* Re: [dspace-tech] Re: Seemingly crazy catch 22 Dspace DB
> migration loop question!!!
> Thank so much Tim,
>
> At no time during the 6.x versions does it tell you to run "database
> migrate ignored". All of the instructions ignore the fact that someone
> that started with version 6.x will not have had those migrations run, and
> yet are forced to do so to upgrade to 7.x, where they will fail.This gives
> me hope though that skipping them may yield a completely workable 7.x
> system. So I'm going to try to skip the other 5.x migration that's now
> holding us up since we skipped the other. (we modified the 5.7 DB script
> called by the cocoon files, that creates the un-needed index, to basically
> have a null output)
>
> I can also try upgrading through the 6.x versions, but I see no reason
> this will succeed, only a slower path to get us to where we are (it failed
> with 6.2 and 6.3, I only haven't tried 6.1).
>
> Any other suggestions form today? I apologize for taking up so much of
> your time. I have a schedule of 'upgrade DB to 7' for end of October, so I
> am actively pursuing a solution.
>
> Another idea was to perform some sort of WP-style export of the data, then
> re-import to individual table on a clean 7 install. Dspace does not have
> that many data tables, so it's possible, but unwieldy and very far from
> ideal, or maybe even no possible.
>
> On Friday, October 15, 2021 at 9:39:21 AM UTC-5 Tim Donohue wrote:
>
> Hi Steven,
>
> I apologize, but I haven't had time to get back to this yet. It is
> definitely an odd error to be occurring, as I would have expected this
> migration to have run *before* you attempted an upgrade to 7.0.
>
> One option might be to go back to your 6.x site and run the "dspace
> database migrate ignored" script there (using the 6.x codebase). If it
> works there, it might be enough to get your database *past* that
> migration (once it is run, it never runs again). Then you could attempt
> the upgrade to 7.0.
>
> You may be correct that there's a migration bug at play here... I honestly
> haven't had time to see if I can replicate this elsewhere. The expectation
> though is that *ideally* all pre-7.0 migrations should have already been
> run on your database (even those which are returned as "ignored")....as
> many upgrades involve running that "dspace database migrate ignored"
> command (which should have previously triggered those ignored migrations to
> run).
>
> The * oddity* here is that somehow you (and a few others on this list)
> have an older 5.7 migration that wasn't triggered until the 7.0
> upgrade...that's unexpected to me as well. Generally, we expect 5.x
> migrations to trigger during 5.x minor upgrades or, at worst, during an
> upgrade from 5.x to 6.x. The fact that it was never triggered before is
> very odd, as it's obviously no longer a valid migration as you move to 7.x
> -- it only worked on a 5.x style database.
>
> I'll see if I can get back to this sometime next week...apologies though,
> as this comes during a very busy couple of weeks. I'll also see if I can
> ask around to find another developer with time to debug what could be going
> on here.
>
> Thanks again for reporting this...hopefully we can find a way to
> workaround this soon.
>
> Tim
> ------------------------------
> *From:* [email protected] <[email protected]> on behalf
> of S. T. <[email protected]>
> *Sent:* Thursday, October 14, 2021 3:09 PM
>
> *To:* DSpace Technical Support <[email protected]>
> *Subject:* Re: [dspace-tech] Re: Seemingly crazy catch 22 Dspace DB
> migration loop question!!!
>
> Hi Tim - did you see the returned DB info data? Do you have any
> suggestions? We are running into a brick wall over here, and so far, we are
> unable to resolve any issues brought about by upgrading using the 'ignored'
> flag. We are not only stopped at this issue, but also at other multiple
> issues involving DB modification using an index. I commented out the 5.7 db
> fix, and the script ran fine, but failed on another, similar issue.
>
> So, to me, this says that we have an unexpected or non-standard table
> structure? There is no reason why our table structure/indexes should be
> non-standard and unexpected, we have done nothing custom to the database.
>
> Any suggestions?
>
> On Thursday, October 14, 2021 at 11:04:30 AM UTC-5 Tim Donohue wrote:
>
> Hi Steven,
>
> What does your "dspace database info" command return?
> We'd need more information here to help debug it. This seems like a very
> odd error to me as well, as that migration was initially added in 5.7 and
> ported to 6.1... so, if you were already running 6.1 or above, this
> migration *should have already run for you*.
>
> That said, it's possible your "dspace database info" command would provide
> us with additional hints. I'm assuming that somehow this migration may
> not have ever been applied to your 6.x site....and now that you are
> updating to 7.x, it's causing issues.
>
> Tim
> ------------------------------
> *From:* [email protected] <[email protected]> on behalf
> of S. T. <[email protected]>
> *Sent:* Thursday, October 14, 2021 10:47 AM
> *To:* DSpace Technical Support <[email protected]>
> *Subject:* [dspace-tech] Re: Seemingly crazy catch 22 Dspace DB migration
> loop question!!!
>
> Thanks Joel, I appreciate the help. I guess I should search the Jira for
> Dspace on their github repo, and / or submit this bug. I am assuming they
> have a Jira and a github repo.Let me run those commands, and I will share.
>
> On Thursday, October 14, 2021 at 7:56:24 AM UTC-5 [email protected] wrote:
>
> Steven,
>
> I claim to be no expert, but maybe you (and another post
> <https://groups.google.com/g/dspace-tech/c/1paSeuUkjFo> from September)
> stumbled upon a bug? Can you share the results of the following commands?
>
>
> *[dspace]/bin/dspace database validate *
> *[dspace]/bin/dspace database info*
>
> It's suspicious that you started on version 6 and the Flyaway migration is
> attempting to issue a patch for something that existed in version 5.7.
>
> Just for fun, you might want to try this, too, if you haven't already.
>
>
> *[dspace]/bin/dspace database repair *
>
> --Joel
>
>
>
>
> On Wednesday, October 13, 2021 at 5:47:46 PM UTC-4 [email protected]
> wrote:
>
> We are trying to upgrade our system from 6 to 7, we are following all the
> steps.
>
> We get this message on DB migration:
> Migration
> V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql
> failed
>
> When we attempt to update the DB, if fails right there, because it is (?)
> trying to create a field WHICH APPARENTLY DOES NOT EXIST in 6.x or 7.x from
> a previous field THAT NEVER EXISTED ON OUR 6.x install.
>
> This script, which is part of the Flyway DB upgrade auto process
>
>
> org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql
>
> Contents are:
> DROP INDEX IF EXISTS metadatavalue_resource_type_id_idx; CREATE INDEX
> metadatavalue_resource_type_id_idx ON metadatavalue (resource_type_id);
>
> However, WE started with V 6.0. So, (1) that column did not exist in the
> 6.0 schema. (2) Furthermore, the field it is trying to create USING the
> value from that 5.7 field, also DOES NOT EXIST in the schema for 6.xx OR
> 7.xx.
>
> So, the migration script seems to be trying to alter the metadatavalue
> table using a column which doesn't exist to create a column that doesn't
> apparently exist in anyone's backend.
>
> Can anyone who either knows migrations very well, or the Dspace backend
> care to comment or assist? This is so super frustrating and confusing. What
> are we missing?
>
> Thanks so much
>
> Steven Turner
>
> --
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/dspace-tech/6fe30d91-4b79-48c6-aa30-2e13ce040132n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/dspace-tech/6fe30d91-4b79-48c6-aa30-2e13ce040132n%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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/dspace-tech/4a8d18a7-1948-4c39-bfab-bcf44b3a1957n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/dspace-tech/4a8d18a7-1948-4c39-bfab-bcf44b3a1957n%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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/dspace-tech/22a71cda-9dd0-450f-a4e7-5826b89d0e3bn%40googlegroups.com
>
> <https://groups.google.com/d/msgid/dspace-tech/22a71cda-9dd0-450f-a4e7-5826b89d0e3bn%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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/dspace-tech/6f76f9ed-ac38-4496-b00a-ae29eae588c8n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/dspace-tech/6f76f9ed-ac38-4496-b00a-ae29eae588c8n%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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/dspace-tech/c84b8b3a-a71d-40f6-8ba2-17fe36ef8c2cn%40googlegroups.com.
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:838)
at
org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:725)
at org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:205)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
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:
SQL State : null
Error Code : 0
Message : Flyway executeSql() error occurred
at
org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:388)
at
org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:275)
at
org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:55)
at
org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:274)
at
org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:247)
at
org.flywaydb.core.internal.command.DbMigrate.lambda$migrateAll$0(DbMigrate.java:141)
at
org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:69)
at
org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:99)
at
org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:139)
at
org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:141)
at
org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:98)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:173)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:124)
at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:214)
at org.flywaydb.core.Flyway.migrate(Flyway.java:124)
at
org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:825)
... 9 more
Caused by: java.sql.SQLException: Flyway executeSql() error occurred
at
org.dspace.storage.rdbms.DatabaseUtils.executeSql(DatabaseUtils.java:1253)
at
org.dspace.storage.rdbms.migration.V5_7_2017_05_05__DS_3431_Add_Policies_for_BasicWorkflow.migrate(V5_7_2017_05_05__DS_3431_Add_Policies_for_BasicWorkflow.java:43)
at
org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.executeOnce(JavaMigrationExecutor.java:55)
at
org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.lambda$execute$0(JavaMigrationExecutor.java:48)
at
org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27)
at
org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.execute(JavaMigrationExecutor.java:47)
at
org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:377)
... 24 more
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
bad SQL grammar [--
-- The contents of this file are subject to the license and copyright
-- detailed in the LICENSE and NOTICE files at the root of the source
-- tree and available online at
--
-- http://www.dspace.org/license/
--
-------------------------------------------------------------------------
-- DS-3431 Workflow system is vulnerable to unauthorized manipulations --
-------------------------------------------------------------------------
-----------------------------------------------------------------------
-- grant claiming permissions to all workflow step groups (step 1-3) --
-----------------------------------------------------------------------
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, epersongroup_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'3' AS resource_type_id,
'5' AS action_id,
'TYPE_WORKFLOW' AS rptype,
workflow_step_1 AS epersongroup_id,
collection_id
FROM collection
WHERE workflow_step_1 IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 3 AND action_id = 5
AND epersongroup_id = workflow_step_1 and resource_id = collection_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, epersongroup_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'3' AS resource_type_id,
'6' AS action_id,
'TYPE_WORKFLOW' AS rptype,
workflow_step_2 AS epersongroup_id,
collection_id
FROM collection
WHERE workflow_step_2 IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 3 AND action_id = 6
AND epersongroup_id = workflow_step_2 and resource_id = collection_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, epersongroup_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'3' AS resource_type_id,
'7' AS action_id,
'TYPE_WORKFLOW' AS rptype,
workflow_step_3 AS epersongroup_id,
collection_id
FROM collection
WHERE workflow_step_3 IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 3 AND action_id = 7
AND epersongroup_id = workflow_step_3 and resource_id = collection_id
);
-----------------------------------------------------------------------
-- grant add permissions to all workflow step groups (step 1-3) --
-----------------------------------------------------------------------
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, epersongroup_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'3' AS resource_type_id,
'3' AS action_id,
'TYPE_WORKFLOW' AS rptype,
workflow_step_1 AS epersongroup_id,
collection_id
FROM collection
WHERE workflow_step_1 IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 3 AND action_id = 3
AND epersongroup_id = workflow_step_1 and resource_id = collection_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, epersongroup_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'3' AS resource_type_id,
'3' AS action_id,
'TYPE_WORKFLOW' AS rptype,
workflow_step_2 AS epersongroup_id,
collection_id
FROM collection
WHERE workflow_step_2 IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 3 AND action_id = 3
AND epersongroup_id = workflow_step_2 and resource_id = collection_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, epersongroup_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'3' AS resource_type_id,
'3' AS action_id,
'TYPE_WORKFLOW' AS rptype,
workflow_step_3 AS epersongroup_id,
collection_id
FROM collection
WHERE workflow_step_3 IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 3 AND action_id = 3
AND epersongroup_id = workflow_step_3 and resource_id = collection_id
);
----------------------------------------------------------------------------------
-- grant read/write/delete/add/remove permission on workflow items to reviewers
--
----------------------------------------------------------------------------------
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'2' AS resource_type_id,
'0' AS action_id,
'TYPE_WORKFLOW' AS rptype,
owner AS eperson_id,
item_id
FROM workflowitem
WHERE
owner IS NOT NULL
AND (state = 2 OR state = 4 OR state = 6)
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 2 AND action_id = 0
AND eperson_id = owner AND resource_id = item_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'2' AS resource_type_id,
'1' AS action_id,
'TYPE_WORKFLOW' AS rptype,
owner AS eperson_id,
item_id
FROM workflowitem
WHERE
owner IS NOT NULL
AND (state = 2 OR state = 4 OR state = 6)
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 2 AND action_id =
1 AND eperson_id = owner AND resource_id = item_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'2' AS resource_type_id,
'2' AS action_id,
'TYPE_WORKFLOW' AS rptype,
owner AS eperson_id,
item_id
FROM workflowitem
WHERE
owner IS NOT NULL
AND (state = 2 OR state = 4 OR state = 6)
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 2 AND action_id =
2 AND eperson_id = owner AND resource_id = item_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'2' AS resource_type_id,
'3' AS action_id,
'TYPE_WORKFLOW' AS rptype,
owner AS eperson_id,
item_id
FROM workflowitem
WHERE
owner IS NOT NULL
AND (state = 2 OR state = 4 OR state = 6)
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 2 AND action_id =
3 AND eperson_id = owner AND resource_id = item_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'2' AS resource_type_id,
'4' AS action_id,
'TYPE_WORKFLOW' AS rptype,
owner AS eperson_id,
item_id
FROM workflowitem
WHERE
owner IS NOT NULL
AND (state = 2 OR state = 4 OR state = 6)
AND NOT EXISTS (
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 2 AND action_id =
4 AND eperson_id = owner AND resource_id = item_id
);
-----------------------------------------------------------------------------------
-- grant read/write/delete/add/remove permission on Bundle ORIGINAL to
reviewers --
-----------------------------------------------------------------------------------
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'1' AS resource_type_id,
'0' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
i2b.bundle_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 1 AND action_id = 0
AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
i2b.bundle_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'1' AS resource_type_id,
'1' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
i2b.bundle_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 1 AND action_id =
1 AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
i2b.bundle_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'1' AS resource_type_id,
'2' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
i2b.bundle_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 1 AND action_id =
2 AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
i2b.bundle_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'1' AS resource_type_id,
'3' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
i2b.bundle_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 1 AND action_id =
3 AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
i2b.bundle_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'1' AS resource_type_id,
'4' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
i2b.bundle_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 1 AND action_id =
4 AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
i2b.bundle_id
);
-------------------------------------------------------------------------------
-- grant read/write/delete/add/remove permission on all Bitstreams of Bundle --
-- ORIGINAL to reviewers --
-------------------------------------------------------------------------------
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'0' AS resource_type_id,
'0' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
b2b.bitstream_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN bundle2bitstream AS b2b
ON b2b.bundle_id = i2b.bundle_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 0 AND action_id =
0 AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
b2b.bitstream_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'0' AS resource_type_id,
'1' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
b2b.bitstream_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN bundle2bitstream AS b2b
ON b2b.bundle_id = i2b.bundle_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 0 AND action_id =
1 AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
b2b.bitstream_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'0' AS resource_type_id,
'2' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
b2b.bitstream_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN bundle2bitstream AS b2b
ON b2b.bundle_id = i2b.bundle_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 0 AND action_id =
2 AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
b2b.bitstream_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'0' AS resource_type_id,
'3' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
b2b.bitstream_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN bundle2bitstream AS b2b
ON b2b.bundle_id = i2b.bundle_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 0 AND action_id =
3 AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
b2b.bitstream_id
);
INSERT INTO resourcepolicy
(policy_id, resource_type_id, action_id, rptype, eperson_id, resource_id)
SELECT
nextval('resourcepolicy_seq') AS policy_id,
'0' AS resource_type_id,
'4' AS action_id,
'TYPE_WORKFLOW' AS rptype,
wfi.owner AS eperson_id,
b2b.bitstream_id AS dspace_object
FROM workflowitem AS wfi
JOIN item2bundle AS i2b
ON i2b.item_id = wfi.item_id
JOIN bundle2bitstream AS b2b
ON b2b.bundle_id = i2b.bundle_id
JOIN metadatavalue AS mv
ON mv.resource_id = i2b.bundle_id
JOIN metadatafieldregistry as mfr
ON mv.metadata_field_id = mfr.metadata_field_id
JOIN metadataschemaregistry as msr
ON mfr.metadata_schema_id = msr.metadata_schema_id
WHERE
msr.namespace = 'http://dublincore.org/documents/dcmi-terms/'
AND mfr.element = 'title'
AND mfr.qualifier IS NULL
AND mv.text_value = 'ORIGINAL'
AND wfi.owner IS NOT NULL
AND (wfi.state = 2 OR wfi.state = 4 OR wfi.state = 6)
AND NOT EXISTS(
SELECT 1 FROM resourcepolicy WHERE resource_type_id = 0 AND action_id =
4 AND resourcepolicy.eperson_id = owner AND resourcepolicy.resource_id =
b2b.bitstream_id
);
]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does
not exist: integer = uuid
Hint: No operator matches the given name and argument types. You might need
to add explicit type casts.
Position: 786
at
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
at
org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431)
at
org.dspace.storage.rdbms.DatabaseUtils.executeSql(DatabaseUtils.java:1250)
... 30 more
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist:
integer = uuid
Hint: No operator matches the given name and argument types. You might need
to add explicit type casts.
Position: 786
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at
org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at
org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
at
org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
at
org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)
... 32 more
