Magento: New order email confirmation being sent, but randomly to other customers

One of my clients who own a tequila store ask me to fix this issue. The problem is really bothering him and his old customers who are not buying any items from his store, but receive new order email confirmation.

What do I find after debugging?

Starting with Magento 1.9.1 the emails are not being sent directly during checkout but instead are being queued. The queue is being processed via Magento cronjob.

There is no problem with Magento cronjobs. The command is correct. Email sent successfully. Even though receivers of the mail are not correct.

How does Magento determine the receivers of the mail? From table `core_email_queue_recipients` that has a relation with table `core_email_queue`.

MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent.

Magento add a CONSTRAINT and FOREIGN KEY `message_id` to table `core_email_queue_recipients`.

ALTER TABLE `core_email_queue_recipients`
 ADD CONSTRAINT `FK_6F4948F3ABF97DE12127EF14B140802A` FOREIGN KEY (`message_id`) REFERENCES `core_email_queue` (`message_id`) ON DELETE CASCADE ON UPDATE CASCADE;

FOREIGN KEY `message_id` will refer to `message_id` from table `core_email_queue`. Both tables are bound each other now. Table `core_email_queue` will be a parent and `core_email_queue_recipients` as a child. If I delete a row with `message_id` = 1 from `core_email_queue`. Every rows with `message_id` = 1 from `core_email_queue_recipients` will also be deleted by MySQL automatically.

So, what is the real problem? I log in to MySQL database server. I find table `core_email_queue_recipients` does not have FOREIGN KEY CONSTRAINT. I do not know why. Perhaps, he or his developer ever remove the table. So, every time cronjobs clean up email queue. MySQL server only delete email queue. Email recipients of deleted queue are ignored. Data integrity of table `core_email_queue_recipients` is broken now.

My solution is to fix the structure of table `core_email_queue_recipients`. I am afraid I cannot share the tips. Someone with database expertise should do it.

Okay, then. Problem solved.