Really need to fix this false unsubscribe problem fast

droidman

Member
Hi, hope someone can help me here.
Using a third party tool to process pmta bounces on a server that had bad reputation one of my databases got absolutely destroyed because it was on a bad neighborhood on the datacenter, their entire /24 got blocked on many isp's. gmail, yahoo and so on.
When i figured out the bug was too late, what happaned was that a bug on the powermta remote bounce processor was marking bounces as unsubscribes, and all the bounces are false because for example i have a database of 17.000 people from belgium that has an absurd ammount of opens, its a very active database and 100% of it was banned because 100% are gmail and yahoo.

So i made this query to try and restore on that database first, to recover false bounces and false unsubscribes:
Code:
UPDATE mw_list_subscriber a
JOIN mw_campaign_track_unsubscribe b ON   a.subscriber_id = b.subscriber_id
JOIN mw_campaign_bounce_log c ON a.subscriber_id = c.subscriber_id
SET a.status = "confirmed" where a.status = "unsubscribed" and a.list_id = 108

so basicly the subscriber id is not on mw_campaign_track_unsubscribe and mw_campaign_bounce_log, not suppressed (the query was bigger also) but the problem is that somehow this query times out.
on a 17k database, it times out:
700 Invalid response: 504 (using navicat)
a similar lock wait timeout running on ssh.

Is there a way to improve this query? am i doing this the wrong way ?
Because i need to fix it and run this on the entire system. not just on database 108
thanks in advance
 
Hi, hope someone can help me here.
Using a third party tool to process pmta bounces on a server that had bad reputation one of my databases got absolutely destroyed because it was on a bad neighborhood on the datacenter, their entire /24 got blocked on many isp's. gmail, yahoo and so on.
When i figured out the bug was too late, what happaned was that a bug on the powermta remote bounce processor was marking bounces as unsubscribes, and all the bounces are false because for example i have a database of 17.000 people from belgium that has an absurd ammount of opens, its a very active database and 100% of it was banned because 100% are gmail and yahoo.

So i made this query to try and restore on that database first, to recover false bounces and false unsubscribes:
Code:
UPDATE mw_list_subscriber a
JOIN mw_campaign_track_unsubscribe b ON   a.subscriber_id = b.subscriber_id
JOIN mw_campaign_bounce_log c ON a.subscriber_id = c.subscriber_id
SET a.status = "confirmed" where a.status = "unsubscribed" and a.list_id = 108

so basicly the subscriber id is not on mw_campaign_track_unsubscribe and mw_campaign_bounce_log, not suppressed (the query was bigger also) but the problem is that somehow this query times out.
on a 17k database, it times out:
700 Invalid response: 504 (using navicat)
a similar lock wait timeout running on ssh.

Is there a way to improve this query? am i doing this the wrong way ?
Because i need to fix it and run this on the entire system. not just on database 108
thanks in advance
update/join/on are slow, try classic select/where as well as mysql workbench/cli
 
yeah but even running the query on ssh under root it times out and asks me to resume. i did it like 10 times and quitted trying
 
Back
Top