3

I guess if you meet the same error when trying to create the Google Sitemap from Magento 1.9.1.0 backend (Unable to generate the sitemap). Under is what I found from the Exception Log:

2015-05-09T09:52:02+00:00 DEBUG (7): Exception message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.entity_id' in 'on clause', query was: SELECT `e`.`entity_id`, `e`.`created_at`, `e`.`updated_at`, `ur`.`request_path` AS `url` FROM `tts_catalog_category_entity` AS `e`
 LEFT JOIN `tts_core_url_rewrite` AS `ur` ON e.entity_id=ur.category_id AND ur.store_id='1' AND ur.product_id IS NULL AND ur.is_system=1
 INNER JOIN `tts_catalog_category_entity_int` AS `t1_is_active` ON main_table.entity_id=t1_is_active.entity_id AND t1_is_active.store_id=0
 LEFT JOIN `tts_catalog_category_entity_int` AS `t2_is_active` ON t1_is_active.entity_id = t2_is_active.entity_id AND t1_is_active.attribute_id = t2_is_active.attribute_id AND t2_is_active.store_id = '1' WHERE (e.path LIKE '1/2/%') AND (t1_is_active.attribute_id='42') AND ((IF(t2_is_active.value_id > 0, t2_is_active.value, t1_is_active.value))=1)
Trace: #0 /home/blairsin/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/blairsin/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/blairsin/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/blairsin/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `e`.`ent...', Array)
#4 /home/blairsin/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.`ent...', Array)
#5 /home/blairsin/public_html/app/code/community/Activo/Xmlsitemap/Model/Sitemap/Resource/Catalog/Category.php(47): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select))
#6 /home/blairsin/public_html/app/code/community/Activo/Xmlsitemap/Model/Sitemap/Sitemap.php(28): Activo_Xmlsitemap_Model_Sitemap_Resource_Catalog_Category->getCollection('1')
#7 /home/blairsin/public_html/app/code/core/Mage/Adminhtml/controllers/SitemapController.php(255): Activo_Xmlsitemap_Model_Sitemap_Sitemap->generateXml()
#8 /home/blairsin/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_SitemapController->generateAction()
#9 /home/blairsin/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('generate')
#10 /home/blairsin/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))
#11 /home/blairsin/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#12 /home/blairsin/public_html/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#13 /home/blairsin/public_html/index.php(87): Mage::run('base', 'website')
#14 {main}

This should be an error happened when generating the MySQL sentence, a "main_table" table was inserted and it cannot be found in the database. I think this is a Magento core code error - please help me figure it out.

Thank you.

John Conde
  • 217,595
  • 99
  • 455
  • 496
Long Nguyen
  • 31
  • 4
  • In order to get a useful answer you will need to provide more details of what you are doing, though the error message seems to be pretty clear about why it's failing: You are missing an expected column. – David Hoelzer May 09 '15 at 10:56
  • I simply try to create the sitemap from Back-end (Catalog --> Google Sitemap --> Generate) then the error message appears "Unable to generate the sitemap". The site map (http://kidsland.vn/sitemap/sitemap.xml) surely is a problem. From the exception log above, it seems to me the table "main_table" does not exist, not the issue of missing expected column. Replace main_table with \`e\` then the SQL runs perfectly. From that I can tell "main_table" was wrongly inserted. – Long Nguyen May 09 '15 at 14:14

1 Answers1

0

You are using a module (Activo_Xmlsitemap) which is rewriting the model of sitemap based on the code of an older Magento version. I used to have the same issue after upgrading Magento 1.7 to Magento 1.9

The override needs to be adjusted by changing the table alias of catalog_category_entity from "e" to "main_table"

dr_hanns
  • 91
  • 1
  • 4