cancel
Showing results for 
Search instead for 
Did you mean: 

Invoice creation sometimes fails

SOLVED

Invoice creation sometimes fails

 

Hi there,

 

 

Once in a while, invoice creation fails. 
The error always is : 

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`sales_flat_invoice_comment`, CONSTRAINT `FK_91CBA4E459B7A4BFF735B1582216D5AE` FOREIGN KEY (`parent_id`) REFERENCES `sales_flat_invoice` (`entity_id`) ON DELETE CASCADE ON UPDAT), query was: INSERT INTO `sales_flat_invoice_comment` (`parent_id`, `is_customer_notified`, `is_visible_on_front`, `comment`, `created_at`) VALUES (?, ?, ?, ?, '2017-03-03 08:39:48')

or

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`sales_flat_invoice_item`, CONSTRAINT `FK_3C0F10CD942A0167D91F88A2ED9E5E73` FOREIGN KEY (`parent_id`) REFERENCES `sales_flat_invoice` (`entity_id`) ON DELETE CASCADE ON UPDATE C), query was: INSERT INTO `sales_flat_invoice_item` (`parent_id`, `base_price`, `tax_amount`, `base_row_total`, `row_total`, `price_incl_tax`, `base_tax_amount`, `base_price_incl_tax`, `qty`, `base_cost`, `price`, `base_row_total_incl_tax`, `row_total_incl_tax`, `product_id`, `order_item_id`, `description`, `sku`, `name`, `hidden_tax_amount`, `base_hidden_tax_amount`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

 

 

The creation is done programmatically with a call to the API (multiple calls in the same process) : 

Mage::getModel('sales/order_invoice_api')->create($order->getIncrementId(), $items, "Invoicing #".$invoicingId, $notifyCustomer, false);

 

Invoice & Invoice items are inserted via a transaction, right ? So I don't get it.

I'm pretty sure it is linked to the DB : We're using mariadb 5.5 in cluster mode (galera - 3 nodes).

(We have the same issue on mariadb 10.1)

 

Any ideas ?

1 REPLY

Re: Invoice creation sometimes fails

Well I think I got it.

Magento is doing this (simplified of course) :

  1. SQL : Start Transaction
  2. SQL: Insert invoice
  3. PHP : Get invoice ID
  4. SQL : Insert invoice item with invoice_id = $PhpInvoiceId
  5. SQL : Commit Transaction

Which is ugly.

If for some reason the transaction fails, and is replayed by the server, the $PhpInvoiceId is now wrong....