search

magento-hackathon / module-eavcleaner-m2

Clean up your Magento database by removing orphaned, unused and wrongly added attribute, attribute values and settings (for M2).
MIT License
67 stars 21 forks source link

`eav:attributes:remove-unused` removes attributes used by configurable products #23

Open lumnn opened 1 year ago

lumnn commented 1 year ago

I've run bin/magento eav:attributes:remove-unused.

It appears that it removed some attributes that were used as configurable attributes (in catalog_product_super_attribute table I believe), which then caused that product pages was displaying error as well as accessing the product from admin was impossible.

The error message then said:

Next Zend_Db_Statement_Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity_value.value' in 'field list', query was: SELECT `entity`.`sku`, `product_entity`.`entity_id` AS `product_id`, `attribute`.`attribute_code`, `entity_value`.`value` AS `value_index`, `attribute_label`.`value` AS `super_attribute_label`, IFNULL(option_value.value, default_option_value.value) AS `option_title`, `default_option_value`.`value` AS `default_title` FROM `catalog_product_super_attribute` AS `super_attribute`
 INNER JOIN `catalog_product_entity` AS `product_entity` ON product_entity.entity_id = super_attribute.product_id
 INNER JOIN `catalog_product_super_link` AS `product_link` ON product_link.parent_id = super_attribute.product_id
 INNER JOIN `eav_attribute` AS `attribute` ON attribute.attribute_id = super_attribute.attribute_id
 INNER JOIN `catalog_product_entity` AS `entity` ON entity.entity_id = product_link.product_id
 INNER JOIN `catalog_product_entity` AS `entity_value` ON entity_value.attribute_id = super_attribute.attribute_id AND entity_value.store_id = 0 AND entity_value.entity_id = entity.entity_id
 LEFT JOIN `catalog_product_super_attribute_label` AS `attribute_label` ON super_attribute.product_super_attribute_id = attribute_label.product_super_attribute_id AND attribute_label.store_id = 0
 LEFT JOIN `eav_attribute_option` AS `attribute_option` ON attribute_option.option_id = entity_value.value
 LEFT JOIN `eav_attribute_option_value` AS `option_value` ON option_value.option_id = entity_value.value AND option_value.store_id = 1
 LEFT JOIN `eav_attribute_option_value` AS `default_option_value` ON default_option_value.option_id = entity_value.value AND default_option_value.store_id = 0 WHERE (super_attribute.product_id = 56814) AND (attribute.attribute_id = '') ORDER BY `attribute_option`.`sort_order` ASC
 in /var/www/html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:109
