Duplicate entry for primary key

Hi @dekadinious, if you haven’t already, we would recommend a fresh uninstall/reinstall of the plugin to see if that fixes the issue. Here’s how:
– Deactivate and delete the plugin from the WordPress plugin admin area
– Confirm that your store is disconnected from the Mailchimp Connected Sites area. If it isn’t, manually disconnect it
– Download the latest plugin from WordPress or directly inside the WordPress plugin admin area by searching for Mailchimp for WooCommerce
– Activate the plugin
– Follow setup instructions in the plugin to sync your store with a Mailchimp Audience

Please let us know if that fixes your problem or if we need to troubleshoot further. Happy to help.

I can’t do that because our store is too large and we got problems the last time we did this. I have therefore done my own research here. I have confirmed that the database table structure is correct and just as you have programmed it to be. I have further confirmed that your hashing function works as expected, so the check for existing rows also works as expected.

The only thing I could think of is that we get a race condition where trackCart fires twice in quick succession, checks the database and sees that there is no existing records, then both function calls tries to insert into the database at the same time. This will mean that the database insert will be locked for the second call while the first is inserting. After that the second call will try to insert, and this will fail because the primary key “email” already exists.

To test this I logged $uid and microtime() before and inside the if-else-block inside trackCart() in the file class-mailchimp-woocommerce-service.php at about line 875. My logs reveal that on every failed insert we have a race condition. Reinstalling the plugin will not fix that:

[19-Aug-2021 08:33:43 UTC] Start: c2d22ee401b8bb66f88fbf81da045b5b 0.38921500
[19-Aug-2021 08:33:43 UTC] Start: c2d22ee401b8bb66f88fbf81da045b5b 0.39203500
[19-Aug-2021 08:33:43 UTC] Insert: c2d22ee401b8bb66f88fbf81da045b5b 0.50978500 
[19-Aug-2021 08:33:43 UTC] Insert: c2d22ee401b8bb66f88fbf81da045b5b 0.50977300
[19-Aug-2021 08:33:43 UTC] WordPress database error Duplicate entry[...]

This fires twice. I am not completely sure how your plugin is set up, but could it be that you fire this on every add to cart? It seems that way? So if we programatically add something to the cart based on other items that are added to the cart, this could provoke a race condition?

If so, you should do a check for this in the plugin and fix it. This is a normal need for large stores – to be able to dynamically and programatically add and remove from cart based on other items added and removed from the cart.

I am quite sure the best solution would be to do a ON DUPLICATE KEY UPDATE query. If you don’t want to do that for some reason, you can lock the table and unlock it like this:

        
$wpdb->query("LOCK TABLES $table WRITE");

try {
	if (($saved_cart = $wpdb->get_row($sql)) && is_object($saved_cart)) {

		$statement = "UPDATE {$table} SET <code>cart</code> = '%s', <code>email</code> = '%s', <code>user_id</code> = %s WHERE <code>id</code> = '%s'";
		$sql = $wpdb->prepare($statement, array(maybe_serialize($this->cart), $email, $user_id, $uid));
		try {
			$wpdb->query($sql);
		} catch (\Exception $e) {
			return false;
		}
	} else {
		try {
			$wpdb->insert("{$wpdb->prefix}mailchimp_carts", array(
				'id' => $uid,
				'email' => $email,
				'user_id' => (int) $user_id,
				'cart'  => maybe_serialize($this->cart),
				'created_at'   => gmdate('Y-m-d H:i:s', time()),
			));
		} catch (\Exception $e) {
			return false;
		}
	}
} finally {
	$wpdb->query("UNLOCK TABLES");
}
	

Tested and works, but I am a bit concerned about the performance implications of this. Let me know if any of this will be implemented.

• This reply was modified 3 years, 3 months ago by Dekadinious.
• This reply was modified 3 years, 3 months ago by Dekadinious.

Is this implemented? Can you give any feedback here at all?

@dekadinious

Sorry about the delay – this would be great to start a thread on the github repo here. Let’s get this rolling for the next version. You’re correct this will take some testing – but to be honest the performance can’t always be a blocker… if it needs to happen to avoid duplication we should do it. Ping me on a new ticket over there if you don’t mind.

Thanks!

Done, will resolve this ticket ??