Reindexing throws error.

[paales]

If I try to reindex using the gui, it gives an error that it can't reindex. If I try to reindex using the shell, it gives me the following:

$ php shell/indexer.php --reindex groupscatalog2_category
GroupsCatalog Categories index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `index_p...', Array)
#5 lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `index_p...', Array)
#6 lib/Zend/Db/Adapter/Abstract.php(632): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `index_p...', Array)
#7 app/code/core/Mage/Index/Model/Resource/Process.php(137): Zend_Db_Adapter_Abstract->update('index_process', Array, Array)
#8 app/code/core/Mage/Index/Model/Resource/Process.php(109): Mage_Index_Model_Resource_Process->_updateProcessData('21', Array)
#9 app/code/core/Mage/Index/Model/Process.php(219): Mage_Index_Model_Resource_Process->failProcess(Object(Mage_Index_Model_Process))
#10 app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#11 shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#12 shell/indexer.php(198): Mage_Shell_Compiler->run()
#13 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `index_p...', Array)
#4 lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `index_p...', Array)
#5 lib/Zend/Db/Adapter/Abstract.php(632): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `index_p...', Array)
#6 app/code/core/Mage/Index/Model/Resource/Process.php(137): Zend_Db_Adapter_Abstract->update('index_process', Array, Array)
#7 app/code/core/Mage/Index/Model/Resource/Process.php(109): Mage_Index_Model_Resource_Process->_updateProcessData('21', Array)
#8 app/code/core/Mage/Index/Model/Process.php(219): Mage_Index_Model_Resource_Process->failProcess(Object(Mage_Index_Model_Process))
#9 app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#10 shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#11 shell/indexer.php(198): Mage_Shell_Compiler->run()
#12 {main}

This is due to that the indexer tries to import 90000 indexes with one query.

In file:

app/code/community/Netzarbeiter/GroupsCatalog2/Model/Indexer/Abstract.php

Replace:

$this->_getWriteAdapter()->insertMultiple($this->_getIndexTable(), $data);

With:

$chunks = array_chunk($data,1000);
foreach($chunks as $chunk)
{
    $this->_getWriteAdapter()->insertMultiple($this->_getIndexTable(), $chunk);
}

And that worked for me.

[Vinai]

Thank you for the issue report and the patch! I'll definitely integrate that. Don't you think that the chunks could be a bit larger, maybe 5-10k records? I'll have to create a larger testing catalog and read up on the limit in the MySQL docs-

Thanks again!

[paales]

I haven't tested it with 5-10k records, works pretty fast right now.

[Vinai]

Thanks for your feedback - corrected in the latest commit.