Magento2: Adding 2 configurable products to cart with custom options causes Integrity constraint violation

[pguedesbr]

Preconditions

1. Magento 2.1.1
2. PHP 5.6.25
3. MySQL 5.6.23

Steps to reproduce

1. Have a configurable product like a t-shirt with 2 attributes (size/color)
2. Create a controller to add 2 of the same product to the cart, except each one will have a custom additional option, resulting in 2 different cart items
3. In the loop to add these products, insert additional options to the product on the fly

    $storeId = $this->_objectManager->get('Magento\Store\Model\StoreManagerInterface')->getStore()->getId();
    $cart = $this->_objectManager->get('\Magento\Checkout\Model\Cart')->getStore()->getId();

    $productId = 115; // Configurable Product

    $colorAttributeId = 90;
    $color = 10; // white

    $sizeAttributeId = 135;
    $size = 13; // small

    $customOptionValues = [
        'print_style_1', 
        'print_style_2',
    ];

    foreach ($customOptionValues as $customOptionValue) {
        $product = $this->_objectManager->create('Magento\Catalog\Model\Product')->setStoreId($storeId)->load($productId);

        // prepare buyRequest
        $buyRequest = new \Magento\Framework\DataObject();
        $buyRequest->setData([
            'qty' => 1,
            'super_attribute' => [
                $colorAttributeId => $color,
                $sizeAttributeId => $size,
            ],
        ]);

        $additionalOptions = array();
        if ($originalAdditionalOptions = $product->getCustomOption('additional_options'))
        {
            $additionalOptions = (array) unserialize($originalAdditionalOptions->getValue());
        }
        $additionalOptions['print_style'] = [
            'label' => 'Print Style',
            'value' => $customOptionValue,
        ];

        // add the additional options array with the option code additional_options
        $product->addCustomOption('additional_options', serialize($additionalOptions));

        $cart->addProduct($product, $buyRequest);
    }
    $cart->save();
    $cart->getQuote()->setTotalsCollectedFlag(false)->collectTotals()->save();

Expected result

1. Upon $cart->save() there should be 2 products in the cart, each one with the same configurable attribute options, but with distinct custom additional options (Print style)

Actual result

1. Whe cart is saved, an exception is thrown regarding the quote_item_option INSERT query.
2. Notice the insert query for the item option is missing the 'item_id' column

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`quote_item_option`, CONSTRAINT `QUOTE_ITEM_OPTION_ITEM_ID_QUOTE_ITEM_ITEM_ID` FOREIGN KEY (`item_id`) REFERENCES `quote_item` (`item_id`) ON DELETE CASCADE), query was: INSERT INTO `quote_item_option` (`product_id`, `code`, `value`) VALUES (?, ?, ?)

1. If you add one product with custom option A in one HTTP request, and then do another HTTP request to add the same product with custom option B, there will be no errors. This only happens when products are added to cart in the same HTTP request.

For 2.1.10 version and later it can be reproduced with the little different scenario:

$storeId = $this->_objectManager->get('Magento\Store\Model\StoreManagerInterface')->getStore()->getId();

        /* @var \Magento\Checkout\Model\Cart $cart */
        $cart = $this->_objectManager->get('\Magento\Checkout\Model\Cart');

        $productId = 67; // Configurable Product

        $colorAttributeId = 93;
        $color = 49; // white

        $sizeAttributeId = 141;
        $size = 167; // small

        $customOptionId = 1;
        $customOptionValues = [
            '1',
            '2',
        ];

        foreach ($customOptionValues as $customOptionValue) {
            /* @var \Magento\Catalog\Model\Product $product */
            $product = $this->_objectManager->create('Magento\Catalog\Model\Product')->setStoreId($storeId)->load($productId);

            // prepare buyRequest
            $buyRequest = new \Magento\Framework\DataObject();
            $buyRequest->setData([
                'qty' => 1,
                'super_attribute' => [
                    $sizeAttributeId => $size,
                    $colorAttributeId => $color,
                ],
                'options' => [
                    $customOptionId => $customOptionValue,
                ]
            ]);

            $cart->addProduct($product, $buyRequest);
        }
        $cart->save();
        $cart->getQuote()->setTotalsCollectedFlag(false)->collectTotals()->save();

[pguedesbr]

Is there a workaroud for this issue in order to add consecutive products (with custom options) to the cart? Maybe some way of reseting the cart before adding the second product?

I'm almost sure the error is due to some mixing/garbage information from the first added product affecting the next one to be added.

[pguedesbr]

This is far from acceptable but since there's no update or aknowledgement of this issue, here is my workaround to insert more than one product with custom options to the cart:

1. On the first submit to the custom controller, set up a queue variable in the checkout session (or any other session of your choice). Also save the referer url.
2. Add the first product to the cart and then remove it from the queue var
3. Redirect to the same controller action url
4. Upon not receiving any post data, just proccess the queue again and keep redirecting until queue is empty
5. Finally, redirect back to the cart or to the referer url saved on the first request

[pguedesbr]

Hi @veloraven

Did you guys have the chance to take a look into this issue? This is still present in 2.1.3
Were you able to reproduce it?

