0

I have started a reindex and it was taking too long so I closed the process and restarted mysql. Now , when I'm tring to run from command line 

php indexer.php --reindex catalog_product_attribute

I'm getting this error

Product Attributes index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction' in /var/html/www/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
0 /var/html/www/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
1 /var/html/www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
2 /var/html/www/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
3 /var/html/www/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
4 /var/html/www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('DELETE FROM `ca...', Array)
5 /var/html/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('DELETE FROM `ca...', Array)
6 /var/html/www/lib/Zend/Db/Adapter/Abstract.php(664): Varien_Db_Adapter_Pdo_Mysql->query('DELETE FROM `ca...')
7 /var/html/www/app/code/core/Mage/Index/Model/Resource/Abstract.php(235): Zend_Db_Adapter_Abstract->delete('catalog_product...')
8 /var/html/www/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(49): Mage_Index_Model_Resource_Abstract->clearTemporaryIndexTable()
9 /var/html/www/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll()
10 /var/html/www/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll()
11 /var/html/www/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
12 /var/html/www/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
13 /var/html/www/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
14 /var/html/www/shell/indexer.php(217): Mage_Shell_Compiler->run()
15 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: DELETE FROM `catalog_product_index_eav_idx`' in /var/html/www/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
0 /var/html/www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
1 /var/html/www/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
2 /var/html/www/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
3 /var/html/www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('DELETE FROM `ca...', Array)
4 /var/html/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('DELETE FROM `ca...', Array)
5 /var/html/www/lib/Zend/Db/Adapter/Abstract.php(664): Varien_Db_Adapter_Pdo_Mysql->query('DELETE FROM `ca...')
6 /var/html/www/app/code/core/Mage/Index/Model/Resource/Abstract.php(235): Zend_Db_Adapter_Abstract->delete('catalog_product...')
7 /var/html/www/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(49): Mage_Index_Model_Resource_Abstract->clearTemporaryIndexTable()
8 /var/html/www/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll()
9 /var/html/www/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll()
10 /var/html/www/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
11 /var/html/www/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
12 /var/html/www/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
13 /var/html/www/shell/indexer.php(217): Mage_Shell_Compiler->run()
14 {main}

When doing this a few times I logged in to mysql and ran a

show processlist; I tried to increase the lock wait timeout to 1000 even and it still blocked.

The table has 9 million+ rows - maybe that is a factor of why it's taking so long ? What to do please ?

Improve this question
1
  • I'm running Magento 1.9 on 12 cores with 32 GB of RAM - SamuraiJ
    – SamuraiJ1
    Commented Mar 28, 2018 at 10:43

2 Answers 2

Reset to default
1

you can first check other posts, for solving lock wait timeout. e.g. here: https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im

Running a Full Magento Index, is consuming a lot of power.

Have you tried to run the full reindex at a time, your shop has less visits?

On our Magento Shop system, when doing full reindex, we're truncating (not deleting ) the magento index table. For catalog attributes you can truncate these

TRUNCATE TABLE catalog_product_index_eav; TRUNCATE TABLE catalog_product_index_eav_idx; TRUNCATE TABLE cataloginventory_stock_status;

But use at your own risk. Then start the indexer.

We're also working with master/slave setup, that all reading-queries are fired on our slave. So starting index processes is no problem

Improve this answer
2
  • I have used this on my dev site and it worked , would you be so kind to give me the tables that need truncating for the prices index? Cheers..
    – SamuraiJ1
    Commented Mar 28, 2018 at 13:57
  • yes.TRUNCATE TABLE cataloginventory_stock_status; TRUNCATE TABLE catalog_product_index_price_idx; TRUNCATE TABLE catalog_product_index_price_final_idx; TRUNCATE TABLE catalog_product_index_price;
    – manu
    Commented Mar 28, 2018 at 14:01
0

check the mysql process list by :-

SHOW PROCESSLIST;

then kill the sleep process first

kill $Id (of th processes)

then run the reindex command

php bin/magento index:reindex

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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