How to delete email recipients from database?

Question

After upgrading from 1.7 to 1.9.1 I have some challenges for the Cron Mail. Mail sends out as it should without any problem.

When the cron job core/email_queue::cleanQueue is running, it only delete the e-mails (rows) in SQL table core_email_queue where processed_at got a time value indication mail have been sent.

Problem is that in SQL table core_email_queue_recipients still keep the recipient's e-mail adresses and when new orders arrive, it add new mail addresses with same message_id and recipient_id as previous sent e-mails. Then the new customer and previous (wrong) customer get same e-mail.

Solving: How can I delete any previous date of core_email_queue_recipients that already got e-mails? Is there something wrong with the DataBase where these two tables should be linked so when e-mail is deleted the recipients should also be deleted? Or should it be an additional deleting process in one of the cron jobs like in core/email_queue::cleanQueue?

Looks like I only have seen one other person have this problem, so I am not sure how I ended up here. But I am no Magento Expert, so any good advices will be warmly welcome.

Answer 1

Ok, i think i have got the solution for the double IDs in the table core_email_queue_recipients of the Magento DB.

I don't exactly know which one of the three extensions is responsible, but i am now sure that it was one of them.

I deactivated the following Modules (which i never really needed) in the Magento configuration:

Aschroder_SMTPPro,

Mage_Advancedsmtp,

Mage_Advancesmtp

One of these 3 Extension ist responsible for "sending" something like an "Insert Ignore".

After that i cleaned the cache and now i have ongoing Message Ids and no more wrong recipients of order confirmations! Hope that works for others with the same problem too! Thx

Answer 2

It seems a Database architecture issue. I've post a fix that uses a foreign key constraint on the core_email_queue_recipients table to delete Recipients records on cascade.

By using this new foreign key, no orphan records will be left on the core_email_queue_recipients table when cleaning the core_email_queue table, so no duplicated messages will be further sent to wrong recipients.

You can find the detailed solution on this post: https://magento.stackexchange.com/a/87299/23057