Magento 异常完整性约束冲突：1048 列 'value' 不能为 null' 在 \lib\Zend\Db\Statement\Pdo.php：234

Question

我正在运行一个Observer方法，使用以下方式手动测试它

<?php
require_once('app/Mage.php');
umask(0);
Mage::app();
$observer = Mage::getModel('promoproductcategoryassign/observer');
$observer->updatePromoCategoryProducts();

Observer中的代码如下：

public function updatePromoCategoryProducts()
{        
    $store = '1';
    $assignedCategories = array();
    $promoCatId = (int)Mage::getStoreConfig('promoprodsec/promoprodgroup/promocategoryid');
    $products = Mage::getModel('catalog/category')->load($promoCatId)
        ->getProductCollection();       
    $products->addAttributeToSelect('*');
    $products->addAttributeToFilter('status', Mage_Catalog_Model_Product_Status::STATUS_ENABLED);
    $products->addAttributeToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH);
    foreach ($products as $prod) {        
        $prodId = (int)$prod->getId();
        $validDateFlag = Mage::app()->getLocale()->isStoreDateInInterval(
            $store,
            $prod->getData('special_from_date'),
            $prod->getData('special_to_date')
        );
        $assignedCategories = $prod->getCategoryIds();
        array_pop($assignedCategories);
        if ($validDateFlag == false || $specialPrice == '') {
            $product = Mage::getModel('catalog/product')->load($prodId);
            $product->setCategoryIds($assignedCategories);
            $product->save();
        }
    }
}

它抛出以下异常：

Fatal error:  Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'value' cannot be null' in C:xampphtdocsmymagentolibZendDbStatementPdo.php:234
Stack trace:
#0 C:xampphtdocsmymagentolibZendDbStatement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:xampphtdocsmymagentolibZendDbAdapterAbstract.php(468): Zend_Db_Statement->execute(Array)
#2 C:xampphtdocsmymagentolibZendDbAdapterPdoAbstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#3 C:xampphtdocsmymagentolibVarienDbAdapterPdoMysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#4 C:xampphtdocsmymagentolibVarienDbAdapterPdoMysql.php(1452): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...', Array)
#5 C:xampphtdocsmymagentoappcodecoreMageCatalogModelResourceEavMysql4Abstract.php(228): Varien_Db_Adapter_Pdo_Mysql->insertOnDuplicate('catalog_product...', Array, Array)
#6 C:xam in C:xampphtdocsmymagentolibZendDbStatementPdo.php on line 234

我试图通过从libVarienDbAdapterPdoMysql.php文件启用mysql查询日志来调试它。

我在/var/debug/sql.txt file 中得到了关注

    ## 2516 ## QUERY
SQL: INSERT INTO `catalog_product_entity_int` (`entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE `value`=VALUES(`value`)
BIND: Array
(
    [0] => 4
    [1] => 114
    [2] => 0
    [3] => 6281
    [4] => 
)
AFF: 1
TIME: 0.0005
## 2516 ## QUERY
SQL: INSERT INTO `catalog_product_entity_varchar` (`entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE `value`=VALUES(`value`)
BIND: Array
(
    [0] => 4
    [1] => 116
    [2] => 0
    [3] => 6281
    [4] => 
)
TIME: 0.0016
EXCEPTION 
exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'value' cannot be null' in C:xampphtdocsmymagentolibZendDbStatementPdo.php:234
Stack trace:
#0 C:xampphtdocsmymagentolibZendDbStatement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:xampphtdocsmymagentolibZendDbAdapterAbstract.php(468): Zend_Db_Statement->execute(Array)
#2 C:xampphtdocsmymagentolibZendDbAdapterPdoAbstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#3 C:xampphtdocsmymagentolibVarienDbAdapterPdoMysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#4 C:xampphtdocsmymagentolibVarienDbAdapterPdoMysql.php(1452): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...', Array)
#5 C:xampphtdocsmymagentoappcodecoreMageCatalogModelResourceEavMysql4Abstract.php(228): Varien_Db_Adapter_Pdo_Mysql->insertOnDuplicate('catalog_product...', Array, Array)
#6 C:xampphtdocsmymagentoappcodecoreMageEavModelEntityAbstract.php(1158): Mage_Catalog_Model_Resource_Eav_Mysql4_Abstract->_insertAttribute(Object(OrganicInternet_SimpleConfigurableProducts_Catalog_Model_Product), Object(Mage_Catalog_Model_Resource_Eav_Attribute), NULL)
#7 C:xampphtdocsmymagentoappcodecoreMageEavModelEntityAbstract.php(955): Mage_Eav_Model_Entity_Abstract->_processSaveData(Array)
#8 C:xampphtdocsmymagentoappcodecoreMageCoreModelAbstract.php(306): Mage_Eav_Model_Entity_Abstract->save(Object(OrganicInternet_SimpleConfigurableProducts_Catalog_Model_Product))
#9 C:xampphtdocsmymagentomuk_specialpriceupdates.php(12): Mage_Core_Model_Abstract->save()
#10 {main}
## 2516 ## TRANSACTION ROLLBACK
TIME: 0.0511

我该怎么解决这个问题？（Magento版本1.4.1.1）

Answer 1

array_pop($assignedCategories);正在删除categories数组的最后一项，如果数组只有一个元素（如果产品只分配给一个类别），那么剩下的是一个空数组，从而使category_ids属性的value成为空字符串''，从而触发EAV表上的NOTNULL约束违反错误。解决方案是移除array_pop($assignedCategories);