Really need to fix this false unsubscribe problem fast

Discussion in 'General discussions' started by droidman, Sep 7, 2018.

  1. droidman

    droidman New Member

    Joined:
    Dec 12, 2017
    Messages:
    26
    Likes Received:
    2
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    1
    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
     
  2. frm.mwz

    frm.mwz Well-Known Member

    Joined:
    Mar 8, 2016
    Messages:
    3,385
    Likes Received:
    637
    S.E:
    2018-11-06 14:46:35
    L.T:
    Regular
    L.C:
    6
    update/join/on are slow, try classic select/where as well as mysql workbench/cli
     
  3. droidman

    droidman New Member

    Joined:
    Dec 12, 2017
    Messages:
    26
    Likes Received:
    2
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    1
    Hi, thanks for the reply. can you give me any example of workbench or something similar?
     
  4. frm.mwz

    frm.mwz Well-Known Member

    Joined:
    Mar 8, 2016
    Messages:
    3,385
    Likes Received:
    637
    S.E:
    2018-11-06 14:46:35
    L.T:
    Regular
    L.C:
    6
  5. droidman

    droidman New Member

    Joined:
    Dec 12, 2017
    Messages:
    26
    Likes Received:
    2
    S.E:
    Expired
    L.T:
    Regular
    L.C:
    1
    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
     
  6. frm.mwz

    frm.mwz Well-Known Member

    Joined:
    Mar 8, 2016
    Messages:
    3,385
    Likes Received:
    637
    S.E:
    2018-11-06 14:46:35
    L.T:
    Regular
    L.C:
    6
    oh, then check my.cnf and other configs for execution/connection time/timeouts
     

Share This Page