On a Magento 220.127.116.11 web site, when customers place order, mail goes out to 3 users. One of the recipient is the actual buyer of the item. Other 2 random users.
When sending email, magneto add it to a MySQL database table. These tables are
core_email_queue stores actual messages.
core_email_queue_recipients table stores recipients.
When a message need to be sent, magento look in table “core_email_queue stores”. Each message have a field called “message_id”, then check table “core_email_queue_recipients” and send email to all recipients that have a message_id field same as current email.
Due to some bug, recipients on table “core_email_queue_recipients” never get deleted. Magento cronjob delete all records from table “core_email_queue” everyday. So when site get first order, it get send to users who have message_id field set to 1. This is not random, these users always get copy of first order in a day. Lucky for us, only 2 users matched this.
To fix this, run following SQL in phpMyAdmin or MySQL command prompt.
DELETE FROM core_email_queue_recipients WHERE message_id NOT IN (SELECT message_id FROM core_email_queue); DELETE FROM core_email_queue_recipients WHERE recipient_id < (SELECT recipient_id FROM (SELECT recipient_id FROM core_email_queue_recipients ORDER BY message_id ASC, recipient_id DESC LIMIT 1) AS r); ALTER TABLE core_email_queue_recipients ADD FOREIGN KEY(message_id) REFERENCES core_email_queue(message_id) ON DELETE CASCADE;
3rd SQL command set a FOREIGN KEY, so when a message is deleted, related records from table “core_email_queue_recipients” get deleted.