SQL error when programmatically adding image to product

Question

I am updating products programmatically and then adding images to them via

$product->addImageToMediaGallery($file, array( < flags > ), true, false);

For some products, this works just fine, for others it gives me

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db11057204-walthermagestaging`.`catalog_product_entity_media_gallery_value`, CONSTRAINT `FK_CAT_PRD_ENTT_MDA_GLR_VAL_VAL_ID_CAT_PRD_ENTT_MDA_GLR_VAL_ID` FOREIGN KEY (`value_id`) REFERENCES `c), query was: INSERT INTO `catalog_product_entity_media_gallery_value` (`value_id`, `store_id`, `label`, `position`, `disabled`) VALUES (?, ?, ?, ?, ?)

I have not been able to determine a pattern yet.

It says that a foreign key constraint fails, but in the error message, the target of the FK is cut off after the first letter, which leaves me clueless. Where to look? What to do?

EDIT

Since these products are created automatically, could it be that there is no media_gallery eav attribute? If so, how can I check?

Answer 1

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();

Answer 2

I had this error msg, when two product pictures had same filename. I do import products via magmi, and when on admin try to save product after some edit, picture is removed. Workaround is to rename product picture or manually upload picture via admin panel, admin panel will rename and handle names correctly.