PHP Fatal error: 1366:Incorrect integer value

Hello everyone.
I'm all of the sudden getting the following error log:

[06-Apr-2020 10:08:56 America/Detroit] Request URI: /ipn_main_handler.php, IP address: XXXXXXXXXXX
#1 trigger_error() called at [/CATALOG/includes/classes/db/mysql/query_factory.php:171]
#2 queryFactory->show_error() called at [/CATALOG/includes/classes/db/mysql/query_factory.php:143]
#3 queryFactory->set_error() called at [/CATALOG/includes/classes/db/mysql/query_factory.php:270]
#4 queryFactory->Execute() called at [/CATALOG/includes/functions/functions_general.php:952]
#5 zen_db_perform() called at [/CATALOG/ipn_main_handler.php:439]
--> PHP Fatal error: 1366:Incorrect integer value: '' for column koolwedd_TEMP.paypal_payment_status_history.paypal_ipn_id at row 1 :: INSERT INTO paypal_payment_status_history (paypal_ipn_id, txn_id, parent_txn_id, payment_status, pending_reason, date_added) VALUES ('', '2PR15233LC148450S', '2SC76806KT052782U', 'Completed', '', now()) ==> (as called by) /CATALOG/includes/functions/functions_general.php on line 952 <== in /CATALOG/includes/classes/db/mysql/query_factory.php on line 171.

Could anyone help me decipher it so I can figure out how to fix the problem?

Thank you in advance for the help!

My interpretation of what is going on is that at some point there was an attempt to store a record that failed such that when data was again presented to retrieve that record, no record was found and therefore the paypal_ipn_id was empty. Problem is that when that empty record is attempted to be updated, it obviously fails...

Would recommend taking some of that other data (parent txn_id and parent_txn_id) and see if you can find information either through paypal or in the database.

What about switching off IPN and going to PayPal Express?

swguy:

What about switching off IPN and going to PayPal Express?
Er, the PayPal Express Checkout goes through the ipn_main_handler.php to record the order's information, too.

oh! Didn't realize that.

I'm using PayPal express. :(

I am embarrassed by my lack of knowledge of how PayPal updates orders - @lat9 would you consider writing up an explanation of this process? It might give us insights that would help us fix the OP's issue. We could put it here:

https://docs.zen-cart.com/user/payment/paypal_misc/

swguy:

I am embarrassed by my lack of knowledge of how PayPal updates orders - @lat9 would you consider writing up an explanation of this process? It might give us insights that would help us fix the OP's issue. We could put it here:

https://docs.zen-cart.com/user/payment/paypal_misc/
I'll be happy to, @swguy. It'll take me a while since the processing is fairly convoluted and I've already got a headache just thinking about it!

lat9:

I'll be happy to, @swguy. It'll take me a while since the processing is fairly convoluted and I've already got a headache just thinking about it!

I'm sorry :( didn't mean to cause any trouble nor headaches :no:

Is there a fix yet? A few transactions are hitting this problem.

#5 zen_db_perform() called at [/CATALOG/ipn_main_handler.php:439]
--> PHP Fatal error: 1366:Incorrect integer value: '' for column koolwedd_TEMP.paypal_payment_status_history.paypal_ipn_id at row 1 :: INSERT INTO paypal_payment_status_history (paypal_ipn_id, txn_id, parent_txn_id, payment_status, pending_reason, date_added) VALUES ('', '2PR15233LC148450S', '2SC76806KT052782U', 'Completed', '', now()) ==> (as called by) /CATALOG/includes/functions/functions_general.php on line 952
The SQL error is a result of trying to put an empty text string into a decimal-only field in the database. In non-strict mode the database would just convert that to 0 automatically. But many servers use strict-mode by default nowadays.

