search

craftcms / commerce

Fully integrated ecommerce for Craft CMS.
https://craftcms.com/commerce
Other
219 stars 170 forks source link

[5.x]: SQL Integrity Constraint Violation #3501

Closed AndrewMac closed 4 months ago

AndrewMac commented 4 months ago

What happened?

Description

I am trying to set up a multi-store (local dev at the moment), but I keep running into an 'integrity constraint violation'. It looks like Commerce might be trying to write the same value into the 'number' column when creating a new row for a different 'storeId'.

Steps to reproduce

  1. I load one site, which works, then when I load a second site (pointing to a different store) I get the error (below, at the end of this post).
  2. I then look in the 'commerce_orders' table in the database: there is already a row with the 'number' value mentioned in the error, with the storeId of the first store.
  3. I have to delete that row to get the second site working again.
  4. But then I get the same error on the first site.

Expected behavior

No error ;-)

Actual behavior

Error ;-)

This is the report I get:

Error info here:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '95e4d14310fce26c0a6237d0dd046c67' for key 'commerce_orders.craft_commerce_orders_number_unq_idx' The SQL being executed was: INSERT INTO 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 (33285, 2, '95e4d14310fce26c0a6237d0dd046c67', NULL, '0', '0', 'example@example.com', NULL, 0, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 0, '0', 'THB', '192.168.228.1', 'en', 1, 'web', 'THB', 1, 0, 0, 0, NULL, NULL, NULL, 'unpaid', 'all', 30305, 30305, '2024-05-16 01:38:34', '2024-05-16 01:38:34', 33283, 33284, 'd8c9f2c6-97b8-46f2-8aff-9f650b0d1b00')

Stack trace below:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '95e4d14310fce26c0a6237d0dd046c67' for key 'commerce_orders.craft_commerce_orders_number_unq_idx' in /var/www/html/vendor/yiisoft/yii2/db/Command.php:1302 Stack trace:

0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()

1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute()

2 /var/www/html/vendor/yiisoft/yii2/db/Schema.php(431): yii\db\Command->execute()

3 /var/www/html/vendor/yiisoft/yii2/db/ActiveRecord.php(604): yii\db\Schema->insert()

4 /var/www/html/vendor/yiisoft/yii2/db/ActiveRecord.php(570): yii\db\ActiveRecord->insertInternal()

5 /var/www/html/vendor/yiisoft/yii2/db/BaseActiveRecord.php(676): yii\db\ActiveRecord->insert()

6 /var/www/html/vendor/craftcms/commerce/src/elements/Order.php(2236): yii\db\BaseActiveRecord->save()

7 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(3673): craft\commerce\elements\Order->afterSave()

8 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1169): craft\services\Elements->craft\services{closure}()

9 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(3489): craft\services\Elements->ensureBulkOp()

10 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1250): craft\services\Elements->_saveElementInternal()

11 /var/www/html/vendor/craftcms/commerce/src/services/Carts.php(182): craft\services\Elements->saveElement()

12 /var/www/html/vendor/craftcms/commerce/src/Plugin.php(1024): craft\commerce\services\Carts->getCart()

13 [internal function]: craft\commerce\Plugin::craft\commerce{closure}()

14 /var/www/html/vendor/yiisoft/yii2/base/Event.php(312): call_user_func()

15 /var/www/html/vendor/yiisoft/yii2/base/Component.php(642): yii\base\Event::trigger()

16 /var/www/html/vendor/craftcms/cms/src/base/ApplicationTrait.php(530): yii\base\Component->trigger()

17 /var/www/html/vendor/yiisoft/yii2/base/Application.php(381): craft\web\Application->trigger()

18 /var/www/html/web/index.php(12): yii\base\Application->run()

19 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '95e4d14310fce26c0a6237d0dd046c67' for key 'commerce_orders.craft_commerce_orders_number_unq_idx' The SQL being executed was: INSERT INTO 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 (33285, 4, '95e4d14310fce26c0a6237d0dd046c67', NULL, '0', '0', 'andrew.macpherson@mac.com', NULL, 0, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 0, '0', 'THB', '192.168.228.1', 'en', 1, 'web', 'THB', 1, 0, 0, 0, NULL, NULL, NULL, 'unpaid', 'all', 30305, 30305, '2024-05-16 01:38:34', '2024-05-16 01:38:34', 33283, 33284, 'd8c9f2c6-97b8-46f2-8aff-9f650b0d1b00') in /var/www/html/vendor/yiisoft/yii2/db/Schema.php:676 Stack trace:

