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=B6C011F3369B678E2FB9ED4B0050: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] >> 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] > .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
