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
