Integrity constraint violation - Duplicate entry

[outline4]

Support Request

Hi, It's been a while since I've used sprig in combination with craft commerce.

I've hit a wall with the most basic action I would like to perform.

I want to make the add-to-cart button reactive.

This is the original and well working code:

<form method="POST" accept-charset="UTF-8">
	{{ csrfInput() }}
	{{ actionInput('commerce/cart/update-cart') }}
	{{ hiddenInput('qty', 1) }}
	{{ hiddenInput('shippingMethod', 'ch') }}
	{{ hiddenInput('purchasableId', variant.id) }}

	<div class="field">
		<input type="submit" value="{{ "In den Warenkorb"|t }}" class="button" />
	</div>
</form>

so I've put this code within a component like this:

{{ sprig('_sprig/add_to_cart', { productId: product.id, purchasableId: variant.id }) }}

the component looks like this:

{% set product = craft.products.id(productId).one() %}
{% set addedToCart = success is defined ? true : false %}

<form sprig s-method="post" s-action="commerce/cart/update-cart">
	{{ hiddenInput('qty', 1) }}
	{{ hiddenInput('shippingMethod', 'ch') }}
	{{ hiddenInput('purchasableId', purchasableId) }}

	<div class="field">
		<input type="submit" value="{{ "In den Warenkorb"|t }}" class="button" />
	</div>
</form>	
	
{% if addedToCart %}
	<script>
		htmx.trigger('#mini-cart', 'refresh');
	</script>
{% endif %}

basically I've just eliminated {{ csrfInput() }} and {{ actionInput('commerce/cart/update-cart') }} and added sprig and the corresponding s-action

I get an error 500 saying:

Integrity constraint violation – yii\db\IntegrityException

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '15240990b0121e9bf6812c3fbf927ccf' for key 'craft_idx_zccrurnkljlvdkbflcexnuqnhqeijrwxibox'
The SQL being executed was: INSERT INTO craft_commerce_orders (id, storeId, number, reference, itemTotal, itemSubtotal, email, orderCompletedEmail, isCompleted, dateOrdered, datePaid, dateAuthorized, shippingMethodHandle, shippingMethodName, paymentSourceId, gatewayId, orderStatusId, couponCode, total, totalPrice, totalPaid, totalDiscount, totalShippingCost, totalTax, totalTaxIncluded, totalQty, totalWeight, currency, lastIp, orderLanguage, orderSiteId, origin, paymentCurrency, customerId, registerUserOnOrderComplete, saveBillingAddressOnOrderComplete, saveShippingAddressOnOrderComplete, returnUrl, cancelUrl, message, paidStatus, recalculationMode, sourceShippingAddressId, sourceBillingAddressId, dateUpdated, dateCreated, shippingAddressId, billingAddressId, uid) VALUES (969, 2, '15240990b0121e9bf6812c3fbf927ccf', NULL, '0', '0', '[email protected]', NULL, 0, NULL, NULL, NULL, 'ch', 'Spedition CH', NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 0, '0', 'CHF', '169.155.235.224', 'de-CH', 1, 'web', 'CHF', 802, 0, 0, 0, NULL, NULL, NULL, 'unpaid', 'all', 828, 828, '2025-01-16 18:33:13', '2025-01-16 18:33:13', 967, 968, '896a32ce-ab07-49ab-940f-531971db7af9')

What am I missing?
It could be very obvious...

Any help appreciated!

-Stefan

Systeminformationen

PHP-Version: 8.3.14
Betriebssystem: Linux 5.14.0-427.42.1.el9_4.x86_64
Datenbanktreiber & Version: MariaDB 10.11.10
Image-Treiber & Version: Imagick 3.7.0 (ImageMagick 7.1.1-39)
Craft-Version: Craft Pro 5.5.9
Yii-Version: 2.0.51
Twig-Version: v3.14.2
Guzzle-Version: 7.9.2

