Is the issue with withdrawing vs. deleting an Item primarily about not allowing
its persistent identifier to be "reassigned/reused"?
Sue Walker-Thornton
Software Developer|Database Administrator
NASA Langley Research Center
SGT, Inc.|LITES Contract
130 Research Drive
Hampton, VA 23666
Office: (757) 864-2368|Fax: (757) 224-4001|Mobile: (757) 506-9903
Email: [email protected]
________________________________
From: Claudia Juergen [[email protected]]
Sent: Friday, February 03, 2012 6:00 PM
To: Thornton, Susan M. (LARC-B702)[LITES]
Cc: [email protected]; Dedmond, Nicole K. (LARC-B702)[LITES]
Subject: Re: [Dspace-tech] Trouble deleting records online in DSpace 1.7.1
Hello Susan,
good, otherwise you wouldn't have been able to to delete a collection or
communitiy. This will most likely occur less frequently, allthough with
regards to persistent id's deleting an item shouldn't occur too, rather
withdrawing it.
cu
Claudia Jürgen
> I did go ahead and delete the other 2 constraints you mentioned, however I
> was able to delete a record online once I dropped just the collection2item
> $2 constraint.
> Thanks again for helping us solve this outstanding problem we've had since
> implementing 1.7.1!
> Best regards,
> Sue
>
>
> Sue Walker-Thornton
> (w): (757) 864-2368
> (m): (757) 506-9903
>
>
> -----Original Message-----
> From: Claudia Juergen [mailto:[email protected]]
> Sent: Friday, February 03, 2012 12:51 PM
> To: Thornton, Susan M. (LARC-B702)[LITES]
> Cc: [email protected]; Dedmond, Nicole K.
> (LARC-B702)[LITES]
> Subject: Re: [Dspace-tech] Trouble deleting records online in DSpace 1.7.1
>
> Hello Susan,
>
> to be precise, there shoul have been 3 constraints referenced as $n to be
> deleted in:
> collection2item
> community2community
> community2collection
> as mentioned in my first post.
>
> Claudia
>
>
>> Thanks so much Claudia! This fixed our problem!!
>> Sue
>>
>>
>> Sue Walker-Thornton
>> (w): (757) 864-2368
>> (m): (757) 506-9903
>>
>>
>> -----Original Message-----
>> From: Claudia Jürgen [mailto:[email protected]]
>> Sent: Friday, February 03, 2012 10:07 AM
>> To: [email protected]
>> Subject: Re: [Dspace-tech] Trouble deleting records online in DSpace
>> 1.7.1
>>
>> Hello Susan,
>>
>> no, in the case you ran into the below mentioned problem, you got to
>> drop the "nameless" constraints, e.g.:
>>
>> ALTER TABLE collection2item DROP CONSTRAINT "$2"
>>
>> The error you posted
>> "org.postgresql.util.PSQLException: ERROR: update or delete on table
>> "item" violates foreign key constraint "$2" on table "collection2item""
>> Detail: Key (item_id)=(429349) is still referenced from table
>> "collection2item""
>> indicates that these constraints were not dropped.
>>
>> Hope that helps
>>
>> Claudia Jürgen
>>
>>
>>
>> Am 03.02.2012 15:52, schrieb Thornton, Susan M. (LARC-B702)[LITES]:
>>> Hi Claudia,
>>>
>>> I don't remember getting these errors when upgrading from 1.5
>>> to 1.6, but we never really "implemented" 1.6 - I ran what I needed
>>> to run for database changes from 1.5 --> 1.6 -- 1.7 and implemented
>>> 1.7.1, so I may have missed it.
>>>
>>>
>>>
>>> So is this what you're saying I need to do?
>>>
>>>
>>>
>>> ALTER TABLE collection2item DROP CONSTRAINT
>>> collection2item_item_id_fkey;
>>>
>>>
>>>
>>> ALTER TABLE community2community DROP CONSTRAINT
>>> community2community_child_comm_id_fkey;
>>>
>>>
>>>
>>> ALTER TABLE community2collection DROP CONSTRAINT
>>> community2collection_collection_id_fkey;
>>>
>>>
>>>
>>> Thanks a bunch,
>>>
>>> Sue
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Sue Walker-Thornton
>>>
>>> (w): (757) 864-2368
>>>
>>> (m): (757) 506-9903
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: Claudia Jürgen [mailto:[email protected]]
>>> Sent: Thursday, February 02, 2012 3:31 AM
>>> To: [email protected]
>>> Subject: Re: [Dspace-tech] Trouble deleting records online in DSpace
>>> 1.7.1
>>>
>>>
>>>
>>> Hello Susan,
>>>
>>>
>>>
>>>
>>>
>>> while updating to 1.6 did you get some errors like that:
>>>
>>>
>>>
>>> psql:database_schema_15-16.sql:105: ERROR: constraint
>>> "community2collection_collection_id_fkey" of relation
>>> "community2collection" does not exist ALTER TABLE
>>>
>>> psql:database_schema_15-16.sql:108: ERROR: constraint
>>> "community2community_child_comm_id_fkey" of relation
>>> "community2community" does not exist ALTER TABLE
>>>
>>> psql:database_schema_15-16.sql:111: ERROR: constraint
>>> "collection2item_item_id_fkey" of relation "collection2item" does not
>>> exist
>>>
>>>
>>>
>>> If so, inspect your database and use the references for the foreign
>>> keys to delete them, e.g::
>>>
>>>
>>>
>>> psql [YourDBName]
>>>
>>> \d [TableName]
>>>
>>> will show you the foreign keys, e.g.:
>>>
>>> \d community2collection
>>>
>>>
>>>
>>> Table "public.community2collection"
>>>
>>> Column | Type | Modifiers
>>>
>>> ---------------+---------+-----------
>>>
>>> id | integer | not null
>>>
>>> community_id | integer |
>>>
>>> collection_id | integer |
>>>
>>> Indexes:
>>>
>>> "community2collection_pkey" PRIMARY KEY, btree (id)
>>>
>>> "community2collection_collection_id_idx" btree (collection_id)
>>>
>>> "community2collection_community_id_idx" btree (community_id)
>>> Foreign-key constraints:
>>>
>>> "$1" FOREIGN KEY (community_id) REFERENCES
>>> community(community_id)
>>>
>>> "$2" FOREIGN KEY (collection_id) REFERENCES
>>> collection(collection_id)
>>>
>>>
>>>
>>> In that case you got to run:
>>>
>>>
>>>
>>> ALTER TABLE collection2item DROP CONSTRAINT "$2"; ALTER TABLE
>>> community2community DROP CONSTRAINT "$2"; ALTER TABLE
>>> community2collection DROP CONSTRAINT "$2";
>>>
>>>
>>>
>>> The reason for this is:
>>>
>>>
>>>
>>> In Step 7 Update the database it can happen that not all the commands
>>> in the database_schema_15-16.sql are executed properly.
>>>
>>>
>>>
>>> The commands to drop foreign keys:
>>>
>>>
>>>
>>> ALTER TABLE collection2item DROP CONSTRAINT
>>> collection2item_item_id_fkey;
>>>
>>>
>>>
>>> ALTER TABLE community2community DROP CONSTRAINT
>>> community2community_child_comm_id_fkey;
>>>
>>>
>>>
>>> ALTER TABLE community2collection DROP CONSTRAINT
>>> community2collection_collection_id_fkey;
>>>
>>>
>>>
>>> assume that the keys got default names, whereas depending on the
>>> database version and operating system on which your instance started,
>>> they can have no names and just be counted internally.
>>>
>>>
>>>
>>> If you got no names or other names the script will skip the commands,
>>> with the above mentioned ERROR.
>>>
>>>
>>>
>>> This will lead to errors running DSpace as now 2 contraints exist and
>>> one is unable to delete items, collections etc.
>>>
>>>
>>>
>>> Hope that helps
>>>
>>>
>>>
>>> Claudia Jürgen
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Am 01.02.2012 20:46, schrieb Thornton, Susan M. (LARC-B702)[LITES]:
>>>
>>>> Hello,
>>>
>>>> After recently upgrading from DSpace 1.5.1 to 1.7.1, we are
>>>> getting the following error whenever we try to delete an Item
>>>> online:
>>>
>>>>
>>>
>>>> 2012-02-01 14:40:17,883 WARN
>>>> org.dspace.app.webui.servlet.DSpaceServlet @
>>>> [email protected]:session_id=B6C011F3369B678E2FB9ED4B0050:ip
>>>> _addr=999.99.99.999:database_error:org.postgresql.util.PSQLException
>>>> \colon<mailto:[email protected]:session_id=B6C011F3369B678E2
>>>> FB9ED4B0050:ip_addr=999.99.99.999:database_error:org.postgresql.util
>>>> .PSQLException\colon>; ERROR\colon; update or delete on table "item"
>>>> violates foreign key constraint "$2" on table "collection2item"
>>>
>>>> Detail\colon; Key (item_id)=(429349) is still referenced from
>>>> table "collection2item".
>>>
>>>> org.postgresql.util.PSQLException: ERROR: update or delete on table
>>>> "item" violates foreign key constraint "$2" on table "collection2item"
>>>
>>>> Detail: Key (item_id)=(429349) is still referenced from table
>>>> "collection2item".
>>>
>>>>
>>>
>>>>
>>>
>>>> I found some old dspace-tech postings regarding this error and am
>>>> wondering if the recommended fix is correct?
>>>
>>>>
>>>
>>>> Not sure how this one happened: after upgrading to dspace 1.6,
>>>> we're
>>>
>>>> having trouble deleting items from dspace. We get an error message
>>>
>>>> explaining that 'update or delete on "item" violates foreign key
>>>
>>>> constraint "$2" on "collection2item"'. Describing collection2item
>>>> yields:
>>>
>>>>
>>>
>>>> Table "public.collection2item"
>>>
>>>> Column | Type | Modifiers
>>>
>>>> ---------------+---------+-----------
>>>
>>>> id | integer | not null
>>>
>>>> collection_id | integer |
>>>
>>>> item_id | integer |
>>>
>>>> Indexes:
>>>
>>>> "collection2item_pkey" PRIMARY KEY, btree (id)
>>>
>>>> "collection2item_collection_idx" btree (collection_id)
>>>
>>>> "collection2item_item_id_idx" btree (item_id) Foreign-key
>>>
>>>> constraints:
>>>
>>>> "$1" FOREIGN KEY (collection_id) REFERENCES
>>>> collection(collection_id)
>>>
>>>> "$2" FOREIGN KEY (item_id) REFERENCES item(item_id)
>>>
>>>> "coll2item_item_fk" FOREIGN KEY (item_id) REFERENCES
>>>
>>>> item(item_id) DEFERRABLE
>>>
>>>>
>>>
>>>> I ran the database scheme update script, and it seems to have added
>>>
>>>> the final, deferrable foreign key constraint; but I can't find any
>>>
>>>> reference at all to the first two FKCs ($1 and $2). As far as I can
>>>
>>>> tell, they aren't supposed to exist at all; I assume they're left
>>>> over
>>>
>>>> from some previous version of dspace, didn't get removed at some
>>>> point
>>>
>>>> in the past, and just never caused problems until now. Can anybody
>>>
>>>> confirm that I can go ahead and drop these constraints (and similar
>>>> constraints in Community2Collection?
>>>
>>>>
>>>
>>>> Bill Anderson
>>>
>>>> Software Developer
>>>
>>>> Digital Library Development
>>>
>>>> Georgia Tech Library
>>>
>>>>
>>>
>>>> Thanks in advance!
>>>
>>>> Sue
>>>
>>>>
>>>
>>>> Sue Walker-Thornton
>>>
>>>> Software Developer/Database Administrator NASA Langley Research
>>>> Center
>>>
>>>> - LITES Contract
>>>> [email protected]<mailto:[email protected]>
>>>
>>>> (W) 757-864-2368
>>>
>>>> (M) 757-506-9903
>>>
>>>>
>>>
>>>>
>>>
>>>>
>>>
>>>>
>>>
>>>>
>>>
>>>> --------------------------------------------------------------------
>>>> -
>>>> -
>>>
>>>> -------- Keep Your Developer Skills Current with LearnDevNow!
>>>
>>>> The most comprehensive online learning library for Microsoft
>>>
>>>> developers is just $99.99! Visual Studio, SharePoint, SQL - plus
>>>
>>>> HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when
>>>> you subscribe now!
>>>
>>>> http://p.sf.net/sfu/learndevnow-d2d
>>>
>>>>
>>>
>>>>
>>>
>>>>
>>>
>>>> _______________________________________________
>>>
>>>> DSpace-tech mailing list
>>>
>>>> [email protected]<mailto:[email protected]
>>>> g
>>>> e.net>
>>>
>>>> https://lists.sourceforge.net/lists/listinfo/dspace-tech
>>>
>>>
>>>
>>> --
>>>
>>> Claudia Juergen
>>>
>>> Universitaetsbibliothek Dortmund
>>>
>>> Eldorado
>>>
>>> 0231/755-4043
>>>
>>> https://eldorado.tu-dortmund.de/
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> -
>>> --------
>>>
>>> Keep Your Developer Skills Current with LearnDevNow!
>>>
>>> The most comprehensive online learning library for Microsoft
>>> developers is just $99.99! Visual Studio, SharePoint, SQL - plus
>>> HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when
>>> you subscribe now!
>>>
>>> http://p.sf.net/sfu/learndevnow-d2d
>>>
>>> _______________________________________________
>>>
>>> DSpace-tech mailing list
>>>
>>> [email protected]<mailto:[email protected]
>>> e
>>> .net>
>>>
>>> https://lists.sourceforge.net/lists/listinfo/dspace-tech
>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> -
>>> -------- Try before you buy = See our experts in action!
>>> The most comprehensive online learning library for Microsoft
>>> developers is just $99.99! Visual Studio, SharePoint, SQL - plus
>>> HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when
>>> you subscribe now!
>>> http://p.sf.net/sfu/learndevnow-dev2
>>>
>>>
>>>
>>> _______________________________________________
>>> DSpace-tech mailing list
>>> [email protected]
>>> https://lists.sourceforge.net/lists/listinfo/dspace-tech
>>
>> --
>> Claudia Juergen
>> Universitaetsbibliothek Dortmund
>> Eldorado
>> 0231/755-4043
>> https://eldorado.tu-dortmund.de/
>>
>> ----------------------------------------------------------------------
>> -------- Try before you buy = See our experts in action!
>> The most comprehensive online learning library for Microsoft
>> developers is just $99.99! Visual Studio, SharePoint, SQL - plus
>> HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you
>> subscribe now!
>> http://p.sf.net/sfu/learndevnow-dev2
>> _______________________________________________
>> DSpace-tech mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/dspace-tech
>>
>> ----------------------------------------------------------------------
>> -------- Try before you buy = See our experts in action!
>> The most comprehensive online learning library for Microsoft
>> developers is just $99.99! Visual Studio, SharePoint, SQL - plus
>> HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you
>> subscribe now!
>> http://p.sf.net/sfu/learndevnow-dev2
>> _______________________________________________
>> DSpace-tech mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/dspace-tech
>>
>
>
> Mit freundlichen Gruessen
>
> Claudia Juergen
> Universitaetsbibliothek Dortmund
> Eldorado
> 0231/755-4043
> https://eldorado.tu-dortmund.de/
> Tel.: 0049-231-755-4043
>
>
>
>
> ------------------------------------------------------------------------------
> Try before you buy = See our experts in action!
> The most comprehensive online learning library for Microsoft developers
> is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
> Metro Style Apps, more. Free future releases when you subscribe now!
> http://p.sf.net/sfu/learndevnow-dev2
> _______________________________________________
> DSpace-tech mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/dspace-tech
>
Mit freundlichen Gruessen
Claudia Juergen
Universitaetsbibliothek Dortmund
Eldorado
0231/755-4043
https://eldorado.tu-dortmund.de/
Tel.: 0049-231-755-4043
------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
