Magento Order saving error: Integrity constraint violation

Question

I am getting the following error when I make an order with a credit/debit card in Magento:

Order saving error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '282-189' for key 2

I could not get an exact trace, as it just didn't want to show that to me, however, I have traced it back to app/code/core/Mage/Checkout/controllers/OnepageController.php on line 501 (unedited). The exact line is:

$this->getOnepage()->saveOrder();

Now, logic tells me this is just two orders having the same key when trying to add an order, or am I completely wrong?

Anyway, so outside this, what is the best way to get this fixed? I have thought of exporting the orders, removing them all, and then reimporting them, but I've got the feeling it won't work.

I am using Magento 1.6.2.0

EDIT: I just realized, if I find out which table is failing the integrity check, I could potentially empty that table (depending on which table it is) and this would fix it self? Any ideas on how to find out which table is messing up?

EDIT2: So after Oğuz Çelikdemir's answer, it turns out the following is the culprit:

2012-03-14T13:59:01+00:00 DEBUG (7): SQL: INSERT INTO `sales_order_tax_item` (`tax_id`, `item_id`, `tax_percent`) VALUES (?, ?, ?) (254, 8, 10)
2012-03-14T13:59:01+00:00 DEBUG (7): SQL: INSERT INTO `sales_order_tax_item` (`tax_id`, `item_id`, `tax_percent`) VALUES (?, ?, ?) (254, 8, 10)

Obviously two of the same tax_id can't be inserted. Anyone any idea how to fix it?

Yes, but nothing that should have interfered with the tax! I have found a solution though. — GManz, Mar 14 '12 at 15:28

score 3 · Answer 1 · edited May 23 '17 at 11:52

Truncate the following log tables. To protect yourself, please make backup from CONSOLE, not in Magento.

TRUNCATE `log_customer`;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;
TRUNCATE `log_quote`;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;
TRUNCATE `log_summary`;
ALTER TABLE `log_summary` AUTO_INCREMENT=1;
TRUNCATE `log_visitor_info`;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
TRUNCATE `log_url`;
ALTER TABLE `log_url` AUTO_INCREMENT=1;
TRUNCATE `log_url_info`;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
TRUNCATE `log_visitor`;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
TRUNCATE `report_event`;
ALTER TABLE `report_event` AUTO_INCREMENT=1;
TRUNCATE `log_summary_type`;
ALTER TABLE `log_summary_type` AUTO_INCREMENT=1;

EDIT : For SQL debugging, open the /lib/Varien/Db/Adapter/Pdo/Mysql.php file and find protected $_debug. The default status should be false, change to true.

After that, the log file should available in var/debug/sql.txt

Also, take this stackoverflow ansver How do I print all the queries in Magento?

EDIT 2 : So, here is the your files that process sales item tax. app/code/core/Mage/Tax/Model/Observer/Observer.php

# Line 144 ( Magento 1.6 edition )
Mage::getModel('tax/sales_order_tax_item')->setData($data)->save();

which is calling sales_order_tax_item model.

app/core/Mage/Tax/Model/Resource/Sales/Order/Tax/Itemp.php

# Line 51
public function getTaxItemsByItemId($item_id)
{
    $adapter = $this->_getReadAdapter();
    $select = $adapter->select()
        ->from(array('item' => $this->getTable('tax/sales_order_tax_item')), array('tax_id', 'tax_percent'))
        ->join(
            array('tax' => $this->getTable('tax/sales_order_tax')),
            'item.tax_id = tax.tax_id',
            array('title', 'percent', 'base_amount')
        )
        ->where('item_id = ?', $item_id);

    return $adapter->fetchAll($select);
}

Here is the function that calling from Observer. So, what can you do now, just add a breakpoint on Observer or Function and look debugger output. Why two times calling this function!

Did that, didn't work! I had tried this before as well, but still didn't work. Someone had listed many other tables to empty including the sales_order_flat tables. But none of it worked. Any other suggestions? — GManz, Mar 14 '12 at 10:22
Did you try to refresh the cache and reindex product and category! Remove `cache` directory from console instead of magento. — Oğuz Çelikdemir, Mar 14 '12 at 10:42
Yes, I did, I have updated the question, maybe you could help with the bit I edited? — GManz, Mar 14 '12 at 10:51
Okay, for some reason yours didn't update log file after the first time, however, the stackoverflow answer certainly helped! I just edited my question, could you please have another look :) — GManz, Mar 14 '12 at 14:03
No idea why it's being called twice, however, I have found it a solution thanks to the sql debugging you provided! I'll post it as a new answer. It's kind of a dirty hack, but it works. — GManz, Mar 14 '12 at 15:29

score 2 · Answer 2 · edited Dec 04 '12 at 13:55

2

Please go to the section Sales -> Tax -> Manage Tax Rules in Admin Panel and check all records there for duplicates of relations zone-class.

In my case I have the same error on Checkout Page for products which had Tax class "Taxable Goods" and in the Tax Rules I had two entries for this class and each entry was with the same tax zone. So the system tried to apply the same tax twice for product.

edited Dec 04 '12 at 13:55

Nilesh

20,521
16
92
148

answered Dec 04 '12 at 13:37

Pavel Tkachenko

31
1
3

Thankyou, was exactly my issue. Because of how broken tax is in prior installations of Magento (ours was 1.6.2.0), this only reared its head in 1.9 due to the substantial amount of changes that were made to the tax system. – evensis Jan 30 '15 at 17:22

GManz · Accepted Answer · 2012-07-26T17:00:53.440

I found a quick dirty hack. I'll try and post this as a bug to the developers. open the file app/code/core/Mmage/Tax/Model/Observer.php and go to line 132. There's an if($item) statement, within that statement, wrap the code around this:

$define = 'FIX_'.$item->getId().'_'.$result->getTaxId();
if(!defined($define) || constant($define) != true) {
            // code
    define($define, true);
}

Example:

if (isset($ratesIdQuoteItemId[$id])) {
    foreach ($ratesIdQuoteItemId[$id] as $quoteItemId) {
        if ($quoteItemId['code'] == $tax['code']) {
            $item = $order->getItemByQuoteItemId($quoteItemId['id']);
            if ($item) {
                $define = 'FIX_'.$item->getId().'_'.$result->getTaxId();
                if(!defined($define) || constant($define) != true) {
                    $data = array(
                        'item_id'       => $item->getId(),
                        'tax_id'        => $result->getTaxId(),
                        'tax_percent'   => $quoteItemId['percent']
                    );
                    Mage::getModel('tax/sales_order_tax_item')->setData($data)->save();
                    define($define, true);
                }
            }
        }
    }
}

Magento Order saving error: Integrity constraint violation

3 Answers3