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
    ]
  );
}
CommentFileSizeAuthor
#13 3018713-13.patch8.89 KBserg.linkin
#11 3018713-11.patch8.89 KBgiuseppe87

Issue fork flag-3018713

Command icon 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:

Comments

Ambidex created an issue. See original summary.

joachim’s picture

Version: 8.x-4.0-alpha3 » 8.x-4.x-dev
Category: Bug report » Feature request
Priority: Normal » Major

> 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.

nvaken’s picture

Yeah, 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?

joachim’s picture

Title: Orphaned by anonymous session flaggings should be deleted » Add optional deletion of anonymous flaggings whose session has expired

Is 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.

emclaughlin’s picture

@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!

murz’s picture

@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!

ahsanalishahid made their first commit to this issue’s fork.

demonde’s picture

Flag 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.

jim005’s picture

We'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?

demonde’s picture

The actual solution until this issue is solved is:

It is highly recommended to add "Disallow: /flag/" to your robots.txt file

giuseppe87’s picture

StatusFileSize
new8.89 KB

I 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 to entity.flag.collection

giuseppe87’s picture

Status: Active » Needs work
serg.linkin’s picture

StatusFileSize
new8.89 KB

Hi @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.

demonde’s picture

To 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/flags or /admin/structure/flags/settings not /admin/config/flag/settings.

ivnish’s picture

Issue tags: +Needs tests, +Needs reroll

Needs reroll to MR, needs tests how it works

chandansha made their first commit to this issue’s fork.

chandansha’s picture

Status: Needs work » Needs review

I have rerolle #13 patch to mr. Testing is Remaining.
Thanks!!

ivnish’s picture

Status: Needs review » Needs work
Issue tags: -Needs reroll

Still needs tests

deaom made their first commit to this issue’s fork.

deaom’s picture

Version: 8.x-4.x-dev » 5.x-dev
Priority: Major » Normal
Status: Needs work » Needs review
Issue tags: -Needs tests

The 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.

tommyk’s picture

MR !189 could use a rebase.

ivnish’s picture

Version: 5.x-dev » 5.2.x-dev

  • ivnish committed 5e38eed0 on 5.2.x authored by deaom
    feat: #3018713 Add optional deletion of anonymous flaggings whose...
ivnish’s picture

Status: Needs review » Fixed

Thanks folks, fixed!

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.

ivnish’s picture

anybody’s picture

Documentation clarification follow-up: #3635771: Clarify anonymous flag removal via cron

anybody’s picture

Important: 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.