phplist bounce processing issue

Based on a complaint received on 19 Nov, checked the server logs and found that Gmail is starting to mark our mails as spam because we're daily sending mail to bouncing gmail accounts. 

PB went to the reconcile users page in phplist and looked at the process of unsubscribing users who have 15 and more bounces. (Found about 9,527 users) - but did not run it since this was not 15 consecutive bounces. Running the bounce processing manually, the bounce processing takes 3 hours, identifies the bounces, but does not unconfirm subscribers with 5 consecutive bounces. So, running bounce processing is not solving the problem. We needed to fix the bounce processing rules which are being applied.

PB found, checking for the location where the rules are defined and found that phplist is picking up rules from the database table. Table names: our_prefix_bounceregex and our_prefix_bounceregex_bounce. But those tables showed empty records. Did not find any web interface to add rules to these tables. Did not find a backup with entries. PB downloaded the sourcecode (version 2.10.10) from https://sourceforge.net/projects/phplist/files/phplist/ and checked the phplist.sql file that creates the database on a new installation. There too the records were empty.

(Edit: Found a discussion about this in the phplist manual here. )

Then, a quick and dirty fix implemented by PB on my request: in phplist web interface "view bounces" selected the message id 4189, got the list of email ids that had bounced for the message id 4189

mysql> select id,email,confirmed from our_prefix_user_user where id IN ( select user from our_prefix_user_message_bounce where message=4189);

- about 1147 records

Unconfirmed the users with the following sql update:

mysql> UPDATE our_prefix_user_user SET confirmed=0  WHERE id IN (SELECT user FROM our_prefix_user_message_bounce WHERE message=4189);

Query OK, 1146 rows affected (0.54 sec)

Rows matched: 1147  Changed: 1146  Warnings: 0