SQL errors on deletion of studies

[Niels Wahlgreen]

Dear all,

We have noticed some error on several of the PACS servers that we use - all of them are dcm4chee-arc-light 5.22.1 psql-secure-ui on Ubuntu

When studies are deleted, we get the following errors. We are not sure, if this means that the studies stay on the disk, or if it is just a timing issue, where they are deleted properly at the next run. But we see this a lot in the logfiles throughout the day.

We have truncated some of the not-so-interesting lines of the log, but have attached a full log file from one of the scheduled runs.

It seems like the issue started in the log files with one of the latest releases - we get the errors when manually rejecting/deleting from the UI and with scheduled deletes based on study age.

Best regards,

Niels

***

#033[0m#033[0m16:38:24,753 INFO  [org.dcm4chee.arc.delete.impl.PurgeStorageScheduler] (EE-ManagedExecutorService-default-Thread-4789) Start deletion on Storage[id=fs1, uri=file:/pacs/storage1/]
#033[0m#033[33m16:38:24,765 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EE-ManagedExecutorService-default-Thread-4789) SQL Error: 0, SQLState: 23503
#033[0m#033[31m16:38:24,766 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EE-ManagedExecutorService-default-Thread-4789) ERROR: update or delete on table "study" violates foreign key constraint "fk_1og1krtgxfh207rtqjg0r7pbd" on table "series"
  Detail: Key (pk)=(71) is still referenced from table "series".
#033[0m#033[0m16:38:24,766 INFO  [org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl] (EE-ManagedExecutorService-default-Thread-4789) HHH000010: On release of batch it still contained JDBC statements
#033[0m#033[31m16:38:24,766 ERROR [org.hibernate.internal.ExceptionMapperStandardImpl] (EE-ManagedExecutorService-default-Thread-4789) HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute statement]
#033[0m#033[33m16:38:24,766 WARN  [com.arjuna.ats.arjuna] (EE-ManagedExecutorService-default-Thread-4789) ARJUNA012125: TwoPhaseCoordinator.beforeCompletion - failed for SynchronizationImple< 0:ffff0a9c011d:709bd884:5eb0d7f5:1ef54, org.wildfly.transaction.client.AbstractTransaction$AssociatingSynchronization@396f884c >: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
...
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "study" violates foreign key constraint "fk_1og1krtgxfh207rtqjg0r7pbd" on table "series"
  Detail: Key (pk)=(71) is still referenced from table "series".
...
#033[0m#033[31m16:38:24,767 ERROR [org.jboss.as.ejb3.invocation] (EE-ManagedExecutorService-default-Thread-4789) WFLYEJB0034: EJB Invocation failed on component DeletionServiceEJB for method public org.dcm4chee.arc.entity.Study org.dcm4chee.arc.delete.impl.DeletionServiceEJB.deleteStudy(org.dcm4chee.arc.delete.StudyDeleteContext): javax.ejb.EJBTransactionRolledbackException: javax.transaction.RollbackException: ARJUNA016053: Could not commit transaction.
...
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "study" violates foreign key constraint "fk_1og1krtgxfh207rtqjg0r7pbd" on table "series"
  Detail: Key (pk)=(71) is still referenced from table "series".
...
#033[0m#033[33m16:38:24,768 WARN  [org.dcm4chee.arc.delete.impl.PurgeStorageScheduler] (EE-ManagedExecutorService-default-Thread-4789) Failed to delete Study[pk=71, uid=1.2.826.0.1.3680043.9.1471.11.901.2] on Storage[id=fs1, uri=file:/pacs/storage1/]: javax.ejb.EJBTransactionRolledbackException: javax.transaction.RollbackException: ARJUNA016053: Could not commit transaction.
...
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "study" violates foreign key constraint "fk_1og1krtgxfh207rtqjg0r7pbd" on table "series"
  Detail: Key (pk)=(71) is still referenced from table "series".
#033[0m#033[33m16:38:25,085 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EE-ManagedExecutorService-default-Thread-4789) SQL Error: 0, SQLState: 23503
#033[0m#033[31m16:38:25,085 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EE-ManagedExecutorService-default-Thread-4789) ERROR: update or delete on table "series" violates foreign key constraint "fk_s4tgrew4sh4qxoupmk3gihtrk" on table "instance"
  Detail: Key (pk)=(514) is still referenced from table "instance".
#033[0m#033[0m16:38:25,085 INFO  [org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl] (EE-ManagedExecutorService-default-Thread-4789) HHH000010: On release of batch it still contained JDBC statements
#033[0m#033[31m16:38:25,086 ERROR [org.hibernate.internal.ExceptionMapperStandardImpl] (EE-ManagedExecutorService-default-Thread-4789) HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute statement]
#033[0m#033[33m16:38:25,086 WARN  [com.arjuna.ats.arjuna] (EE-ManagedExecutorService-default-Thread-4789) ARJUNA012125: TwoPhaseCoordinator.beforeCompletion - failed for SynchronizationImple< 0:ffff0a9c011d:709bd884:5eb0d7f5:1ef59, org.wildfly.transaction.client.AbstractTransaction$AssociatingSynchronization@1ff941b0 >: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement