Running custom php script results in SQLSTATE[40001]

Question

I have created a custom script to reset to the default values for certain attributes across all my products and my 3 store views.

Here's the code

<?php
require_once "../app/Mage.php";
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
set_time_limit(0);
echo "Starting reseting; <br>";
try {
    $collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('visibility')
        ->addAttributeToSelect('status')
        ->addAttributeToSelect('instagrampro_source')
        ->addAttributeToSelect('ebizmarts_mark_visited')
        ->addAttributeToSelect('searchindex_weight')
        ->addAttributeToSelect('am_shipping_type')
        ->addAttributeToSelect('tax_class_id')
        ->addAttributeToSelect('product')
        ->addAttributeToSelect('stone_details')
        ->addAttributeToSelect('dimensions')
        ->addAttributeToSelect('details')
        ->addAttributeToSelect('country_of_manufacture')
        ->addAttributeToSelect('inventory_manage_stock')
        ->addAttributeToSelect('inventory_min_qty')
        ->addAttributeToSelect('inventory_min_sale_qty')
        ->addAttributeToSelect('inventory_max_sale_qty')
        ->addAttributeToSelect('inventory_backorders')
        ->addAttributeToSelect('inventory_notify_stock_qty')
        ->addAttributeToSelect('inventory_enable_qty_increments')
        ->addAttributeToSelect('meta_title')
        ->addAttributeToSelect('meta_keyword')
        ->addAttributeToSelect('meta_description');

    foreach ($collection as $product) {
        for ($i = 1; $i < 4; $i++) {
            $product = Mage::getModel('catalog/product')
                ->load($product->getID())
                ->setStoreId($i)
                ->setData('visibility', false)
                ->setData('status', false)
                ->setData('instagrampro_source', false)
                ->setData('ebizmarts_mark_visited', false)
                ->setData('searchindex_weight', false)
                ->setData('am_shipping_type', false)
                ->setData('tax_class_id', false)
                ->setData('product', false)
                ->setData('stone_details', false)
                ->setData('dimensions', false)
                ->setData('details', false)
                ->setData('country_of_manufacture', false)
                ->setData('inventory_manage_stock', false)
                ->setData('inventory_min_qty', false)
                ->setData('inventory_min_sale_qty', false)
                ->setData('inventory_max_sale_qty', false)
                ->setData('inventory_backorders', false)
                ->setData('inventory_notify_stock_qty', false)
                ->setData('inventory_enable_qty_increments', false)
                ->setData('meta_title', false)
                ->setData('meta_keyword', false)
                ->setData('meta_description', false)
                ->save();
        }
        flush();
    }
} catch (Exception $ex) {
    echo $ex;
}
echo 'Done';

I have put my script named reset.php under /test/reset.php

Here's the error i get:

