Magento SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘000000254’ for key ‘UNQ_SALES_FLAT_ORDER_INCREMENT_ID’

Posted on by

In some cases, customers encounter this error on your website :

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '000000254' for key 'UNQ_SALES_FLAT_ORDER_INCREMENT_ID'

You don’t understand and drive you crazy ?
Don’t worry, I will help you.

First, this error probably happend when customer quit violenty the payment page, without canceling, so your order is not “finished” and your quote is not “released” (is_active = 1 in db). Customer come back, recover his quote and try to proceed it again.
The reserved_increment_id is not updated and when magento try to create the order, mySQL give him the previous error.

How a so obvious bug like this can exist ? and why does it crash only on my website, it seems to work fine on other website ?
Let me guest, your client (clients always have weird needs …) ask you to start order number at 0 instead of 100000000, or ask you to put a letter into it.

So, why does this reserved_increment_id is not updated ?
Let check on the function which update the reserved_increment_id field :

// /app/code/core/Mage/Sales/Model/Resource/Quote.php
    /**
     * Check is order increment id use in sales/order table
     *
     * @param int $orderIncrementId
     * @return boolean
     */
    public function isOrderIncrementIdUsed($orderIncrementId)
    {
        $adapter   = $this->_getReadAdapter();
        $bind      = array(':increment_id' => (int)$orderIncrementId);
        $select    = $adapter->select();
        $select->from($this->getTable('sales/order'), 'entity_id')
            ->where('increment_id = :increment_id');
        $entity_id = $adapter->fetchOne($select, $bind);
        if ($entity_id > 0) {
            return true;
        }

        return false;
    }

You see it, the little (int) in this line $bind = array(‘:increment_id’ => (int)$orderIncrementId); ?
This transform your increment_id in your request from “000000254” to “254”, mySQL doesn’t find an order with the increment_id “254” and your quote is not updated.

To fix it, override this function :
In your Sales/etc/config.xml file, add

<global>
  <model>
    <sales_resource>
                <rewrite>
                    <quote>Adin_Sales_Model_Resource_Sales_Quote</quote>
                </rewrite>
            </sales_resource>
   </model>
</global>

And create the new class file /app/code/local/Adin/Sales/Model/Resource/Sales/Quote.php

class Adin_Sales_Model_Resource_Sales_Quote extends Mage_Sales_Model_Resource_Quote {

    /**
     * Check is order increment id use in sales/order table
     *
     * @param int $orderIncrementId
     * @return boolean
     */
    public function isOrderIncrementIdUsed($orderIncrementId)
    {
        $adapter   = $this->_getReadAdapter();
        $bind      = array(':increment_id' => $orderIncrementId);
        $select    = $adapter->select();
        $select->from($this->getTable('sales/order'), 'entity_id')
            ->where('increment_id = :increment_id');
        $entity_id = $adapter->fetchOne($select, $bind);
        if ($entity_id > 0) {
            return true;
        }

        return false;
    }

}

That should do the tricks.

Leave a Reply