QA » openQA Project

I support keeping our database clean.

@cdywan Maybe you need to take this change into account since you're now using this table for statistics.

Because Sergio asked on Rocket Chat:

Could you add information about what would need to be done?

This could be implemented as a Minion job which is triggered periodically (e.g. every day). The basic algorithm which would be executed within that Minion job is already given in the ticket description under 'Suggestions'. Maybe there's a more efficient way to do it (iterating over all comments seems expensive). For instance we could only consider comments which have been updated since the last time the cleanup task ran.

This is more about comments that were deleted.
But it would be more than fine if it was executed once per month or so.

When deleting (or updating) a comment we don't know whether any other comments still have that bug reference. Iterating over any other comment to find it out seems quite expensive. Therefore I suggested to do this only e.g. once a day within a Minion job.

But of course it is also conceivable to add a database table for the comment-to-bug linking or a refcount to the bugs table and really handle all of this when adding/deleting/updating comments. This would also require a manual database migration to initialize the linking table or refcount and to delete dangling bugs.

Different to asset cleanup where we have actual file system traversal involved deleting entries from a database table is certainly less expensive but of course one would need for N comments and M bugs N*M potential lookups. But still, deleting a comment is probably also not that common. So I would really suggest to try out e..g. in a production database dump to go with the following: When a comment is deleted, query for all bugs and for each check if mentioned in any comment. Maybe this is even possible to do within one DB call?

Or would it be possible to look for bugs where "t_updated" is old?

The question is if this comment_deleted hook would get executed when old test results are deleted (are they actually deleted after some time)?

By simple querying all comments and generating a hash/set of all bugids and then querying that set for each entry in the bugs table the execution time should be quite fast.

The problem with removing a bugref when a comment is deleted is that there might be other comments left that reference this bugid so we would need to query all comments when a single comment is deleted.

dheidler wrote:

The question is if this comment_deleted hook would get executed when old test results are deleted (are they actually deleted after some time)?

Yes, old test results are deleted with the "asset cleanup" if the according time and quota is exceeeded. I would not care if a comment deletion is triggered by asset cleanup or manually by a user.

By simple querying all comments and generating a hash/set of all bugids and then querying that set for each entry in the bugs table the execution time should be quite fast.

The problem with removing a bugref when a comment is deleted is that there might be other comments left that reference this bugid so we would need to query all comments when a single comment is deleted.

Exactly. So my suggestion still holds: Whenever a comment is deleted, query for all bugs and for each bug check if mentioned in any comment, delete bug otherwise

Maybe this is even possible to do within one DB call?

I don't think so because the logic to find bugrefs in comments is written using Perl regular expressions. But I agree that it is worth a checking how long it takes. And if it takes too long we can still do it but within a Minion job.

The question is if this comment_deleted hook would get executed when old test results are deleted (are they actually deleted after some time)?

That is a good question. When implementing this we need to be aware of the following events:

1. A comment which contained one or more bugrefs is deleted
2. A comment which contained one or more bugrefs is deleted because its job has been deleted
3. A comment which contained one or more bugrefs is updated and bugrefs have been removed

Not sure whether a DBIx hook would be executed in case 2. We should test it and if not just trigger the deletion manually when we delete the job.

PR got merged.
Waiting for next openQA deployment.

It is deployed now on OSD:

martchus@openqa:~> systemctl status openqa-enqueue-bug-cleanup.timer
● openqa-enqueue-bug-cleanup.timer - Enqueues an audit event cleanup task for the openQA database every day.
   Loaded: loaded (/usr/lib/systemd/system/openqa-enqueue-bug-cleanup.timer; disabled; vendor preset: disabled)
   Active: active (waiting) since Fri 2020-02-21 20:35:06 CET; 3 days ago
  Trigger: Mon 2020-03-02 00:00:00 CET; 5 days left

The description of the systemd timer/service should be adjusted. Otherwise it seems to work: https://openqa.suse.de/minion/jobs?offset=0&task=limit_bugs