Fix SQL Error When Programmatically Adding Image to Product in Magento 1.9

magento-1.9productproduct-images

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?

Best Answer

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