cannot generate sitemap

[JoshCber]

Hi,

I am seeing the "We can't generate the sitemap right now." in our Magento 2 admin panel when cliclking the generate button fomr the Marketing-> SEO and Search ->Sitemap option.

Can anyone tell em why this is happening and how I can solve this?

Thanks,

JoshCber

[OST Agency]

If you have enabled a sitemap through the Catalog configuration and one is not generating on the schedule - make sure you've also set one up in the Marketing panel!

Marketing > [SEO & Search] Site Map

You must have created a sitemap in that portion of the admin in order for a sitemap to be generated by the configuration. For More details, Check this:

https://www.fastcomet.com/tutorials/magento2/sitemap

[JoshCber]

Yes I have done that but still I see the same error message

[JoshCber]

We recently migrated to magento 2 from previous magento 1 store.

Trying to generate the sitemap through the magento backend console was not achievable due to the above error.

So we copied our existing sitemap from magento 1 and pasted in the new magento folder.

Will this be an acceptable solution and will google update the sitemap once we ask google to crawl them?

[JoshCber]

We found this error log :

When running the sitemap generation I am seeing the following error in your magento system log:

[2016-07-21 12:49:31] main.CRITICAL: Exception message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't2_name.value' in 'field list', query was: SELECT `e`.`entity_id`, `e`.`updated_at`, `url_rewrite`.`request_path` AS `url`, IFNULL(t2_name.value, t1_name.value) AS `name`, IFNULL(t2_thumbnail.value, t1_thumbnail.value) AS `thumbnail` FROM `catalog_product_entity` AS `e`  INNER JOIN `catalog_product_website` AS `w` ON e.entity_id = w.product_id  LEFT JOIN `url_rewrite` ON e.entity_id = url_rewrite.entity_id AND url_rewrite.is_autogenerated = 1 AND url_rewrite.store_id = '1' AND url_rewrite.entity_type = 'product'

 LEFT JOIN `catalog_product_entity_int` AS `t1_visibility` ON e.entity_id = t1_visibility.entity_id AND  t1_visibility.store_id = 0 AND t1_visibility.attribute_id = '102'

 LEFT JOIN `catalog_product_entity_int` AS `t2_visibility` ON t1_visibility.entity_id = t2_visibility.entity_id AND t1_visibility.attribute_id = t2_visibility.attribute_id AND t2_visibility.store_id = '1'

 LEFT JOIN `catalog_product_entity_int` AS `t1_status` ON e.entity_id = t1_status.entity_id AND  t1_status.store_id = 0 AND t1_status.attribute_id = '96'

 LEFT JOIN `catalog_product_entity_int` AS `t2_status` ON t1_status.entity_id = t2_status.entity_id AND t1_status.attribute_id = t2_status.attribute_id AND t2_status.store_id = '1'

 LEFT JOIN `catalog_product_entity_varchar` AS `t1_name` ON e.entity_id = t1_name.entity_id AND  t1_name.store_id = 0 AND t1_name.attribute_id = '71'

 LEFT JOIN `catalog_product_entity_varchar` AS `t1_thumbnail` ON e.entity_id = t1_thumbnail.entity_id AND  t1_thumbnail.store_id = 0 AND t1_thumbnail.attribute_id = '87'

 LEFT JOIN `catalog_product_entity_varchar` AS `t2_thumbnail` ON t1_thumbnail.entity_id = t2_thumbnail.entity_id AND t1_thumbnail.attribute_id = t2_thumbnail.attribute_id AND t2_thumbnail.store_id = '1' WHERE (w.website_id = '1') AND ((IF(t2_visibility.value_id > 0, t2_visibility.value, t1_visibility.value)) IN(3, 2, 4)) AND ((IF(t2_status.value_id > 0, t2_status.value, t1_status.value)) IN(1))

Trace: #0 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)

#1 /chroot/home/muezartc/muezart.com/html/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)

#2 /chroot/home/muezartc/muezart.com/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)

#3 /chroot/home/muezartc/muezart.com/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `e`.`ent...', Array)

#4 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(444): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.`ent...', Array)

#5 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(499): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT `e`.`ent...', Array)

