Guidelines

This site is for tech Q&A. Please keep your posts focused on the subject at hand.

Ask one question at a time. Don't conflate multiple problems into a single question.

Make sure to include all relevant information in your posts. Try to avoid linking to external sites.

Links to documentation are fine, but in addition you should also quote the relevant parts in your posts.

0 votes
109 views
109 views

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.

in Sysadmin
by (100)
1 4 11
edit history

Your answer

Preview

Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
By submitting this post you agree to our Terms & Conditions.
To avoid this verification in future, please log in or register.

1 Answer

0 votes
 

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.


edited by
by (100)
1 4 11
edit history
...