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?
