Bouncing email newsletters

When someone creates an account, signs up for email newsletters and then their email address becomes invalid MKDoc continues to send out email that then bounce back.

There is no way in the admin or superuser interface to know that email is bouncing or to do anything about it other than delete users accounts.

The only way to deal with this at the moment is for the mail servers postmaster to keep track of the accounts that bounce and then to manually disable the sending of emails to these users at a MySQL level.

Stopping the Newsletter Manually

The bounce emails will just have the email address for the user so the first thing to do is to get their ID:

mysql> select ID from Editor where Email="example@example.org";
+----+
| ID |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

Then find out which newsletters they have signed up for:

mysql> select * from Preference where Editor_ID="2";
+----+-----------+--------------------+-------+
| ID | Editor_ID | Name               | Value |
+----+-----------+--------------------+-------+
|  1 |         2 | newsletter-daily   | 1     |
|  2 |         2 | newsletter-weekly  | 1     |
|  3 |         2 | newsletter-monthly | 1     |
+----+-----------+--------------------+-------+
3 rows in set (0.01 sec)

These first two commands can be done as one to make it simpler:

mysql> select Editor.Email, Preference.Name, Preference.Value from Preference,
       Editor where Preference.Editor_ID = Editor.ID and
       Editor.Email="example@example.org";
+---------------------+--------------------+-------+
| Email               | Name               | Value |
+---------------------+--------------------+-------+
| example@example.org | newsletter-daily   | 1     |
| example@example.org | newsletter-weekly  | 1     |
| example@example.org | newsletter-monthly | 1     |
+---------------------+--------------------+-------+
3 rows in set (0.00 sec)

Then you can get a list of rows to delete from the Preference table like this:

mysql> select Preference.ID from Preference, Editor where
       Preference.Editor_ID = Editor.ID and
       Editor.Email="example@example.org";
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

But it's easier to delete all rows corresponding to a email address, so the simplest thing is to use the first command to get the Editor.ID and then delete all rows for this user from the Preference table:

mysql> delete from Preference where Editor_ID="2";
Query OK, 3 rows affected (0.07 sec)