Integrity constraint violation when syncing order

[GaryReckard]

We've been having an elusive, recurring issue in our Craft Commerce store where amongst thousands of successful orders, the occasional few will have an issue where our custom code that should fire on EVENT_AFTER_ORDER_PAID does not get executed, so the order does not get sent to our shipping partner.

The common thread that I have found is that the following error shows in the logs right after our custom code for EVENT_AFTER_COMPLETE_ORDER executes:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '6326993' for key 'PRIMARY'
The SQL being executed was: INSERT INTO `craft_mc_orders_synced` (`orderId`, `isCart`, `lastSynced`) VALUES (6326993, 0, '2021-01-21 05:58:55')

Whatever is causing this error, it's preventing a small number of our orders to not get shipped out, until we catch the error.

Any idea why this might be happening, how to prevent it, or at the very least prevent it from derailing the rest of the events that should fire after it?

Thanks!

We're on Mailchimp-Commerce v1.2.2 and Craft 3.4.25.

[rsanchez]

Seems like a race condition, which can be triggered if you manually save orders in code. Might I recommend using an upsert query in the _createOrder method rather than a plain insert query? Yii does have an upsert helper: https://www.yiiframework.com/doc/api/2.0/yii-db-command#upsert()-detail

[GaryReckard]

Hi again! Any chance this 'upsert' suggestion could be implemented in an updated version of this plugin? Thanks!