13

I'm helping a merchant track down the root cause for some failed payment transactions (during a heavy order day), that failed with the following error

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '51986' for key 'UNQ_SALES_FLAT_INVOICE_INCREMENT_ID'

The UNQ_SALES_FLAT_INVOICE_INCREMENT_ID index is a unique key on the increment_id column in the sales_flat_invoice table. When I look in this table for the increment_id mentioned in the error (51986), I find there's already a invoice with this increment_id in there, and it's for an order placed by a different customer.

My 2 questions related to this

  • Where in Magento CE 1.9.0.1 is an invoice ID normally created?

  • Are there known issues in a stock Magento CE 1.9.0.1 with colliding invoice IDs for near simultaneous orders?

I realize the increment ID of 51986 means the store has some sort of extension for changing the increment IDs installed, but I want to make sure there isn't known science w/r/t to this before going too far down that path.

Alana Storm
  • 44,345
  • 35
  • 168
  • 353
  • 1
    Adding Mage_Eav_Model_Entity_Type::fetchNewIncrementId() as a debug point. – Alana Storm Sep 09 '15 at 20:32
  • 1
    I've seen this before, but it was due to someone placing a save() method call in a specific observer event which sometimes would cause this issue - in the days before code review ;) – Erfan Sep 10 '15 at 01:38
  • @AlanStorm, just out of curiosity, why going into Eav entity, I think, Invoice is a flat model. – Prateek Sep 29 '15 at 12:40
  • I believe this can also happen with default Magento http://stackoverflow.com/questions/25918091/magento-1-6-1-integrity-constraint-violation-1062-duplicate-entry-104187-14-f – Kristof at Fooman Oct 21 '15 at 05:02
  • 1
    I know this older, but was the eav_entity_store table copied for any reason.. This is a common error, where the last order id does not match with the current placed order. So Magento uses the eav_entity_store table to determine what ID to insert into the order table, and in this case it already exists. Also, note this is very common problem with the FooMan order number extension as it can bypass this check and cause this issue out of the blue. – Rob Nov 08 '15 at 16:16

1 Answers1

3

Order, invoice, creditmemo, shipping was EAV until 1.6(?)

@Prateek invoice WAS an EAV model and the increment_id still is.

Increment_id creation and problem

Increment ID is created here

\Mage_Eav_Model_Entity_Attribute_Backend_Increment which calls
\Mage_Eav_Model_Entity_Abstract::setNewIncrementId which calls
\Mage_Eav_Model_Entity_Type::fetchNewIncrementId

I would assume because in the last method the transaction is started (and the table/row is not locked) a second order creation can pass by and take the same newly created increment_id.

Solution

I would assume, that if you lock the row/table before reading, you can avoid that any other process reads the table until you write a new increment_id. This might help: How do I lock a row after using load()?

But I fear, that locking the row make bad performance loss.

Community
  • 1
Fabian Blechschmidt
  • 35,388
  • 8
  • 75
  • 182
  • 1
    Just saw this post and @Fabian, that's good to know. SE should also trigger notifications when someone is mentioned in an answer. – Prateek Dec 17 '15 at 09:43