The sample code in the OP uses object manager to make it easier to copy/reproduce. The actual code I'm using uses DI as best practices suggest.

[eInyzant]

Problem still present in 2.1.4... :-(

[eInyzant]

I found a quick fix. The problem is the item is not set correctly on item option.
I will try to track this bug deeper, but at least for the moment instead of looping and redirecting in your controller you can create some plugin on :

Magento\Quote\Model\Quote\Item::saveItemOption (around line 732 : magento 2.1.4)

     /**
       * Save item options
       *
       * @return $this
       */
      public function saveItemOptions()
      {
          foreach ($this->_options as $index => $option) {
              if ($option->isDeleted()) {
                  $option->delete();
                  unset($this->_options[$index]);
                  unset($this->_optionsByCode[$option->getCode()]);
              } else {
                  // Add this code to test if item is set or not
                  if (!$option->getItem() || !$option->getItem()->getId()) {
                      $option->setItem($this);
                  }
                  $option->save();
              }
          }

          $this->_flagOptionsSaved = true;
          // Report to watchers that options were saved

          return $this;
      }

There must be an issue during the process of addProduct to the quote.

[vkranjith]

I also faced similar issue with few customized product. In my case the add to cart with custom options work fine. But I get the same exact issue when I reorder the same customized product.

As @eInyzant suggested I can add option to the quote item by using plugin on saveItemOptions(), but it doesn't help in my case as I need to differentiate between the items (customized and non-customized) before Magento adds the product to the quote at the time of reorder. For this I have to use addCustomOption() on the product instead of addOption() on the quote item. But this throws the foreign key constraint error.

Any suggestions on this why it's happening on reorder? We are stuck with the development due to this issue right now. Any fix or suggestion would be a life saver for us now.

Edit
We face this issue on both 2.1.3 and 2.1.5.

[stansm]

This is caused by cache corruption.
Product::_customOptions are overwritten in Quote::addProduct() and on subsequent calls options with quote item set are used.

The easiest fix is to clone a product instance in _prepareProduct() (Magento\ConfigurableProduct\Model\Product\Type\Configurable:958):

if ($subProduct) {
    $subProduct = $this->getProductByAttributes($attributes, $product);
}

change to:

if ($subProduct) {
    $subProduct = clone $this->getProductByAttributes($attributes, $product);
}

Not sure if there is some better solution for the problem. Let me know if more details are needed.

[SamantaPM]

Any update on this issue? In which version will be resolved?

[mbrzuzy]

Also running into this problem. Need to add the product in two separate requests. Above solutions did not fix it.

[magento-engcom-team]

@pguedesbr, thank you for your report.
We've created internal ticket(s) MAGETWO-84526 to track progress on the issue.

[ghost]

[Magento 2.2.0]
I found a quick fix in Magento\Checkout\Controller\Cart\Add::_initProduct()

Before:
return $this->productRepository->getById($productId, false, $storeId);
After:
return $this->productRepository->getById($productId, false, $storeId, true);

In fact I have a plugin (aroundExecute) on this controller with own _initProduct method. The plugin process each product except the last one (the last one is processed by Magento\Checkout\Controller\Cart\Add). I copied most of logic (remember to do not save a cart in a plugin) from Magento\Checkout\Controller\Cart\Add::execute and before return $proceed(); I set proper request params for last item.

[kllee824]

More understanding the custom option issue, I have a problem, the configurable product's custom options are missing when click 'add to cart' button. But only a few products are missing, we need deleted the option and re-created them to resume the function. But we have found the issue still occurs after the day but a different configurable product.

Thanks
Kalun

[codeavoid]

Have any found the solution i am also facing the same issue ?

thanks

[codex5]

If you debug inside saveItemOptions() you can see that $option has item_id equal to null.
And when you save option, the item_id was missing.
The solution is add a plugin to set item_id equal to current quoteItem

[elioermini]

@magento-engcom-team could you please check the status of MAGETWO-84526 ? Thanks

[vflirt]

Hi,
we are facing the exact same problem on 2.2.5.
The scenario:
1) add configurable product with custom option value1 into cart
2) add same configuration (same super attributes) with custom option value2 into cart
3) create order -> at this step all is good because we are adding 1 product at a time
now the fun step:
4) go to the order and reorder
inspect the quote table and more specific quote_item_option table and see how for the second simple item there are no options

If anyone has exact code/patch for a workaround would be great.

[sdzhepa]

Hello @pguedesbr @vflirt @elioermini

Thank you for contribution and collaboration!

The initially described issue was fixed in the scope of #13036 PR by @vinayshah
The changes have been delivered into develop branch

[jmoralescedw]

Hi!, Some solution? Same problem in magento 2.2.5.

[aliomattux]

Understanding this issue is closed, however i'd like to comment that the fix for this issue may have introduced a different problem.

I have a very similar scenario where I add a configurable product to the cart via code twice. The same product, but with different lengths. I noticed that when adding the second product, it used the options of the first product. I triple checked my code. It seems like a cache issue with a product instance. Using the above solution - cloning a product instance fixes the problem for me and adding the same product twice to cart shows both products.