Please explain the difference (CASCADING DELETE not working as expected)

JStefan · July 2, 2024, 3:22pm

Hi,

I have 2 entities:

@Entity
@Table(name = "lessonInstance")
@Data
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class LessonInstance extends SorEntity implements Serializable {
    @EmbeddedId
    private BaseIdentity baseIdentity;

    @Column(name = "name", columnDefinition = "VARCHAR(150)")
    private String name;

    @ToString.Exclude
    @OneToMany(targetEntity = LessonInstanceSchedule.class, cascade = {CascadeType.ALL})
    @JoinColumns({
            @JoinColumn(name = "schoolId", referencedColumnName = "schoolId"),
            @JoinColumn(name = "id", referencedColumnName = "id")
    })
    @JsonManagedReference
    private List<LessonInstanceSchedule> lessonInstanceScheduleList;

    ... and a lot of attributes...

}

and

LessonInstanceSchedule extends SorEntity implements Serializable {

    @EmbeddedId
    private ScheduleIdentity scheduleIdentity;

    @ToString.Exclude
    @ManyToOne(targetEntity = LessonInstance.class, fetch = FetchType.EAGER)
    @JoinColumns({
            @JoinColumn(name = "schoolId", referencedColumnName = "schoolId", insertable = false, updatable = false),
            @JoinColumn(name = "id", referencedColumnName = "id", insertable = false, updatable = false)
    })
    @JsonBackReference
    private LessonInstance lessonInstance;

    @Column(name = "startDate", columnDefinition = "DATE")
    private LocalDate startDate;

    @Column(name = "endDate", columnDefinition = "DATE")
    private LocalDate endDate;

    @Column(name = "startTime", columnDefinition = "TIME")
    private LocalTime startTime;

    @Column(name = "endTime", columnDefinition = "TIME")
    private LocalTime endTime;

    ... and whatever stuff it needs...
}

both composite IDs containing some (2, resp. 3) integers

Now there is the repo:

@Repository
public interface LessonInstanceRepo extends Repo<LessonInstance> {

    // pragmatic original approach (A)
    @Modifying
    @Query("delete from LessonInstance t where t.baseIdentity.schoolId = :schoolId")
    int deleteBySchoolId(@Param("schoolId") Integer schoolId);

    // recommended version by the book (B)
    long deleteByBaseIdentitySchoolId(@Param("schoolId") Integer schoolId);

   ...and whatever queries it needs...

}

Now the problem is:
I was trying to remove all “hand written” queries.

Both deletes seem to be identical, but version (B) seems not to fully recognize the @JoinColumns annotations, cause when calling it, I get

2024-07-01 20:40:01,392 [scheduling-1] WARN  o.h.e.j.s.SqlExceptionHelper SQL Error: 23502, SQLState: 23502 
2024-07-01 20:40:01,392 [scheduling-1] ERROR o.h.e.j.s.SqlExceptionHelper NULL nicht zulässig für Feld "SCHOOL_ID"
NULL not allowed for column "SCHOOL_ID"; SQL statement:
update lesson_instance_schedule set id=null, school_id=null where id=? and school_id=? [23502-224]

while Version (A) works fine.
So, spoken in DB language (which I must admit I feel much more comfortable with than I do with Hibernate) it occurs that a CASCADE SET NULL is executed in case (B), while the expected CASCADE DELETE is executed in Version (A).

I’d really appreciate if somebody would explain

what’s the reason of this different behaviour and
what would be the solution without @Query -

thx

PS. there are NO foreign keys defined on the database side

mbladel · July 4, 2024, 7:10am

Hi, @Repository is not an Hibernate annotation. I think you should ask these kinds of questions to the Spring folks, as we do not have any control or visibility into what their query methods do internally.

I can say that for the first case, i.e. the one using @Query and JPQL, you’re running an Hibernate mutation query and that does not trigger cascade deletes - since we do not the identity of the affected entities (i.e. rows affected by the delete query), we’re not able to understand which associations we should cascade.

JStefan · July 4, 2024, 1:19pm

Hi, thanks for the comment, but, hm, my findings were just the opposite:
Both queries seem to recognize the 1:n relationship declared by@OneToMany and @JoinColumns.
The difference is that the @Modifying @Query obviously does a DELETE CASCADE, while the derived query performs a DELETE SET NULL (speaking in DB language), thus ignoring the updatable=false attribute, causing a crash on database level (PK, NOT NULL).
But you are right, I should ask the spring guys why that happens under their hood here.

