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

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)

image

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();

About this issue

  • Original URL
  • State: closed
  • Created 8 years ago
  • Reactions: 8
  • Comments: 19 (5 by maintainers)

Most upvoted comments

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.

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

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.