Delete user records from 2 separate tables

Question

Many of the accounts spammers try to register on my Question2Answer site never confirm their e-mail address, so I wanted to facilitate cleanup by automatically removing unconfirmed accounts after a grace period of a couple hours. The following SQL statement removes accounts older than 8 hours that are unconfirmed (still have their e-mail code set):

DELETE FROM qa_users
WHERE emailcode != '' AND date_sub(now(), INTERVAL 8 HOUR) > created;

This works fine. However, after a number of unconfirmed accounts got removed I noticed that the user list in the web UI got paginated when it shouldn't have.

Apparently new users are added not only to the table qa_users, but also to the table qa_userpoints. Records in the latter table remain even if the referenced user is deleted from qa_users.

Can I delete the records from both tables in a single statement? I would prefer to avoid having to build a list of user IDs first and then use that in two subsequent DELETE statements.

Answer 1

You can delete records from 2 tables in one statement if you JOIN the tables in the DELETE statement. For your scenario that would look like this:

DELETE u,p FROM qa_users u
  INNER JOIN qa_userpoints p ON u.userid = p.userid
WHERE u.emailcode != '' AND date_sub(now(), INTERVAL 8 HOUR) > u.created;

Note that you need to specify the table aliases (u, p) after the DELETE keyword.

However, that alone won't suffice for getting rid of the unwanted pagination, because the algorithm uses a cached count of the qa_userpoints records from the table qa_options for its calculation. You need to update that value too.

BEGIN;
DELETE u,p FROM qa_users u
  INNER JOIN qa_userpoints p ON u.userid = p.userid
WHERE u.emailcode != '' AND date_sub(now(), INTERVAL 8 HOUR) > u.created;
UPDATE qa_options
SET content = (SELECT count(*) FROM qa_userpoints)
WHERE title = 'cache_userpointscount';
COMMIT;

Wrap both statements in a transaction (BEGIN; ...; COMMIT;), so that either both statements are executed or none of them.

However, after making a feature request about adding an ON CASCADE DELETE constraint to the table qa_userpoints, one of the developers explained to me that Q2A does a lot of internal caching (as evidenced by the cache_userpointscount field in the options table). Therefore operations on the database backend are note recommended. Instead one should determine the user IDs of the accounts to be deleted and then use the Q2A function qa_delete_user() for the actual cleanup.

Hence I came up with the following PHP script:

#!/usr/bin/php
<?php
$include_dir = '/path/to/q2a/qa-include';

require_once "${include_dir}/qa-base.php";
require_once "${include_dir}/app/users.php";
require_once "${include_dir}/app/users-edit.php";

$query = 'SELECT userid,handle,email FROM ^users WHERE level = 0 AND date_sub(now(), INTERVAL 8 HOUR) > created AND NOT flags & 1';

$unconfirmed_users = qa_db_read_all_assoc(qa_db_query_sub($query));
foreach($unconfirmed_users as $user) {
  qa_delete_user($user['userid']);
}

level = 0 selects only normal users (so that manually added moderator or admin accounts don't get deleted by mistake), and the clause NOT flags & 1 selects only users that don't have the "confirmed" flag set.

Put the code in a file and run it e.g. like this:

php -f /path/to/cleanup.php

If you intend to run the script as a cron job for periodic cleanups I'd recommend adding some logging statements (e.g. to syslog), so that you have a log trail of deletions and/or errors.