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.

(TODO: add the MySQL commands needed here)

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 then 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 to get a list of rows to delete from the Preference table:

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

Then delete the rows corresponding to this users account:

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

Sign up

Bouncing email newsletters

Stopping the Newsletter Manually