@OnDeleteInverse(DeletePolicy.CASCADE) not working for me

chicojeff · April 9, 2023, 5:53pm

jmix 1.5.1
studio 1.5.1
MySQL

I am trying to hard delete child entities when the parent entity is hard deleted. I cannot get this to work and I keep getting this error due to the foreign key constraint on the child table (stack trace at the bottom)

The parent entity School has the OneToMany defined like this:

    @OrderBy("renewalDate DESC")
    @OneToMany(mappedBy = "school")
    private List<SchoolRenewal> renewals;

The child entity SchoolRenewal has the ManyToOne defined like this:

    @OnDeleteInverse(DeletePolicy.CASCADE)
    @JoinColumn(name = "SCHOOL_ID")
    @ManyToOne(fetch = FetchType.LAZY)
    private School school;

This is the earliest part of the stack trace showing the error and resulting exceptions:

Caused by: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.6-jmix): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`ajjf`.`school_renewal`, CONSTRAINT `FK_SCHOOL_RENEWAL_ON_SCHOOL` FOREIGN KEY (`SCHOOL_ID`) REFERENCES `school` (`ID`))
Error Code: 1451
Call: DELETE FROM SCHOOL WHERE ((ID = ?) AND (VERSION = ?))
	bind => [502, 1]
Query: DeleteObjectQuery(org.ajjf.membership.entity.School-502 [managed,removed])
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:984) ~[org.eclipse.persistence.jpa-2.7.9-6-jmix.jar:na]
	at io.jmix.eclipselink.impl.JpaDataStore.beforeSaveTransactionCommit(JpaDataStore.java:428) ~[jmix-eclipselink-1.5.1.jar:na]
	at io.jmix.core.datastore.AbstractDataStore.save(AbstractDataStore.java:229) ~[jmix-core-1.5.1.jar:na]
	at io.jmix.eclipselink.impl.JpaDataStore.save(JpaDataStore.java:227) ~[jmix-eclipselink-1.5.1.jar:na]
	at io.jmix.core.impl.UnconstrainedDataManagerImpl.saveContextToStore(UnconstrainedDataManagerImpl.java:257) ~[jmix-core-1.5.1.jar:na]
	at io.jmix.core.impl.UnconstrainedDataManagerImpl.save(UnconstrainedDataManagerImpl.java:216) ~[jmix-core-1.5.1.jar:na]
	at io.jmix.ui.RemoveOperation.commitIfNeeded(RemoveOperation.java:214) ~[jmix-ui-1.5.1.jar:na]
	at io.jmix.ui.RemoveOperation.removeItems(RemoveOperation.java:173) ~[jmix-ui-1.5.1.jar:na]
	at io.jmix.ui.RemoveOperation.performAction(RemoveOperation.java:158) ~[jmix-ui-1.5.1.jar:na]
	at io.jmix.ui.RemoveOperation.lambda$performActionWithConfirmation$1(RemoveOperation.java:296) ~[jmix-ui-1.5.1.jar:na]
	at io.jmix.core.common.event.EventHub.publish(EventHub.java:170) ~[jmix-core-1.5.1.jar:na]
	at io.jmix.ui.action.BaseAction.actionPerform(BaseAction.java:220) ~[jmix-ui-1.5.1.jar:na]
	at io.jmix.ui.sys.DialogsImpl$OptionDialogBuilderImpl.lambda$show$0(DialogsImpl.java:344) ~[jmix-ui-1.5.1.jar:na]
	at io.jmix.ui.widget.JmixButton.fireClick(JmixButton.java:77) ~[jmix-ui-1.5.1.jar:na]
	at com.vaadin.ui.Button$1.click(Button.java:57) ~[vaadin-server-8.14.3-2-jmix.jar:8.14.3-2-jmix]
	... 95 common frames omitted
