Order Error Creating Duplicate Entry

Question

A customer has paid yet his order is stuck in his shopping cart. I confirmed this with looking in my paypal his payment has definitely gone through.

So I tried to Create an order for him so I could give him invoice...etc. Upon trying to create an invoice it gives me error;

Order saving error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '100000103' for key 'UNQ_SALES_FLAT_ORDER_INCREMENT_ID', query was: INSERT INTO sales_flat_order (coupon_code, protect_code, shipping_description, is_virtual, store_id, customer_id, base_discount_amount, base_grand_total, base_shipping_amount, base_shipping_tax_amount, base_subtotal, base_tax_amount, base_to_global_rate, base_to_order_rate, discount_amount, grand_total, shipping_amount, shipping_tax_amount, store_to_base_rate, store_to_order_rate, subtotal, tax_amount, total_qty_ordered, customer_is_guest, customer_note_notify, customer_group_id, quote_id, base_shipping_discount_amount, base_subtotal_incl_tax, shipping_discount_amount, subtotal_incl_tax, weight, customer_dob, increment_id, applied_rule_ids, base_currency_code, customer_email, customer_firstname, customer_lastname, customer_middlename, customer_prefix, customer_suffix, customer_taxvat, discount_description, global_currency_code, order_currency_code, remote_ip, shipping_method, store_currency_code, store_name, x_forwarded_for, customer_note, created_at, updated_at, total_item_count, customer_gender, hidden_tax_amount, base_hidden_tax_amount, shipping_hidden_tax_amount, base_shipping_hidden_tax_amnt, shipping_incl_tax, base_shipping_incl_tax, coupon_rule_name, gift_message_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '2016-03-10 03:27:10', '2016-03-10 03:27:10', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

It says the Duplicate Entry "100000103" already exists which it does when I look in my invoices. I've taken my website offline for now until I get this sorted as I don't want to tarnish my brand.

Anyone had this error before?

EDIT1: I tried to create again and it went to "100000104" then "100000105", upon the fourth try it worked as it defaulted to "100000106" which does not have a sale number.

Thanks, Steve

Answer 1

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

OR

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.