EDIT: previous "solution" removed ... misread the details :(

The post by mc12345678 correctly gives some troubleshooting suggestions.

I would have expected to see other logs before this one which indicated trouble storing records into another paypal table.

In v1.5.7 we attempt to prevent this in paypal_functions.php like this:

  function ipn_create_order_history_array($insert_id) {
    $sql_data_array = array ('paypal_ipn_id' => [B](int)[/B]$insert_id,
...

I seem to be having this issue as well on one of my systems. v1.57, Database Patch Level: 1.5.7 with PHP v7.2.34, curl 7.73

I'm seeing and hearing from customers that even though the order processing has completed successfully they do not get a "success" page presented. As a result, many customers order the same item 2 or 3 times. I've checked IPN communication which passed. And I see this error a lot in my logs. I also just turned on logging for the paypal module to see if more info can be discovered.

[ names and codes altered for security reasons ]

PHP Fatal error: 1366:Incorrect decimal value: '14.95 USD' for column 'settle_amount' at row 1 :: INSERT INTO paypal (order_id, txn_type, module_name, module_mode, reason_code, payment_type, payment_status, pending_reason, invoice, first_name, last_name, payer_business_name, address_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_id, payer_status, payment_date, business, receiver_email, receiver_id, txn_id, parent_txn_id, num_cart_items, mc_gross, mc_fee, mc_currency, settle_amount, settle_currency, exchange_rate, notify_version, verify_sign, date_added, memo) VALUES ('1144', 'cart', 'paypalwpp', 'PayPal', 'None', 'PayPal Express Checkout (instant)', 'Completed', 'None', 'EC-8XW6XX42M8XXX633K', 'Branden', 'Murcam', '', '', '', '', '', '', '', '', '[email protected]', 'TQ4PUV5SVA2B4', 'unverified', '2020-10-04 00:30:16', '', 'paypal.website.com', '', '84W2214UB609470W', '', '1', '14.95', '0.73', 'USD', '14.95 USD', 'USD', '1', '0', '', now(), '{Record generated by payment module}') ==> (as called by) /home/public_html/opm/includes/functions/database.php on line 44 <== in /home/public_html/opm/includes/classes/db/mysql/query_factory.php on line 170.

There was more to that log, wasn't there? Specifically, the 'backtrace' of who called who to get to that error.

Another thing to check, your /includes/modules/payment/paypalwpp, line 536 ... does it read:

                          'settle_amount' => (float)(isset($this->responsedata['PAYMENTINFO_0_SETTLEAMT'])) ? $this->urldecode($this->responsedata['PAYMENTINFO_0_SETTLEAMT']) : $this->amt,

Yes, sorry.

[21-Sep-2020 19:42:37 UTC] Request URI: /opm/index.php?main_page=checkout_process, IP address: 207.228.78.224
#1 trigger_error() called at [/home/kwadmin1/public_html/opm/includes/classes/db/mysql/query_factory.php:170]
#2 queryFactory->show_error() called at [/home/kwadmin1/public_html/opm/includes/classes/db/mysql/query_factory.php:142]
#3 queryFactory->set_error() called at [/home/kwadmin1/public_html/opm/includes/classes/db/mysql/query_factory.php:269]
#4 queryFactory->Execute() called at [/home/kwadmin1/public_html/opm/includes/functions/database.php:44]
#5 zen_db_perform() called at [/home/kwadmin1/public_html/opm/includes/modules/payment/paypalwpp.php:544]
#6 paypalwpp->after_process() called at [/home/kwadmin1/public_html/opm/includes/classes/payment.php:261]
#7 payment->after_process() called at [/home/kwadmin1/public_html/opm/includes/modules/pages/checkout_process/header_php.php:17]
#8 require(/home/kwadmin1/public_html/opm/includes/modules/pages/checkout_process/header_php.php) called at [/home/kwadmin1/public_html/opm/index.php:35]
--> PHP Fatal error: 1366:Incorrect decimal value: '14.95 USD' for column 'settle_amount' at row 1 :: INSERT INTO paypal (order_id, txn_ty
etc...

And yes, that line 536 in paypalwpp.php is exactly as your example.

I'll suggest changing line 536 of paypalwpp.php to reflect the change made for zc157a:

                          'settle_amount' => (float)(isset($this->responsedata['PAYMENTINFO_0_SETTLEAMT']) ? urldecode($this->responsedata['PAYMENTINFO_0_SETTLEAMT']) : $this->amt),

Note the subtle difference between that and zc157, the movement of the closing parentheses from after the isset() clause to after the $this->amt element.

lat9:

I'll suggest changing line 536 of paypalwpp.php to reflect the change made for zc157a:

                      'settle_amount' => (float)(isset($this->responsedata['PAYMENTINFO_0_SETTLEAMT']) ? urldecode($this->responsedata['PAYMENTINFO_0_SETTLEAMT']) : $this->amt),

> Note the subtle difference between that and zc157, the movement of the closing parentheses from after the isset() clause to after the $this->amt element.

OK, I've made the change, now waiting to see if the next purchase has any issues.  
It seems that the only issue really is that an error message is displayed to the user after a successful transaction. And all it says is "An Error has occurred" on a white background.  I'll report back once I have a sale go through again.

lynbor:

OK, I've made the change, now waiting to see if the next purchase has any issues.
It seems that the only issue really is that an error message is displayed to the user after a successful transaction. And all it says is "An Error has occurred" on a white background. I'll report back once I have a sale go through again.

Well, there is the fact though that the transaction(s) didn't get logged in the PayPal table, but that's minor compared to 1) not receiving money, 2) not having logging/recognition of an order, and 3) customer not getting their product. :)