Caused by: org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`ajjf`.`school_renewal`, CONSTRAINT `FK_SCHOOL_RENEWAL_ON_SCHOOL` FOREIGN KEY (`SCHOOL_ID`) REFERENCES `school` (`ID`))
Error Code: 1451
Call: DELETE FROM SCHOOL WHERE ((ID = ?) AND (VERSION = ?))
	bind => [502, 1]
Query: DeleteObjectQuery(org.ajjf.membership.entity.School-502 [detached,removed])
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:952) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:1014) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:656) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2102) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:313) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:277) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.executeCall(ExpressionQueryMechanism.java:2938) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:263) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.deleteObject(DatasourceCallQueryMechanism.java:245) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.deleteObject(StatementQueryMechanism.java:106) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.queries.DeleteObjectQuery.executeDatabaseQuery(DeleteObjectQuery.java:220) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:810) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:110) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.queries.DeleteObjectQuery.executeInUnitOfWorkObjectLevelModifyQuery(DeleteObjectQuery.java:121) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:87) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3043) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1895) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1877) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1827) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:344) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:293) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1502) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithPreBuiltChangeSet(UnitOfWorkImpl.java:1645) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.writeChanges(RepeatableWriteUnitOfWork.java:457) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:979) ~[org.eclipse.persistence.jpa-2.7.9-6-jmix.jar:na]
	... 109 common frames omitted
Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`ajjf`.`school_renewal`, CONSTRAINT `FK_SCHOOL_RENEWAL_ON_SCHOOL` FOREIGN KEY (`SCHOOL_ID`) REFERENCES `school` (`ID`))
Caused by: java.lang.reflect.InvocationTargetException: null

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-j-8.0.32.jar:8.0.32]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.0.32.jar:8.0.32]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) ~[mysql-connector-j-8.0.32.jar:8.0.32]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061) ~[mysql-connector-j-8.0.32.jar:8.0.32]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009) ~[mysql-connector-j-8.0.32.jar:8.0.32]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320) ~[mysql-connector-j-8.0.32.jar:8.0.32]
Caused by: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.6-jmix): org.eclipse.persistence.exceptions.DatabaseException

	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994) ~[mysql-connector-j-8.0.32.jar:8.0.32]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:942) ~[org.eclipse.persistence.core-2.7.9-6-jmix.jar:na]
	... 135 common frames omitted

Caused by: org.eclipse.persistence.exceptions.DatabaseException: 

Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`ajjf`.`school_renewal`, CONSTRAINT `FK_SCHOOL_RENEWAL_ON_SCHOOL` FOREIGN KEY (`SCHOOL_ID`) REFERENCES `school` (`ID`))

gorbunkov · April 12, 2023, 1:52pm

Hi,

Do you have the onDelete="CASCADE" attribute in the liquibase changeset that creates a foreign key constraint?

    <changeSet id="3" author="ondelete">
        <createIndex indexName="IDX_SCHOOL_RENEWAL_SCHOOL" tableName="SCHOOL_RENEWAL">
            <column name="SCHOOL_ID"/>
        </createIndex>

        <addForeignKeyConstraint baseColumnNames="SCHOOL_ID" baseTableName="SCHOOL_RENEWAL"
                                 constraintName="FK_SCHOOL_RENEWAL_ON_SCHOOL" onDelete="CASCADE"
                                 referencedColumnNames="ID" referencedTableName="SCHOOL"/>
    </changeSet>

chicojeff · April 13, 2023, 12:22pm

I am pretty sure it does not. When the association/FK was created there was no setting chosen, and when I changed it from nothing to cascade there was no liquibase changelog generated.

I was also wondering if there was an event I could listen for before the actual delete/save was issued? This would allow me to manually delete the child records before the actual delete of the parent. When I looked in the documentation, I did not see such an event.

gorbunkov · April 17, 2023, 6:23am

It seems that Studio generates correct foreign key constraints definitions for liquibase when you create a new entity, but doens’t handle the OnDeleteInverse updates for an existing entity. Here is an issue for that: https://youtrack.jmix.io/issue/JST-3629