---

Plugins

Plugin	Version
CKEditor	4.4.0
CP Site Icons	3.0.2
Craft Commerce	5.2.11
Dumper	5.0.1
GeoMate	3.0.0
Language Redirector	3.1.3
Mollie for Craft Commerce	4.2.0
Preparse	3.0.0-alpha.2
Sherlock	5.1.0
Sprig	3.5.1
Stripe for Craft Commerce	5.0.4.3

Plugin Version

3.5.1

[bencroker]

That is clearly a Commerce error. Does it work again when you revert sprigifying the form? I'm not seeing anything immediately obvious that might cause the error.

[outline4]

Ok, thanks, I just wanted to be sure that I haven't committed a basic error.

Also I've made a small component to test and it all worked out fine:

{{ sprig('_sprig/_test', { count: 1 }) }}

Component:

<button sprig s-val:count="{{ count + 1 }}" class="btn submit">
	Re-render
</button>
<p><em>Component re-rendered {{ count }} times</em></p>

Thanks for having looked into it.

[outline4]

That is clearly a Commerce error. Does it work again when you revert sprigifying the form? I'm not seeing anything immediately obvious that might cause the error.

Yes, it works like it should when I revert it to normal.

[nfourtythree]

Hi @outline4

Thank you for writing into support to notify us about this one.

Catching up with the comments above, am I right in understanding that when this is just a normal form without using Sprig and just using the native Commerce functionality everything works correctly?

Thanks!

[outline4]

Catching up with the comments above, am I right in understanding that when this is just a normal form without using Sprig and just using the native Commerce functionality everything works correctly?

A normal form works correctly.
The form with sprig generates an error.

[outline4]

I've disabled all plugins but these:

Still, the error persists.

[outline4]

To be a 100% sure, I also deactivated "Language Redirector".

Still: the error persists

[nfourtythree]

Catching up with the comments above, am I right in understanding that when this is just a normal form without using Sprig and just using the native Commerce functionality everything works correctly?

A normal form works correctly. The form with sprig generates an error.

I think if this is the case we need to hand it back over to @bencroker to see if he can decipher what might be happening. Would be interesting to know what requests are being sent Commerce's way based on the component you came up with.

[outline4]

Hi @bencroker. Are you able to test this on a commerce 5 site?

[bencroker]

Yes, I’ll do some testing today and let you know what I find.

[bencroker]

@outline4 could you please test whether this is still an issue using Sprig 3.5.2?

[outline4]

I just did a test. Sprig 3.5.2 generates the same error.

[bencroker]

I tested your code above and it works without any issues – the product is added to the cart. Can you please test using different product and variant IDs.

[outline4]

Ok, so I guess it has something to do with sites. Sites have caused other problems with craft commerce.
Particularly because my shop is not on the default site.

Thanks for testing!

[bencroker]

Sure thing. If it turns out to be Sprig related, please let me know.

[nfourtythree]

Hi

This sounds like potentially the current site is being lost during the interaction with Sprig.

It could be worth trying to add s-headers='{"X-Craft-Site": "{{ currentSite.handle}}"}' to your Sprig component (assuming Sprig renders the component in the site it is requested in). This should ensure the update cart request is using the correct site when processing.

Hope this helps, thanks!

[outline4]

@nfourtythree you're a live saver! This did the trick!

@bencroker is this the official way or a bug?

[outline4]

Now I try to update the #mini-cart component, and it's failing me.

In the _add_to_cart component I have this:

{% if addedToCart %}
	<script>
		htmx.trigger(htmx.find('#mini-cart'), 'refresh');
	</script>
{% endif %}

(I did also try htmx.trigger('#mini-cart', 'refresh'))

I call the mini-cart component the following way:

{{sprig(
	'_sprig/_mini_cart',
	{_cartPath: cartPath, _sectionHandle: entry.section.handle },
	{ 'id': 'mini-cart', 's-headers': '{ "X-Craft-Site" : "deCH" }' }
)}}

