Magento 1.9 Upgrade – Fix SQLSTATE[42S22] Column Not Found Error

ce-1.9.2.0magento-1.9

We have just updated from 1.90 to 1.92 and the following error is showing…

Any idea how to fix it?

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.status' in 'where clause',
query was:
SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_flat_1` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index`
ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id = '301' AND cat_index.is_parent=1
WHERE (e.status = 1)


Trace:
#0 public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT COUNT(DI...', Array)
#4 public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT COUNT(DI...', Array)
#5 public_html/lib/Zend/Db/Adapter/Abstract.php(828): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 public_html/lib/Varien/Data/Collection/Db.php(225): Zend_Db_Adapter_Abstract->fetchOne(Object(Varien_Db_Select), Array)
#7 public_html/app/design/frontend/novaworks/bearstore/template/page/html/home-content.phtml(104): Varien_Data_Collection_Db->getSize()
#8 public_html/app/code/core/Mage/Core/Block/Template.php(241): include('...')
#9 public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/novawo...')
#10 public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#11 public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#12 public_html/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#13 public_html/app/code/core/Mage/Core/Block/Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('home.content', true)
#14 public_html/app/design/frontend/novaworks/bearstore/template/page/html/header.phtml(117): Mage_Core_Block_Abstract->getChildHtml('home.content')
#15 public_html/app/code/core/Mage/Core/Block/Template.php(241): include('...')
#16 public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/novawo...')
#17 public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#18 public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#19 public_html/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#20 public_html/app/code/core/Mage/Core/Block/Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('header', true)
#21 public_html/app/design/frontend/novaworks/bearstore/template/page/1column.phtml(65): Mage_Core_Block_Abstract->getChildHtml('header')
#22 public_html/app/code/core/Mage/Core/Block/Template.php(241): include('...')
#23 public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/novawo...')
#24 public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#25 public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#26 public_html/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#27 public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#28 public_html/app/code/core/Mage/Cms/Helper/Page.php(137): Mage_Core_Controller_Varien_Action->renderLayout()
#29 public_html/app/code/core/Mage/Cms/Helper/Page.php(52): Mage_Cms_Helper_Page->_renderPage(Object(Mage_Cms_IndexController), 'shop')
#30 public_html/app/code/core/Mage/Cms/controllers/IndexController.php(45): Mage_Cms_Helper_Page->renderPage(Object(Mage_Cms_IndexController), 'shop')
#31 public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Cms_IndexController->indexAction()
#32 public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('index')
#33 public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#34 public_html/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#35 public_html/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#36 public_html/index.php(83): Mage::run('', 'store')
#37 {main}

Best Answer

It seems a re-indexing issue. Try the following Steps to Re-index:

Magento Admin Panel
System
Index Management
Select All
Submit (Re-index Data)

Below are steps to follow if you can't login to the Admin Console

...which is probably the case if you're getting that SQL Error when you access any page...

To do perform the following steps, you'll need to be able to ssh into your hosting provider's terminal console.

ssh into your webhost provider
navigate to the <magento_root>/shell folder

cd /path/to/magento/installation/folder/shell
Run the indexer to regenerate all indices (this may take a while...)

php -f indexer.php reindexall
If you have compilation enabled (and you probably shouldn't, but if you DO), then re-compile:

php -f compiler.php compile
Clear out all your Magento caches

php -f cleanCache.php clean all

After the above is all said and done then you should have a working installation. You will be able to login to your Admin Console and the error you mention above should go away.

Related Solutions

Magento 1.9 Installation – Fix Invalid Backend Model for Zitec: Site Branding

Look in the table eav_attribute for a record with backend_model = 'scena/catalog_attribute_backend_cimage' and delete the row.
Clear the cache when you are done.

[EDIT]
I took a look through the extension code.
To completely uninstall the extension you need to remove the tables zitec_branding_store and zitec_branding. and remove the extension from the core_resource table` with

DELETE FROM `core_resource` WHERE `code` = 'zitec_branding_setup';

The extension does not add any product or category attributes. My hunch is that you have an other extension that is responsible for this error.
I see that you have an extension called BL_CustomGrid that has an eval in it. Never use extensions that have eval in their php code. Even if they might work, they are very dangerous.

Magento – SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘core_store’ doesn’t exist

The table you are looking for is a simple table.

When your host restored your database, it sounds like it did not have the core_store table inside of it. Also, it might be that there is a table prefix that might or might not be at play here.

Try these first:

Check your database to ensure there is a core_store (or variant) table available.
Check to make sure your local.xml config file is pointing to the right database.
Ask your host to restore from another restore point.
Check to ensure your local.xml didn't have a table prefix recently added or removed.

If that does not work, you can always do raw SQL inside your database.

PLEASE NOTE: TEST THIS ON A NON PRODUCTION ENVIRONMENT BEFORE PRODUCTION!

Core Store Table:

CREATE TABLE `core_store` (
  `store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store Id',
  `code` varchar(32) DEFAULT NULL COMMENT 'Code',
  `website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
  `group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
  `name` varchar(255) NOT NULL COMMENT 'Store Name',
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Sort Order',
  `is_active` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Activity',
  PRIMARY KEY (`store_id`),
  UNIQUE KEY `UNQ_core_store_CODE` (`code`),
  KEY `IDX_core_store_WEBSITE_ID` (`website_id`),
  KEY `IDX_core_store_IS_ACTIVE_SORT_ORDER` (`is_active`,`sort_order`),
  KEY `IDX_core_store_GROUP_ID` (`group_id`),
  CONSTRAINT `FK_core_store_GROUP_ID_core_store_GROUP_GROUP_ID` FOREIGN KEY (`group_id`) REFERENCES `core_store_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_core_store_WEBSITE_ID_WBCORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `wbcore_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='Stores';

Insert default entries:

INSERT INTO `core_store` VALUES (0,'admin',0,0,'Admin',0,1),(1,'default',1,1,'Default Store View',0,1);

Best Answer

Below are steps to follow if you can't login to the Admin Console

Related Solutions

Magento 1.9 Installation – Fix Invalid Backend Model for Zitec: Site Branding

Magento – SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘core_store’ doesn’t exist

Related Topic