4

So, I'm trying to update product quantities programmatically. On a standard magento install, it works flawlessly, but on the actual magento shop I'm developing it for, it fails with a SQLSTATE[23000]: Integrity constraint violation.

It happens when I save the alterations I made on the product. The relevant bit of code,

// Check if the product exists
$id = Mage::getModel('catalog/product')->getIdBySku($sku);
if ($id) {
    $product = Mage::getModel('catalog/product')->load($sku, 'sku'); 
    $stockItem = $product->getStockItem();
    $totalStock = $stockItem->getData('qty') + $qty;

    $stockItem->setData('qty', $totalStock)
              ->setData('is_in_stock', $totalStock > 0 ? 1 : 0);
    try {
      $stockItem->save();
    } catch (Exception $e) {
        echo $e->getMessage();
    }
}

which returns the exception,

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a 
child row: a foreign key constraint fails 
(`staging_shop`.`cataloginventory_stock_item`, CONSTRAINT 
`FK_CATINV_STOCK_ITEM_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) O)

I'm a bit at a loss here, and googling hasn't really helped at all. Might this be some extension or something that is messing with me, or is the database corrupt? Any help, even the smallest, is appreciated!

UPDATE: Solution below

Thanks to @boomer, I managed to get it working. Just for future reference (and fellow googlers), the following code is what works (at least with magento 1.8.x),

// Check if the product exists
if (Mage::getModel('catalog/product')->getIdBySku($sku)) {
    // Load the product stock object
    $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct(
        Mage::getModel('catalog/product')->loadByAttribute('sku', $sku)
    );
    // Add to the existing quantity of the product
    $stockItem->addQty($qty)->save();
}
3

This won't work and is returning 0:

$product = Mage::getModel('catalog/product')->load($sku, 'sku');

Hence the FK constraint, note the 0 at the end of your error.

See:

FK_CATINV_STOCK_ITEM_PRD_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (product_id) REFERENCES catalog_product_entity (entity_id) O)

You already have the entity_id of the product with this line:

$id = Mage::getModel('catalog/product')->getIdBySku($sku);

Which means the condition is met with the if, but later all is assumed.

A better approach:

$productModel = Mage::getModel('catalog/product');
$product = $productModel->loadByAttribute('sku', $sku);
if($product) {
    $stockItem = $product->getStockItem();
...
  • 1
    Thanks! Finally a solution that treated the illness, instead of the symptoms. Also, I apparently had messed up a bit, not noticing that I was testing against magento 1.9, but the production environment was running 1.8.0.1. Didn't expect the API to change so frequently... – Tehnix Nov 18 '14 at 8:14
  • I know this is an old thread, but I am running into this now and found this answer. The attempt to load by sku shouldn't return a zero, and that's not actually what's in the error. You said "...note the 0 at the end", but what's at the end is a capital 'oh' (O), not a 'zero' (0). What does the 'O' mean? – BrianVPS Mar 12 at 19:49
  • @BrianVPS Most likely the remaining ON ... was left out and a ) got into OPs as REFERENCES will point to what table and field along with what ON FK event/fields/etc. dev.mysql.com/doc/refman/5.7/en/… – B00MER Mar 20 at 6:04
3
  1. Back up your current DB.
  2. Turn ON MySQL query logging by changing the following lines in lib\Varien\Db\Adapter\Pdo\Mysql.php:

    protected $_debug = true; 
    protected $_logAllQueries = true; 
    protected $_debugFile = 'var/debug/pdo_mysql.log';
    
  3. Search for CREATE TABLE and change it to CREATE TABLE IF NOT EXISTS in code/core/mage/.

  4. Rename /errors/local.xml.sample to /errors/local.xml to enable error_reporting.
  5. Clear Magento’s cache by deleting all data from var/cache.
  6. Go to your browser and type the path of the store and keep your eyes on the browser and on the var/debug/pdo_mysql.log file. You can use a bash command:

    tail -f var/debug/pdo_mysql.log
    
  7. Do not close the browser! Just wait until the updating process is finished or will fail (it most likely will). Notice last query that caused an error.

  8. Most likely you should truncate all the problematic tables. For me it was all the sales_flat_ tables. For you it could be catalog_ or customer_ ones.
  9. To truncate a table using phpMyAdmin tool (or command line) you will need to disable foreign key and unique key checks. For example, to to truncate a sales_flat_order table you'll need to run this query:

    SET FOREIGN_KEY_CHECKS =0;
    SET UNIQUE_CHECKS =0;
    TRUNCATE TABLE `sales_flat_order`
    

Please go trough these steps and after step 6 post in comments or update the answer about the query which causes an error.

Hope it would help you or anyone else.

P.S. I have managed to update my store from 1.4.0.1 to the most recent today 1.9.0.1 (including all the latest patches with USPS, PayPal SSLv3 vulnerability). So good luck with your case as well!

  • I didn't do all you said, just truncated the tables (also a couple of sales_flat tables) that were in the error message and, joy of joys, it seems to have sold the problem! My worry is why did it start being a problem in the first place?! – PedroKTFC Jul 7 '17 at 8:39
  • Actually, the problem then recurs next time I try to log in! – PedroKTFC Jul 7 '17 at 10:26
1

Have you chxked both tables as the error suggests to see if a row with the respective entity ID exists?

Ie.

  • cataloginventory_stock_item
  • catalog_product_entity
1

In our experience, we had to initialise the stock item, otherwise I would get the Integrity constraint violation when I tried to save the product.

$stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($productId);
if($stockItem && !$stockItem->getProductId()) { // CHECK STOCK ITEM IS INITIALISED
    $stockItem->setData('product_id', $productId);
    $stockItem->setData('stock_id', 1);
}

$stockItem->setData('use_config_manage_stock', 0);
$stockItem->setData('manage_stock', 1);
$stockItem->setData('is_in_stock', 1);
$stockItem->setData('qty', 5);
$stockItem->save();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.