#6 /chroot/home/muezartc/muezart.com/html/vendor/magento/module-sitemap/Model/ResourceModel/Catalog/Product.php(309): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select))

#7 /chroot/home/muezartc/muezart.com/html/vendor/magento/module-sitemap/Model/Sitemap.php(245): Magento\Sitemap\Model\ResourceModel\Catalog\Product->getCollection('1')

#8 /chroot/home/muezartc/muezart.com/html/vendor/magento/module-sitemap/Model/Sitemap.php(338): Magento\Sitemap\Model\Sitemap->_initSitemapItems()

#9 /chroot/home/muezartc/muezart.com/html/vendor/magento/module-sitemap/Controller/Adminhtml/Sitemap/Generate.php(27): Magento\Sitemap\Model\Sitemap->generateXml()

#10 /chroot/home/muezartc/muezart.com/html/var/generation/Magento/Sitemap/Controller/Adminhtml/Sitemap/Generate/Interceptor.php(25): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate->execute()

#11 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/App/Action/Action.php(102): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor->execute()

#12 /chroot/home/muezartc/muezart.com/html/vendor/magento/module-backend/App/AbstractAction.php(226): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))

#13 [internal function]: Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))

#14 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)

#15 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor->___callParent('dispatch', Array)

#16 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Sitemap...', 'dispatch', Object(Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor), Array, 'adminAuthentica...')

#17 /chroot/home/muezartc/muezart.com/html/vendor/magento/module-backend/App/Action/Plugin/Authentication.php(143): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))

#18 [internal function]: Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))

#19 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)

#20 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Sitemap...', 'dispatch', Object(Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor), Array, 'designLoader')

#21 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/App/Action/Plugin/Design.php(39): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))

#22 [internal function]: Magento\Framework\App\Action\Plugin\Design->aroundDispatch(Object(Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))

#23 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)

#24 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Sitemap...', 'dispatch', Object(Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor), Array, 'adminMassaction...')

#25 /chroot/home/muezartc/muezart.com/html/vendor/magento/module-backend/App/Action/Plugin/MassactionKey.php(33): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))

#26 [internal function]: Magento\Backend\App\Action\Plugin\MassactionKey->aroundDispatch(Object(Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))

#27 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Interceptor.php(140): call_user_func_array(Array, Array)

#28 /chroot/home/muezartc/muezart.com/html/var/generation/Magento/Sitemap/Controller/Adminhtml/Sitemap/Generate/Interceptor.php(40): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor->___callPlugins('dispatch', Array, Array)

#29 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/App/FrontController.php(54): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))

#30 [internal function]: Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))

#31 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)

#32 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)

#33 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'install')

#34 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Module/Plugin/DbStatusValidator.php(69): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))

#35 [internal function]: Magento\Framework\Module\Plugin\DbStatusValidator->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))

#36 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/Interception/Interceptor.php(140): call_user_func_array(Array, Array)

#37 /chroot/home/muezartc/muezart.com/html/var/generation/Magento/Framework/App/FrontController/Interceptor.php(26): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)

#38 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/App/Http.php(115): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))

#39 /chroot/home/muezartc/muezart.com/html/vendor/magento/framework/App/Bootstrap.php(258): Magento\Framework\App\Http->launch()

#40 /chroot/home/muezartc/muezart.com/html/index.php(39): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))

#41 {main} [] []

It appears that there may be an issue with the sitemap gnerator in magento 2 or with a table in the database that is being referenced. Can someone investigate whether the issue is with the database table that is being called or if this is an error or misconfiguration related to the code or extensions of the magento 2 site.

[jarhody]

I have the same SQL error...did you ever find a solution?

[makanaashish]

did you ever find out what was causing this issue as i am getting the same error right now

[Benoit Xylo]

Hi guys,

what Magento version are you using?

Cheers,

BX

[jarhody]

I am using 2.1.3. Migrated from 1.9.2.4

[elvinr]

Related github issue:

- https://github.com/magento/magento2/issues/8671

You can also try to disable "Add Images into Sitemap" under 

- Stores > Configuration > Catalog > XML Sitemap >> Product Options