Updating stock causing foreign key constraint fail

Question

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

Answer 1

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:

• Why can't I load a product by Sku?

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

Answer 2

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!

Answer 3

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

Answer 4

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