Magento 1.9 - issue creating coupon rule. An error occurred while saving the rule data

Question

I can create a new Shopping Cart Price Rule, but when using Actions or Conditions it won't save, resulting in a red error:

An error occurred while saving the rule data. Please review the log and try again.

Reviewing the exception.log file shows an error about duplicate key in MySql table

    EXCEPTION 
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8-1-0-160' for key 'PRIMARY'' in /root/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /root/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /root/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /root/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /root/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /root/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `sa...', Array)
#5 /root/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `sa...', Array)
#6 /root/lib/Varien/Db/Adapter/Pdo/Mysql.php(2100): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `sa...', Array)
#7 /root/lib/Varien/Db/Adapter/Pdo/Mysql.php(2073): Varien_Db_Adapter_Pdo_Mysql->insertArray('salesrule_produ...', Array, Array)
#8 /root/app/code/core/Mage/SalesRule/Model/Resource/Rule.php(293): Varien_Db_Adapter_Pdo_Mysql->insertMultiple('salesrule_produ...', Array)
#9 /root/app/code/core/Mage/SalesRule/Model/Resource/Rule.php(133): Mage_SalesRule_Model_Resource_Rule->setActualProductAttributes(Object(Mage_SalesRule_Model_Rule), Array)
#10 /root/app/code/core/Mage/Core/Model/Resource/Db/Abstract.php(463): Mage_SalesRule_Model_Resource_Rule->_afterSave(Object(Mage_SalesRule_Model_Rule))
#11 /root/app/code/core/Mage/Core/Model/Abstract.php(318): Mage_Core_Model_Resource_Db_Abstract->save(Object(Mage_SalesRule_Model_Rule))
#12 /root/app/code/core/Mage/Adminhtml/controllers/Promo/QuoteController.php(163): Mage_Core_Model_Abstract->save()
#13 /root/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_Promo_QuoteController->saveAction()
#14 /root/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('save')
#15 /root/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#16 /root/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#17 /root/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#18 /root/index.php(86): Mage::run('', 'store')
#19 {main}

After much digging and troubleshooting, I found in pdo_mysql.log the exact INSERT INTO query that was generated by the system, and it appears that the first array has an empty value for customer_group_id field - which ends up as "0" in MySql (since the filed is NOT NULL), thus resulting in a duplicate key error for the next array!

See below:

INSERT INTO `salesrule_product_attribute` 
(`rule_id`,`website_id`,`customer_group_id`,`attribute_id`) 
VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?)
BIND: array (
  0 => '8',
  1 => '1',
  2 => '',
  3 => '160',
  4 => '8',
  5 => '1',
  6 => '0',
  7 => '160',
  8 => '8',
  9 => '1',
  10 => '1',
  11 => '160',
  12 => '8',
  13 => '1',
  14 => '2',
  15 => '160',
  16 => '8',
  17 => '1',
  18 => '3',
  19 => '160',
)

I have not touched the Customer Groups in Magento, only got the 4 default groups that are 0 1 2 3.

So why is this extra empty array generated, and how do I prevent it from happening?

Answer 1

the error is being generated by a catch block inside this class: app/code/core/Mage/Adminhtml/controllers/Promo/QuoteController.php at line 184 .

Mage::helper('catalogrule')->__('An error occurred while saving the rule data. Please review the log and try again.'));

you could add an $e->getMessage() to the error output if you are not on a production server and then you will know what was the problem. Good luck and happy coding !!!