search

Vinai / groupscatalog2

Magento extension to enable you to hide categories and products from customers depending on their customer group. This is a Magento 1.6 and newer compatible version of the Netzarbeiter Customer Groups Catalog extension.
139 stars 60 forks source link

GroupsCatalog Categories and GroupsCatalog Products cannot rebuild the indexes #14

Closed diazwatson closed 11 years ago

diazwatson commented 12 years ago

Hi Vinai,

I have a problem with one of your extensions (groupscatalog2).

I cannot manage to reindex GroupsCatalog Categories and GroupsCatalog Products. Also, when activating the extension all menus and products dissapear

Do you have any idea where to start looking at?

I have a Magento CE 1.7.0.2

Not sure if it helps but..

Extension Installed are: AddThis_SharingTool Aoe_Scheduler ArtsOnIT_OfflineMaintenance Auguria_Sponsorship Belvg_All Belvg_FacebookFree Clockworkgeek_Rememberme Devinc_Multipledeals Ebizmarts_MageMonkey Ebizmarts_Mandrill Idev_OneStepCheckout Mage_Admin Mage_AdminNotification Mage_Api Mage_Api2 Mage_Authorizenet Mage_Backup Mage_Bundle Mage_Captcha Mage_Catalog Mage_CatalogIndex Mage_CatalogInventory Mage_CatalogRule Mage_CatalogSearch Mage_Centinel Mage_Checkout Mage_Cms Mage_Compiler Mage_Connect Mage_Contacts Mage_Core Mage_Cron Mage_CurrencySymbol Mage_Customer Mage_Dataflow Mage_Directory Mage_Downloadable Mage_Eav Mage_GiftMessage Mage_GoogleAnalytics Mage_GoogleCheckout Mage_ImportExport Mage_Index Mage_Install Mage_Log Mage_Media Mage_Newsletter Mage_Oauth Mage_Page Mage_PageCache Mage_Paygate Mage_Payment Mage_Paypal Mage_PaypalUk Mage_Persistent Mage_Poll Mage_ProductAlert Mage_Rating Mage_Reports Mage_Review Mage_Rss Mage_Rule Mage_Sales Mage_SalesRule Mage_Sendfriend Mage_Shipping Mage_Sitemap Mage_Tag Mage_Tax Mage_Usa Mage_Weee Mage_Widget Mage_Wishlist Mage_XmlConnect Netzarbeiter_GroupsCatalog2 Netzarbeiter_LoginCatalog OrganicInternet_SimpleConfigurableProducts Phoenix_Moneybookers TBT_Enhancedgrid

When attempting to rebuild the indexes, the stack-traces in the file var/log/exception.log related to this are:

2012-10-05T02:02:53+00:00 DEBUG (7): Exception message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.value' in 'field list' Trace: 0 /home/sites/domainname.com/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) 1 /home/sites/domainname.com/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array) 2 /home/sites/domainname.com/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) 3 /home/sites/domainname.com/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `e`.`ent...', Array) 4 /home/sites/domainname.com/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.`ent...', Array) 5 /home/sites/domainname.com/public_html/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array) 6 /home/sites/domainname.com/public_html/app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Indexer/Abstract.php(172): Zend_Db_Adapter_Abstract->fetchAll(Object(Varien_Db_Select)) 7 /home/sites/domainname.com/public_html/app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Indexer/Abstract.php(136): Netzarbeiter_GroupsCatalog2_Model_Resource_Indexer_Abstract->_reindexEntity() 8 /home/sites/domainname.com/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Netzarbeiter_GroupsCatalog2_Model_Resource_Indexer_Abstract->reindexAll() 9 /home/sites/domainname.com/public_html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll() 10 /home/sites/domainname.com/public_html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll() 11 /home/sites/domainname.com/public_html/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything() 12 /home/sites/domainname.com/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Index_Adminhtml_ProcessController->reindexProcessAction() 13 /home/sites/domainname.com/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess') 14 /home/sites/domainname.com/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http)) 15 /home/sites/domainname.com/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch() 16 /home/sites/domainname.com/public_html/app/Mage.php(683): Mage_Core_Model_App->run(Array) 17 /home/sites/domainname.com/public_html/index.php(87): Mage::run('', 'store') 18 {main}

2012-10-05T02:02:59+00:00 DEBUG (7): Exception message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.value' in 'field list' Trace: 0 /home/sites/domainname.com/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) 1 /home/sites/domainname.com/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array) 2 /home/sites/domainname.com/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) 3 /home/sites/domainname.com/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `e`.`ent...', Array) 4 /home/sites/domainname.com/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.`ent...', Array) 5 /home/sites/domainname.com/public_html/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array) 6 /home/sites/domainname.com/public_html/app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Indexer/Abstract.php(172): Zend_Db_Adapter_Abstract->fetchAll(Object(Varien_Db_Select)) 7 /home/sites/domainname.com/public_html/app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Indexer/Abstract.php(136): Netzarbeiter_GroupsCatalog2_Model_Resource_Indexer_Abstract->_reindexEntity() 8 /home/sites/domainname.com/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Netzarbeiter_GroupsCatalog2_Model_Resource_Indexer_Abstract->reindexAll() 9 /home/sites/domainname.com/public_html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll() 10 /home/sites/domainname.com/public_html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll() 11 /home/sites/domainname.com/public_html/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything() 12 /home/sites/domainname.com/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Index_Adminhtml_ProcessController->reindexProcessAction() 13 /home/sites/domainname.com/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess') 14 /home/sites/domainname.com/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http)) 15 /home/sites/domainname.com/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch() 16 /home/sites/domainname.com/public_html/app/Mage.php(683): Mage_Core_Model_App->run(Array) 17 /home/sites/domainname.com/public_html/index.php(87): Mage::run('', 'store') 18 {main}

