action #51923

[tools][functional][u] Check for bug entries that do not exist within any comment

Added by dheidler 9 months ago. Updated 1 day ago.

Status:ResolvedStart date:23/05/2019
Priority:HighDue date:
Assignee:dheidler% Done:

0%

Category:Feature requests
Target version:SUSE QA tests - Milestone 30
Difficulty:
Duration:

Description

The bug table in openQA contains a list of all bugs referenced from comments in openQA to show bug status and title within openQA.
Whenever a comment is created, it is parsed for bugids and the according entry is created in the bug table.
All bugs in the bug table will get refreshed by openqa_bugfetcher in regular intervals.
As there are >4000 bugs in the osd database, we should make sure to delete any bug entry, that is not needed anymore.
This can be the case when a job group is deleted including all contained jobs and their comments.

Suggestion

  1. Iterate over all job and jobgroup comments in the database and create a set of bugids. The regex can already be found in the openQA code.
  2. Iterate over all bug entries and delete the ones that do not appear in the previously created set.

History

#1 Updated by mkittler 9 months ago

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.

#2 Updated by SLindoMansilla 4 months ago

  • Priority changed from Normal to High

#3 Updated by SLindoMansilla 2 months ago

  • Status changed from New to Workable
  • Target version set to Milestone 28

#4 Updated by mkittler 2 months ago

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.

#5 Updated by dheidler 2 months ago

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

#6 Updated by mkittler 2 months ago

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.

#7 Updated by okurz 2 months ago

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?

#8 Updated by dheidler 2 months ago

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.

#9 Updated by okurz about 1 month ago

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

#10 Updated by mgriessmeier about 1 month ago

  • Target version changed from Milestone 28 to Milestone 30

needs to be discussed

#11 Updated by mkittler about 1 month ago

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.

#12 Updated by dheidler about 1 month ago

  • Status changed from Workable to In Progress
  • Assignee set to dheidler

#13 Updated by dheidler about 1 month ago

  • Status changed from In Progress to Workable

#14 Updated by dheidler 30 days ago

  • Status changed from Workable to In Progress

#15 Updated by dheidler 26 days ago

  • Status changed from In Progress to Feedback

#16 Updated by dheidler 9 days ago

PR got merged.
Waiting for next openQA deployment.

#17 Updated by mkittler 1 day ago

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

#18 Updated by dheidler 1 day ago

  • Status changed from Feedback to Resolved

Also available in: Atom PDF