Update SQL error on customers_basket table

[PanZC2020]

On the file "mysql_upgrade_zencart_156.sql" while I'm upgrading my Zen Cart to 1.5.6a.
There is one section deal with "customers_basket" table.

# Clean up expired prids from baskets
#NEXT_X_ROWS_AS_ONE_COMMAND:3
DELETE FROM customers_basket WHERE CAST(products_id AS unsigned) NOT IN (
SELECT products_id
FROM products WHERE products_status > 0);
#NEXT_X_ROWS_AS_ONE_COMMAND:3
DELETE FROM customers_basket_attributes WHERE CAST(products_id AS unsigned) NOT IN (
SELECT products_id
FROM products WHERE products_status > 0);

However, this lead to SQL error of the following:

[22-Jan-2019 20:06:46 UTC] MySQL error 1292 encountered during zc_install:
Truncated incorrect INTEGER value: '178:d098d2cbb96515debaa325b4732e4976'
FROM products WHERE products_status > 0);
---------------


[22-Jan-2019 20:06:46 UTC] MySQL error 1292 encountered during zc_install:
Truncated incorrect INTEGER value: '178:d098d2cbb96515debaa325b4732e4976'
FROM products WHERE products_status > 0);
---------------

This lead to those statements skipped through and left my table with unclean baskets.
I checked the "customers_basket" table and this is what I see.



The field "products_id" stored as "text."



Therefore, the cleaning was not completed.
(This database upgrade after I removed all zero dates.)

-PanZC2020

[mc12345678]

On the file "mysql_upgrade_zencart_156.sql" while I'm upgrading my Zen Cart to 1.5.6a.
There is one section deal with "customers_basket" table.

# Clean up expired prids from baskets
#NEXT_X_ROWS_AS_ONE_COMMAND:3
DELETE FROM customers_basket WHERE CAST(products_id AS unsigned) NOT IN (
SELECT products_id
FROM products WHERE products_status > 0);
#NEXT_X_ROWS_AS_ONE_COMMAND:3
DELETE FROM customers_basket_attributes WHERE CAST(products_id AS unsigned) NOT IN (
SELECT products_id
FROM products WHERE products_status > 0);

However, this lead to SQL error of the following:

[22-Jan-2019 20:06:46 UTC] MySQL error 1292 encountered during zc_install:
Truncated incorrect INTEGER value: '178:d098d2cbb96515debaa325b4732e4976'
FROM products WHERE products_status > 0);
---------------


[22-Jan-2019 20:06:46 UTC] MySQL error 1292 encountered during zc_install:
Truncated incorrect INTEGER value: '178:d098d2cbb96515debaa325b4732e4976'
FROM products WHERE products_status > 0);
---------------

This lead to those statements skipped through and left my table with unclean baskets.
I checked the "customers_basket" table and this is what I see.



The field "products_id" stored as "text."



Therefore, the cleaning was not completed.
(This database upgrade after I removed all zero dates.)

-PanZC2020

Looks like perhaps the following SQL may address that issue? It is untested other than knowing that if the field being searched does not include the needle that the entire field is returned which seems like would fit the described/expected condition.

# Clean up expired prids from baskets
#NEXT_X_ROWS_AS_ONE_COMMAND:3
DELETE FROM customers_basket WHERE CAST(SUBSTRING_INDEX(products_id, ":", 1) AS unsigned) NOT IN (
SELECT products_id
FROM products WHERE products_status > 0);
#NEXT_X_ROWS_AS_ONE_COMMAND:3
DELETE FROM customers_basket_attributes WHERE CAST(SUBSTRING_INDEX(products_id, ":", 1) AS unsigned) NOT IN (
SELECT products_id
FROM products WHERE products_status > 0);

If this can be worked into the section that does the cast, then it would provide a result showing the numeric portion of the products_id or the products_id in whole if there were no attributes.

I'm away from a real computer to fully suss it out, but seems to provide the necessary content to convert to an integer:

SUBSTRING_INDEX(products_id, ":", 1)

Note that as "displayed" the second delete sql spans 4 lines instead of 3. I do not know why it is different. Running out of time to edit this post. Proposed revision substitutes the above substring_index for the products_id.

[mc12345678]

After checking this SQL with a select statement instead of a delete statement, pull request #2113 has been submitted. Changes expected are likely to be seen at: https://github.com/zencart/zencart/pull/2113/files