0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException()

1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute()

2 /var/www/html/vendor/yiisoft/yii2/db/Schema.php(431): yii\db\Command->execute()

3 /var/www/html/vendor/yiisoft/yii2/db/ActiveRecord.php(604): yii\db\Schema->insert()

4 /var/www/html/vendor/yiisoft/yii2/db/ActiveRecord.php(570): yii\db\ActiveRecord->insertInternal()

5 /var/www/html/vendor/yiisoft/yii2/db/BaseActiveRecord.php(676): yii\db\ActiveRecord->insert()

6 /var/www/html/vendor/craftcms/commerce/src/elements/Order.php(2236): yii\db\BaseActiveRecord->save()

7 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(3673): craft\commerce\elements\Order->afterSave()

8 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1169): craft\services\Elements->craft\services{closure}()

9 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(3489): craft\services\Elements->ensureBulkOp()

10 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1250): craft\services\Elements->_saveElementInternal()

11 /var/www/html/vendor/craftcms/commerce/src/services/Carts.php(182): craft\services\Elements->saveElement()

12 /var/www/html/vendor/craftcms/commerce/src/Plugin.php(1024): craft\commerce\services\Carts->getCart()

13 [internal function]: craft\commerce\Plugin::craft\commerce{closure}()

14 /var/www/html/vendor/yiisoft/yii2/base/Event.php(312): call_user_func()

15 /var/www/html/vendor/yiisoft/yii2/base/Component.php(642): yii\base\Event::trigger()

16 /var/www/html/vendor/craftcms/cms/src/base/ApplicationTrait.php(530): yii\base\Component->trigger()

17 /var/www/html/vendor/yiisoft/yii2/base/Application.php(381): craft\web\Application->trigger()

18 /var/www/html/web/index.php(12): yii\base\Application->run()

19 {main}

Additional Information: Array ( [0] => 23000 [1] => 1062 [2] => Duplicate entry '95e4d14310fce26c0a6237d0dd046c67' for key 'commerce_orders.craft_commerce_orders_number_unq_idx' )

Craft CMS version

Craft Pro 5.1.3

Craft Commerce version

5.0.6

PHP version

8.3.6

Operating system and version

DDEV

Database type and version

MySQL 8.0.33

Image driver and version

No response

Installed plugins and versions

-

linear[bot] commented 4 months ago

PT-1732 [5.x]: SQL Integrity Constraint Violation

nfourtythree commented 4 months ago

Hi @AndrewMac

Thank you for reporting, unfortunately, I cannot replicate this with my local development environment.

Looking at what you have described would lead me to think something is going on with the site setup/retrieval of the cart.

Cart number is a randomly generated ID so there can never be an instance where the same one is being generated. Here I am thinking that it must be trying to retrieve the cart using the number stored in the cookie but not finding it (due to it being on a different site) and then trying to create a new one with the same number.

Are you able to give us some insight into how you have your sites setup (e.g. are they on identical domains?) and have you done anything different in terms of the configuration of cookies for your project?

Thanks!

AndrewMac commented 4 months ago

Thanks for your thoughts, @nfourtythree. I'll poke around a bit more at my end to see if I can find out more, but just quickly before that:

(a) no, each site is on its own domain, although each of those sites does have an associated second-language site (b) I'm not consciously doing anything with the configuration of the cookies, but I'll investigate

What I think I might have narrowed it down to, though, is that the problem seems to start when the same user logs in to both sites. There's no problem when browsing the sites without logging in, nor when people are logged in to only one of the sites. It seems to be that the problem starts when they log in to both at the same time.

I will keep investigating, though, to see if I can find anything else. Admittedly, too, having the same user logged in to both sites at the same time may not be very common, but still … it's better to have no problem at all ;-)

Thanks again, Andrew u

nfourtythree commented 4 months ago

Hi @AndrewMac

Thank you for the further information. We believe we were able to track down the issue and have pushed a fix.

This fixed will be included in the next release of Commerce 5.

Thanks!

lukeholder commented 4 months ago

5.0.7 is now out and fixes this issue. Thanks!

AndrewMac commented 4 months ago

Thanks @lukeholder and @nfourtythree, things are working as expected now. If I do run into anything I will post again, but I'm not expecting any … it all looks pretty good!

AndrewMac commented 4 months ago

Oh, wait a minute … I just tested something and the SQL error popped up again. Let me check a few things …

AndrewMac commented 4 months ago

Sorry, no need for me to panic. It came up when I tested with a new third site, but after logging out of everything and back in again it seems fine now.