Can't delete user when they have approval history

[bubblesnout]

If I attempt to delete a user that has events related to timesheet approval Kimai pops up saying the user was deleted, but the user is never deleted.

The logs show the following:

[2024-05-22T05:03:40.080146+00:00] app.CRITICAL: An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (kimai.kimai2_ext_approval_history, CONSTRAINT FK_A8341CE3FE65F000 FOREIGN KEY (approval_id) REFERENCES kimai2_ext_approval (id)) [] {"channel":"app"}

I can see the approval history for the user but don't see any way to delete this data so there is no way for me to delete the user.

[KatjaGlassConsulting]

Hi, I had not been aware that this is not an issue - I can have a look at this issue somewhen later - maybe someone else can have a look? There should not be a delete option for this - I am not sure how different plugings handle person deletions - ideally in the person deletion all related entries should be removed.

For now I admit you need to delete the content directly from the database.

DELETE * FROM kimai2_ext_approval_history WHERE user_id = X;
DELETE * FROM kimai2_ext_approval_workday_history WHERE user_id = X;
DELETE * FROM kimai2_ext_approval_overtime_history WHERE user_id = X;
DELETE * FROM kimai2_ext_approval WHERE user_id = X;

[kevinpapst]

@KatjaGlassConsulting I think the problem is that this line is missing the onDelete: 'CASCADE': https://github.com/KatjaGlassConsulting/ApprovalBundle/blob/main/Entity/ApprovalHistory.php#L25

[KatjaGlassConsulting]

@kevinpapst, do you have another tip? Is it somehow possible to define an order for the cascade? The kimai2_ext_approval_history has three foreign_key dependencies. Including the onDelete does not work: SHA: 9f0ad8a17abd2ea86f458519c1e96a0e8a142a90 .

        $this->addSql('ALTER TABLE kimai2_ext_approval_history ADD CONSTRAINT FK_A8341CE3FE65F000 FOREIGN KEY (approval_id) REFERENCES kimai2_ext_approval (id)');
        $this->addSql('ALTER TABLE kimai2_ext_approval_history ADD CONSTRAINT FK_A8341CE3A76ED395 FOREIGN KEY (user_id) REFERENCES kimai2_users (id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE kimai2_ext_approval_history ADD CONSTRAINT FK_A8341CE36BF700BD FOREIGN KEY (status_id) REFERENCES kimai2_ext_approval_status (id)');

"An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (kimai_2_5.kimai2_ext_approval_history, CONSTRAINT FK_A8341CE3FE65F000 FOREIGN KEY (approval_id) REFERENCES kimai2_ext_approval (id)) [] []"

[kevinpapst]

Did you create and run a migration that adds it to the DB? The first of these three lines FK_A8341CE3FE65F000 is missing the ON DELETE CASCADE.

[KatjaGlassConsulting]

I see it's missing there. So I will add a new migration, remove the two foreign keys and and re-add them with the ON DELETE CASCADE statement. I will test this. Thanks.

[KatjaGlassConsulting]

@bubblesnout: This is now fixed and released under 2.0.7 (https://github.com/KatjaGlassConsulting/ApprovalBundle/tree/2.0.7) @kevinpapst: Thanks for the hints.