444010 - Add cron job to delete inactive users

Status: RESOLVED FIXED

(addons.mozilla.org Graveyard :: Maintenance Scripts, defect)

Description

[Michael Morgan [:morgamic]]

There might be another bug about this, but couldn't find it.  We need a crob job to delete the inactive user records out of the add-ons database.  This should probably be users who have been completely inactive for 6 months or more OR accounts that were never confirmed.

Comment 1

[Fred Wenzel [:wenzel]]

I don't think there's currently a way for us to find out who has been completely inactive. Not writing reviews doesn't make a user inactive. We'd have to store a timestamp of their last login in order to find out their last active date. Before we remove their accounts though we should probably send them an email too, to remind them of AMO and only delete them after they had time to become active again.

So for now, I suggest the cronjob only removes unconfirmed users, unless you want all of the above to be in-scope for this bug as well?

Comment 2

[Fred Wenzel [:wenzel]]

Attachment: Delete unconfirmed users

The new maintenance task will delete all users who haven't confirmed their accounts in two weeks.

Note that in order for this to work quickly, we'll want to execute
ALTER TABLE users ADD INDEX (created, confirmationcode);
in production. However, this took 3.5 hours (sic) to create on my laptop, so you can run the task to test like that, but it'll take a while without an index.

Also, using DELETE IGNORE because some unconfirmed people are referenced as authors, which will make the command fail, so I ignore these and just delete the unreferenced ones.

MySQL also doesn't seem to return an "affected rows" count. Don't know why.

Comment 3

[Michael Morgan [:morgamic]]

Comment on attachment 329951 [details] [diff] [review]
Delete unconfirmed users

[morgamic@khan bin]$ php -a maintenance.php unconfirmed
Interactive mode enabled

Segmentation fault
[morgamic@khan bin]$ php maintenance.php unconfirmed
Removing user accounts that haven't been confirmed for two weeks...
PHP Warning:  mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/morgamic/public_html/amo/bin/maintenance.php on line 346
PHP Stack trace:
PHP   1. {main}() /home/morgamic/public_html/amo/bin/maintenance.php:0
PHP   2. mysql_num_rows() /home/morgamic/public_html/amo/bin/maintenance.php:346

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/morgamic/public_html/amo/bin/maintenance.php on line 346

Call Stack:
    0.0015      98672   1. {main}() /home/morgamic/public_html/amo/bin/maintenance.php:0
    0.0480     151808   2. mysql_num_rows() /home/morgamic/public_html/amo/bin/maintenance.php:346

Affected rows:     Time: 0.044013977050781
Exiting ...


Since this returns an error, we should clean this up:
* use a select to determine # of affected rows -- it's another query but oh well (and you may need to join on addons_users for exclusion of fk relationships that ignore would throw away)
* remove the num_rows() call, which will always produce error listed

Comment 4

[Michael Morgan [:morgamic]]

(In reply to comment #3)
> (From update of attachment 329951 [details] [diff] [review])
> [morgamic@khan bin]$ php -a maintenance.php unconfirmed
> Interactive mode enabled

That was command-line PHP fail, ignore that part... ;)

Comment 5

[Fred Wenzel [:wenzel]]

Attachment: Delete unconfirmed users, v2

Thanks for the hints: Using mysql_affected_rows() helps with the row count. I tried out joining against addons_users but it slowed down performance significantly (compared to a well-indexed users table).

Comment 6

[Fred Wenzel [:wenzel]]

Checked into r17214, along with the SQL schema change for the index (comment 2).

Comment 7

[Wil Clouser [:clouserw]]

Attachment: sql fix

DELETE IGNORE converts errors to warnings but it also appears to stop on the first warning when attempting to delete across a FK so this query is doing nothing right now.

The query in my attachment is far slower (8min to clear out our backlog of 650k users, 2min after that) but since this is a maintenance script I'm not too worried about it.

Comment 8

[Fred Wenzel [:wenzel]]

Comment on attachment 377347 [details] [diff] [review]
sql fix

Looks good! Sad how I missed this though :-/

Comment 9

[Wil Clouser [:clouserw]]

Thanks, r25716