Vinai commented 12 years ago

Thanks for your report. I'm unable to reproduce the exception. Just to test, could you please deactivate the OrganicInternet_SimpleConfigurableProducts module in app/etc/modules/OrganicInternet_SimpleConfigurableProducts.xml, flush the configuration cache, and then try to rebuild the indexes?

diazwatson commented 12 years ago

Done but does not seems to work

2012-10-18 00:17:21 EXCEPTION exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.value' in 'field list'' in /home/sites/domainname.es/public_html/lib/Zend/Db/Statement/Pdo.php:228 Stack trace: 0 /home/sites/domainname.es/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array) 1 /home/sites/domainname.es/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) 2 /home/sites/domainname.es/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array) 3 /home/sites/domainname.es/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) 4 /home/sites/domainname.es/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT e.ent...', Array) 5 /home/sites/domainname.es/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECTe.ent...', Array) 6 /home/sites/domainname.es/public_html/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array) 7 /home/sites/domainname.es/public_html/app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Indexer/Abstract.php(172): Zend_Db_Adapter_Abstract->fetchAll(Object(Varien_Db_Select)) 8 /home/sites/domainname.es/public_html/app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Indexer/Abstract.php(136): Netzarbeiter_GroupsCatalog2_Model_Resource_Indexer_Abstract->_reindexEntity() 9 /home/sites/domainname.es/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Netzarbeiter_GroupsCatalog2_Model_Resource_Indexer_Abstract->reindexAll() 10 /home/sites/domainname.es/public_html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll() 11 /home/sites/domainname.es/public_html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll() 12 /home/sites/domainname.es/public_html/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(178): Mage_Index_Model_Process->reindexEverything() 13 /home/sites/domainname.es/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Index_Adminhtml_ProcessController->massReindexAction() 14 /home/sites/domainname.es/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('massReindex') 15 /home/sites/domainname.es/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http)) 16 /home/sites/domainname.es/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch() 17 /home/sites/domainname.es/public_html/app/Mage.php(683): Mage_Core_Model_App->run(Array) 18 /home/sites/domainname.es/public_html/index.php(87): Mage::run('', 'store') 19 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.value' in 'field list'' in /home/sites/domainname.es/public_html/lib/Zend/Db/Statement/Pdo.php:234 Stack trace: 0 /home/sites/domainname.es/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) 1 /home/sites/domainname.es/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array) 2 /home/sites/domainname.es/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) 3 /home/sites/domainname.es/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT e.ent...', Array) 4 /home/sites/domainname.es/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECTe.ent...', Array) 5 /home/sites/domainname.es/public_html/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array) 6 /home/sites/domainname.es/public_html/app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Indexer/Abstract.php(172): Zend_Db_Adapter_Abstract->fetchAll(Object(Varien_Db_Select)) 7 /home/sites/domainname.es/public_html/app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Indexer/Abstract.php(136): Netzarbeiter_GroupsCatalog2_Model_Resource_Indexer_Abstract->_reindexEntity() 8 /home/sites/domainname.es/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Netzarbeiter_GroupsCatalog2_Model_Resource_Indexer_Abstract->reindexAll() 9 /home/sites/domainname.es/public_html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll() 10 /home/sites/domainname.es/public_html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll() 11 /home/sites/domainname.es/public_html/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(178): Mage_Index_Model_Process->reindexEverything() 12 /home/sites/domainname.es/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Index_Adminhtml_ProcessController->massReindexAction() 13 /home/sites/domainname.es/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('massReindex') 14 /home/sites/domainname.es/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http)) 15 /home/sites/domainname.es/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch() 16 /home/sites/domainname.es/public_html/app/Mage.php(683): Mage_Core_Model_App->run(Array) 17 /home/sites/domainname.es/public_html/index.php(87): Mage::run('', 'store') 18 {main}

