When I use the Mass Unsubscribe function, I get no errors and all the right feedback - but nothing has changed afterwards. Example:

Confirmation message:

But when I go back and check those addresses, they are still active and subscribed to that newsletter. There are no errors in the dblog.

I made a view to try and figure out what was happening and found that there appear to be two subscriptions for each address, with different SIDs:

SUBSCRIBER SOURCE SUBSCRIBER ID ACTIVATED SUB STATUS NEWSLETTER TIMESTAMP
worlingham@hotmail.com mass unsubscribe 31892 Yes No Well Versed 22/03/2014 - 17:35
worlingham@hotmail.com mass subscribe 31893 Yes Yes Well Versed 25/02/2014 - 13:10
ygg.aberdar@rctednet.net mass unsubscribe 31900 No No Well Versed 02/04/2014 - 19:47
ygg.aberdar@rctednet.net mass subscribe 31901 Yes Yes Well Versed 25/02/2014 - 13:10

I did a full export from SimpleNews (5377 addresses) and compared it with a view (6208 subscriptions) - while most subscriptions are fine, there is a large number which repeat the same email address from two to four times.

Is there any way to fix this, short of exporting the data, fixing it, and generating a whole new subscription list? I have about 10 newsletters which need to be checked, each with between 100 and 20,000 addresses.

Comments

kmajzlik’s picture

kmajzlik CreditAttribution: kmajzlik commented

I really dont know how these items were created but here is my snippet which helped me to clean:


/**
 * Delete duplicates
 */
function HOOK_update_7000(&$sandbox) {
  $sel = db_select('simplenews_subscriber', 's');
  $sel->addExpression('COUNT(s.mail)', 'cnt');
  $sel->addField('s', 'mail');
  $sel->groupBy('mail');
  $sel->orderBy('cnt', 'DESC');
  $sel->range(0, 500);
  $result = $sel->execute()->fetchAll();
  $i = 0;
  foreach ($result as $row) {
    if ($row->cnt > 1) {
      $subscribers = db_query("SELECT s.snid, s.uid, s.mail, u.name, u.created FROM {simplenews_subscriber} s LEFT JOIN {users} u ON s.uid = u.uid WHERE s.mail = :mail", array(':mail' => $row->mail))->fetchAll();
      foreach ($subscribers as $subscriber) {
        if ($subscriber->uid == 0) {
          db_query("DELETE FROM {simplenews_subscription} WHERE snid = :snid", array(':snid' => $subscriber->snid));
          db_query("DELETE FROM {simplenews_subscriber} WHERE snid = :snid", array(':snid' => $subscriber->snid));
          watchdog('simplenews_uid0', 'deleted snid @snid for user @uid - @name', array('@snid' => $subscriber->snid, '@uid' => $subscriber->uid, '@name' => $subscriber->name));
          $i++;
        } elseif (is_null($subscriber->created)) {
          db_query("DELETE FROM {simplenews_subscription} WHERE snid = :snid", array(':snid' => $subscriber->snid));
          db_query("DELETE FROM {simplenews_subscriber} WHERE snid = :snid", array(':snid' => $subscriber->snid));
          watchdog('simplenews_snid', 'deleted snid @snid for user @uid - @name', array('@snid' => $subscriber->snid, '@uid' => $subscriber->uid, '@name' => $subscriber->name));
          $i++;
        } else {
          watchdog('simplenews_skip', 'Skipped snid @snid for user @uid - @name', array('@snid' => $subscriber->snid, '@uid' => $subscriber->uid, '@name' => $subscriber->name));
        }
      }
    }
  }
  return 'Deleted ' . $i . ' items';
}