HostOnNet Blog

Magento sending order confirmation mail to random users

On a Magento 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.

Posted in Magento