View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0003716||mantisbt||sql||public||2004-04-03 09:36||2006-05-12 17:45|
|Platform||Intel||OS||Linux||OS Version||Debian Unstable|
|Target Version||Fixed in Version|
|Summary||0003716: Add a function to the "Manage Users" page to purge users older than 'n' days/years|
Currently, our system has 935 users in it, most of which who have never reported bugs at all, but are community people interested in development and bugs filed.
So I came up with a quick query to purge users older than 1 year, who have only ever logged in once, from the database.
|Steps To Reproduce|
Note, this does not take into account that any of these users may have logged in once, reported a bug (or added a bugnote to an existing bug), and never logged in again.
I will try to update the query to handle that situation and post a patch here, unless someone else wants to give it a go.
|Tags||No tags attached.|
Actually, I just realized MySQL has a NOW() function:
NOW() - INTERVAL 1 YEAR
Slightly updated query to handle some of this, but NOT yet sanitized to KEEP users who are REPORTER on all projects, but DEVELOPER on some other projects. Stay tuned as I figure that one out:
It looks like we also have to go through mantis_project_user_list_table, and make sure u.username doesn't match A.username above, and that l.access_level is = 25.
a Purge utility should only purge users who have never generated an event though - e.g. not created a bug, added a bug note etc.
Here's how I managed to do this...
SELECT u.id, u.last_visit
DELETE FROM u using mantis_user_table u L
This worked out great for me.
|2004-04-03 09:36||hacker||New Issue|
|2004-04-03 09:39||hacker||Note Added: 0005338|
|2004-04-03 10:36||hacker||Note Added: 0005339|
|2004-08-27 13:06||grangeway||Note Added: 0007244|
|2004-08-27 13:06||grangeway||Status||new => acknowledged|
|2006-05-12 17:45||hacker||Note Added: 0012827|