Magento order confirmation is sent to all customers

Attention!

What this code does is: every time the magento-cronjob removes all sent messages from the core_email_queue database table, it also removes all recipients of these messages. So, basically, it does not work for you until this cronjob-task has run at least once.

Solution

I found the answer thanks to another question here: the core_email_queue_recipients table was not emptied by the cronjob. The method Mage_Core_Model_Email_Queue::cleanQueue() calls Mage_Core_Model_Resource_Email_Queue::removeSentMessages(), which is pretty simple:

public function removeSentMessages() {
    $this->_getWriteAdapter()->delete($this->getMainTable(), 'processed_at IS NOT NULL');
    return $this;
}

Anyway, this method does not remove the old recipients. Thus, as soon as a new message with message_id n is queued, all old recipients with message_id n will also get the new email. The thing I don't understand is: why has the core team not seen this, and why doesn't this lead to more issues?

I wrote a small module to fix this. It uses a class override for Mage_Core_Model_Resource_Email_Queue, so if anybody can suggest a better (event-based?) solution, I would be glad.

app/code/local/Namespace/EmailQueueFix/etc/config.xml

<?xml version="1.0"?>
<config>
    <modules>
        <Namespace_EmailQueueFix>
            <version>1.0</version>
        </Namespace_EmailQueueFix>
    </modules>
    <global>
        <models>
            <core_resource>
                <rewrite>
                    <email_queue>Namespace_EmailQueueFix_Model_Resource_Email_Queue</email_queue>
                </rewrite>
            </core_resource>
        </models>
    </global>
</config>

app/code/local/Namespace/EmailQueueFix/Model/Resource/Email/Queue.php

<?php

class Namespace_EmailQueueFix_Model_Resource_Email_Queue extends Mage_Core_Model_Resource_Email_Queue {
    /**
     * Remove already sent messages
     * ADDED: also remove all recipients of sent messages!
     *
     * @return Mage_Core_Model_Resource_Email_Queue
     */
    public function removeSentMessages() {
        $writeAdapter = $this->_getWriteAdapter();
        $readAdapter = $this->_getReadAdapter();
        $select = $readAdapter->select()->from(array("ceqr" => $this->getTable('core/email_recipients')), array('*'))->joinLeft(array('ceq' => $this->getMainTable()), 'ceqr.message_id = ceq.message_id', array('*'))->where('ceq.processed_at IS NOT NULL OR ceq.message_id IS NULL');
        $recipients = $readAdapter->fetchAll($select);
        if ( $recipients ) {
            foreach ( $recipients as $recipient ) {
                $writeAdapter->delete($this->getTable('core/email_recipients'), "recipient_id = " . $recipient['recipient_id']);
            }
        }
        $writeAdapter->delete($this->getMainTable(), 'processed_at IS NOT NULL');
        return $this;
    }

}

app/etc/modules/Namespace_EmailQueueFix.xml

<?xml version="1.0"?>
<config>
    <modules>
        <Namespace_EmailQueueFix>
            <codePool>local</codePool>
            <active>true</active>
        </Namespace_EmailQueueFix>
        <depends>
            <Mage_Core/>
        </depends>
    </modules>
</config>

I've post a different fix that doesn't require to install a new module, and probably is a little cleaner.

It just 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