How to find out what is causing PrestaShopException codice 23000]: SQLSTATE[23000]: Integrity constraint violation:

[Izar74]

Hi all,

i got an error while trying to update an order status of a specific order in admin. The error i got is:

 [PrestaShopException codice 23000]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '38-63-0-0' for key 'product_sqlstock'

It is caused by something trying to add an entry in the database that already exist, the problem is that i didn't get any other info except a red banner, nothing in logs in prestashop, nothing in logs on plesk panel.

How can i find what is causing the problem?

Already tried with debug mode but it didn't show any more info other than the red banner. I disabled all the overrides and all the non native module, nothing works

Prestashop 8.2

PHP 8.030

Thanks for any suggestion

[Prestashop Addict]

Hi, are you in multi store?.

[Knowband Plugins]

14 hours ago, Izar74 said:

1062 Duplicate entry '38-63-0-0' for key 'product_sqlstock'

product_sqlstock index is in the "ps_stock_available" available. 

38 Indicates the Product ID

63 id_product_attribute

0 - ID Shop

0 - ID Shop Group.

I am not sure of the reason for the error.

You can try deleting the existing entry with these combinations in ps_stock_available table (Ensure to take the table backup before deleting).

 

[QuickUpdate.net]

So basically it says that for product ID 38 with attribute ID 63 there is another entry in the "stock_available" table but it needs to be unique, if it happens when you change an order status it means that changing the order tries to update some stock information and instead of updating it tries to insert in that table.

This MIGHT still be caused by a module hooked to the order update process - did you disable all custom modules?

Edited March 28 by QuickUpdate.net (see edit history)

[Izar74]

Hi

thanks all for the replies.

No, it is not a multi store

Yes i suspect it is another module causing the problem but im'n not sure which one is and when the error is made (probably when the order is created because i tried disabling all the non native module and it still didin't allow me to update the order status).

I'm going to see the stock_available table to see if deleting the entry will solve anything.

Edited March 28 by Izar74 (see edit history)

[QuickUpdate.net]

If you intend to delete the entry then you may change the order and then check if the entry was recreated, then change the order again and check if you still get the same error, if you get it it confirms that there is some module trying to insert every time.

[Izar74]

16 minutes ago, QuickUpdate.net said:

If you intend to delete the entry then you may change the order and then check if the entry was recreated, then change the order again and check if you still get the same error, if you get it it confirms that there is some module trying to insert every time.

👍

[Izar74]

Hi again,

i manage to understand what is happening:

I'm trying to update an order status where is present a product variant which doesn't exist anymore. It's ID is no longer available.

The product variant with id_product_attribute 63 has been deleted so it is not present anymore in the product_attribute table.

I've deleted the entry, as suggested, in the stock_available table. Unfortunately if i try to update the order status it keep giving the error and it recreate a new entry in the table with the same data (id_product etc.) and an icreased id_stock_available.

So i find a brute solution but it worked, i edited the order_detail table, find the product_id (in my case it was 63) and modified the product_attribute_id to a variant that still exist.

In this way i was able to update the order status. One final note, i was updating the order status to "Invoice" so that prestashop create an invoice for the order.

I don't know if it is a prestashop bug, would be interesting to try reproduce the error. To see if the basic version of prestashop has this bug the step should be the following:

1. Create an order with some product with variants (be sure that the products added have more than 1 variant).

2. Modify the product you added to the order deleting the specific variant you just put in the order.

3. Try to update the order status and try update it to a status with the "Invoice" function" active (so that prestashop create an invoice of the order)

 

Hope it will help

[QuickUpdate.net]

Do you know what order statuses create invoice so we can reproduce it easier.

Also:
1. What would be the reason to create and invoice for an order with a product that no longer exists (besides fixing the specific error)?

2. "if i try to update the order status it keep giving the error and it recreate a new entry in the table with the same data (id_product etc.) and an icreased id_stock_available." - what is the new order status you are using?

3. From what you are saying my assumption is Prestashop is trying to create the product stock instead of updating when then order is updated and the product does not exist (maybe some code that gets triggered in this corner case)

Edited March 28 by QuickUpdate.net (see edit history)

[Izar74]

Hi,

I have modified many order status according to my shop needs, the order statu triggering the error is the one with ID 18 that for me is "Invoice", it has the flag delivery and invoice turned on.

1. The reason is simple, when the order has been created, the variant of the product existed. The variant has been deleted after because I dont sell that variant anymore.

2. The order status was "Shipped" (id 12) and i changed it to "Invoice" (id 18)

3. Probably it is some code that is triggered in this specific case, unfortunately i can't find any log with any error except the red banner showing what i posted. The fact that the error still exist even when i disable all the override and all the non native module, have me thinking that is something related to prestashop core and not some module. It would be nice to try to reproduce the error on a fresh install to see if it happen. I will try it as soon as i have some time.

Edited March 28 by Izar74 (see edit history)

[moashrafi]

The following sql command solved my problem:

UPDATE `ps_stock_available` SET `id_shop` = 1 WHERE `id_shop` = 0;