Vinai commented 12 years ago

Thanks for the info. Since I'm unable to reproduce and debug this myself, I would like to ask you to help me by doing the following. In the file /app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Indexer/Abstract.php, after line 151, please add the following debug code:

    Mage::log(array(
        'entity-code'             => $this->_getEntityTypeCode(),
        'entity-table'            => $entityType->getEntityTable(),
        'attribute-table'         => $attribute->getBackend()->getTable(),
        'attribute-backendtable'  => $attribute->getBackendTable()
    ), Zend_Log::DEBUG, 'groupscatalog-debug.log', true);

This code should go between the existing lines

$attribute = Mage::getSingleton('eav/config')->getAttribute(
    $this->_getEntityTypeCode(), Netzarbeiter_GroupsCatalog2_Helper_Data::HIDE_GROUPS_ATTRIBUTE
);
// DEBUG CODE HERE
$select = $this->_getReadAdapter()->select()

Then try to rebuild the groupscatalog2 indexes (once for catalog and once for products). After that, please open the file var/log/groupscatalog-debug.log and post the results here. Thank you for your help!

nrunde commented 12 years ago

I've got same problem for Magento 1.6.2.0. After implementing your debug code I get the following output

2012-10-18T09:14:20+00:00 DEBUG (7): Array
(
    [entity-code] => catalog_category
    [entity-table] => catalog/category
    [attribute-table] => catalog_category_entity_text
    [attribute-backendtable] => catalog_category_entity_text
)
nrunde commented 12 years ago

In our shop the OrganicInternet_SimpleConfigurableProducts module is not installed, I might have to say

Vinai commented 12 years ago

Thank you for your feedback, it is valuable. The strange thing is that the column a.value mentioned in the error message should be present in the table catalog_category_entity_text (which is joined using the table alias a).

I would like to ask you for an additional debug statement to further clarify what is happening: After line 171 (of the original file, almost at the end of the method) please add the line:

Mage::log(strval($select), Zend_Log::DEBUG, 'groupscatalog-debug.log', true);

right before the statement where the select is executed, like this:

Mage::log(strval($select), Zend_Log::DEBUG, 'groupscatalog-debug.log', true);
$result = $this->_getReadAdapter()->fetchAll($select);

Then please rebuild the groupscatalog2 indexes again and please post the output.

nrunde commented 12 years ago

Here's what I get

2012-10-18T09:42:24+00:00 DEBUG (7): Array
(
    [entity-code] => catalog_category
    [entity-table] => catalog/category
    [attribute-table] => catalog_category_entity_text
    [attribute-backendtable] => catalog_category_entity_text
)

2012-10-18T09:42:25+00:00 DEBUG (7): SELECT `e`.`entity_id`, `a`.`value` AS `group_ids`, `a`.`store_id` FROM `catalog_category_entity` AS `e`
 LEFT JOIN `catalog_category_entity_text` AS `a` ON e.entity_id=a.entity_id AND a.attribute_id = '164' ORDER BY `e`.`entity_id` ASC, `a`.`store_id` ASC
Vinai commented 12 years ago

Thank you @nrunde . Please confirm that the exception message you are getting also is 

Unknown column 'a.value' in 'field list'

just like the issue creator posted. If so, please also post the result of the SQL query

DESCRIBE `catalog_category_entity_text`;

