Randomly perform garbage collection on database tables

[ocram]

The following three database tables need garbage collection, clearing any rows that have been marked as expired:

users_remembered
users_resets
users_throttling

For most use cases, the best solution may be to trigger the garbage collection for a table randomly, e.g. during related operations, using an implementation like this:

function random_float($min = null, $max = null) {
    $min = isset($min) && \is_numeric($min) ? (float) $min : 0;
    $max = isset($max) && \is_numeric($max) ? (float) $max : 1;

    return $min + \mt_rand(0, \mt_getrandmax()) / \mt_getrandmax() * ($max - $min);
}

$probability = 0.02;

if (\random_float() < $probability) {
    // perform garbage collection ...
}

For the users_confirmations table, garbage collection would be more complex. For each user_id and for each email in that table, the row with the largest id must be preserved so as not to break the re-sending of previous confirmations.

[ponasromas]

If you considering to implement garbage collection, you should think about converting DB from MyISAM to InnoDB.

[ocram]

@ponasromas Can you explain this? Is there any relation between the storage engine and the garbage collection that we’re planning to implement?

As noted above, we’re planning to clear the unused rows from those database tables via an implementation in the library’s code (i.e. in PHP).

Switching from MyISAM to InnoDB for MySQL is something we always have in mind. But it may be better to do this in a future update when there are other changes to the database schema as well. This issue here can probably be resolved without any such breaking changes.

[Aristotle22]

I have three thoughts on this topic:

1.) The garbage collection might include:

• rows in users_confirmations without id in the table users (account cancellation)
• rows in users_confirmations where the user is verified and the email is equal to the one in the table users
• users that registered but never logged/verified after a grace period
• (non-active members some days after an information message)

2.) The garbage collection should be initiated by a scheduled cronjob of the server (not managed within the library). One advantage would be that the timing of the garbage collection can be better managed.

3.) I find there is a connection between garbage collection and the issue regarding InnoDB because with InnoDB it would be possible to use foreign key constraints for a customized table with additional user information. That would mean that updates and deletions could cascade automatically between the table users and the table with additional user information. Data consistency could be improved and the general handling would be easier. I would like to see a change to InnoDB.

[Aristotle22]

One additional thought:

If users that never logged in/are not verified are deleted after a grace period in the users table then the garbage collection of the users_confirmations table simply would be to delete all rows that are expired (taking the above mentioned grace period into account).

[ocram]

@Aristotle22

(1) I see no reason why all this cannot simply be handled by deleting expired entries only. Do you have strict (storage/performance) requirements that make it impossible for you to have obsolete entries in those tables for a few days (until they’re actually recognized as expired based on the date)?

(2) This is why it’s not here yet. That question is not as easy as you think. We have many users who are on shared hosting or on platforms where they can’t easily schedule cron jobs or similar things. But I also see how some people might not want this to be randomly performed on regular requests. So we might need both.

(3) Foreign keys and cascading deletions really are a tiny fraction of the problem we have here. The vast majority of garbage is entries that have expired simply because of their age, not because there has been some deletion in another table. You can check this in your own database tables if you want. Finally, we maintain support for other database systems, too.

[Aristotle22]

@ocram

(1) My suggestions are only additional entries that could be deleted.
(3) The foreign keys could be used for additional user information saved in a separate table independent from the library. You don't have to use foreign keys at all.

https://github.com/delight-im/PHP-Auth/issues/10