cancel
Showing results for 
Search instead for 
Did you mean: 

SQL ERROR: SQLSTATE[42S22] - how to mitigate problem

Highlighted

SQL ERROR: SQLSTATE[42S22] - how to mitigate problem

I own a Magento CE 1.7.0.x based website where I keep getting variants of the following error (usually after the daily morning site reindex):

 

" SQL ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.data' in 'field list'

SQL QUERY: SELECT DISTINCT `main_table`.`data`, `main_table`.`lifetime`, `main_table`.`expire`, `main_table`.`priority`, `additional_table`.*, IFNULL(al.value, main_table.frontend_label) AS `store_label` FROM `eav_attribute` AS `main_table`

 INNER JOIN `catalog_eav_attribute` AS `additional_table` ON additional_table.attribute_id = main_table.attribute_id

 LEFT JOIN `eav_attribute_label` AS `al` ON al.attribute_id = main_table.attribute_id AND al.store_id = 1 WHERE (main_table.entity_type_id

 (see full error below)"

 

I have a website down detector set up so that I can know immediately when the site is unavailable. However it obviously doesn't detect whenever the site is inoperative due to errors such as “SQL ERROR: SQLSTATE[42S22]”. That means that my team must be regularly monitoring manually (!!!) if the site is indeed working appropriately (by clicking on product and category links)!

 

Usually we solve the error by just cleaning the cache or performing a new reindex.

 

My questions are the following:

 

1 - Is there a way of routinely perform a detection of this type of errors in Magento so that - if it occurs - a cache cleaning (or a site reindex) is immediately run and an alert is sent to the webmaster?

 

2 – If such an error is detected, is there a way of it not be shown to the person accessing the site? That is, if the error is detected, is it possible to immediately display a message (“We’ll be back soon”) while the cache is being cleaned or the site reindexed?

 

I will be grateful for any help you can provide.

Thank you!

 

Full error example:

SQL ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.data' in 'field list'SQL QUERY: SELECT DISTINCT `main_table`.`data`, `main_table`.`lifetime`, `main_table`.`expire`, `main_table`.`priority`, `additional_table`.*, IFNULL(al.value, main_table.frontend_label) AS `store_label` FROM `eav_attribute` AS `main_table` INNER JOIN `catalog_eav_attribute` AS `additional_table` ON additional_table.attribute_id = main_table.attribute_id LEFT JOIN `eav_attribute_label` AS `al` ON al.attribute_id = main_table.attribute_id AND al.store_id = 1 WHERE (main_table.entity_type_id = 4) AND (additional_table.is_filterable > 0)

Trace:#0 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)#1 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)#2 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)#3 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT DISTINCT...', Array)#4 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECT DISTINCT...', Array)#5 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query('SELECT DISTINCT...', Array)#6 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT DISTINCT...', Array)#7 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(521): Varien_Data_Collection_Db->_fetchAll('SELECT DISTINCT...', Array)#8 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Data/Collection/Db.php(566): Mage_Core_Model_Resource_Db_Collection_Abstract->getData()#9 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Data/Collection.php(741): Varien_Data_Collection_Db->load()#10 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Core/Helper/Data.php(286): Varien_Data_Collection->getIterator()#11 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Filters/Model/Filter2.php(36): Mana_Core_Helper_Data->collectionFind(Object(Mage_Catalog_Model_Resource_Product_Attribute_Collection), 'attribute_code', 'produto_configu...')#12 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Filters/Block/View.php(83): Mana_Filters_Model_Filter2->getAttribute()#13 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Core/Helper/Layout.php(31): Mana_Filters_Block_View->delayedPrepareLayout()#14 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Core/Model/Observer.php(96): Mana_Core_Helper_Layout->prepareDelayedLayoutBlocks()#15 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Model/App.php(1338): Mana_Core_Model_Observer->postProcessBlocks(Object(Varien_Event_Observer))#16 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Mana_Core_Model_Observer), 'postProcessBloc...', Object(Varien_Event_Observer))#17 /var/www/vhosts/DOMAIN.com/httpdocs/app/Mage.php(447): Mage_Core_Model_App->dispatchEvent('controller_acti...', Array)#18 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(351): Mage::dispatchEvent('controller_acti...', Array)#19 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Catalog/controllers/CategoryController.php(146): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()#20 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Catalog_CategoryController->viewAction()#21 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('view')#22 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))#23 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()#24 /var/www/vhosts/DOMAIN.com/httpdocs/app/Mage.php(683): Mage_Core_Model_App->run(Array)#25 /var/www/vhosts/DOMAIN.com/DOMAIN.pt/index.php(68): Mage::run('pt', 'store')#26 {main}

1 REPLY 1
Highlighted

Re: SQL ERROR: SQLSTATE[42S22] - how to mitigate problem

Hello @Maria 

 

Just for the suggestion, if you have good server resources, you can put a cron to refresh the cache every 15 mins or something. 

Found something useful, "Kudos" and "Accept as Solution" for Token of Appreciation.

Magento Tutorials