Editor's draft changes lost when Publisher approves, SQL23000 Integrity constraint violation error

[bliswebagency]

Describe the bug

When an editor saves a draft and requests approval, the publisher will be able to see and review those drafted changes. Then when the publisher approves and publishes those changes will be lost.

A MySQL exception 'integrity constraint violation 1452' is logged when the publisher attempts to approve and publish, attached below.

If the publisher uses the regular 'Apply Draft' it saves without failing. It only occurs when the 'Approve and Publish' or 'Approve Only' options are used.

This only seems to occur for certain types of entries, particular larger ones with 30+ matrix blocks which contain large strings (eg. instagram embed code). For simpler entries with just 1 or 2 matrix blocks this issue does not occur.

SQLSTATE[23000]: Integrity constraint violation: 1452
Cannot add or update a child row: a foreign key constraint fails
(`DB_NMAE`.`elementactivity`, CONSTRAINT `fk_rpvbgloelqstdtgditjbezgrinbrvvdqiwov` FOREIGN KEY (`draftId`) REFERENCES `drafts` (`id`) ON DELETE CASCADE)
at .../vendor/yiisoft/yii2/db/Command.php:1320)"}

Steps to reproduce

1. Editor edits of a relatively large entry with 30+ matrix blocks.
2. Editor saves draft and makes request for approval
3. Publisher views draft and uses 'Approve and Publish' or 'Approve only' to make the draft live
4. Changes from draft are not in the live version

Craft CMS version

4.16.8

Plugin version

2.0.16

Multi-site?

Yes

Additional context

We have only been notified of this issue recently, but we're not certain if it may have resulted from the 2.0.14 or a later update. Thank you.

[engram-design]

I'm not even familiar with the elementactivity database table, but that seems to track the history of editing elements (view, save, edit, etc), so it sounds like something is messing up with that, or maybe even a race condition. It's not something I can replicate with my test setup, but sounds like it's a bit edge-casey in certain scenarios.

[bliswebagency]

Thank you for the reply. I agree, it does seem like a race condition of some kind.

However the only way we can consistently avoid this issue is to use 'Apply Draft' which doesn't trigger any race condition or exception. While it works it's not an ideal solution, as the client would very much prefer to use Workflow's approval system.

We're not familiar with elementactivity table either sorry, but there seems to have been this model added as of Craft 4.5.0 which could be dependent on it? https://docs.craftcms.com/api/v4/craft-models-elementactivity.html

[engram-design]

Yep, still looking into this one! It'd be amazing if you could let me know the smallest reproducible case for this - if you can. You mention 30+ Matrix blocks, but would be good to know their complexity, types of fields, etc. But, I'll keep at it.