Re: [Dspace-tech] DSpace cleanup error
Michael, if you can confirm that Brian's solution indeed works, then we can file this as a bug. This problem never occurred to me. It is also possible that those items with a primary bitstream set (that wasn't cleared when they were deleted or during the cleanup run) come from an older version of DSpace, in which case we still should check for it in the cleanup code. Regards, ~~helix84 Compulsory reading: DSpace Mailing List Etiquette https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_mar ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] DSpace cleanup error
On Thu, Mar 14, 2013 at 2:11 PM, Hilton Gibson hilton.gib...@gmail.com wrote: Nope. It does not work on a new test system using DSpace 1.8.2. My bundle table has blanks for primary_bitstream_id. Here are my results (DSpace 3.0, database originally from 1.6, but cleaned in the past): dspace= SELECT bundle.* FROM bitstream, bundle2bitstream, bundle WHERE bitstream.bitstream_id = bundle2bitstream.bitstream_id AND bundle2bitstream.bundle_id = bundle.bundle_id AND deleted='t'; bundle_id | name | primary_bitstream_id ---+--+-- (0 rows) Regards, ~~helix84 Compulsory reading: DSpace Mailing List Etiquette https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_mar___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] DSpace cleanup error
This works in isolating the deleted bitstreams. SELECT bitstream.* FROM public.bitstream WHERE bitstream.deleted = 't'; On 14 March 2013 15:59, helix84 heli...@centrum.sk wrote: On Thu, Mar 14, 2013 at 2:11 PM, Hilton Gibson hilton.gib...@gmail.com wrote: Nope. It does not work on a new test system using DSpace 1.8.2. My bundle table has blanks for primary_bitstream_id. Here are my results (DSpace 3.0, database originally from 1.6, but cleaned in the past): dspace= SELECT bundle.* FROM bitstream, bundle2bitstream, bundle WHERE bitstream.bitstream_id = bundle2bitstream.bitstream_id AND bundle2bitstream.bundle_id = bundle.bundle_id AND deleted='t'; bundle_id | name | primary_bitstream_id ---+--+-- (0 rows) Regards, ~~helix84 Compulsory reading: DSpace Mailing List Etiquette https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- *Hilton Gibson* Systems Administrator JS Gericke Library Room 1025D Stellenbosch University Private Bag X5036 Stellenbosch 7599 South Africa Tel: +27 21 808 4100 | Cell: +27 84 646 4758 http://library.sun.ac.za http://scholar.sun.ac.za http://ar1.sun.ac.za http://aj1.sun.ac.za -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_mar___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] DSpace cleanup error
And this gets me the primary_bitstream_id. SELECT bitstream.deleted, bundle.primary_bitstream_id FROM public.bitstream, public.bundle, public.bundle2bitstream WHERE bitstream.deleted = 't'; So the complete query is: UPDATE SET bundle.primary_bitstream_id = 'NULL' WHERE bitstream.deleted = 't' IN (SELECT bitstream.deleted, bundle.primary_bitstream_id FROM public.bitstream, public.bundle, public.bundle2bitstream WHERE bitstream.deleted = 't';) Is this correct? On 14 March 2013 15:59, helix84 heli...@centrum.sk wrote: On Thu, Mar 14, 2013 at 2:11 PM, Hilton Gibson hilton.gib...@gmail.com wrote: Nope. It does not work on a new test system using DSpace 1.8.2. My bundle table has blanks for primary_bitstream_id. Here are my results (DSpace 3.0, database originally from 1.6, but cleaned in the past): dspace= SELECT bundle.* FROM bitstream, bundle2bitstream, bundle WHERE bitstream.bitstream_id = bundle2bitstream.bitstream_id AND bundle2bitstream.bundle_id = bundle.bundle_id AND deleted='t'; bundle_id | name | primary_bitstream_id ---+--+-- (0 rows) Regards, ~~helix84 Compulsory reading: DSpace Mailing List Etiquette https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- *Hilton Gibson* Systems Administrator JS Gericke Library Room 1025D Stellenbosch University Private Bag X5036 Stellenbosch 7599 South Africa Tel: +27 21 808 4100 | Cell: +27 84 646 4758 http://library.sun.ac.za http://scholar.sun.ac.za http://ar1.sun.ac.za http://aj1.sun.ac.za -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_mar___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] DSpace cleanup error
Hi, if you can confirm that Brian's solution indeed works, then we can file this as a bug. OK, I’ve been investigating and testing and this approach does appear to work for me – the following cleared the primary_bitstream_id from the bundles that had primary bitstreams that were marked for deletion in the bitstream table: Update bundle set primary_bitstream_id = NULL where bundle_id in (select bundle_id from bundle, bitstream where deleted = TRUE and primary_bitstream_id = bitstream_id) Before running this (on my DEV system), following the clean up yesterday, I picked things apart to see what needed to happen. There were 16 bitstreams that caused errors during my cleanup of my DEV system yesterday – to get around these errors, I reset the deleted value for each of these to FALSE so cleanup would skip them, but I had the foresight to take a note of them, so I knew the bitstream IDs of the problem bitstreams – so, visually checking the bundle table, I could see that these were all listed in the (mostly empty) primary_bitstream_id column. I then reset the deleted value back to “TRUE” for the bitstreams in question and checked the bundles again via SQL, using the following to return bundles that had a primary bitstream that had “deleted=TRUE”: select bundle_id from bundle, bitstream where deleted = TRUE and primary_bitstream_id = bitstream_id - and, cross checking from the other side, bitstreams that had “deleted=TRUE” that were also primary bitstreams: select bitstream_id from bitstream, bundle where deleted = TRUE and primary_bitstream_id = bitstream_id As all this data seemed to cross check, I then ran the Update SQL suggested by Brian (with a couple of minor syntactic tweaks) which fixed up the problem primary_bitstream_ids in the bundle table. I then ran the cleanup script again, and it happily removed the previously problematic bitstreams (and their records in the bitstream table) – excellent! This is all with DSpace v1.6.2 on Postgres . . . Hope that helps. Mike Michael White eLearning Liaison and Development (eLD) Information Services S8, Library University of Stirling Stirling SCOTLAND FK9 4LA Email: michael.wh...@stir.ac.ukmailto:michael.wh...@stir.ac.uk Tel: +44 (0) 1786 466877 Fax: +44 (0) 1786 466880 http://www.stir.ac.uk/is/staff/about/teams/aldt/#eld From: Hilton Gibson [mailto:hilton.gib...@gmail.com] Sent: 14 March 2013 13:11 To: Ivan Masár Cc: Michael White; Brian Freels-Stendel; dspace-tech@lists.sourceforge.net Subject: Re: [Dspace-tech] DSpace cleanup error Nope. It does not work on a new test system using DSpace 1.8.2. My bundle table has blanks for primary_bitstream_id. See attached. On 14 March 2013 13:58, helix84 heli...@centrum.skmailto:heli...@centrum.sk wrote: Michael, if you can confirm that Brian's solution indeed works, then we can file this as a bug. This problem never occurred to me. It is also possible that those items with a primary bitstream set (that wasn't cleared when they were deleted or during the cleanup run) come from an older version of DSpace, in which case we still should check for it in the cleanup code. Regards, ~~helix84 Compulsory reading: DSpace Mailing List Etiquette https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- Hilton Gibson Systems Administrator JS Gericke Library Room 1025D Stellenbosch University Private Bag X5036 Stellenbosch 7599 South Africa Tel: +27 21 808 4100 | Cell: +27 84 646 4758 http://library.sun.ac.za http://scholar.sun.ac.za http://ar1.sun.ac.za http://aj1.sun.ac.za -- The University of Stirling is ranked in the top 50 in the world in The Times Higher Education 100 Under 50 table, which ranks the world's best 100 universities under 50 years old. The University of Stirling is a charity registered in Scotland, number SC 011159. -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_mar___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] DSpace cleanup error
Hi, I get this error when doing a cleanup with -v. Error: ERROR: update or delete on table bitstream violates foreign key constraint bundle_primary_bitstream_id_fkey on table bundle Detail: Key (bitstream_id)=(67984) is still referenced from table bundle. Is there a query that can fix this and is this a bug? I've also been plagued by this error today (slightly different text, but using v1.6.2 not v1.8.2?) whilst experimenting with cleanup on my DEV system (which is an old and probably slightly mangled copy of our PROD system). I've now completed the assetstore cleanup of our DEV system, but I'm building up to doing it on our PROD system (which has 3 times as many records) so I would also be interested if there is a nice bit of SQL that can find the problem records and sort them out in one go :). The first few times I got this error, I checked our bundle2bitstream table, but there was no reference to the bitstreams in question, so I wasn't sure where the (foreign key) reference from the bundle table could be coming from . . . Being a bit of philistine, I've just been watching the output from the job with one eye, and when it fails with the above error, sorting out the bitstream record in question by simply updating the delete flag to FALSE - i.e. forcing the cleanup task to just ignore the bitstream record in question, e.g.: UPDATE bitstream SET deleted = 'FALSE' WHERE bitstream_id = 67984 - then kick off the job again. This manual intervention is not ideal though, as you say, but it has got me past the problem records. As I said, I have no idea why this error is being generated as I could find no references to the problematic bitstreams elsewhere, so it felt like a bug to me, but this is our DEV system, and I have done lots of strange things with it over the years, so anything is possible! Regards, Mike Michael White eLearning Liaison and Development (eLD) Information Services S8, Library University of Stirling Stirling SCOTLAND FK9 4LA Email: michael.wh...@stir.ac.ukmailto:michael.wh...@stir.ac.uk Tel: +44 (0) 1786 466877 Fax: +44 (0) 1786 466880 http://www.stir.ac.uk/is/staff/about/teams/aldt/#eld -- Message: 1 Date: Wed, 13 Mar 2013 12:23:41 +0200 From: Hilton Gibson hilton.gib...@gmail.commailto:hilton.gib...@gmail.com Subject: [Dspace-tech] DSpace cleanup error To: dspace-tech dspace-tech@lists.sourceforge.netmailto:dspace-tech@lists.sourceforge.net Message-ID: caav1wv6de_qybsmakw7wqsufw13hntgwqevyukwbsamhokk...@mail.gmail.commailto:caav1wv6de_qybsmakw7wqsufw13hntgwqevyukwbsamhokk...@mail.gmail.com Content-Type: text/plain; charset=utf-8 Hi All I get this error when doing a cleanup with -v. Error: ERROR: update or delete on table bitstream violates foreign key constraint bundle_primary_bitstream_id_fkey on table bundle Detail: Key (bitstream_id)=(67984) is still referenced from table bundle. The fault is in the bundle2bitstream table. This table lists the order of the bitstreams, but the bitstream listed as 0 does not appear in the bundle table as the bitstream_id. Is there a query that can fix this and is this a bug? I think there are many records like this and it would take forever to fix manually. DSpace 1.8.2 and XMLUI. -- *Hilton Gibson* Systems Administrator JS Gericke Library Room 1025D Stellenbosch University Private Bag X5036 Stellenbosch 7599 South Africa Tel: +27 21 808 4100 | Cell: +27 84 646 4758 http://library.sun.ac.za http://scholar.sun.ac.za http://ar1.sun.ac.za http://aj1.sun.ac.za -- The University of Stirling is ranked in the top 50 in the world in The Times Higher Education 100 Under 50 table, which ranks the world's best 100 universities under 50 years old. The University of Stirling is a charity registered in Scotland, number SC 011159. -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_mar___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] DSpace cleanup error
Hi there, You should be able to get past this by updating the primary_bitstream_id field to Null for those records that refer to deleted bitstreams. For postgresql, try Update bundle set primary_bitstream_id = 'NULL' where bundle_id in (select bundle_id from bundle, bitstream where deleted = '1' and primary_bitstream_id = bitstream_id) That should be close, anyway. B-- From: Michael White [mailto:michael.wh...@stir.ac.uk] Sent: Wednesday, March 13, 2013 9:08 AM To: Hilton Gibson (hilton.gib...@gmail.com); dspace-tech@lists.sourceforge.net Subject: Re: [Dspace-tech] DSpace cleanup error Hi, I get this error when doing a cleanup with -v. Error: ERROR: update or delete on table bitstream violates foreign key constraint bundle_primary_bitstream_id_fkey on table bundle Detail: Key (bitstream_id)=(67984) is still referenced from table bundle. Is there a query that can fix this and is this a bug? I've also been plagued by this error today (slightly different text, but using v1.6.2 not v1.8.2?) whilst experimenting with cleanup on my DEV system (which is an old and probably slightly mangled copy of our PROD system). I've now completed the assetstore cleanup of our DEV system, but I'm building up to doing it on our PROD system (which has 3 times as many records) so I would also be interested if there is a nice bit of SQL that can find the problem records and sort them out in one go :). The first few times I got this error, I checked our bundle2bitstream table, but there was no reference to the bitstreams in question, so I wasn't sure where the (foreign key) reference from the bundle table could be coming from . . . Being a bit of philistine, I've just been watching the output from the job with one eye, and when it fails with the above error, sorting out the bitstream record in question by simply updating the delete flag to FALSE - i.e. forcing the cleanup task to just ignore the bitstream record in question, e.g.: UPDATE bitstream SET deleted = 'FALSE' WHERE bitstream_id = 67984 - then kick off the job again. This manual intervention is not ideal though, as you say, but it has got me past the problem records. As I said, I have no idea why this error is being generated as I could find no references to the problematic bitstreams elsewhere, so it felt like a bug to me, but this is our DEV system, and I have done lots of strange things with it over the years, so anything is possible! Regards, Mike Michael White eLearning Liaison and Development (eLD) Information Services S8, Library University of Stirling Stirling SCOTLAND FK9 4LA Email: michael.wh...@stir.ac.ukmailto:michael.wh...@stir.ac.uk Tel: +44 (0) 1786 466877 Fax: +44 (0) 1786 466880 http://www.stir.ac.uk/is/staff/about/teams/aldt/#eld -- Message: 1 Date: Wed, 13 Mar 2013 12:23:41 +0200 From: Hilton Gibson hilton.gib...@gmail.commailto:hilton.gib...@gmail.com Subject: [Dspace-tech] DSpace cleanup error To: dspace-tech dspace-tech@lists.sourceforge.netmailto:dspace-tech@lists.sourceforge.net Message-ID: caav1wv6de_qybsmakw7wqsufw13hntgwqevyukwbsamhokk...@mail.gmail.commailto:caav1wv6de_qybsmakw7wqsufw13hntgwqevyukwbsamhokk...@mail.gmail.com Content-Type: text/plain; charset=utf-8 Hi All I get this error when doing a cleanup with -v. Error: ERROR: update or delete on table bitstream violates foreign key constraint bundle_primary_bitstream_id_fkey on table bundle Detail: Key (bitstream_id)=(67984) is still referenced from table bundle. The fault is in the bundle2bitstream table. This table lists the order of the bitstreams, but the bitstream listed as 0 does not appear in the bundle table as the bitstream_id. Is there a query that can fix this and is this a bug? I think there are many records like this and it would take forever to fix manually. DSpace 1.8.2 and XMLUI. -- *Hilton Gibson* Systems Administrator JS Gericke Library Room 1025D Stellenbosch University Private Bag X5036 Stellenbosch 7599 South Africa Tel: +27 21 808 4100 | Cell: +27 84 646 4758 http://library.sun.ac.za http://scholar.sun.ac.za http://ar1.sun.ac.za http://aj1.sun.ac.za The University of Stirling is ranked in the top 50 in the world in The Times Higher Education 100 Under 50 table, which ranks the world's best 100 universities under 50 years old. The University of Stirling is a charity registered in Scotland, number SC 011159. -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_mar___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