JStefan · July 15, 2024, 3:05pm

It’s like I had Internet by Provider 1 and the leased line by Provider 2. Now I have a problem and both blame each other leaving me back in the rain. Everyone in Germany knows that joke.

JStefan · July 15, 2024, 3:26pm

Mapping is a classical bidirectional one to many. pls see above.

Code (abbreviated):


    if (applicationProperties.getApi().bulkDelete()) {
      // JPQL delete
       if (lessonInstanceRepo.deleteInBulkBySchoolId(school.getId()) > 0) {
            lessonInstanceRepo.flush();
          updated = true;
       }
     } else {
       // derived query delete
       if (lessonInstanceRepo.deleteByBaseIdentitySchoolId(school.getId()) > 0)
          updated = true;
     }

    var lessonInstanceList = queryProcessor.queryLessonInstances(school);  // *** 
  // here we get 3 Lesson_instances, 3 have a li_schedule, last one has no schedule
        lessonInstanceRepo.saveAllAndFlush(lessonInstanceList);
    // Exceptions are thrown here (or at TX end if I leave out the flush)

Now , the log from the 4 cases:

case 1) FOREIGN KEY is defined in DATABASE with CASCADE DELETE
case 1a) JPQL (this is the only working variant)

Hibernate: delete from lesson_instance where school_id=?