I've temporarily hard coded the Site for debugging purposes (I tried with and without s-headers).

The mini-cart component is the following:

{% set cart = craft.commerce.carts.cart %}
{% set totalQty = totalQty ?? cart.totalQty %}

<a href="{{ _cartPath }}" id="header-warenkorb"{% if totalQty > 0 or _sectionHandle == "warenkorb" %} class="active"{% endif %} title="{{ "Warenkorb"|t }}">
	{% if totalQty > 0 %}
		<div id="number" class="{% if totalQty > 9 and totalQty <= 99 %}more_than_9{% elseif totalQty > 99 %}more_than_99{% endif %}">{{ totalQty }}</div>
	{% endif %}
</a>

But it's not refreshing the component.
Could this be also related to sites?

How can I debug it further?

Everything is on the latest version.

[bencroker]

The site ID should be passed through as part of the config param. You should see it encoded in the URL, something like this: sprig%3Aconfig=24cc7...{"siteId"%3A1%...

The issue with refreshing the cart component is likely related, I’d suggest figuring out the site ID issue first.

FYI you can use the Twig tags Sprig provides.

{% if addedToCart %}
    {% do sprig.triggerRefresh('#mini-cart') %}
{% endif %}

[outline4]

ok, thanks.

I've added the following:

{{ sprig('_sprig/_add_to_cart', { productId: product.id, purchasableId: variant.id }, { id: 'add-to-cart', siteId: currentSite.id }) }}

with the following output:

data-hx-vals='{"sprig:config":"4122febb4b128ca6c3161bff003521393bf67fbbd4d2e591477c48f987bdc762{\u0022id\u0022:\u0022add-to-cart\u0022,\u0022siteId\u0022:1,\u0022template\u0022:\u0022_sprig\\\/_add_to_cart\u0022,\u0022variables\u0022:{\u0022productId\u0022:85,\u0022purchasableId\u0022:214}}"}'

and for the mini-cart component:

{{sprig(
	'_sprig/_mini_cart',
	{_cartPath: cartPath, _sectionHandle: entry.section.handle },
	{ id: 'mini-cart', siteId: currentSite.id }
)}}

with this output:

data-hx-vals='{"sprig:config":"1b15573d94d05f9f9acafd0c316e13a9ba36191f022d8f3f0fc084838d477075{\u0022id\u0022:\u0022mini-cart\u0022,\u0022siteId\u0022:1,\u0022template\u0022:\u0022_sprig\\\/_mini_cart\u0022,\u0022variables\u0022:{\u0022_cartPath\u0022:\u0022https:\\\/\\\/www.mysite.shop\\\/de-ch\\\/warenkorb\\\/schritt-1\u0022,\u0022_sectionHandle\u0022:\u0022pages\u0022}}"}'

still: no refresh...

Why is this so hard?
Without multiple sites it's a breeze! I can remember leaving the room feeling like a superhero, whereas now I feel a little stupid...

hope I can get this sorted somehow! I love sprig! Especially for online shops!

Edit: added the siteId to the component attributes
Edit 2: I think the siteId is transmitted even without specifying it.

[bencroker]

still: no refresh...

You haven’t really changed anything. My assumption that the add to cart action is still failing?

[outline4]

I've added the siteId like you indicated.

The add-to-cart is working, I get a status 200 and an output.

The refresh for #mini-cart is not working.

I have the exact same code on a commerce 4 website with only one shop and one site, and all is working out well.

Adding s-headers='{"X-Craft-Site": "{{ currentSite.handle}}"}' was the only thing that changed something.

Where would I need to put this in the #mini-cart component?

Maybe you see an obvious error here

[bencroker]

I've added the siteId like you indicated.

What I meant was that the site ID should already be passed through as part of the config param. I see that it is from your previous comment.

When I add a product to the cart at https://www.ingwerer.shop/de-ch/ it appears to work. It just is not refreshing the cart. Correct?

Can you show me the Sprig component as it is now?

[outline4]

This is the mini-cart component:

{#
	Set the cart...
#}
{% set cart = craft.commerce.carts.cart %}
{% set totalQty = totalQty ?? cart.totalQty %}

<a href="{{ _cartPath }}" id="header-warenkorb"{% if totalQty > 0 or _sectionHandle == "warenkorb" %} class="active"{% endif %} title="{{ "Warenkorb"|t }}">
	{% if totalQty > 0 %}
		<div id="number" class="{% if totalQty > 9 and totalQty <= 99 %}more_than_9{% elseif totalQty > 99 %}more_than_99{% endif %}">{{ totalQty }}</div>
	{% endif %}
</a>

This is the add-to-cart component:

{#

	This component is defined within shop/_products and
	productId and purchasableId are set
	====================================================================

#}
{% set product = craft.products.id(productId).one() %}
{% set addedToCart = success is defined ? true : false %}

<form sprig s-method="post" s-headers='{"X-Craft-Site": "{{ currentSite.handle}}"}' s-action="commerce/cart/update-cart">
	{{ hiddenInput('qty', 1) }}
	{{ hiddenInput('shippingMethod', 'ch') }}
	{{ hiddenInput('purchasableId', purchasableId) }}

	<div class="field">
		<input type="submit" value="{{ "In den Warenkorb"|t }}" class="button" />
	</div>
</form>


{% if addedToCart %}
	{% do sprig.triggerRefresh('#mini-cart') %}
{% endif %}

note that without s-headers='{"X-Craft-Site": "{{ currentSite.handle}}"}'the add-to-cart is not working.

so I guess something similar needs to be transmitted to refresh the #mini-cart component.

[bencroker]

As per the docs, can you check for success as follows?

{% if sprig.isSuccess %}
  {% do sprig.triggerRefresh('#mini-cart') %}
{% elseif sprig.isError %}
  {{ sprig.message }}
{% endif %}

[outline4]

I've added this: still the same.

I was pretty sure that it already was a success since the product is added.

What else could I be watching?

[bencroker]

That seems to have helped. The cart component is now being refreshed. The result is an empty cart, but that is likely to do with the site ID issue.

[outline4]

I've deleted the siteId config param.

I don't see a change in behaviour...

I click the add-to-cart button. The success message is displayed. The product is added to the cart. I don't see a change in the mini-cart component until I manually refresh the site.

[outline4]

I am sorry: I see a different output:

[bencroker]

What is the error? You may have to find it in the logs.

[outline4]

the error 500 is the same as before:

"Integrity constraint violation – yii\db\IntegrityException
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '15240990b0121e9bf6812c3fbf927ccf' for key 'craft_idx_zccrurnkljlvdkbflcexnuqnhqeijrwxibox'"

ok, we're getting somewhere!

[outline4]

my guess is that you need to incorporate the s-headers within {% do sprig.triggerRefresh('#mini-cart') %}

Edit: but I don't know how?

[bencroker]

I’ll have to look into this. This is the first report I’ve had of such an issue, so it may be specific to Commerce or to your setup.

[outline4]

I am really sorry that you have to go through all of this and really glad that you are trying to help!

Can I send you something per email? DB, templates, composer.json?

[outline4]

There's some things that might be worthy of knowing:

1. the shop isn't on the primary site.
2. the primary site has no entry uri format
3. there's multiple stores and multiple sites.

this has also lead to some commerce bugs.
it's been a bumpy ride so far!

[bencroker]

I am really sorry that you have to go through all of this and really glad that you are trying to help!
Can I send you something per email? DB, templates, composer.json?

I’m always happy to help, just please do keep in mind that Sprig is a free plugin. If you need me to prioritise this and have some budget for it, please get in touch at [email protected]