DSpace 7.4 Problem with the cleanup operation

[Technologiczny Informator]

Hi,

I have a problem with the cleanup operation. I am getting an error like this:

[dspace@dspace dspace]$ ./bin/dspace cleanup
Exception: org.hibernate.exception.ConstraintViolationException: could not execute statement
javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1626)
        at org.hibernate.query.Query.getResultList(Query.java:165)
        at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:76)
        at org.dspace.core.AbstractHibernateDAO.executeCriteriaQuery(AbstractHibernateDAO.java:427)
        at org.dspace.core.AbstractHibernateDAO.list(AbstractHibernateDAO.java:162)
        at org.dspace.content.dao.impl.BitstreamDAOImpl.findDuplicateInternalIdentifier(BitstreamDAOImpl.java:65)
        at org.dspace.content.BitstreamServiceImpl.findDuplicateInternalIdentifier(BitstreamServiceImpl.java:351)
        at org.dspace.storage.bitstore.BitstreamStorageServiceImpl.cleanup(BitstreamStorageServiceImpl.java:287)
        at org.dspace.storage.bitstore.Cleanup.main(Cleanup.java:81)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:568)
        at org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:277)
        at org.dspace.app.launcher.ScriptLauncher.handleScript(ScriptLauncher.java:133)
        at org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:98)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:109)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3702)
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3958)
        at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:123)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
        at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
        at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:721)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
        at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:344)
        at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:57)
        at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107)
        at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1367)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1447)
        at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
        ... 15 more
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "bitstream" violates foreign key constraint "bundle_primary_bitstream_id_fkey" on table "bundle"
  Szczegóły: Key (uuid)=(8bd73341-cb91-4e8b-be4b-bb994a9e7ec5) is still referenced from table "bundle".
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:149)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
        ... 29 more

In verbose mode it looks like this:

[dspace@dspace dspace]$ ./bin/dspace cleanup -v
 - Deleting bitstream information (ID: 94bfc45b-4e73-41a6-bacb-eb23666b84fe)
 - Deleting bitstream record from database (ID: 94bfc45b-4e73-41a6-bacb-eb23666b84fe)
 - Deleting bitstream information (ID: 942aad03-6a8d-4f26-82f4-c12978b2f147)
 - Deleting bitstream record from database (ID: 942aad03-6a8d-4f26-82f4-c12978b2f147)
 - Deleting bitstream information (ID: 8bd73341-cb91-4e8b-be4b-bb994a9e7ec5)
 - Deleting bitstream record from database (ID: 8bd73341-cb91-4e8b-be4b-bb994a9e7ec5)
Exception: org.hibernate.exception.ConstraintViolationException: could not execute statement
javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1626)
        at org.hibernate.query.Query.getResultList(Query.java:165)
        at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:76)
        at org.dspace.core.AbstractHibernateDAO.executeCriteriaQuery(AbstractHibernateDAO.java:427)
        at org.dspace.core.AbstractHibernateDAO.list(AbstractHibernateDAO.java:162)
        at org.dspace.content.dao.impl.BitstreamDAOImpl.findDuplicateInternalIdentifier(BitstreamDAOImpl.java:65)
        at org.dspace.content.BitstreamServiceImpl.findDuplicateInternalIdentifier(BitstreamServiceImpl.java:351)
        at org.dspace.storage.bitstore.BitstreamStorageServiceImpl.cleanup(BitstreamStorageServiceImpl.java:287)
        at org.dspace.storage.bitstore.Cleanup.main(Cleanup.java:81)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:568)
        at org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:277)
        at org.dspace.app.launcher.ScriptLauncher.handleScript(ScriptLauncher.java:133)
        at org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:98)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:109)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3702)
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3958)
        at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:123)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
        at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
        at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:721)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
        at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:344)
        at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:57)
        at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107)
        at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1367)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1447)
        at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
        ... 15 more

Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "bitstream" violates foreign key constraint "bundle_primary_bitstream_id_fkey" on table "bundle"
  Szczegóły: Key (uuid)=(8bd73341-cb91-4e8b-be4b-bb994a9e7ec5) is still referenced from table "bundle".
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:149)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
        ... 29 more

Thank you very much for any hints.

Regards,

Mariusz

[Technologiczny Informator]

Hi all,

I reported this problem at the end of December last year, but no one responded. That's why I'm writing again, because the problem has not disappeared after updating to DSpace 7.5.
Not only that ... I had the opportunity to update to version 7.5 on a completely different database than mine. Her assetstore folder is over 170 GB. The owner of this DSpace wanted to split the database into two separate instances. This is what happened and in each of them a large number of items had to be removed (over 24,000 in one of them). After this operation, I wanted to clean the database with the cleanup option, but the effect so far - I only post the error message this time::

Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "bitstream" violates foreign key constraint "bundle_primary_bitstream_id_fkey" on table "bundle"

  Detail: Key (uuid)=(02caead4-de93-4011-8105-0e6921f286d8) is still referenced from table "bundle".

Of course, the size of the assetstore folder has not changed. Maybe someone can help this time.

Thank you in advance.

Regards,

Mariusz

[Mark H. Wood]

On Mon, Jun 12, 2023 at 10:58:26PM -0700, Technologiczny Informator wrote:
> I reported this problem at the end of December last year, but no one
> responded. That's why I'm writing again, because the problem has not
> disappeared after updating to DSpace 7.5.
> Not only that ... I had the opportunity to update to version 7.5 on a
> completely different database than mine. Her assetstore folder is over 170
> GB. The owner of this DSpace wanted to split the database into two separate
> instances. This is what happened and in each of them a large number of
> items had to be removed (over 24,000 in one of them). After this operation,
> I wanted to clean the database with the cleanup option, but the effect so
> far - I only post the error message this time::
>
> Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on
> table "bitstream" violates foreign key constraint
> "bundle_primary_bitstream_id_fkey" on table "bundle"
> Detail: Key (uuid)=(02caead4-de93-4011-8105-0e6921f286d8) is still
> referenced from table "bundle".

I get those sometimes. Here are the notes that I made for myself
about manually resolving the situation:

DSpace 6+:

\set a BITSTREAM-UUID
SELECT uuid AS b FROM bundle WHERE primary_bitstream_id = :'a';
\gset
SELECT * FROM bundle2bitstream WHERE bundle_id = :'b';

-- Is bitstream A in bundle B? Yes: problem?

SELECT deleted FROM bitstream WHERE uuid = :'a';

-- Deleted? No: problem?

UPDATE bundle SET primary_bitstream_id = null WHERE uuid = :'b';

where BITSTREAM-UUID is replaced by the actual UUID. I type the first
'\set' in by hand and paste the UUID; after that I just copy/paste
each command and watch for errors, including those noted in the
comments. This procedure isn't *quite* right yet to be scripted, but
it works for me.

I sometimes find that there are multiple bitstreams with this problem.
Unfortunately, the cleanup tool exits after the first one (aborting
its single transaction, alas!), so sometimes I need to run cleanup
several times, fixing one bitstream at a time.

--
Mark H. Wood
Lead Technology Analyst

University Library
Indiana University - Purdue University Indianapolis
755 W. Michigan Street
Indianapolis, IN 46202
317-274-0749
www.ulib.iupui.edu

[Technologiczny Informator]

Hi Mark,

your answer was very helpful. I have about 35 records of this type. I have a lot of work ahead of me. Thank you very much.

I will add that my repository is after updating from DSpace 5.5. I have no way to check now whether the problem was still in the old database or was created only in DSpace 7. So it cannot definitely be connected with the potential problems of the new version.

Regards,

Mariusz