Hibernate: insert into lesson_instance (active_enrollments, allow_over_booking, already_registered, course_number, description, end_date, max_enrollments, min_enrollments, name, planned_enrollments, start_date, topic_id, id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into lesson_instance_schedule (building, day_of_week, end_date, end_time, recurrence_period, region, room, start_date, start_time, id, schedule_id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into lesson_instance (active_enrollments, allow_over_booking, already_registered, course_number, description, end_date, max_enrollments, min_enrollments, name, planned_enrollments, start_date, topic_id, id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into lesson_instance_schedule (building, day_of_week, end_date, end_time, recurrence_period, region, room, start_date, start_time, id, schedule_id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into lesson_instance (active_enrollments, allow_over_booking, already_registered, course_number, description, end_date, max_enrollments, min_enrollments, name, planned_enrollments, start_date, topic_id, id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into lesson_instance_schedule (building, day_of_week, end_date, end_time, recurrence_period, region, room, start_date, start_time, id, schedule_id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into lesson_instance (active_enrollments, allow_over_booking, already_registered, course_number, description, end_date, max_enrollments, min_enrollments, name, planned_enrollments, start_date, topic_id, id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
# (this one has no schedule)
# Following updates make no sense cause the keys are set in QueryProcessor:
Hibernate: update lesson_instance_schedule set id=?, school_id=? where id=? and schedule_id=? and school_id=?
Hibernate: update lesson_instance_schedule set id=?, school_id=? where id=? and schedule_id=? and school_id=?
Hibernate: update lesson_instance_schedule set id=?, school_id=? where id=? and schedule_id=? and school_id=?

case 1b) Derived Query:
There is no delete statement in the log:

Hibernate: select lessoninst0_.id as id1_11_, lessoninst0_.school_id as school_i2_11_, lessoninst0_.active_enrollments as active_e3_11_, lessoninst0_.allow_over_booking as allow_ov4_11_, lessoninst0_.already_registered as already_5_11_, lessoninst0_.course_number as course_n6_11_, lessoninst0_.description as descript7_11_, lessoninst0_.end_date as end_date8_11_, lessoninst0_.max_enrollments as max_enro9_11_, lessoninst0_.min_enrollments as min_enr10_11_, lessoninst0_.name as name11_11_, lessoninst0_.planned_enrollments as planned12_11_, lessoninst0_.start_date as start_d13_11_, lessoninst0_.topic_id as topic_i14_11_ from lesson_instance lessoninst0_ where lessoninst0_.school_id=?

Hibernate: select lessoninst0_.id as id1_12_0_, lessoninst0_.school_id as school_i3_12_0_, lessoninst0_.schedule_id as schedule2_12_0_, lessoninst0_.id as id1_12_1_, lessoninst0_.schedule_id as schedule2_12_1_, lessoninst0_.school_id as school_i3_12_1_, lessoninst0_.building as building4_12_1_, lessoninst0_.day_of_week as day_of_w5_12_1_, lessoninst0_.end_date as end_date6_12_1_, lessoninst0_.end_time as end_time7_12_1_, lessoninst0_.recurrence_period as recurren8_12_1_, lessoninst0_.region as region9_12_1_, lessoninst0_.room as room10_12_1_, lessoninst0_.start_date as start_d11_12_1_, lessoninst0_.start_time as start_t12_12_1_ from lesson_instance_schedule lessoninst0_ where lessoninst0_.id=? and lessoninst0_.school_id=?
Hibernate: select lessoninst0_.id as id1_12_0_, lessoninst0_.school_id as school_i3_12_0_, lessoninst0_.schedule_id as schedule2_12_0_, lessoninst0_.id as id1_12_1_, lessoninst0_.schedule_id as schedule2_12_1_, lessoninst0_.school_id as school_i3_12_1_, lessoninst0_.building as building4_12_1_, lessoninst0_.day_of_week as day_of_w5_12_1_, lessoninst0_.end_date as end_date6_12_1_, lessoninst0_.end_time as end_time7_12_1_, lessoninst0_.recurrence_period as recurren8_12_1_, lessoninst0_.region as region9_12_1_, lessoninst0_.room as room10_12_1_, lessoninst0_.start_date as start_d11_12_1_, lessoninst0_.start_time as start_t12_12_1_ from lesson_instance_schedule lessoninst0_ where lessoninst0_.id=? and lessoninst0_.school_id=?
Hibernate: select lessoninst0_.id as id1_12_0_, lessoninst0_.school_id as school_i3_12_0_, lessoninst0_.schedule_id as schedule2_12_0_, lessoninst0_.id as id1_12_1_, lessoninst0_.schedule_id as schedule2_12_1_, lessoninst0_.school_id as school_i3_12_1_, lessoninst0_.building as building4_12_1_, lessoninst0_.day_of_week as day_of_w5_12_1_, lessoninst0_.end_date as end_date6_12_1_, lessoninst0_.end_time as end_time7_12_1_, lessoninst0_.recurrence_period as recurren8_12_1_, lessoninst0_.region as region9_12_1_, lessoninst0_.room as room10_12_1_, lessoninst0_.start_date as start_d11_12_1_, lessoninst0_.start_time as start_t12_12_1_ from lesson_instance_schedule lessoninst0_ where lessoninst0_.id=? and lessoninst0_.school_id=?
Hibernate: select lessoninst0_.id as id1_12_0_, lessoninst0_.school_id as school_i3_12_0_, lessoninst0_.schedule_id as schedule2_12_0_, lessoninst0_.id as id1_12_1_, lessoninst0_.schedule_id as schedule2_12_1_, lessoninst0_.school_id as school_i3_12_1_, lessoninst0_.building as building4_12_1_, lessoninst0_.day_of_week as day_of_w5_12_1_, lessoninst0_.end_date as end_date6_12_1_, lessoninst0_.end_time as end_time7_12_1_, lessoninst0_.recurrence_period as recurren8_12_1_, lessoninst0_.region as region9_12_1_, lessoninst0_.room as room10_12_1_, lessoninst0_.start_date as start_d11_12_1_, lessoninst0_.start_time as start_t12_12_1_ from lesson_instance_schedule lessoninst0_ where lessoninst0_.id=? and lessoninst0_.school_id=?

2024-07-15 16:26:12,576 [task-6] ERROR o.h.e.j.s.SqlExceptionHelper NULL nicht zulässig für Feld "SCHOOL_ID"
NULL not allowed for column "SCHOOL_ID"; SQL statement:
update lesson_instance_schedule set id=null, school_id=null where id=? and school_id=? [23502-224]

So, the Derived Query tries a SET null on the PK (i.e. wants to create orphans)

case 2) NO FOREIGN KEY IN DATABASE

case 2a) JPQL

Hibernate: delete from lesson_instance where school_id=?

Hibernate: insert into lesson_instance (active_enrollments, allow_over_booking, already_registered, course_number, description, end_date, max_enrollments, min_enrollments, name, planned_enrollments, start_date, topic_id, id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into lesson_instance_schedule (building, day_of_week, end_date, end_time, recurrence_period, region, room, start_date, start_time, id, schedule_id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2024-07-15 16:33:55,322 [task-1] WARN  o.h.e.j.s.SqlExceptionHelper SQL Error: 23505, SQLState: 23505 
2024-07-15 16:33:55,322 [task-1] ERROR o.h.e.j.s.SqlExceptionHelper Eindeutiger Index oder Primärschlüssel verletzt: "PUBLIC.PRIMARY_KEY_5 ON PUBLIC.LESSON_INSTANCE_SCHEDULE(SCHOOL_ID, ID, SCHEDULE_ID) VALUES ( /* key:1 */ 1, CAST(510 AS BIGINT), CAST(1 AS BIGINT))"
Unique index or primary key violation: "PUBLIC.PRIMARY_KEY_5 ON PUBLIC.LESSON_INSTANCE_SCHEDULE(SCHOOL_ID, ID, SCHEDULE_ID) VALUES ( /* key:1 */ 1, CAST(510 AS BIGINT), CAST(1 AS BIGINT))"; SQL statement:
insert into lesson_instance_schedule (building, day_of_week, end_date, end_time, recurrence_period, region, room, start_date, start_time, id, schedule_id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-224] 
2024-07-15 16:33:55,327 [task-1] ERROR d.f.s.s.BackendLoadService [SOR-LDSVC-024] Tenant 1/DEMO: Aborted loading. 
2024-07-15 16:33:55,330 [task-1] ERROR d.f.s.s.BackendLoadService org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PUBLIC.PRIMARY_KEY_5 ON PUBLIC.LESSON_INSTANCE_SCHEDULE(SCHOOL_ID, ID, SCHEDULE_ID) VALUES ( /* key:1 */ 1, CAST(510 AS BIGINT), CAST(1 AS BIGINT))"; SQL statement:
insert into lesson_instance_schedule (building, day_of_week, end_date, end_time, recurrence_period, region, room, start_date, start_time, id, schedule_id, school_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-224]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:276)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)

This means the records in LI_schedule are still there and HAVE NOT been deleted
ergo delete has not been CASCADEd down

case 2b) Derived Query
again, no DELETE

Hibernate: select lessoninst0_.id as id1_11_, lessoninst0_.school_id as school_i2_11_, lessoninst0_.active_enrollments as active_e3_11_, lessoninst0_.allow_over_booking as allow_ov4_11_, lessoninst0_.already_registered as already_5_11_, lessoninst0_.course_number as course_n6_11_, lessoninst0_.description as descript7_11_, lessoninst0_.end_date as end_date8_11_, lessoninst0_.max_enrollments as max_enro9_11_, lessoninst0_.min_enrollments as min_enr10_11_, lessoninst0_.name as name11_11_, lessoninst0_.planned_enrollments as planned12_11_, lessoninst0_.start_date as start_d13_11_, lessoninst0_.topic_id as topic_i14_11_ from lesson_instance lessoninst0_ where lessoninst0_.school_id=?
Hibernate: select lessoninst0_.id as id1_12_0_, lessoninst0_.school_id as school_i3_12_0_, lessoninst0_.schedule_id as schedule2_12_0_, lessoninst0_.id as id1_12_1_, lessoninst0_.schedule_id as schedule2_12_1_, lessoninst0_.school_id as school_i3_12_1_, lessoninst0_.building as building4_12_1_, lessoninst0_.day_of_week as day_of_w5_12_1_, lessoninst0_.end_date as end_date6_12_1_, lessoninst0_.end_time as end_time7_12_1_, lessoninst0_.recurrence_period as recurren8_12_1_, lessoninst0_.region as region9_12_1_, lessoninst0_.room as room10_12_1_, lessoninst0_.start_date as start_d11_12_1_, lessoninst0_.start_time as start_t12_12_1_ from lesson_instance_schedule lessoninst0_ where lessoninst0_.id=? and lessoninst0_.school_id=?
Hibernate: select lessoninst0_.id as id1_12_0_, lessoninst0_.school_id as school_i3_12_0_, lessoninst0_.schedule_id as schedule2_12_0_, lessoninst0_.id as id1_12_1_, lessoninst0_.schedule_id as schedule2_12_1_, lessoninst0_.school_id as school_i3_12_1_, lessoninst0_.building as building4_12_1_, lessoninst0_.day_of_week as day_of_w5_12_1_, lessoninst0_.end_date as end_date6_12_1_, lessoninst0_.end_time as end_time7_12_1_, lessoninst0_.recurrence_period as recurren8_12_1_, lessoninst0_.region as region9_12_1_, lessoninst0_.room as room10_12_1_, lessoninst0_.start_date as start_d11_12_1_, lessoninst0_.start_time as start_t12_12_1_ from lesson_instance_schedule lessoninst0_ where lessoninst0_.id=? and lessoninst0_.school_id=?
Hibernate: select lessoninst0_.id as id1_12_0_, lessoninst0_.school_id as school_i3_12_0_, lessoninst0_.schedule_id as schedule2_12_0_, lessoninst0_.id as id1_12_1_, lessoninst0_.schedule_id as schedule2_12_1_, lessoninst0_.school_id as school_i3_12_1_, lessoninst0_.building as building4_12_1_, lessoninst0_.day_of_week as day_of_w5_12_1_, lessoninst0_.end_date as end_date6_12_1_, lessoninst0_.end_time as end_time7_12_1_, lessoninst0_.recurrence_period as recurren8_12_1_, lessoninst0_.region as region9_12_1_, lessoninst0_.room as room10_12_1_, lessoninst0_.start_date as start_d11_12_1_, lessoninst0_.start_time as start_t12_12_1_ from lesson_instance_schedule lessoninst0_ where lessoninst0_.id=? and lessoninst0_.school_id=?
Hibernate: select lessoninst0_.id as id1_12_0_, lessoninst0_.school_id as school_i3_12_0_, lessoninst0_.schedule_id as schedule2_12_0_, lessoninst0_.id as id1_12_1_, lessoninst0_.schedule_id as schedule2_12_1_, lessoninst0_.school_id as school_i3_12_1_, lessoninst0_.building as building4_12_1_, lessoninst0_.day_of_week as day_of_w5_12_1_, lessoninst0_.end_date as end_date6_12_1_, lessoninst0_.end_time as end_time7_12_1_, lessoninst0_.recurrence_period as recurren8_12_1_, lessoninst0_.region as region9_12_1_, lessoninst0_.room as room10_12_1_, lessoninst0_.start_date as start_d11_12_1_, lessoninst0_.start_time as start_t12_12_1_ from lesson_instance_schedule lessoninst0_ where lessoninst0_.id=? and lessoninst0_.school_id=?

Hibernate: update lesson_instance_schedule set id=null, school_id=null where id=? and school_id=?
2024-07-15 16:37:36,378 [task-3] WARN  o.h.e.j.s.SqlExceptionHelper SQL Error: 23502, SQLState: 23502 
2024-07-15 16:37:36,378 [task-3] ERROR o.h.e.j.s.SqlExceptionHelper NULL nicht zulässig für Feld "SCHOOL_ID"
NULL not allowed for column "SCHOOL_ID"; SQL statement:
update lesson_instance_schedule set id=null, school_id=null where id=? and school_id=? [23502-224]

Derived Query tries a Set null on the PK (i.e. wants to create orphans) and fails.

The DB contains other entities (no relations)
in derived mode hibernate generates a select and deletes every single row.
Which is expected, but waste of resources … but, well that’s what an ORM can do.

Hibernate: select configurat0_.id as id1_4_, configurat0_.school_id as school_i2_4_, configurat0_.cfg_boolean as cfg_bool3_4_, configurat0_.cfg_date as cfg_date4_4_, configurat0_.cfg_decimal as cfg_deci5_4_, configurat0_.cfg_long as cfg_long6_4_, configurat0_.cfg_string as cfg_stri7_4_, configurat0_.cfg_type as cfg_type8_4_, configurat0_.name as name9_4_ from configuration configurat0_ where configurat0_.school_id=?
Hibernate: delete from configuration where id=? and school_id=?
Hibernate: delete from configuration where id=? and school_id=?
Hibernate: delete from configuration where id=? and school_id=?
...

I think I don’t have to show that the JPQL works fine for non related tables and just does the DB delete. As there are no objects that could miss a detach, this is no problem for me. All this data will only be read non-transactional afterwards.

Now, back to my original question: Why is the CASCADE not performed?
This mapping stuff is totally JPA related and has nothing to do with Spring DATA.

JStefan · July 15, 2024, 3:37pm

I should add I am using Spring boot 2.7.18 with standard dependencies (i.e. hibernate 6.2.5).
I didnt dare to update since hibernate 5.6.5, versions after that didn’t work well with Spring 2.

Topic		Replies	Views
HQL join for Relational mapping and add extra check Hibernate ORM	1	167	March 28, 2024
Search for Entity with foreign key Hibernate ORM	3	1301	April 30, 2021
Not an '@Entity' type error? Hibernate ORM	3	2155	March 4, 2024
Spring + hibernate versus Spring Data JPA: Are they different? Hibernate ORM	1	606	October 23, 2019
Performance nativeQuery vs JDBC Hibernate ORM	3	3561	April 5, 2018

Please explain the difference (CASCADING DELETE not working as expected)

Related topics