Lingering requests

[Baer,Helen]

Hello DSpacers,

 

We are unable to delete some collections and subcommunities. We think this is because there are leftover requests from when we got hit by Russian spammers. Here's the error message:

 

ERROR: update or delete on table "item" violates foreign key constraint "requestitem_item_id_fkey" on table "requestitem" Detail: Key (uuid)=(b57203c3-0c68-4471-9771-0175e3ecab81) is still referenced from table "requestitem".

 

How do we find these requests so we can get rid of them?

 

Best,

 

Helen Baer

Colorado State University Libraries

[FILIPPOS KOLOVOS]

Well, the error explanation is straightforward, but the resolution might require a bit more work. It actually says that you cannot delete an item, which is located in the item table, because it is referenced by another table, the requestitem table, which table has a constraint to forbid the deletion of items from the item table, if there are references to this table from the requestitem table (that is the requestitem_item_id_fkey constraint that states that).

If you connect to postgresql server as the dspace user and to the dspace database and execute a \dS requestitem, you will see the structure and the constraints of the requestitem table as follows:

dspace=> \dS requestitem;
                Table "public.requestitem"
     Column      |            Type             | Modifiers
-----------------+-----------------------------+-----------
 requestitem_id  | integer                     | not null
 token           | character varying(48)       |
 allfiles        | boolean                     |
 request_email   | character varying(64)       |
 request_name    | character varying(64)       |
 request_date    | timestamp without time zone |
 accept_request  | boolean                     |
 decision_date   | timestamp without time zone |
 expires         | timestamp without time zone |
 request_message | text                        |
 item_id         | uuid                        |
 bitstream_id    | uuid                        |
Indexes:
    "requestitem_pkey" PRIMARY KEY, btree (requestitem_id)
    "requestitem_token_key" UNIQUE CONSTRAINT, btree (token)
    "requestitem_bitstream" btree (bitstream_id)
    "requestitem_item" btree (item_id)
Foreign-key constraints:
    "requestitem_bitstream_id_fkey" FOREIGN KEY (bitstream_id) REFERENCES bitstream(uuid)
    "requestitem_item_id_fkey" FOREIGN KEY (item_id) REFERENCES item(uuid)

These requests are requests that have been made from users for items in your repository that were not available for download. You seem to have one and it is causing the error and you have to delete it from the requestitems table, before deleting the items from the collections.

DISCLAIMER: The following commands must be carried out by trained IT personnel of your institution or organisation. Wrong usage of these commands might lead to making your dspace instance inoperable and its database irrecoverably inconsistent. You should also keep a backup of your database before you try working directly in the database management system.

Unfortunately, as far as I know, there are no menu commands from the administration interface that can give you the ability to manipulate these requests for items, which means that you must find them and delete them manually and directly from the database.

What you can do in this case, is to connect to the postgresql server as user of your dspace instance and to the database of your instance, and then issue the command:

SELECT * FROM requestitem WHERE item_id='b57203c3-0c68-4471-9771-0175e3ecab81';

You have to put in there, the uuid from the error report of the deletion. This command will retrieve the request from the requestitem table that is causing the problem. Then you must issue the command:

DELETE FROM requestitem WHERE item_id='b57203c3-0c68-4471-9771-0175e3ecab81';

It will report on how many rows were affected (deleted) from requestitem and it should be equal to the number of rows returned from the SELECT command.

This command will delete the request from the table of requestitem. THEN, you should retry the deletion of items from the graphical user interface of dspace and see if it completes. DO NOT try to delete the items directly from the items table in the database, because in this case you will corrupt the database.

I hope to have helped you.

Best Regards,

-Fk

--
All messages to this mailing list should adhere to the Code of Conduct: https://duraspace.org/about/policies/code-of-conduct/
---
You received this message because you are subscribed to the Google Groups "DSpace Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-communi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-community/CY4PR07MB314258EC4AE3606E1A11693E88749%40CY4PR07MB3142.namprd07.prod.outlook.com.