Rows not removed from ps_stock_available upon combination removal. Wrong stock quantity

[Shorty]

There are old rows in this table ps_stock_available left over from a bad combination that was removed.

All the other tables have been updated as far as I can tell but this one and it's affecting the BO quantity.

I'm not sure what other effects are caused so I'm touching base here to see if there are any other tables I need to update.

 

How to reproduce:

Create a product with combinations. For example 4 combinations each with 10 items quantity then save.

The combinations I created were 2 sizes and 2 flavours so 2 different attributes.

View the product from BO Products page, quantity is correct (40).

Edit that item and remove 2 combinations, say flavours leaving the sizes combinations then save.

View the product from BO Products page and the quantity is incorrect and still counts the deleted combination quantities (40).

Update quantities in the edit item page as much as you like but the deleted quantities are still counted.

 

MariaDB [prestashop]> select * from ps_stock_available where id_product = 31;
+--------------------+------------+----------------------+---------+---------------+----------+-------------------+-------------------+------------------+--------------+----------+
| id_stock_available | id_product | id_product_attribute | id_shop | id_shop_group | quantity | physical_quantity | reserved_quantity | depends_on_stock | out_of_stock | location |
+--------------------+------------+----------------------+---------+---------------+----------+-------------------+-------------------+------------------+--------------+----------+
|                127 |         31 |                    0 |       1 |             0 |       41 |                41 |                 0 |                0 |            0 |          |
|                176 |         31 |                  125 |       1 |             0 |       15 |                15 |                 0 |                0 |            0 |          |
|                177 |         31 |                  126 |       1 |             0 |        0 |                 0 |                 0 |                0 |            0 |          |
|                179 |         31 |                   96 |       1 |             0 |       13 |                13 |                 0 |                0 |            0 |          |
|                181 |         31 |                   97 |       1 |             0 |       13 |                13 |                 0 |                0 |            0 |          |
+--------------------+------------+----------------------+---------+---------------+----------+-------------------+-------------------+------------------+--------------+----------+
5 rows in set (0.001 sec)

MariaDB [prestashop]> select * from ps_product_attribute where id_product = 31;
+----------------------+------------+-----------+--------------------+----------+---------------+------+------+------+-----------------+-----------+----------+----------+----------+-------------------+------------+------------------+---------------------+-----------------+----------------+
| id_product_attribute | id_product | reference | supplier_reference | location | ean13         | isbn | upc  | mpn  | wholesale_price | price     | ecotax   | quantity | weight   | unit_price_impact | default_on | minimal_quantity | low_stock_threshold | low_stock_alert | available_date |
+----------------------+------------+-----------+--------------------+----------+---------------+------+------+------+-----------------+-----------+----------+----------+----------+-------------------+------------+------------------+---------------------+-----------------+----------------+
|                  125 |         31 |           |                    |          |      |      |      |      |        0.000000 |  4.990000 | 0.000000 |        0 | 0.100000 |          0.000000 |       NULL |                1 |                NULL |               0 | 0000-00-00     |
|                  126 |         31 |           |                    |          |      |      |      |      |        0.000000 | 13.990000 | 0.000000 |        0 | 0.500000 |          0.000000 |          1 |                1 |                NULL |               0 | 0000-00-00     |
+----------------------+------------+-----------+--------------------+----------+---------------+------+------+------+-----------------+-----------+----------+----------+----------+-------------------+------------+------------------+---------------------+-----------------+----------------+
2 rows in set (0.001 sec)

 

In the above query on ps_stock_available where id_product_attribute = 0, the quantity should be 15 and the last 2 rows where id_stock_available = 179 and 181 should be removed.

 

I'm not sure what caused this problem but I couldn't find anywhere in /var/www/prestashop with grep that references the ps_stock_available  table.

 

Any help is much appreciated.

[Shorty]

I'm considering removing the following records:

