Odd IntegrityError on MySQL: #1452

Question

This is sort of an odd one but I'll try to explain as best I can. I have 2 models: one representing an email message (Message), the other a sales lead (AffiliateLead). When a form is submitted through the site, the system generates a lead and then emails. The Message model has an optional FK back to the Lead. From the Message models file:

lead = models.ForeignKey('tracking.AffiliateLead', blank=True, null=True)

Now, this basic shell works:

from tracking.models import Affiliate, AffiliateLead
from messages.models import Message
from django.contrib.auth.models import User

u = User.objects.get(username='testguy')
a = Affiliate.objects.get(affiliate_id = 'ACD023')
l = AffiliateLead(affiliate = a)
l.save()
m = Message(recipient=u, sender=u, subject='s', body='a', lead=l)
m.save()

However, the form view itself does not. It throws an IntegrityError when I try to save a Message that points to an AffiliateLead:

(1452, 'Cannot add or update a child row: a foreign key constraint fails (`app`.`messages_message`, CONSTRAINT `lead_id_refs_id_6bc546751c1f96` FOREIGN KEY (`lead_id`) REFERENCES `tracking_affiliatelead` (`id`))')

This is despite the fact that the view is simply taking the form, creating and saving the AffiliateLead, then creating and (trying) to save the Message. In fact, when this error is thrown, I can go into MySQL and see the newly-created lead. It even throws this error in the view when I re-retrieve the lead from the DB immediately before saving:

af_lead = AffiliateLead.objects.get(id = af_lead.id)
msg.lead = af_lead
msg.save()

Finally, if I immediately refresh (re-submitting the form), it works. No IntegrityError. If I have Django print out the SQL it's doing, I can indeed see that it is INSERTing the AffiliateLead before it tries to INSERT the Message, and the Message INSERT is using the correct AffiliateLead ID. I'm really stumped at this point. I've even tried manual transaction handling to no avail.

Answer 1

I'm not exactly sure why it happened, but I did seem to find a solution. I'm using South to manage the DB; it created Messages as InnoDB and AffiliateLead as MyISAM. Changing the AffiliateLead table to InnoDB ended the IntegrityErrors. Hope this helps someone else.