Page MenuHomePhabricator

Decrease number of open tickets with assignee field set for more than two years (aka cookie licking) (Oct-Dec 2020 edition)
Closed, ResolvedPublic

Description

See T228575: Decrease number of open tickets with assignee field set for more than two years (aka cookie licking) (March-June 2020 edition).
Next round, basically.

However, the initial SQL query must also exclude tasks which have seen assignee changes in the last two years (that's not the case in T228575), plus should exclude tasks with periodic-update or Epic or Patch-For-Review, so that means:

SELECT u.userName, ue.address, t.id FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND (e.dst = "PHID-PROJ-onnxucoedheq3jevknyr" OR e.dst = "PHID-PROJ-iuj2chpabktaluedrqsf" OR e.dst = "PHID-PROJ-hwr4habt5dzuabb5w2xz")) AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) ORDER BY u.userName, t.id;

To make the output of that query more complete, it would also be great (though time-intense) beforehand to check those tasks which had their current assignee set more than two years ago and do have a Patch-For-Review tag, and did not have a reassignment in the last two years again, because some tasks should be changed from Patch-For-Review to Patch-Needs-Improvement (e.g. when rebasing needed):
SELECT u.userName, ue.address, CONCAT("https://phabricator.wikimedia.org/T",t.id) FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst = "PHID-PROJ-onnxucoedheq3jevknyr") AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) ORDER BY u.userName, ta.dateModified;

Event Timeline

Aklapper changed the task status from Open to Stalled.Jun 22 2020, 4:50 PM
Aklapper triaged this task as Lowest priority.
Aklapper created this task.

I appreciate you taking on this initiative. Since this seems like it's going to be a regular process going forward, can we create and link to a wikipage that explains what's happening, instead of linking everything back to a single phab task. It's hard to even get to the comments on T228575 because of how many mentions it has.

@Legoktm: Very good point, thanks! I'll do that before acting on this task in a few months.

Since this seems like it's going to be a regular process going forward, can we create and link to a wikipage that explains what's happening

Done now: https://www.mediawiki.org/wiki/Bug_management/Assignee_cleanup

Aklapper changed the task status from Stalled to Open.Oct 15 2020, 12:37 PM

2020-10-15: Reviewed tasks with Patch-For-Review and checked if they should have Patch-Needs-Improvement instead. Number of tasks (some duplicates included though) increased from 405 to 554.

2020-10-27: Emails sent to 183 task assignees about 490 open tasks (duplicate items removed) which have their assignee set for more than two years.

2020-11-23: (Second and last) Emails sent to remaining 132 task assignees about 323 open tasks left (duplicate items removed).
Before mass-unassigning around Dec 18.

2020-12-19: 113 remaining task assignees with 254 open tasks (duplicate items removed) assigned to them for more than two years (+53 days).

Unassigned 251 of them (as I am unable to access 3 tasks) and added the following comment:

Removing task assignee due to inactivity, as this open task has been assigned to the same person for more than two years (see the emails sent to the task assignee on Oct27 and Nov23). Please assign this task to yourself again if you still realistically [plan to] work on this task - it would be welcome.
(See https://www.mediawiki.org/wiki/Bug_management/Assignee_cleanup for tips how to best manage your individual work in Phabricator.)