Clean up orphaned line items

I have the same error. It's appears when line item is added to an order by anonymous user and the order's form is not submitted.

Hi! I have the same problem. I add a product to the cart under anonymous user, do not delete it from cart or submit an order, exit from site and then when I open my site on this or another another computer, the cart block shows summary with that product, but when I open shopping cart - It's empty.

Thank you for quick response! I have found my problem - I used my own view's block in shopping cart which couldn't get the argument "order id" and couldn't show correct summary.

If it is possible - add to documentation about impossibility of use own cart blocks.

One problem with these orphaned line items I stumbled upon is inability to delete the corresponding product after anonymous added it to cart.

I doubt #2 alone would be a solution in such a situation. Why not also exclude orphans when determining the possibility of deleting a product?

Are there other issues with orphaned line items?

I remarked this:

The shoppingcart isn't reset.

I start buying on product for 0.80. I finished the process via commerce_Ogone. I use the flat rate shipping.
Second time, I buy again a product 0.80, on the line-Item there is only the one product I bought. But the order total is 1.60. And again and again.
I disabled the Ogone and shipping module. I tried the example module of Paypal.

Tried it logged in as a user with admin-rights and as visiter not logged in.

In another browser, same case.

I changed the standard Shopping Cart view where I did't show the total price. Is it true that there is an impossibility of use an custom Shopping Cart Blocks ?

I put this issue on Major, b/c Commerce is based on Views, and when we cannot change the "Shopping cart block" I think this is "Major".

Even if I thought that Views only showed a view, I restored the view as before and (for the moment) I didn't got the problem back. Prices are correct.

I saw a closed issue about (I believe) the same problem.

http://drupal.org/node/1151222

That issue is closed because "it works as designed" . But when the Shopping Cart Block cannot be changed, that's not as it is designed , right ?
I this a bug, or a user-fault ?

We really need a way to clean up carts and kill them off. I continually have to go into the database to solve problems that happen when an imported product gets into the cart and then can't be rolled back. And we have no UI for invalidating carts.

Ah the delights of building on Views. Adding Bulk Operations to the regular Orders admin view and removing the filter that excludes items in checkout status does the job for a UI that shows carts.

subscribe

subscribe

Same issue here, can't delete products that have orphaned line items.

Despite all orders being deleted and all product display nodes being deleted, the orphaned line items in commerce_line_items mean the products can't be deleted.

Subscribing.

Just an update, in order to delete the products, I needed to manually delete the orphaned line items from

commerce_line_item

I also then needed to manually deleted the reference to the line item from

field_data_commerce_product
field_revision_commerce_product

After that, the products could _finally_ be deleted!

The module really needs a way to prevent these orphaned line items persisting in the database in the first place.

Note that this issue is about orphaned line items, created when adding / editing an order.
Cart expiration is a whole different problem, and we have an issue open for that: #1272474: Cart expire .

I agree - this is badly needed functionality. If a client catches this problem - they're going to consider their website broken.

Hey rszrama,

My client just caught this issue while using my feature transactional stock management.
The feature has an event that create a stock entry when a line item is created (it checks the order status, as there is no "line item status" level).

Since the line item is created with an order id in the line item manager (even if the order has no "relation" with it yet), it screws up the workflow.

Is there any news or sandbox maybe on the IPF ?

Many thanks !

IPF is being worked on, and is now called Inline Entity Form. Only supports products at the moment. You can follow http://drupal.org/sandbox/rszrama/1181848

Inline Entity Form now provides this functionality (custom widget for the line item reference field).

I thought this issue was about cleaning up dangling line items in the db... not a UI issue? What am I missing here?

See #16.

This is what I got into using Backoffice:

1. Create a product (display with at least one variant (type) inline)
2. Place it in the cart
3. Delete the product (admin/commerce/products)

=> The prdocut display is deleted but the variants (types) are still there. (When Backoffice is installed, you cant access themm. To make them show up, use the Display Manager module or uninstall Backoffice.)

Trying to delete the variants (types), gives me the error message "This product is referenced by..."

I used the Method of #15. (Delete the respective database entry in "field_data_commerce_product" and "field_revision_commerce_product" and then delete the product type in Commerce)

This issue is about line items, you're talking about products, backoffice and inline entity form.
Open an issue in the backoffice issue queue for more assistance on that.

Was this ever resolved?

dropped record from field_data_commerce_product table using phpMyadmin and that worked.

However, I did have to disable commerce back office modules to be able to manage products. With back office enabled, product views reports no products have been created yet.