Stack trace:
#0 /var/www/html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(91): Magento\Framework\DB\Statement\Pdo\Mysql->tryExecute(Object(Closure))
#1 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement.php(313): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `entity`...', Array)
#4 /var/www/html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(564): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `entity`...', Array)
#5 /var/www/html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(634): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT `entity`...', Array)
#6 /var/www/html/generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php(95): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select), Array)
#7 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(737): Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor->query(Object(Magento\Framework\DB\Select), Array)
#8 /var/www/html/generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php(1085): Zend_Db_Adapter_Abstract->fetchAll(Object(Magento\Framework\DB\Select), Array, 2)
#9 /var/www/html/vendor/magento/module-configurable-product/Model/AttributeOptionProvider.php(57): Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor->fetchAll(Object(Magento\Framework\DB\Select))
#10 /var/www/html/generated/code/Magento/ConfigurableProduct/Model/AttributeOptionProvider/Proxy.php(95): Magento\ConfigurableProduct\Model\AttributeOptionProvider->getAttributeOptions(Object(Magento\Catalog\Model\ResourceModel\Eav\Attribute\Interceptor), '56814')
#11 /var/www/html/vendor/magento/module-configurable-product/Model/ResourceModel/Product/Type/Configurable.php(239): Magento\ConfigurableProduct\Model\AttributeOptionProvider\Proxy->getAttributeOptions(Object(Magento\Catalog\Model\ResourceModel\Eav\Attribute\Interceptor), '56814')
#12 /var/www/html/vendor/magento/module-configurable-product/Model/ResourceModel/Product/Type/Configurable/Attribute/Collection.php(324): Magento\ConfigurableProduct\Model\ResourceModel\Product\Type\Configurable->getAttributeOptions(Object(Magento\Catalog\Model\ResourceModel\Eav\Attribute\Interceptor), '56814')
#13 /var/www/html/vendor/magento/module-configurable-product/Model/ResourceModel/Product/Type/Configurable/Attribute/Collection.php(205): Magento\ConfigurableProduct\Model\ResourceModel\Product\Type\Configurable\Attribute\Collection->loadOptions()
#14 /var/www/html/vendor/magento/framework/Data/Collection/AbstractDb.php(594): Magento\ConfigurableProduct\Model\ResourceModel\Product\Type\Configurable\Attribute\Collection->_afterLoad()
#15 /var/www/html/generated/code/Magento/ConfigurableProduct/Model/ResourceModel/Product/Type/Configurable/Attribute/Collection/Interceptor.php(338): Magento\Framework\Data\Collection\AbstractDb->loadWithFilter(false, false)
#16 /var/www/html/vendor/magento/framework/Data/Collection/AbstractDb.php(565): Magento\ConfigurableProduct\Model\ResourceModel\Product\Type\Configurable\Attribute\Collection\Interceptor->loadWithFilter(false, false)
#17 /var/www/html/generated/code/Magento/ConfigurableProduct/Model/ResourceModel/Product/Type/Configurable/Attribute/Collection/Interceptor.php(329): Magento\Framework\Data\Collection\AbstractDb->load(false, false)
#18 /var/www/html/vendor/magento/framework/Data/Collection.php(840): Magento\ConfigurableProduct\Model\ResourceModel\Product\Type\Configurable\Attribute\Collection\Interceptor->load()
#19 /var/www/html/generated/code/Magento/ConfigurableProduct/Model/ResourceModel/Product/Type/Configurable/Attribute/Collection/Interceptor.php(671): Magento\Framework\Data\Collection->getIterator()
#20 /var/www/html/vendor/magento/module-configurable-product/Helper/Product/Options/Loader.php(54): Magento\ConfigurableProduct\Model\ResourceModel\Product\Type\Configurable\Attribute\Collection\Interceptor->getIterator()
#21 /var/www/html/vendor/magento/module-configurable-product/Model/Product/ReadHandler.php(48): Magento\ConfigurableProduct\Helper\Product\Options\Loader->load(Object(Magento\Catalog\Model\Product\Interceptor))
#22 /var/www/html/vendor/magento/framework/EntityManager/Operation/Read/ReadExtensions.php(48): Magento\ConfigurableProduct\Model\Product\ReadHandler->execute(Object(Magento\Catalog\Model\Product\Interceptor), Array)
#23 /var/www/html/vendor/magento/framework/EntityManager/Operation/Read.php(112): Magento\Framework\EntityManager\Operation\Read\ReadExtensions->execute(Object(Magento\Catalog\Model\Product\Interceptor), Array)
#24 /var/www/html/vendor/magento/framework/EntityManager/EntityManager.php(80): Magento\Framework\EntityManager\Operation\Read->execute(Object(Magento\Catalog\Model\Product\Interceptor), '56814', Array)
#25 /var/www/html/vendor/magento/module-catalog/Model/ResourceModel/Product.php(735): Magento\Framework\EntityManager\EntityManager->load(Object(Magento\Catalog\Model\Product\Interceptor), '56814')
#26 /var/www/html/generated/code/Magento/Catalog/Model/ResourceModel/Product/Interceptor.php(185): Magento\Catalog\Model\ResourceModel\Product->load(Object(Magento\Catalog\Model\Product\Interceptor), '56814', NULL)
#27 /var/www/html/vendor/magento/framework/Model/AbstractModel.php(543): Magento\Catalog\Model\ResourceModel\Product\Interceptor->load(Object(Magento\Catalog\Model\Product\Interceptor), '56814', NULL)
#28 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\Model\AbstractModel->load('56814')
#29 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Catalog\Model\Product\Interceptor->___callParent('load', Array)
#30 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Catalog\Model\Product\Interceptor->Magento\Framework\Interception\{closure}('56814')
#31 /var/www/html/generated/code/Magento/Catalog/Model/Product/Interceptor.php(1661): Magento\Catalog\Model\Product\Interceptor->___callPlugins('load', Array, Array)
#32 /var/www/html/vendor/mailchimp/mc-magento2/Ui/Component/Listing/Column/Products.php(70): Magento\Catalog\Model\Product\Interceptor->load('56814')
#33 /var/www/html/vendor/magento/framework/View/Element/UiComponent/Context.php(396): Ebizmarts\MailChimp\Ui\Component\Listing\Column\Products->prepareDataSource(Array)
#34 /var/www/html/vendor/magento/framework/View/Element/UiComponent/Context.php(393): Magento\Framework\View\Element\UiComponent\Context->prepareDataSource(Array, Object(Ebizmarts\MailChimp\Ui\Component\Listing\Column\Products))
#35 /var/www/html/vendor/magento/framework/View/Element/UiComponent/Context.php(393): Magento\Framework\View\Element\UiComponent\Context->prepareDataSource(Array, Object(Amasty\Pgrid\Ui\Component\Listing\Columns))
#36 /var/www/html/vendor/magento/framework/View/Element/UiComponent/Context.php(251): Magento\Framework\View\Element\UiComponent\Context->prepareDataSource(Array, Object(Magento\Ui\Component\Listing))
#37 /var/www/html/vendor/magento/framework/View/Element/UiComponent/ContentType/Json.php(69): Magento\Framework\View\Element\UiComponent\Context->getDataSourceData(Object(Magento\Ui\Component\Listing))
#38 /var/www/html/vendor/magento/module-ui/Component/AbstractComponent.php(158): Magento\Framework\View\Element\UiComponent\ContentType\Json->render(Object(Magento\Ui\Component\Listing), 'templates/listi...')
#39 /var/www/html/vendor/magento/module-ui/Controller/Adminhtml/Index/Render.php(91): Magento\Ui\Component\AbstractComponent->render()
#40 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Ui\Controller\Adminhtml\Index\Render->execute()
#41 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callParent('execute', Array)
#42 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->Magento\Framework\Interception\{closure}()
#43 /var/www/html/generated/code/Magento/Ui/Controller/Adminhtml/Index/Render/Interceptor.php(23): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callPlugins('execute', Array, Array)
#44 /var/www/html/vendor/magento/framework/App/Action/Action.php(111): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->execute()
#45 /var/www/html/vendor/magento/module-backend/App/AbstractAction.php(151): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#46 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))
#47 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callParent('dispatch', Array)
#48 /var/www/html/vendor/magento/module-backend/App/Action/Plugin/Authentication.php(145): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#49 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(135): Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#50 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#51 /var/www/html/generated/code/Magento/Ui/Controller/Adminhtml/Index/Render/Interceptor.php(41): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callPlugins('dispatch', Array, Array)
#52 /var/www/html/vendor/magento/framework/App/FrontController.php(245): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#53 /var/www/html/vendor/magento/framework/App/FrontController.php(212): Magento\Framework\App\FrontController->getActionResponse(Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor), Object(Magento\Framework\App\Request\Http))
#54 /var/www/html/vendor/magento/framework/App/FrontController.php(147): Magento\Framework\App\FrontController->processRequest(Object(Magento\Framework\App\Request\Http), Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor))
#55 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#56 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#57 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#58 /var/www/html/generated/code/Magento/Framework/App/FrontController/Interceptor.php(23): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)
#59 /var/www/html/vendor/magento/framework/App/Http.php(116): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#60 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\App\Http->launch()
#61 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\App\Http\Interceptor->___callParent('launch', Array)
#62 /var/www/html/vendor/justbetter/magento2-sentry/Plugin/GlobalExceptionCatcher.php(57): Magento\Framework\App\Http\Interceptor->Magento\Framework\Interception\{closure}()
#63 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(135): JustBetter\Sentry\Plugin\GlobalExceptionCatcher->aroundLaunch(Object(Magento\Framework\App\Http\Interceptor), Object(Closure))
#64 /var/www/html/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Framework\App\Http\Interceptor->Magento\Framework\Interception\{closure}()
#65 /var/www/html/generated/code/Magento/Framework/App/Http/Interceptor.php(23): Magento\Framework\App\Http\Interceptor->___callPlugins('launch', Array, NULL)
#66 /var/www/html/vendor/magento/framework/App/Bootstrap.php(264): Magento\Framework\App\Http\Interceptor->launch()
#67 /var/www/html/pub/index.php(30): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http\Interceptor))
#68 {main} [] []

The fix was to restore removed data from following tables:

fredden commented 1 year ago

Can you share some information about which attributes were removed / caused the problem?

sprankhub commented 1 year ago

Looking at the code in https://github.com/magento-hackathon/module-eavcleaner-m2/blob/447e799f627a37fadcee23e9f7b6783e46078785/Console/Command/RemoveUnusedAttributesCommand.php, we currently check for attributes, which either have no value assigned in any product or which are not assigned to any attribute set. If you say your attribute was used in a configurable product, then some (simple) product would have a value assigned for that attribute...?