MariaDB [prestashop]> select * from ps_stock_available psa WHERE psa.id_product_attribute <> 0 and NOT EXISTS (select pa.id_product_attribute from ps_product_attribute pa where psa.id_product = pa.id_product and pa.id_product_attribute = psa.id_product_attribute);
+--------------------+------------+----------------------+---------+---------------+----------+-------------------+-------------------+------------------+--------------+----------+
| id_stock_available | id_product | id_product_attribute | id_shop | id_shop_group | quantity | physical_quantity | reserved_quantity | depends_on_stock | out_of_stock | location |
+--------------------+------------+----------------------+---------+---------------+----------+-------------------+-------------------+------------------+--------------+----------+
|                144 |         34 |                  107 |       1 |             0 |       15 |                15 |                 0 |                0 |            2 |          |
|                172 |         30 |                   95 |       1 |             0 |        8 |                 8 |                 0 |                0 |            2 |          |
|                174 |         30 |                   94 |       1 |             0 |       15 |                15 |                 0 |                0 |            2 |          |
|                175 |         32 |                   98 |       1 |             0 |       12 |                12 |                 0 |                0 |            2 |          |
|                179 |         31 |                   96 |       1 |             0 |       13 |                13 |                 0 |                0 |            0 |          |
|                181 |         31 |                   97 |       1 |             0 |       13 |                13 |                 0 |                0 |            0 |          |
|                185 |         28 |                   93 |       1 |             0 |       -3 |                -3 |                 0 |                0 |            2 |          |
|                187 |         28 |                   92 |       1 |             0 |       13 |                13 |                 0 |                0 |            2 |          |
|                191 |         27 |                   90 |       1 |             0 |       14 |                15 |                 1 |                0 |            2 |          |
|                193 |         27 |                   91 |       1 |             0 |       11 |                11 |                 0 |                0 |            2 |          |
|                197 |         26 |                   88 |       1 |             0 |       11 |                12 |                 1 |                0 |            2 |          |
|                199 |         26 |                   89 |       1 |             0 |        4 |                 4 |                 0 |                0 |            2 |          |
|                203 |         24 |                   80 |       1 |             0 |      -10 |               -10 |                 0 |                0 |            0 |          |
|                205 |         24 |                   83 |       1 |             0 |       14 |                14 |                 0 |                0 |            0 |          |
|                213 |         23 |                   77 |       1 |             0 |       13 |                13 |                 0 |                0 |            2 |          |
|                215 |         23 |                   78 |       1 |             0 |       -6 |                -6 |                 0 |                0 |            2 |          |
+--------------------+------------+----------------------+---------+---------------+----------+-------------------+-------------------+------------------+--------------+----------+
16 rows in set (0.001 sec)

And then updating the remaining products or running a query to update the ps_stock_available.id_product_attribute = 0 records with the new count but I'm not sure how these records are normally maintained since I can't find any reference to them in /var/www/prestashop

[Shorty]

In classes/Combination.php what is the purpose of this parent::delete check?
 

    /**
     * Deletes current Combination from the database.
     *
     * @return bool True if delete was successful
     *
     * @throws PrestaShopException
     */
    public function delete()
    {
        if (!parent::delete()) {
            return false;
        }

Could it have prevented my ps_stock_available lines from being deleted?

In my example above, I had initially created 2 sizes and 1 flavour and later created combinations of just sizes without flavour and copied the data from the original lines before deleting them. From memory there may have been just 2 entries which were deleted.

I'm not sure this is a valid use case, but when I was setting it up I wasn't aware how combinations worked and once I saw it I realised that having a drop down for flavour with only 1 flavour wasn't the best design choice.

Can anyone say with certainty that removing the lines in the select clause above will fix this and weather any other remedial action may be required?

Cheers,

Shorts.

[FabriceFrance]

Hello Shorty,

I have updated Prestashop from 1.4 (yes, it's true!) to Prestashop 1.7.8.9 last month.

I have exactly the same problem than you!

How to solve this? 

[FabriceFrance]

The problem has existed since 2019: https://github.com/PrestaShop/PrestaShop/issues/12814