Having trouble deleting from Blacklist and changing Status

[Papapooch]

Hi Guys.
We uploaded 18,000 names to the customer blacklist by mistake. We have 2.3 million subscribers so that is really a small number in comparison.
I have been trying to delete those names (I use the word "names" for email addresses...habit from 30 years in the direct mail business...long before email was invented) from the blacklist as well as setting the status back to "confirmed" for the corresponding emails in all-subscribers.

A couple questions related to this. Is there a way to do that from the dashboard?

1. Is there a way to look at settings and see if and when subscribers marked as blacklisted are removed from the mailing lists?

In lieu of that I have been trying to run sql scripts in phpmyadmin to delete the names from the blacklists. The scripts I am running finish in 2 seconds on our sql server. But this takes 29 minutes on the MW tables and responds that 0 records were affected.

delete from mw_customer_email_blacklist where email in (SELECT email from blacklist_remove);

When I use an inner join to accomplish the same thing it also takes a very long time.
There are only 122,000 names in the blacklist and 18000 in the blacklist_remove table.
Tiny by most database standards.

I hesitate to use the same query to change the status on our 2.3 million mw_list_subscriber.

Must I take the MW app offline to run these? Are the tables locked up maybe?
Any help would be appreciated.

Thanks

 

[twisted1919]

A couple questions related to this. Is there a way to do that from the dashboard?

Unfortunately now. You can just run a query against both tables and take into consideration the "last_update" column. match it with the day you did this, and you should be fine.

The scripts I am running finish in 2 seconds on our sql server. But this takes 29 minutes on the MW tables and responds that 0 records were affected.

The wonders of MySQL... we do have indexes which makes this process slow. We're interested in fast reads, with the tradeoff of slow deletes and updates. We can live with that.

Must I take the MW app offline to run these?

That would assure the process is faster.

Are the tables locked up maybe?

Nope, innodb locks the rows not the entire table.

 

[Papapooch]

Thanks for the answers.
I was able to delete the entries in the Customer Blacklist. I then ran a query to change the status in List Subscriber but it appeared that the query ran for 6 hours and did not finish so I stopped it.

But then the next day Jyll told me that all those names had indeed been restored to confirmed status, which surprised me, because I thought the changes would have been rolled back on an incomplete query.

Is the system set up so that if the emails are not in the blacklist then they are marked confirmed?
The only other explanation would be that the query did finish but was not reported as such in the HeidiSQL client software.

We're interested in fast reads, with the tradeoff of slow deletes and updates. We can live with that.

Maybe that is why some subscribers have complained that there was no response when either Clicking on an Email Link or trying to Unsubscribe. With 32 million records in the Campaign Delivery Log maybe it's time to archive that data. I see there is a Delivery Log Archive table. Are there instructions for archiving without breaking the system?

Thanks

 

[twisted1919]

But then the next day Jyll told me that all those names had indeed been restored to confirmed status, which surprised me, because I thought the changes would have been rolled back on an incomplete query.

Most likely you didn't kill the query but just the connection to the server, which left the query running.

Is the system set up so that if the emails are not in the blacklist then they are marked confirmed?

Nope.

With 32 million records in the Campaign Delivery Log maybe it's time to archive that data

Why don't you use Backend > Settings > Cron > Delete Delivery logs (set to yes)?
This will remove the logs but keep the stats, thus keeping your database very lean.