lynbor:

OK, I've made the change, now waiting to see if the next purchase has any issues.
It seems that the only issue really is that an error message is displayed to the user after a successful transaction. And all it says is "An Error has occurred" on a white background. I'll report back once I have a sale go through again.

This appears to have solved the issue. I've had two more purchases and no additional orders attempted nor complaints from the customers. The log files generated were a little disconcerting though. The first sale generated 8 logs and the second one generated 6. Some ended with SUCCESS while others appeared to indicate some kind of issue may have happened. But for now, I'm not going to pursue it further. I've set the PayPal logging level back to "Alerts only" and I'll ride with it that way for a bit and see how things go. Thank you very much for the assist lat9!

Having same problem, apologies if I am supposed to open new thread, please let me know in case.

Almost same error log as OP by mvstudio as you can see below.

Infos:
Running 1.5.6c (I know, will update as soon as possible)
Mid december there was a migration done by hosting company to new server, before that everything was working fine, meaning orders would get in after payment.

My question:
Can I apply the suggested changes (DrByte #12, and lat9 #16) as suggested?

Thank you
Happy 2021

[31-Dec-2020 20:38:25 Europe/London] Request URI: /index.php?main_page=checkout_process, IP address: xxx.xxx.xxx.xxx
#1 trigger_error() called at [/path-to/public_html/includes/classes/db/mysql/query_factory.php:171]
#2 queryFactory->show_error() called at [/path-to/public_html/includes/classes/db/mysql/query_factory.php:143]
#3 queryFactory->set_error() called at [/path-to/public_html/includes/classes/db/mysql/query_factory.php:270]
#4 queryFactory->Execute() called at [/path-to/public_html/includes/functions/functions_general.php:952]
#5 zen_db_perform() called at [/path-to/public_html/includes/classes/order.php:695]
#6 order->create() called at [/path-to/public_html/includes/modules/checkout_process.php:95]
#7 require(/path-to/public_html/includes/modules/checkout_process.php) called at [/path-to/public_html/includes/modules/pages/checkout_process/header_php.php:14]
#8 require(/path-to/public_html/includes/modules/pages/checkout_process/header_php.php) called at [/path-to/public_html/index.php:36]
--> PHP Fatal error: 1366:Incorrect integer value: '' for column tesoreri_zen156c.orders.delivery_address_format_id at row 1 :: INSERT INTO orders (customers_id, customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, payment_module_code, shipping_method, shipping_module_code, coupon_code, cc_type, cc_owner, cc_number, cc_expires, date_purchased, orders_status, order_total, order_tax, currency, currency_value, ip_address) VALUES ('100', 'NAME REMOVED', '', 'ADDRESS REMOVED', '', 'TOWN REMOVED', 'ZIP REMOVED', 'STATE REMOVED', 'COUNTRY REMOVED', 'PHONE REMOVED', 'MAIL REMOVED', '5', ' ', '', '', '', '', '', '', '', '', 'NAME REMOVED', '', 'ADDRESS REMOVED', '', 'TOWN REMOVED', 'ZIP REMOVED', 'STATE REMOVED', 'COUNTRY REMOVED', '5', 'PayPal', 'paypalwpp', 'free_free', 'free', '', '', '', '', '', now(), '2', '50', '0', 'EUR', '1.000000', 'xxx.xxx.xxx.xxx - xxx.xxx.xxx.xxx') ==> (as called by) /path-to/public_html/includes/functions/functions_general.php on line 952 <== in /path-to/public_html/includes/classes/db/mysql/query_factory.php on line 171.