> Starting reseting;
> exception 'PDOException' with message
> 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
> trying to get lock; try restarting transaction' in
> /home/myclient/public_html/lib/Zend/Db/Statement/Pdo.php:228 Stack
> trace: 
> #0 /home/myclient/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array) 
> #1 /home/myclient/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110):
> Zend_Db_Statement_Pdo->_execute(Array) 
> #2 /home/myclient/public_html/app/code/core/Zend/Db/Statement.php(291):
> Varien_Db_Statement_Pdo_Mysql->_execute(Array) 
> #3 /home/myclient/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array) 
> #4 /home/myclient/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238):
> Zend_Db_Adapter_Abstract->query('UPDATE `catalog...', Array) 
> #5 /home/myclient/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504):
> Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `catalog...', Array) 
> #6 /home/myclient/public_html/lib/Zend/Db/Adapter/Abstract.php(635): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `catalog...', Array) 
> #7 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Resource/Stock/Status.php(83):
> Zend_Db_Adapter_Abstract->update('cataloginventor...', Array, Array) 
> #8 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Stock/Status.php(397): Mage_CatalogInventory_Model_Resource_Stock_Status->saveProductStatus(Object(Mage_CatalogInventory_Model_Stock_Status),
> '230', '1', '0.0000', 1, 1) 
> #9 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Stock/Status.php(337): Mage_CatalogInventory_Model_Stock_Status->saveProductStatus('230',
> '1', '0.0000', 1, 1) 
> #10 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Stock/Status.php(265): Mage_CatalogInventory_Model_Stock_Status->_processChildren('230',
> 'simple', '0.0000', '1', 1, NULL) 
> #11 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Observer.php(149):
> Mage_CatalogInventory_Model_Stock_Status->updateStatus('230') 
> #12 /home/myclient/public_html/app/code/core/Mage/Core/Model/App.php(1358):
> Mage_CatalogInventory_Model_Observer->saveInventoryData(Object(Varien_Event_Observer))
> 
> #13 /home/myclient/public_html/app/code/core/Mage/Core/Model/App.php(1337):
> Mage_Core_Model_App->_callObserverMethod(Object(Mage_CatalogInventory_Model_Observer),
> 'saveInventoryDa...', Object(Varien_Event_Observer)) 
> #14 /home/myclient/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('catalog_product...', Array) 
> #15 /home/myclient/public_html/app/code/core/Mage/Core/Model/Abstract.php(466):
> Mage::dispatchEvent('catalog_product...', Array) 
> #16 /home/myclient/public_html/app/code/core/Mage/Catalog/Model/Product.php(549):
> Mage_Core_Model_Abstract->_afterSave() 
> #17 /home/myclient/public_html/app/code/core/Mage/Core/Model/Abstract.php(319):
> Mage_Catalog_Model_Product->_afterSave() 
> #18 /home/myclient/public_html/test/reset.php(63): Mage_Core_Model_Abstract->save() 
> #19 {main} Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
> trying to get lock; try restarting transaction, query was: UPDATE
> `cataloginventory_stock_status` SET `qty` = ?, `stock_status` = ?
> WHERE (product_id=230) AND (website_id=1) AND (stock_id=1)' in
> /home/myclient/public_html/lib/Zend/Db/Statement/Pdo.php:235 Stack
> trace: 
> #0 /home/myclient/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110):
> Zend_Db_Statement_Pdo->_execute(Array) 
> #1 /home/myclient/public_html/app/code/core/Zend/Db/Statement.php(291):
> Varien_Db_Statement_Pdo_Mysql->_execute(Array) 
> #2 /home/myclient/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array) 
> #3 /home/myclient/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238):
> Zend_Db_Adapter_Abstract->query('UPDATE `catalog...', Array) 
> #4 /home/myclient/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504):
> Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `catalog...', Array) 
> #5 /home/myclient/public_html/lib/Zend/Db/Adapter/Abstract.php(635): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `catalog...', Array) 
> #6 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Resource/Stock/Status.php(83):
> Zend_Db_Adapter_Abstract->update('cataloginventor...', Array, Array) 
> #7 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Stock/Status.php(397): Mage_CatalogInventory_Model_Resource_Stock_Status->saveProductStatus(Object(Mage_CatalogInventory_Model_Stock_Status),
> '230', '1', '0.0000', 1, 1) 
> #8 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Stock/Status.php(337): Mage_CatalogInventory_Model_Stock_Status->saveProductStatus('230',
> '1', '0.0000', 1, 1) 
> #9 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Stock/Status.php(265): Mage_CatalogInventory_Model_Stock_Status->_processChildren('230',
> 'simple', '0.0000', '1', 1, NULL) 
> #10 /home/myclient/public_html/app/code/core/Mage/CatalogInventory/Model/Observer.php(149):
> Mage_CatalogInventory_Model_Stock_Status->updateStatus('230') 
> #11 /home/myclient/public_html/app/code/core/Mage/Core/Model/App.php(1358):
> Mage_CatalogInventory_Model_Observer->saveInventoryData(Object(Varien_Event_Observer))
> 
> #12 /home/myclient/public_html/app/code/core/Mage/Core/Model/App.php(1337):
> Mage_Core_Model_App->_callObserverMethod(Object(Mage_CatalogInventory_Model_Observer),
> 'saveInventoryDa...', Object(Varien_Event_Observer)) 
> #13 /home/myclient/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('catalog_product...', Array) 
> #14 /home/myclient/public_html/app/code/core/Mage/Core/Model/Abstract.php(466):
> Mage::dispatchEvent('catalog_product...', Array) 
> #15 /home/myclient/public_html/app/code/core/Mage/Catalog/Model/Product.php(549):
> Mage_Core_Model_Abstract->_afterSave() 
> #16 /home/myclient/public_html/app/code/core/Mage/Core/Model/Abstract.php(319):
> Mage_Catalog_Model_Product->_afterSave() 
> #17 /home/myclient/public_html/test/reset.php(63): Mage_Core_Model_Abstract->save() 
> #18 {main}Done

I tried to comment out the product with id 230 as that's what I found from the stack trace but it resulted on a 404 page after running for a couple of minutes. On top of that I have a sandbox version of my site with almost 200 less products and this script, though it was really slow, run without any problems. I am guessing it's a product specific issue but can't seem to find it.

Any ideas/help would be useful.

Dominic Pixie · Accepted Answer · 2019-08-08 12:22:11Z

I recommend that you implement a looping mechanism ran than just one big loop

https://www.atwix.com/magento/working-with-large-collections/

So initial collection can be minimal as you are loading by ID within the loop.

$productsCollection = Mage::getModel('catalog/product')->getCollection();

Better yet use this to return just IDs - might need to adapt your loop slightly

$productsCollection->getAllIds();

And then

    $productsCollection->setPageSize(10);
    $pages = $productsCollection->getLastPageNumber();
    $currentPage = 1;
    do {
        $productsCollection->setCurPage($currentPage);
        $productsCollection->load();
        foreach ($productsCollection as $_product) {
            // delete code
            // $product = Mage::getModel('catalog/product')
            //     ->load($_product->getId()) 
            // etc
        }
        $currentPage++;
        //clear collection and free memory
        $productsCollection->clear();
    } while ($currentPage <= $pages);

That should get your closer

Stack Exchange Network

Running custom php script results in SQLSTATE[40001]

1 Answer 1

Your Answer

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

Hot Network Questions

Running custom php script results in SQLSTATE[40001]

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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