I've done an Ooopsie.... Bulk Remove from Blacklist

[sendmedialtd]

Hey,

I know there isn't a tool on MW where you can do a bulk Blacklist removal (like the Import Blacklist tool but the reverse) so I'm going to run a SQL query in the database to do this because I've just imported the wrong file to the blacklist because I'm an idiot and need to get them all removed.

My first option was to go off the email addresses, but then I thought well that's not going to work correctly as what if the email was already in the blacklist and it needs to stay there. So my second option is to go off the Timestamp, so anything that was added to the Customers Blacklist between 4/4/18 1:50pm and 4/4/18 1:55pm will be removed.

@twisted1919 can you confirm if running a simple SQL query to the `mw_customer_email_blacklist` table is all I need to do to get them removed. There isn't another table or anything is there, ive checked the database myself and pretty sure theres not but would like confirmation before I continue

Anyone who wants to do this then you can do this by running the following SQL Query in your database:

DELETE FROM `mw_customer_email_blacklist` WHERE `customer_id` = 1 AND `date_added` BETWEEN '2018-04-04 12:50:00.000000' AND '2018-04-04 12:56:00.000000'

Just make sure you change the Customer ID number to ensure only records from that customer are deleted and update BOTH timestamps.

Note: Check the timestamps in your database first, as in MW it will give you the time based off your selected time zone. Where as in the Database the time is stored from your server time. So for example in MW it says I imported the file to the blacklist at 1:50PM, but in the database it says 12:50pm.

 

[twisted1919]

@sendmedialtd - take care of mw_email_blacklist too and also, set the status to confirmed to all subscribers that have the blacklisted status and their last_updated date is in your above timeframe.

 

[sendmedialtd]

@twisted1919 since the data was added via the Customer Blacklist it doesn't affect the mw_email_blacklist table as it seems the data was only stored in the mw_customer_email_blacklist table. Also in regards to changing the status of the subscribers from blacklisted to confirmed is being a little troublesome. The issue with this that the "last_updated" date has not been updated from when I added them to the blacklist. Is this a bug? If not then ill put a feature request in so when using the import tool for the Customer Blacklist that when a subscriber is added to the blacklist the "last_updated" column is updated with the new date/time the status was changed to blacklisted.

At the moment I'm having to create a little PHP script in order to change the users back to Confirmed, otherwise id of just done a SQL query based off the "last_updated" time like in the first query above.

EDIT: If I was to update the "last_updated" column in the database and change the DEFAULT to CURRENT_TIMESTAMP I know that when ever that record is changed the "last_updated" will update to the current time/date. Would that cause an issue with MW? Something tells me it would..... but was just a thought.

 

[twisted1919]

Would that cause an issue with MW?

It might have side effects, so better don't do it.

 

[frm.mwz]

If I was to update the "last_updated" column in the database and change the DEFAULT to CURRENT_TIMESTAMP I know that when ever that record is changed the "last_updated" will update to the current time/date.

Perhaps only change that state for the subs in question, and only temporarily (and test first on a test dbf via clone dbf but new name and mwz to another dir on same server with new config (dbf name), and no crons, so you can check also a few things from gui).