search

lat9 / edit_orders

Edit Orders: Updates for continued operation on Zen Cart v1.5.8 and later
GNU General Public License v2.0
5 stars 9 forks source link

SQL error when tax not set #193

Closed scottcwilson closed 3 years ago

scottcwilson commented 3 years ago

--> PHP Fatal error: 1366:Incorrect decimal value: '' for column live.orders_products.products_tax at row 1 :: INSERT INTO orders_products (orders_id, products_id, products_model, products_name, products_price, final_price, onetime_charges, products_tax, products_quantity, products_priced_by_attribute, product_is_free, products_discount_type, products_discount_type_from, products_prid) VALUES ('36423', '7722', 'IBM_WAL1219', 'XXXXX', '5.9900', '0', '0', '', '7', '0', '0', '0', '0', '7722') ==> (as called by) includes/functions/database.php on line 44

6 eo_add_product_to_order() called at [.../admin/edit_orders.php:473]

EO version 4.5.4, Zen Cart 1.5.7, PHP 7.2. Perhaps products_tax in eo_add_product_to_order should be cast to (float) before the insert?

lat9 commented 3 years ago

No (float) casts needed, I'm thinking to check for empty() and set the value to 0 if so.

Could you fill in a couple of blanks so that I can reproduce the issue? When you say that the tax is "not set", specifically how is that value 'not set'?

scottcwilson commented 3 years ago

The value was ''. So empty() is a good check.

lat9 commented 3 years ago

Was that an entered value? I can see from the log that the value was an empty string, but I'm trying to determine where that value came from.

scottcwilson commented 3 years ago

I only have the logfile.

lat9 commented 3 years ago

How about posting (with admin obfuscation) the entire log file?

scottcwilson commented 3 years ago

(Found it!)

[22-Mar-2021 11:02:13 America/New_York] Request URI: /mysite/admin/index.php?cmd=edit_orders&oID=36423&action=update_order, IP address: xx.xx.xx.xx

1 trigger_error() called at [/home/myaccount/public_html/mysite/includes/classes/db/mysql/query_factory.php:170]

2 queryFactory->show_error() called at [/home/myaccount/public_html/mysite/includes/classes/db/mysql/query_factory.php:142]

3 queryFactory->set_error() called at [/home/myaccount/public_html/mysite/includes/classes/db/mysql/query_factory.php:269]

4 queryFactory->Execute() called at [/home/myaccount/public_html/mysite/includes/functions/database.php:44]

5 zen_db_perform() called at [/home/myaccount/public_html/mysite/admin/includes/functions/extra_functions/edit_orders_functions.php:971]

6 eo_add_product_to_order() called at [/home/myaccount/public_html/mysite/admin/edit_orders.php:473]

7 require(/home/myaccount/public_html/mysite/admin/edit_orders.php) called at [/home/myaccount/public_html/mysite/admin/index.php:11]

--> PHP Fatal error: 1366:Incorrect decimal value: '' for column myaccount_live.orders_products.products_tax at row 1 :: INSERT INTO orders_products (orders_id, products_id, products_model, products_name, products_price, final_price, onetime_charges, products_tax, products_quantity, products_priced_by_attribute, product_is_free, products_discount_type, products_discount_type_from, products_prid) VALUES ('36423', '7722', 'IBM_WAL1219-Paid', 'Product Name', '5.9900', '0', '0', '', '7', '0', '0', '0', '0', '7722') ==> (as called by) /home/myaccount/public_html/mysite/includes/functions/database.php on line 44 <== in /home/myaccount/public_html/mysite/includes/classes/db/mysql/query_factory.php on line 170.

lat9 commented 3 years ago

I'm unable to reproduce this on EO 4.6.0-beta, zc157c, PHP 7.3.

I've tried setting a product's tax field to an empty string on the main page, but the processing sets a 0-value tax rate with no issue.

I'll keep this open, just in case you can find the underlying reason for the empty-string product tax.

Does the site, by any chance, have an EO observer that's in effect?

scottcwilson commented 3 years ago

Does the site, by any chance, have an EO observer that's in effect?

No. I wonder if it's related to DB_MODE: STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION ?

At any rate, doing an empty() on products_tax and final_price in admin/includes/functions/extra_functions/edit_orders_functions.php line 960 seems pretty harmless and will prevent these issues from occurring.

lat9 commented 3 years ago

That's the mode I run my localhost sites' MySQL in. I'm not a fan of plunking code to correct an issue that can't be reproduced, since there's no way of knowing what the underlying issue is.

Does the product indicated in the log have a tax-rate assigned?

scottcwilson commented 3 years ago

Yes

lat9 commented 3 years ago

I can't explain the sequence that caused this issue, but I'll note that the current release version of EO is 4.5.7.

lat9 commented 3 years ago

My bad. I didn't have the database-override set for the admin-side of my localhost account.

To recreate, simply blank-out the tax-rate and submit; error occurs.

lat9 commented 3 years ago

... noting that the Product Pricing Method must be set the "As entered" for the issue to surface.