Thank you.

nrunde commented 12 years ago

No, I get another Exception Message: 

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`gruessing_mage`.`groupscatalog_category_idx`, CONSTRAINT `FK_GROUPSCAT_CTGR_IDX_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID` FOREIGN KEY (`group_id`) REFERENCES `customer_group` (`customer_group_id`) O)
Vinai commented 12 years ago

@nrunde: Oh, then it is a different issue. In your case a category is referencing a customer group that has been deleted. Look for an non existant customer group ID >= 0 in the output of the query (ignore the records with -2 or -1)

SELECT `e`.`entity_id`, `a`.`value` AS `group_ids` FROM `catalog_category_entity` AS `e`

LEFT JOIN catalog_category_entity_text AS a ON e.entity_id=a.entity_id AND a.attribute_id = '164' ORDER BY e.entity_id ASC, a.store_id ASC

Vinai commented 12 years ago

@diazwatson Please add the debug code to your instance and post the output. Thanks!

Please refer to the comments https://github.com/Vinai/groupscatalog2/issues/14#issuecomment-9554489 and https://github.com/Vinai/groupscatalog2/issues/14#issuecomment-9558202 for the debug code I mean.

nrunde commented 12 years ago

Oh sorry, my fault. Thank's vinai your advice helped solving the problem

Vinai commented 12 years ago

@nrunde I just pushed a commit that should take care that the issue with invalid customer group id's during indexing. See https://github.com/Vinai/groupscatalog2/commit/6d04ecd40148fb877067a7935deef6df3725f789

diazwatson commented 12 years ago

Hi @Vinai, Sorry for delay.

The content of my groupscatalog-debug.log is this:

2012-10-19T01:13:08+00:00 DEBUG (7): Array ( [entity-code] => catalog_category [entity-table] => catalog/category [attribute-table] => catalog_category_entity [attribute-backendtable] => catalog_category_entity ) 2012-10-19T01:13:16+00:00 DEBUG (7): Array ( [entity-code] => catalog_product [entity-table] => catalog/product [attribute-table] => catalog_product_entity [attribute-backendtable] => catalog_product_entity )

diazwatson commented 12 years ago

My second output is this:

2012-10-19T01:21:28+00:00 DEBUG (7): Array ( [entity-code] => catalog_product [entity-table] => catalog/product [attribute-table] => catalog_product_entity [attribute-backendtable] => catalog_product_entity )

2012-10-19T01:21:28+00:00 DEBUG (7): SELECT e.entity_id, a.value AS group_ids, a.store_id FROM catalog_product_entity AS e LEFT JOIN catalog_product_entity AS a ON e.entity_id=a.entity_id AND a.attribute_id = '' ORDER BY e.entity_id ASC, a.store_id ASC

diazwatson commented 12 years ago

the result of previous query is:

Unknown column 'a.value' in 'field list'

Vinai commented 12 years ago

Thanks @diazwatson , now we are making progress! :) Please give me the output of the query

 SELECT * FROM eav_attribute WHERE attribute_code = 'groupscatalog2_groups';

I'll not be able to work on this over the weekend but am positive the issue can be resolved during the upcoming week.

diazwatson commented 12 years ago

Hi @Vinai, No problem at all.

The output of query is an "empty table"

Vinai commented 11 years ago

@diazwatson Thank you. The GroupsCatalog2 extension doesn't seem to be installed completely. My guess is that the extension was installed, removed (without reseting the installation status in the core_resource table), and then reinstalled again. Currently you probably also can't save any product or category specific GroupsCatalog2 settings. To confirm my suspicion, please post the result of the following SQL query:

SELECT * FROM core_resource WHERE code = 'netzarbeiter_groupscatalog2_setup';
diazwatson commented 11 years ago

The result as follow: code: netzarbeiter_groupscatalog2_setup version: 0.1.9 data_version: 0.1.9

Vinai commented 11 years ago

Thank you. Please execute the following SQL zu re-create the attribute definitions correctly.

    DELETE FROM core_resource WHERE code = 'netzarbeiter_groupscatalog2_setup';

Then refresh the configuration cache and try to rebuild the indexes again.

diazwatson commented 11 years ago

I can confirm indexes were rebuilt successfully after delete record in db.

Thanks @Vinai

Vinai commented 11 years ago

Thanks for reporting back. Another mystery solved.