Problem Reindexing Catalog Url

Question

I am trying to reindex my magento installation but I get this error when I run:

php indexer.php --reindex catalog_url

Catalog URL Rewrites index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1206 The total number of locks exceeds the lock table size' in /srv/www/htdocs/MascagniB2B/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /srv/www/htdocs/MascagniB2B/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /srv/www/htdocs/MascagniB2B/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /srv/www/htdocs/MascagniB2B/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /srv/www/htdocs/MascagniB2B/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /srv/www/htdocs/MascagniB2B/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `core_ur...', Array)
#5 /srv/www/htdocs/MascagniB2B/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `core_ur...', Array)
#6 /srv/www/htdocs/MascagniB2B/lib/Zend/Db/Adapter/Abstract.php(632): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `core_ur...', Array)
#7 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Resource/Url.php(317): Zend_Db_Adapter_Abstract->update('core_url_rewrit...', Array, Array)
#8 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Url.php(366): Mage_Catalog_Model_Resource_Url->saveRewrite(Array, Object(Varien_Object))
#9 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Url.php(568): Mage_Catalog_Model_Url->_refreshProductRewrite(Object(Varien_Object), Object(Varien_Object))
#10 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Url.php(255): Mage_Catalog_Model_Url->refreshProductRewrites('4')
#11 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Url.php(248): Mage_Catalog_Model_Url->refreshRewrites('4')
#12 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Indexer/Url.php(257): Mage_Catalog_Model_Url->refreshRewrites()
#13 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Index/Model/Process.php(210): Mage_Catalog_Model_Indexer_Url->reindexAll()
#14 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#15 /srv/www/htdocs/MascagniB2B/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()
#16 /srv/www/htdocs/MascagniB2B/shell/indexer.php(212): Mage_Shell_Compiler->run()
#17 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 1206 The total number of locks exceeds the lock table size' in /srv/www/htdocs/MascagniB2B/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /srv/www/htdocs/MascagniB2B/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /srv/www/htdocs/MascagniB2B/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /srv/www/htdocs/MascagniB2B/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /srv/www/htdocs/MascagniB2B/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `core_ur...', Array)
#4 /srv/www/htdocs/MascagniB2B/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `core_ur...', Array)
#5 /srv/www/htdocs/MascagniB2B/lib/Zend/Db/Adapter/Abstract.php(632): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `core_ur...', Array)
#6 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Resource/Url.php(317): Zend_Db_Adapter_Abstract->update('core_url_rewrit...', Array, Array)
#7 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Url.php(366): Mage_Catalog_Model_Resource_Url->saveRewrite(Array, Object(Varien_Object))
#8 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Url.php(568): Mage_Catalog_Model_Url->_refreshProductRewrite(Object(Varien_Object), Object(Varien_Object))
#9 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Url.php(255): Mage_Catalog_Model_Url->refreshProductRewrites('4')
#10 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Url.php(248): Mage_Catalog_Model_Url->refreshRewrites('4')
#11 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Catalog/Model/Indexer/Url.php(257): Mage_Catalog_Model_Url->refreshRewrites()
#12 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Index/Model/Process.php(210): Mage_Catalog_Model_Indexer_Url->reindexAll()
#13 /srv/www/htdocs/MascagniB2B/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#14 /srv/www/htdocs/MascagniB2B/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()
#15 /srv/www/htdocs/MascagniB2B/shell/indexer.php(212): Mage_Shell_Compiler->run()
#16 {main}

What should I do to solve this "The total number of locks exceeds the lock table size" problem?

The problem came out after I changed my import script adding:

$processes = Mage::getSingleton('index/indexer')->getProcessesCollection();
$processes->walk('setMode', array(Mage_Index_Model_Process::MODE_MANUAL));
$processes->walk('save');

...import...

$processes->walk('reindexAll');
$processes->walk('setMode', array(Mage_Index_Model_Process::MODE_REAL_TIME));
$processes->walk('save');

score 3 · Accepted Answer · answered Oct 14 '14 at 08:41

Upon using the Google I came accross this post which seems to provide a solution by increasing the innodb_buffer_pool_size in your servers my.cnf.

If you don't have access to that file it might help to just truncate the core_url_rewrite tabel since it can grow pretty big. I've seen tables with close to 500.000 rows, locking all of these might cause an issue with InnoDB.

Note that truncating the table will delete all old and custom redirects tho and please try this on a dev environment first and make backups. Although the table should rebuild itself after reindexing there might be issues.

Increasing the innodb_buffer_pool_size to 50MB worked! Thank you so much! :) — Niko Zarzani, Oct 14 '14 at 10:02

score 0 · Answer 2 · edited Oct 28 '15 at 11:48

0

I truncated table core_url_rewrite and still got the same issue.

So, I accessed server via ssh with PuTTY and then opened my /etc/my.cnf file and increased the innodb_buffer_pool_size from 2 to 64MB.

Try this one it should work fine.

edited Oct 28 '15 at 11:48

7ochem

7,532
14
51
80

answered Oct 28 '15 at 11:21

user2215155

1

Problem Reindexing Catalog Url

2 Answers2