Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Generating sitemap while setting option "Add Images into Sitemap" to yes #8671

Closed
zkliouel opened this issue Feb 24, 2017 · 9 comments

Comments

@zkliouel
Copy link

commented Feb 24, 2017

Preconditions

  1. Magento 2.1.4
  2. On back-office Store > Configuration > Catalog > Xml Sitemap set option "Add Images into Sitemap" to yes

Steps to reproduce

  1. Create a new sitemap
  2. Generate Sitemap

Expected result

  1. Sitemap generated

Actual result

Error when generating site :
ERROR 1054 (42S22): Unknown column 't2_name.value' in 'field list'
SQL
SELECT
e.entity_id,
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.metadata IS NULL 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 = '99'
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 = '97'
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 = '73'
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 = '89'
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))

@aabumuslimov

This comment has been minimized.

Copy link

commented Apr 7, 2017

Duplicate of #5941.
Internal ticket MAGETWO-56482.

@magento-engcom-team

This comment has been minimized.

Copy link
Contributor

commented Sep 29, 2017

@zkliouel, thank you for your report.
We were not able to reproduce this issue by following the steps you provided. If you'd like to update it, please reopen the issue.

@ehsankhakbaz

This comment has been minimized.

Copy link

commented Oct 25, 2017

This issue is still there.

@jeremib

This comment has been minimized.

Copy link

commented Oct 30, 2017

Confirmed issue still exists.

@erlisdhima

This comment has been minimized.

Copy link

commented Oct 31, 2017

The same for me. I get the following error:

[2017-10-31 14:47:30] 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`.`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.metadata IS NULL 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 = '99'
 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 = '97'
 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 = '73'
 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 = '89'
 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 (t1_status.value IN(1))

Trace: #0 /vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#1 /vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `e`.`ent...', Array)
#4 /vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(465): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.`ent...', Array)
#5 /vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(520): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT `e`.`ent...', Array)
#6 /vendor/magento/module-sitemap/Model/ResourceModel/Catalog/Product.php(289): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select))
#7 /vendor/magento/module-sitemap/Model/Sitemap.php(245): Magento\Sitemap\Model\ResourceModel\Catalog\Product->getCollection('1')
#8 /vendor/magento/module-sitemap/Model/Sitemap.php(338): Magento\Sitemap\Model\Sitemap->_initSitemapItems()
#9 /vendor/magento/module-sitemap/Controller/Adminhtml/Sitemap/Generate.php(27): Magento\Sitemap\Model\Sitemap->generateXml()
#10 /var/generation/Magento/Sitemap/Controller/Adminhtml/Sitemap/Generate/Interceptor.php(24): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate->execute()
#11 /vendor/magento/framework/App/Action/Action.php(102): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor->execute()
#12 /vendor/magento/module-backend/App/AbstractAction.php(226): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#13 /vendor/magento/framework/Interception/Interceptor.php(74): Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))
#14 /vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor->___callParent('dispatch', Array)
#15 /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...')
#16 /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))
#17 /vendor/magento/framework/Interception/Chain/Chain.php(67): Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#18 /vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Sitemap...', 'dispatch', Object(Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor), Array, 'adminMassaction...')
#19 /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))
#20 /vendor/magento/framework/Interception/Interceptor.php(142): Magento\Backend\App\Action\Plugin\MassactionKey->aroundDispatch(Object(Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#21 /var/generation/Magento/Sitemap/Controller/Adminhtml/Sitemap/Generate/Interceptor.php(39): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor->___callPlugins('dispatch', Array, Array)
#22 /vendor/magento/framework/App/FrontController.php(55): Magento\Sitemap\Controller\Adminhtml\Sitemap\Generate\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#23 /vendor/magento/framework/Interception/Interceptor.php(74): Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#24 /vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#25 /vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'install')
#26 /vendor/magento/framework/Module/Plugin/DbStatusValidator.php(69): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#27 /vendor/magento/framework/Interception/Chain/Chain.php(67): Magento\Framework\Module\Plugin\DbStatusValidator->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#28 /vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'infortis_cgen_m...')
#29 /app/code/Infortis/Cgen/Plugin/Magento/Framework/App/FrontController.php(32): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#30 /vendor/magento/framework/Interception/Interceptor.php(142): Infortis\Cgen\Plugin\Magento\Framework\App\FrontController->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#31 /var/generation/Magento/Framework/App/FrontController/Interceptor.php(26): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)
#32 /vendor/magento/framework/App/Http.php(135): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#33 /vendor/magento/framework/App/Bootstrap.php(258): Magento\Framework\App\Http->launch()
#34 /index.php(39): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))
#35 {main} [] []

I had to set to None Store > Configuration > Catalog > Xml Sitemap > Products Options > Add Images into Sitemap

@lmancuso

This comment has been minimized.

Copy link

commented Nov 3, 2017

The issue still exists!

@lmancuso

This comment has been minimized.

Copy link

commented Nov 3, 2017

Guys, i can generate sitemap.xml with images.

Step 1: Stores > configuration > XML Sitemap > Product Options > Set "Add Images into Sitemap" NONE.

Step 2: Marketing > Sitemap >Generate

Step 3: The sitemap is generated.

Step 4: Step 1 but set atribute in "ALL"

Step 5: Step 2

Works for me, the images appears in Facebook for example

Regards

@cornips

This comment has been minimized.

Copy link

commented Dec 4, 2017

Same issue here. Can't generate sitemaps with option "Add Images into Sitemap". Magento 2.1.9.

main.CRITICAL: Exception message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't2_thumbnail.value' in 'field list', query was: SELECT `e`.`entity_id`, `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.metadata IS NULL AND url_rewrite.store_id = '2' 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 = '99'
 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 = '2'
 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 = '97'
 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 = '2'
 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 = '73'
 LEFT JOIN `catalog_product_entity_varchar` AS `t2_name` ON t1_name.entity_id = t2_name.entity_id AND t1_name.attribute_id = t2_name.attribute_id AND t2_name.store_id = '2'
 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 = '89' 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 /data/web/magento2/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#1 /data/web/magento2/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /data/web/magento2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /data/web/magento2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `e`.`ent...', Array)
#4 /data/web/magento2/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(465): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.`ent...', Array)
#5 /data/web/magento2/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(520): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT `e`.`ent...', Array)
#6 /data/web/magento2/vendor/magento/module-sitemap/Model/ResourceModel/Catalog/Product.php(289): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select))
@mbrzuzy

This comment has been minimized.

Copy link

commented Mar 22, 2018

This still happens in v2.1.11.

One piece of missing information here is that the product name attribute has to be global. If it's global, the table t2_name isn't joined.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
9 participants
You can’t perform that action at this time.