I am using "Sage Pay" for payment transaction in magento. But when I place order it show the following error:

An sage pay error ocurred: SQLSTATE[23000]: Integrity constraint violation:1062 Duplicate entry '2000734" for key 'UNQ_SALES_FLAT_INVOICE_INCREMENT_ID'


To fix this issue, you need to change your increment_last_id of your invoice entity type.

By default in magento invoice entity type is 6. Which you can find in eav_entity_type table.

You need to change increment_last_id value for your store. The increment_last_id is in eav_entity_store table. You can run following query to update your invoice increment_last_id. Where X is your new increment_last_id and STORE_ID_HERE is your store id to whom you changing invoice increment_last_id.

UPDATE eav_entity_store
       INNER JOIN eav_entity_type
         ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET    eav_entity_store.increment_last_id = 'X'
WHERE  eav_entity_type.entity_type_code = 'invoice'
       AND eav_entity_store.store_id = {STORE_ID_HERE};


UPDATE `eav_entity_store` SET `increment_last_id` = 'X' WHERE `entity_type_id` = '6';

Notice that the value for increment_last_id already contains the increment_prefix. Use your entity_type increment_prefix

For more details check here : https://www.warpconduit.net/2012/04/18/how-to-change-the-order-increment-id-and-prefix-in-magento/

Hope it helps.

  • Hi @Rohit, I have found that the increment_last_id of invoice is 20004159 from the eav_entity_store. So now should I directly update it to 20004160 in the eav_entity_store ? or is there any other table associated with it ? Mar 12 '16 at 6:23
  • @KishorePatra You can directly update it. :-) Mar 12 '16 at 6:26
  • I want to know why this number is getting duplicate. Is the code creates a problem ? or it is created by the database ? Mar 12 '16 at 7:20
  • My first guess would be there's a third party extension, core hack, or maybe even core code, that isn't handling operations with the Order objects transactionally. That, combined with an unexpected exceptions somewhere between saving an order and the internal increment number being increased, could result in orders with the same number being created. I'd check your Error and Exception logs for problems, clean those problems up, and see if the problem continues. Otherwise you're going to need to reproduce the conditions of the errors in a test environment and debug debug debug. Mar 12 '16 at 7:34
  • @KishorePatra Is this worked for you? Mar 12 '16 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.