#15 seems reasonable.
If you are working in sandbox, try the following desparate steps (DO NOT try it on your real db):
- Remove all Orders at: /admin/commerce/orders
- Remove all Shopping cards at: /admin/commerce/orders/carts
- Scan the db dump for line item references e.g. via SKU and truncate those tables:
Example:

drush --ordered-dump sql-dump | grep field_ | grep MY_SKU | grep -v cache
drush sqlq "TRUNCATE field_data_field_active_subscription_meta"
drush sqlq "TRUNCATE field_revision_field_active_subscription_meta"

- If no luck, you may try to remove the product from the database, e.g.

drush sqlq "DELETE FROM commerce_product WHERE product_id = 23"

You may try to use: https://drupal.org/project/inline_entity_form
but I found it not useful for one-time removal of line item references.

Related:
http://www.drupalcommerce.org/discussions/2856/can-delete-product-becaus...

I'm having the same problem with orphaned line items in commerce_line_items table and deleting them directly from the table doesn't feel very comfortable.
Does anyone have a solution to get rid of the rows without having to install IEF module or to perform a SQL-query?

@rszrama there is an order_id on the commerce_line_item table. Could you we do something like:

On Order Save:

DELETE FROM commerce_line_item li 
LEFT JOIN field_data_commerce_line_items field 
  ON field.commerce_line_items_line_item_id = li.line_item_id 
    AND field.bundle = 'commerce_order' 
    AND field.entity_id = 106 
WHERE order_id = 106 
  AND field.commerce_line_items_line_item_id IS NULL

Or the equivalent entity query.

Hi,

We are using Discount and Fees which are two modules who have both been having problems with their "line item refresh engines". There are separate issues with lots of work done on this of course.

With our order_ids approaching 200,000 we now have about 7,000,000 line items created in the database.

Deleting old cart orders only deletes the line items that are actually referenced by the order, which is why we need this.

Our temporary solution is writing a cron job that somehow loops orders and uses code from #31.

Any help is greatly appreciated, we have a large data set to run it on :-)

Per

Hi again,

Our emergency workaround for this issue, based on #31:

// Load the batch of orphaned line items
$sql = "SELECT * FROM commerce_line_item li 
LEFT JOIN field_data_commerce_line_items field 
ON li.line_item_id = field.commerce_line_items_line_item_id
WHERE field.commerce_line_items_line_item_id IS NULL
ORDER BY li.order_id
LIMIT 1000";

$result = db_query($sql);

foreach ($result AS $row) {
  try {
    commerce_line_item_delete($row->line_item_id);
    echo "Deleted line item ".$row->line_item_id." for order ".$row->order_id.".\n";
  } catch (Exception $e) {
    echo "Line item ".$row->line_item_id." for order ".$row->order_id." could not be deleted due to an Exception.\n";
  }
}

Per

That script in #33 works great. I do feel like there should be a Cron event that triggers this line item cleanup every week or something (configurable). Right now it is wreaking havoc on our product reports, though there is a patch and issue for that at #2233739: Products Sold doesn't match actual products sold by sku. Fixing the root cause would be ideal, but at this point I don't know exactly what the culprit is. Lots of Commerce systems manipulate line items (the Discounts module especially!) so it's possible one of these is not good at tracking / fixing relationships to orders.

My personal feeling is that authors for contrib modules for Drupal Commerce are writing very basic code to take resposibility for manipulating line items. It's very easy as a developer to make a small mistake removing the line item reference but not actually the line item itself.

In the short term, we are using this scripts to clean discount and shipping line items in the db.

In the long run, let's collaborate around a design pattern where DC helps the contrib developer to work with line items in a more unified way.

I used code from #33 as follows:

1. Add code to new file called delete_orphaned_line_items.php
2. Execute using drush: drush @sitealias scr delete_orphaned_line_items.php

And then, once tested, added as a cron job.

Certainly helps reduce the size of the massive commerce_line_item table! Some orders have every single product included as a orphan line item...

Here's a patch that follows @joelpittet's idea of firing during order save, but also makes sure field data of the orphaned line item gets deleted too.

One of the primary things that causes this is adding a line item to the form via line item manager (admin orders), but never saving the form. Unfortunately, my patch doesn't address that edge case if the order is never saved again.

Thanks @andyg5000 Some review notes

