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
]
);
}