We've got a client that uses the Flag module on his website for anonymous visitors. A little while ago he reported that he was unable to delete nodes. After a little investigation we noticed that this was caused by PHP timeouts. Diving further, we noticed it was crunching away on cache tags and flaggings. There were an insane amount of cache tags and flaggings in the database. The flagging table contained around 1.2 million records.
Investigating the flagging table, we noticed that there were a LOT of orphaned flaggings; session_id's which where long time gone. I think the flag module should delete flags when sessions expire, true?
We are fixing this currently with the below cleanup script, to make sure we delete flaggings that do not have any active session. It is currently still crunching through the data, but should fix the issue.
function _flagging_orphaned_cleanup ($count_flags = 10000) {
$time_start = microtime(true);
$connection = \Drupal::database();
// Amount of flags to process
$previous_last_created = \Drupal::state()->get('flagging_cleanup_last_created');
if (!$previous_last_created) {
$previous_last_created = 0;
}
\Drupal::logger('flag')->notice('Starting Flagging cleanup, starting at created date %start_created...', ['%start_created' => $previous_last_created]);
// Get active sessions
$sql = 'SELECT sid FROM {sessions} ';
$query = $connection->query($sql);
$active_sids = $query->fetchCol();
// Get all flagging rows,
$sql = 'SELECT id, session_id FROM {flagging} WHERE created > :created AND session_id IS NOT NULL ORDER BY created ASC LIMIT 0, ' . $count_flags;
$query = $connection->query($sql, [':created' => $previous_last_created]);
$flags = $query->fetchAll();
// If no flaggings have been returned, we can assume that there are no flaggings with a higher created date then last run, therefor, reset it to 0
if (empty($flags)) {
\Drupal::logger('flag')->notice('No flaggings found with created above %created.', ['%created' => $previous_last_created]);
\Drupal::state()->set('flagging_cleanup_last_created', 0);
return;
}
// Loop out all flags and check if their session_id still matches with one from the sessions table, if not, it should be safe to be deleted
$inactive_flag_ids = [];
foreach ($flags as $flag) {
// Hash the session_id the same way they are hashed in the sessions table so we can compare them
$sid_hashed = Crypt::hashBase64($flag->session_id);
if (!in_array($sid_hashed, $active_sids)) {
$inactive_flag_ids[] = $flag->id;
}
}
// Load up the inactive flaggings and delete them
$flaggings = Drupal::entityTypeManager()->getStorage('flagging')->loadMultiple($inactive_flag_ids);
$last_created = NULL;
foreach ($flaggings as $flagging) {
$last_created = $flagging->get('created')->value;
$flagging->delete();
}
// Save the last created date, so we know where to resume next time.
\Drupal::state()->set('flagging_cleanup_last_created', $last_created);
$time_end = microtime(true);
$execution_time = ($time_end - $time_start) / 60;
\Drupal::logger('flag')->notice(
'Finished cleanup, check count was %check_count, found %total_found_flaggings flaggings, deleted %inactive_count inactive flaggings, last created date was %last_created. (Exec time: %exec_time)',
[
'%total_found_flaggings' => count($flags),
'%check_count' => $count_flags,
'%inactive_count' => count($inactive_flag_ids),
'%last_created' => $last_created,
'%exec_time' => $execution_time
]
);
}
| Comment | File | Size | Author |
|---|---|---|---|
| #13 | 3018713-13.patch | 8.89 KB | serg.linkin |
| #11 | 3018713-11.patch | 8.89 KB | giuseppe87 |
Issue fork flag-3018713
Show commands
Start within a Git clone of the project using the version control instructions.
Or, if you do not have SSH keys set up on git.drupalcode.org:
- add-option-5-x
changes, plain diff MR !189
- 3018713-add-optional-deletion
changes, plain diff MR !107
Comments
Comment #2
joachim commented> Investigating the flagging table, we noticed that there were a LOT of orphaned flaggings; session_id's which where long time gone. I think the flag module should delete flags when sessions expire, true?
No, not necessarily. It entirely depends on what your flags *mean*.
For instance:
1. Flaggings are personal bookmarks. It maybe makes sense to delete them because once the anon session is gone, there is no user who can access those flaggings and see their bookmarks. But what if you also keep track of how many times a thing has been bookmarked, as a metric of what's popular?
2. Flaggings mark abusive or spam content. You most likely don't want to delete these.
Comment #3
nvakenYeah, that sounds fair. Though, in our client's use-case, it will eventually lock-up without cleaning the database. The only reason Flag is being used is to mark certain products as "Favorite". We have no desire to get metrics from inactive session flags. So, how would one prevent this problem?
Comment #4
joachim commentedIs there anything in core that fires a hook or an event when a session is expired?
If so, use that.
If not, it'll have to be a cron job.
In either case, this should be configurable per flag.
Comment #5
emclaughlin commented@joachim It doesn't look like there's any core event/hook for when a session is expired, based on some extensive googling and this s.o answer: https://drupal.stackexchange.com/questions/259102/run-code-when-a-sessio...
So unless I'm missing something, it looks like the only way to do this is through a cron job as you suggested, correct? I'd like to tackle this but I don't want to take the wrong approach!
Comment #6
murz@Ambidex, thanks, your example function works well and successfully cleaned up our database with millions of expired flags!
Will be good to see this feature in module itself!
Comment #8
demonde commentedFlag is designed to be used for anoymous users.
Without an expire function any public website with this function will exceed its flag database after some time.
Therefore this should be considered as a bug, because you cannot use flag as designed.
Comment #9
jim005 commentedWe've same issue for our websites. Anonymous flags theirs favorites pages... but after sometime users (and smart crawl) filled with millions records in the 'flaggings' table. Could you add this fix as an option per flag?
Comment #10
demonde commentedThe actual solution until this issue is solved is:
It is highly recommended to add "Disallow: /flag/" to your robots.txt fileComment #11
giuseppe87 commentedI had the same issues, so implemented a cron to clear expired anon flagging.
It collects the flagging created before the requestTime + sessione expiration timeout.
There' s a settings form at
/admin/config/flag/settings, I have placed in no menù or else, because currently the configuration link points toentity.flag.collectionComment #12
giuseppe87 commentedComment #13
serg.linkinHi @Giuseppe87, thanks for this patch.
Looks like there's a mistake with the condition, because flag's "created" date will always be less then "current time + session lifetime" as a future time. There should be a "-" sign instead, the updated patch is attached.
Comment #14
demonde commentedTo me #13 works fine. Thanks.
Though I could not find the settings page in the admin menu. (I applied the patch quite a while ago but now I found the settings page by monitoring the code.)
The path should be
/admin/config/flagsor/admin/structure/flags/settingsnot/admin/config/flag/settings.Comment #15
ivnishNeeds reroll to MR, needs tests how it works
Comment #18
chandansha commentedI have rerolle #13 patch to mr. Testing is Remaining.
Thanks!!
Comment #19
ivnishStill needs tests
Comment #22
deaom commentedThe MR 107 is for 8.x. branch and is for all flags, the MR 189 is for 5.x branch and is meant to be used per flag, meaning the checkbox to enable the deletion of stale anonymous flags is added to the flag form. The code then handles possible deletion per flag. The collector was cherry picked from the 8.x commit, so credentials go to the author as well. Added a Kernel test that demonstrates the cron service which handles the logic for deleting flagging is working as expected. Changing status to needs review as tests are passing.
Comment #23
tommyk commentedMR !189 could use a rebase.
Comment #24
ivnishComment #26
ivnishThanks folks, fixed!
Comment #28
ivnishI also created #3634072: Check that the anonymous session still exists before cron deletes its flaggings as separate issue
Comment #29
anybodyDocumentation clarification follow-up: #3635771: Clarify anonymous flag removal via cron
Comment #30
anybodyImportant: Enabling that function decreases the flag count - might be clear for everyone, but wasn't for me!
That's super important for cases where you for example use an anonymous "Like" counter! See #3635771: Clarify anonymous flag removal via cron for a MR.