1. +++ b/modules/line_item/commerce_line_item.module
   @@ -1590,3 +1590,30 @@ function commerce_line_item_field_views_data($field) {
   +      $ine_item_ids = array();
   

   typo and if you expect exceptions should they be logged?

2. +++ b/modules/line_item/commerce_line_item.module
   @@ -1590,3 +1590,30 @@ function commerce_line_item_field_views_data($field) {
   +    foreach ((array) $orphaned_line_items as $orphaned_line_item) {
   +      commerce_line_item_delete($orphaned_line_item);
   +    }
   

   I think there is a commerce_line_item_delete_multiple() that could work here too.

Hai,
Thanks for the patch. Update the patch by refer #39 and thanks for the suggestion.

Wellme, thanks for the update. The multiple doesn't need the foreach loop too, sorry if I wasn't clear

This approach (that I started), doesn't resolve the issue if the order is never saved again. Should we implement this and a cron routine to prune the line items or just the cron routine?

Cron job ones a year! :) Some time in a the spring would be apt;) (Sarcasm aside, yes a cron job sounds good)

Here's an updated patch that includes feedback from @joelpittet and a cron routine to delete orphaned line items that are greater than a day old. The date filter allows us to not delete line items of an admin order that someone may be working on during a cron run.

Arg! forgot to wrap commerce_line_item_delete_multiple with a !empty check. Also, added comments.

2 updates here:

You can't call NOT IN on a db_query if the array is empty. This update conditionally adds that clause.

Ryan suggested an option to turn the cron job on/off on the order settings page.

Damn, deleting line items during hook_commerce_order_update() causes issues with add to cart forms due to the order refresh routine and save() being called several times. For our purposes, I think we'll just go with the cron clean up routine. Setting to needs work and uploading the patch we're using.

This might be a bad idea because other modules use commerce line items too. See Commerce RMA which uses line items not associated with orders.

@Neo13: That sounds like bad design. If an RMA is generated for an order, shouldn't a line item be created for that order? Sounds like something that Commerce RMA should consider factoring in.

Or, failing that, if Commerce RMA creates an "RMA" line item type, then perhaps the patch in this module could offer another configuration option to allow store admins to select which type of line items should be pruned.

@torgosPizza: You are probably right. The issue here is that the return (RMA) line items have order_id = 0. So all of them would be deleted. At the moment here is a quick and dirty fix I did - first I delete all orphaned line_items with orde_id > 0 and then all orphaned rma line items.

    $query = 'SELECT line_item_id FROM {commerce_line_item} li
    LEFT JOIN {field_data_commerce_line_items} fdcli
    ON li.line_item_id = fdcli.commerce_line_items_line_item_id
    WHERE fdcli.commerce_line_items_line_item_id IS NULL
    AND li.created < :timestamp
    AND li.order_id > 0
    ORDER BY li.order_id DESC
    LIMIT 100';

    $line_item_ids = db_query($query, array(':timestamp' => strtotime('-1 day')))->fetchAllKeyed(0, 0);
    if (!empty($line_item_ids)) {
      commerce_line_item_delete_multiple($line_item_ids);
    }
  
    $query_returns = 'SELECT line_item_id FROM {commerce_line_item} li
    LEFT JOIN {field_data_commerce_return_line_items} fdcli
    ON li.line_item_id = fdcli.commerce_return_line_items_line_item_id
    WHERE fdcli.commerce_return_line_items_line_item_id IS NULL
    AND li.created < :timestamp
    AND li.order_id = 0
    LIMIT 100';

    $line_item_ids_returns = db_query($query_returns, array(':timestamp' => strtotime('-1 day')))->fetchAllKeyed(0, 0);
    if (!empty($line_item_ids_returns)) {
      commerce_line_item_delete_multiple($line_item_ids_returns);
    }
  

I don't understand how the patch, by using commerce_line_item_delete_multiple cleans up the commerce_line_item table. The reason we find ourselves in this situation, I believe, is some custom code which runs commerce_line_item_delete (not multiple), but does delete the lines from field_data_commerce_line_items and field_revision_commerce_line_items tables but not the commerce_line_items table. Isn't there a problem with commerce_line_item_delete if it calling that programmatically doesn't delete the lines from the main commerce_line_itemstable as well? Is there something else that needs to be done after calling commerce_line_item_delete, like re-saving the order?

We will likely be implementing #52 to clean up records for now, and since we don't have any other line item types other than those on orders, but it would also be nice if views had a way to ignore line items which aren't attached to the current revision of the order. Perhaps that's something for which it's worth creating another issue, although it wouldn't be necessary if line items were being properly deleted from the DB...

it would also be nice if views had a way to ignore line items which aren't attached to the current revision of the order.

There actually is - add the Filter Commerce Line Item: Line item is referenced by its order to your View, and it will exclude any orphaned line items. I think that filter was added as a result of this issue (but I could be wrong; it's been a while).

Brilliant! We'll just add that to the view for now and look at implementing #52 to clean up periodically. Still don't get how the patch is supposed to work nor why this is still happening 10 years later...seems like it may come from commerce_entity_reference_delete but i can't really tell.