Fixing Duplicate Entry Error From Custom Order Prefix

This is something we ran into during the testing of a new Magento website. We started getting “Payment Transaction Failed Reminder” emails from the system. The client reported that they would just be presented with an error message during checkout when they tried to place the order and it would just keep returning the error no matter how many times they tried to submit it.

Looking at the email sent by the system, the error message being returned was:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘T000112’ for key 2

After investigating a bit, I found that someone else had already placed an order and taken that increment_id which is why this order was returning an error. This was a bit odd since Magento is supposed to be keeping track of these things and automatically ensure order numbers are never the same. Even looking at the sales_flat_quote table showed two entries with the same reserved_order_id which should not happen.

Apparently this is a known bug that was introduced with 1.6 and still remains in the system to this day. It stems from using a non-numeric character for the prefix of the order numbers. To see why this is causing an issue, first open up app/code/core/Mage/Sales/Model/Resource/Quote.php and look at this function:

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

The issue comes from the fact it forces the $orderIncrementId to (int) when it binds it to the param. Having a character prepended to order numbers means this will never match any row currently in the system so it will always think the order number has not been taken.

The fix to this is pretty easy, remove that cast to (int) from the line and you should no longer get those pesky emails! But this is not a good way to do it as modifying Magento core is a big no no!

The correct way to do this is to extend the resource model and override this one method to apply the fix. To do this we have to create a module, I will not go into details of creating a new module as that is pretty generic knowledge. What you will need to do is the following:

Create a new file inside the module at Model/Resource/Quote.php with the following:

<?php

class MyCompany_MyModule_Model_Resource_Quote extends Mage_Sales_Model_Resource_Quote
{

    /**
    * Check is order increment id use in sales/order table
    *
    * Overriding to fix issue with characters in order increment id
    *
    * @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;
    }

}

Save this file and we now need to tell Magento to use it instead of the core file. To do this we just have to add the following XML block into our modules config.xml inside the model tag:

<sales_resource>
    <rewrite>
        <quote>MyCompany_MyModule_Model_Resource_Quote</quote>
    </rewrite>
</sales_resource>

Now just save the config.xml and flush the Magento cache and all should be fixed!