SQL error when programmatically adding image to product

I have seen this before and it was the result of writing code like this:

Broken code do not run:

<?php

$collection = Mage::getModel('catalog/product')->getCollection();
foreach($collection as $product){
    $product->addImageToMediaGallery($file, array( < flags > ), true, false);
    $product->save();
}

Solution

This not only causes load and locking, but the image gallery is not loaded on the collection, which causes foreign key constraints to fail.

Instead of saving in the loop, your script can emulate the admin panel so you can save collections. Also, you'll want to add the media gallery to your collection:

<?php

require('app/Mage.php');
umask(0);
Mage::setIsDeveloperMode(true);
Mage::app();
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToSelect(array('image', 'media_gallery'));

foreach($collection as $product){
    $product->addImageToMediaGallery($file, array( < flags > ), true, false);
}
$collection->save();