问

Magento 1.8重新注入所有

 袁沮战_246 发布于 2023-02-06 17:36
  • process
  • io
  • pdo
  • sql
  • int
  • const

    • 几个星期后,我们的reindex将不再起作用并因以下错误而崩溃:

    Product Attributes index process unknown error:
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`hcomputers_mag`.`catalog_product_index_eav`, CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE)' in /var/www/hcomputers/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/hcomputers/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/hcomputers/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/hcomputers/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/hcomputers/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /var/www/hcomputers/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#5 /var/www/hcomputers/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#6 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#7 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(48): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_product...', Array, false)
#8 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(153): Mage_Index_Model_Resource_Helper_Mysql4->insertData(Object(Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source), Object(Varien_Db_Select), 'catalog_product...', Array, false)
#9 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_product...', 'catalog_product...', false)
#10 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): Mage_Index_Model_Resource_Abstract->syncData()
#11 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll()
#12 /var/www/hcomputers/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll()
#13 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll()
#14 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#15 /var/www/hcomputers/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()
#16 /var/www/hcomputers/shell/indexer.php(212): Mage_Shell_Compiler->run()
#17 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`hcomputers_mag`.`catalog_product_index_eav`, CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE)' in /var/www/hcomputers/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /var/www/hcomputers/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/hcomputers/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /var/www/hcomputers/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /var/www/hcomputers/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#4 /var/www/hcomputers/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#5 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(179): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#6 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(48): Mage_Index_Model_Resource_Abstract->insertFromSelect(Object(Varien_Db_Select), 'catalog_product...', Array, false)
#7 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(153): Mage_Index_Model_Resource_Helper_Mysql4->insertData(Object(Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source), Object(Varien_Db_Select), 'catalog_product...', Array, false)
#8 /var/www/hcomputers/app/code/core/Mage/Index/Model/Resource/Abstract.php(113): Mage_Index_Model_Resource_Abstract->insertFromTable('catalog_product...', 'catalog_product...', false)
#9 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): Mage_Index_Model_Resource_Abstract->syncData()
#10 /var/www/hcomputers/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll()
#11 /var/www/hcomputers/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll()
#12 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll()
#13 /var/www/hcomputers/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#14 /var/www/hcomputers/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()
#15 /var/www/hcomputers/shell/indexer.php(212): Mage_Shell_Compiler->run()

    我试过的事情:

    截断所有catalog_category_flat_store_#(1-40)表

    截断所有catalog_product_flat_#(1-40)表

    试图,正如其他地方建议删除约束.

    当apache被关闭时,跑动Reindexer.

    奇怪的是,"没有索引器运行"时,"产品价格"索引的状态仍为"正在处理".当我尝试删除var/locks中的锁定文件时,它们会立即再次生成,就像仍有东西试图索引一样,但事实并非如此.

    任何帮助都会受到赞赏,因为我们在这里运行空白!

    1 个回答

    • 该catalog_product_index_eav表具有一个名为的外键约束

      FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

      看看这个表的定义

      CREATE TABLE `catalog_product_index_eav` (
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Value',
  PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID` (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`),
  CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product EAV Index Table';

      我们可以看到外键定义是 

      CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` 
FOREIGN KEY (`entity_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) 
ON DELETE CASCADE ON UPDATE CASCADE

      这意味着对于每一entity_id行catalog_product_index_eav,都需要有相同的相应entity_id值catalog_product_entity.

      您的问题的根源是出于某种原因(无论是网络扩展,从互联网上随机输入SQL导致的错误,还是使用禁用的索引执行数据更新),Magento的索引尝试更新数据catalog_product_index_eav都违反了此规则.下一步是确定Magento正在做什么,以便您可以修复数据.

      如果我们查看你的调用堆栈,这看起来是一个开始调试的好地方 

      #10 app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): 
Mage_Index_Model_Resource_Abstract->syncData()

      跳转到该源文件,我们看到以下代码 

      public function syncData()
{
    $this->beginTransaction();
    try {
        /**
         * Can't use truncate because of transaction
         */
        $this->_getWriteAdapter()->delete($this->getMainTable());
        $this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
        $this->commit();
    } catch (Exception $e) {
        $this->rollBack();
        throw $e;
    }
    return $this;
}

      作为索引过程的一部分,Magento尝试将数据从"索引表"(getIdxTable)转换为"源"(getMainTable)表. 

      public function insertFromTable($sourceTable, $destTable, $readToIndex = true)
{
    //...
}

      对于此特定索引,索引表是catalog_product_index_eav_idx,而源表是catalog_product_index_eav.

      注意:请注意这里的术语,事情容易引起混淆."源"表是我们要复制的表.(我相信它被称为源表,因为它是普通Magento系统从需要信息时查询的"源")

      因此,Magento正在尝试将一行同步catalog_product_index_eav_idx到表中catalog_product_index_eav.但是,这会导致前面提到的外键错误.这导致我们得出两个可能的结论

        该catalog_product_index_eav_idx具有entity_id不中不存在行catalog_product_entity.

        该catalog_product_index_eav表具有(通过先前关闭索引检查的操作)entity_id不存在的行catalog_product_entity.

      因此,您的任务是确定哪些entity_id行catalog_product_index_eav和catalog_product_index_eav_idx不存在catalog_product_entity,并手动删除所述行(来自catalog_product_index_eav和catalog_product_index_eav_idx).

      如果是我,而且我的catalog_product_entity表不是太大,我会从以下查询开始(这些是未经测试的,因为我没有任何带有上述无效数据状态的Magento表)

      SELECT * 
FROM catalog_product_index_eav_idx 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

SELECT * FROM catalog_product_index_eav 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

      祝好运!

      2023-02-06 17:38 回答
    撰写答案
    今天，你开发时遇到什么问题呢？
    立即提问
    热门标签
    PHP1.CN | 中国最专业的PHP中文社区 | PNG素材下载 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
    Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有