Error on deleting studies with MySQL on 5.33.1

[Juan Manuel Rivera]

Hello,

I was updating from 5.27.0 to 5.33.1 on the MySQL Secure version. Neverthless, when deleting a study from the UI I run into an error:

Environment

• dcm4chee-arc-light 5.33.1 (dcm4chee-arc-ear-5.33.1-mysql-secure.ear)
• WildFly 34.0.1.Final, Hibernate 6.6.1.Final, MySQL Connector/J 8.3.0
• Database: Amazon Aurora MySQL (MySQL 8.0 compatible)
• "Allow delete study permanently" set to rejected-only (the study is rejected first, then deleted)

Where it fails
The failure is in DeletionServiceEJB.markForDeletion(DeletionServiceEJB.java:343), called from DeletionServiceImpl.deleteStudy(DeletionServiceImpl.java:234). It is a bulk HQL update executed through QuerySqmImpl.executeUpdate. The transaction rolls back, so the study stays in the database.

This query is build in dcm4chee-arc-entity/src/main/java/org/dcm4chee/arc/entity/Location.java, on line 123:

        @NamedQuery(name = Location.MARK_FOR_DELETION_BY_STUDY,
                query = "update Location l set l.status = ?2, l.instance = null " +
                        "where l in (" +
                        "select l2 from Location l2 where l2.instance.series.study = ?1 " +
                        "and l2.status != ?2 and l2.objectType = ?3 " +
                        "and l2.multiReference is null and l2.uidMap.pk is null)"),

Cause
MySQL does not allow an UPDATE on a table that is also read in a subquery (error 1093), while PostgreSQL accepts the same statement. The query selects location pks and then updates location in the same statement.

I found an issue from 2025 with this same problem issue 4847