Magento exception Integrity constraint violation: 1048 Column 'value' cannot be null' in \lib\Zend\Db\Statement\Pdo.php:234

Question

I am running an Observer Method using following way manually to test it

<?php
require_once('app/Mage.php');
umask(0);
Mage::app();

$observer = Mage::getModel('promoproductcategoryassign/observer');
$observer->updatePromoCategoryProducts();

code in Observer is as following :

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();
        }
    }
}

It throws following exception :

Fatal error:  Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'value' cannot be null' in C:\xampp\htdocs\mymagento\lib\Zend\Db\Statement\Pdo.php:234
Stack trace:
#0 C:\xampp\htdocs\mymagento\lib\Zend\Db\Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\xampp\htdocs\mymagento\lib\Zend\Db\Adapter\Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 C:\xampp\htdocs\mymagento\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#3 C:\xampp\htdocs\mymagento\lib\Varien\Db\Adapter\Pdo\Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#4 C:\xampp\htdocs\mymagento\lib\Varien\Db\Adapter\Pdo\Mysql.php(1452): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...', Array)
#5 C:\xampp\htdocs\mymagento\app\code\core\Mage\Catalog\Model\Resource\Eav\Mysql4\Abstract.php(228): Varien_Db_Adapter_Pdo_Mysql->insertOnDuplicate('catalog_product...', Array, Array)
#6 C:\xam in C:\xampp\htdocs\mymagento\lib\Zend\Db\Statement\Pdo.php on line 234

I tried to debug it by enabling the mysql query logging from \lib\Varien\Db\Adapter\Pdo\Mysql.php file.

I get following in the /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:\xampp\htdocs\mymagento\lib\Zend\Db\Statement\Pdo.php:234
Stack trace:
#0 C:\xampp\htdocs\mymagento\lib\Zend\Db\Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\xampp\htdocs\mymagento\lib\Zend\Db\Adapter\Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 C:\xampp\htdocs\mymagento\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#3 C:\xampp\htdocs\mymagento\lib\Varien\Db\Adapter\Pdo\Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#4 C:\xampp\htdocs\mymagento\lib\Varien\Db\Adapter\Pdo\Mysql.php(1452): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...', Array)
#5 C:\xampp\htdocs\mymagento\app\code\core\Mage\Catalog\Model\Resource\Eav\Mysql4\Abstract.php(228): Varien_Db_Adapter_Pdo_Mysql->insertOnDuplicate('catalog_product...', Array, Array)
#6 C:\xampp\htdocs\mymagento\app\code\core\Mage\Eav\Model\Entity\Abstract.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:\xampp\htdocs\mymagento\app\code\core\Mage\Eav\Model\Entity\Abstract.php(955): Mage_Eav_Model_Entity_Abstract->_processSaveData(Array)
#8 C:\xampp\htdocs\mymagento\app\code\core\Mage\Core\Model\Abstract.php(306): Mage_Eav_Model_Entity_Abstract->save(Object(OrganicInternet_SimpleConfigurableProducts_Catalog_Model_Product))
#9 C:\xampp\htdocs\mymagento\muk_specialpriceupdates.php(12): Mage_Core_Model_Abstract->save()
#10 {main}

## 2516 ## TRANSACTION ROLLBACK
TIME: 0.0511

How can I fix this? (Magento version 1.4.1.1)

Answer 1

You could:

change your fields to allow NULL

check a value is set before you try to use it

change your database fields to have some sort of default value; even if that value is an empty string, e.g. bill_by_post NOT NULL DEFAULT '' (or equivalent, depending on the data type you're working with)

I hope this will help you.