Orphans in commerce_order_item table

Could it be caused by modifying the order in the admin UI somehow? Otherwise, maybe some custom code (a cron job?) that only kicks in occasionally?

Closing this issue for now, since we cannot reproduce it.

I do wonder if it was caused by a buggy admin form. Commerce 2.14 fixed some bugs where inline forms were auto-saved when another element was triggered, I wonder if that was the cause.

Hi @bojanz,

I'm having the same issue as @carstenG described.

I can't reproduce it intentionally, but what I've discovered about this is that the Commerce module has two ways to reference items into orders: There is this "foreign key" in commerce_order_item table, that is used on the view witch presents the order itself: /admin/commerce/orders/ORDER_ID And there is this other relation table commerce_order__order_items, that's used to load the order edit page /admin/commerce/orders/ORDER_ID/edit.

The inconsistency is between the order view page and the order edit page.

As I needed a quick fix for that on the project I'm working, I removed the inconsistent data from commerce_order_item table. It was necessary mainly because with this inconsistency, the Total on the view page seems to be wrongly calculated, but in fact it's the same as the value payed by the client.

This issue doesn't seem to be harmful as the correct values and logic are working perfectly, but it's causing misunderstood for the administrators (they keep complaining about the issue) .

Thanks a lot for this module!

Unfortunately, I still have no way to reproduce, so a fix from me is not possible.

I'm also experiencing this issue. I also tried to reproduce manually and was unable to do so, but I have my first order in the system that is displaying the exact scenario referenced here, namely an item exists in the commerce_order_item table which I see from the log was added to, then removed from the cart. This item does not exist in the commerce_order__order_items table.

I'm not sure how to go about debugging this one either. I can't find any documentation about why these two tables exist which seem to serve the same purpose.

Hi,

Same issue here. I can't reproduce it either but also happens every twenty orders or so. The timestamps aren't always the same.

The 2 major problems with this issue are:

1. Ghost purchased order items displayed in the admin order page and in the order receipt
2. Views data that displays order items is incorrect

For others having the same issue until this get sorted out.

Every time the cron runs it removes those orphan rows:

/**
 * Implements hook_cron().
 */
function mymodule_cron() {

  //Dirty fix for #3069966: Orphans in commerce_order_item table
  $database = \Drupal::database();
  $database->query("DELETE FROM {commerce_order_item} WHERE order_item_id in (select * from (SELECT c.order_item_id FROM {commerce_order_item} c JOIN {commerce_order_item} ci ON ci.order_id = c.order_id AND ci.purchased_entity = c.purchased_entity AND c.uuid != ci.uuid LEFT JOIN {commerce_order__order_items} co ON c.order_item_id = co.order_items_target_id WHERE co.order_items_target_id is NULL GROUP BY c.order_item_id) as t);");

}

added #3119159: commerce_order_item_table needs to filter out orphan order items as a related issue

I was having the same issue of line items being orphaned when I delete using $order->removeItem(). The only way to fix this was to use the delete method on the item itself.

foreach ($order->getItems() as $item) {
  if ($item->getPurchasedEntity()->bundle() == $type || $type == '') {
    $order->removeItem($item);
    $item->delete();
  }
}

I'm also having this issue when the user removes order items from the cart. It only happens sometimes, very rarely, like 1 in 50 orders.

I ran into this issue today. Order came through and the commerce_order_item_table had 30+ items in the table, then the edit screen only had the one item actually ordered.

I might be able to provide some context of how it came about, too, in case that helps figure out the issue. Adding that cron hook to my custom module didn't solve my issue either, unfortunately. Was wondering if it was supposed to fix all the previously problematic views displays.

I was doing some random testing on adding items to carts as anonymous users very quickly. At one point there were 3 or 4 anonymous carts open. Then, I went in and just manually deleted the carts through /admin/commerce/orders/carts by bulk deleting them when I was finished testing a few things. I haven't confirmed this was the actual cause of the issue but what is showing up in the commerce_order_item_table looks suspiciously like the items I was randomly adding to carts and the order that was displaying weird in commerce_order_item_table was the very next order processed.

Hopefully helps to get to the bottom of this. Cannot figure out how to fix the table for my client.

Thanks!

I also have this issue but cannot reproduce. It happened during the removal of order items. Not all were removed from table commerce_order_item.

I also have this issue. I think in some cases commerce_combine_carts is causing this. It is merging the carts and creates items that the customer never pays for, but show up in the backend.

Recently I discovered orders that didn't combine also have this issue.

I think those are two separate issues, event if the resulting problems are similar. When running the query in #9, the orders that were not combined, get fixed. The others stay the way they are.

Reproducing this issue is really hard. I also have a very low percentage of orders that have this issue.

Bumping priority, since my client currently ships items for which the customers didn't pay for :)

I'll be looking into a quick fix in #3119159: commerce_order_item_table needs to filter out orphan order items for the contrib weekend.

I found a workaround an posted it here: https://www.drupal.org/project/commerce/issues/3119159#comment-13981362

The related issue #3119159: commerce_order_item_table needs to filter out orphan order items has been resolved, can we close this one? Does updating commerce and updating your views config as noted in https://www.drupal.org/node/3197301 solve this for you?

Having the same issue with random orphaned products in the 'commerce_order_item' table.

The query in #9 did not remove these orphans.

Surely a simpler query in one that selects the rows from the 'commerce_order_item' table that are not part of any order in the 'commerce_order' table. Just wondering why @introfini didn't do that instead. Here's the query:

$database->query("DELETE {commerce_order_item} FROM {commerce_order_item} LEFT JOIN {commerce_order} ON {commerce_order_item}.order_id = {commerce_order}.order_id WHERE {commerce_order}.order_id IS NULL;");

Is it ok to delete these orphans like this or is it possible these rows are still needed even though they don't belong to any order?

I may have misread this issue, un-postponing for now. Seems like the fix to the commerce_order_item_table "component" should fix this for some, but perhaps not for everyone.

I'm not sure if it's the same problem in Commerce Ajax ATC (Add to cart) ( https://www.drupal.org/project/commerce_ajax_atc/issues/3471969 ), since I got similar problem with that module.

I also just tried Ajax Add to Cart module ( https://www.drupal.org/project/ajax_add_to_cart ). Same problem.

If you click "add to cart" quickly enough, you will run into this problem easily for both modules.

In my case, I just found that I have some ECA model that cause this problem. So, it's not problem with Commerce Core or any Ajax cart modules.