Erros MySQL 1054 and 1292

[colivier]

By performing a user deletion request from the backend, I got the MySQL errors :

• 1054 Unknown column 'engage_comment_id' in 'field list'
• 1292 Incorrect datetime value: '1000' for column 'created' at row 1

Steps to reproduce the issue

The issue occurs in engage plugin when a user makes a request to delete this data and an administrator wants to delete the user in the backend from the com_privacy component

Expected result

User comments should be replaced with the string COM_ENGAGE_COMMENTS_LBL_DELETEDCOMMENT and his name replaced with the string COM_ENGAGE_COMMENTS_LBL_DELETEDUSER

Actual result

The engage plugin returns error 1054 Unknown column 'engage_comment_id' in 'field list' and the data removal process is aborted

Troubleshooting already performed

The issue occurs in the pseudonymisedUserComments method of components/com_engage/src/Helper/Meta.php

To fix error 1054 Unknown column 'engage_comment_id' in 'field list'

In lines 328, 342 and 370, I replaced 'engage_comment_id' with 'id', the current primary key of the table #__engage_comments

I think it would also be appropriate to replace 'engage_comment_id' with 'id' in plugins/privacy/engage/src/Extension/Engage.php in lines 103 and 117

To fix error 1292 Incorrect datetime value: '1000' for column 'created' at row 1

In lines 330 and 336, I replaced 'created' which is a datetime type field by created_by which is the identifier of the user who created the comment

System information

Joomla! version : 4.1.4 PHP version : 8.1 Engage version : 3.0.8 Am I using HTTPS : Yes Server Operating System : Linux My device's Operating System : Windows My device's Operating System version : 10 Browser : Firefox Browser version : 101.0.1 MySQL : 5.7.23

[nikosdion]

You are correct in everything you said. Would you like to submit a PR with these changes or would you rather me do these changes myself? Either way works for me, the former gives you attribution for the code changes :)

[colivier]

Code attribution is not important ;), so you can make these changes yourself.

Thank you for your excellent work

[nikosdion]

Thank you for the very thorough bug report! I wish everybody would submit issues like this :)