Fatal Error when purchasing Gift Certificate

Zen Cart 1.5.6c
One Page Checkout recently updated to version 2.2.0
Latest Square Up version

The problem
Customers complained they see a blank page and/or an error message when trying to purchase Gift Certificates. Error message on screen "Warning: and error occurred...."

After running all the tests I could think of, disabling OPC and using check/money order as a payment method the issue persists. Reverting to the original store template, disabling OPC and paying with check/money order the issue persist, which leads me to believe there is something happening non related to either Square nor OPC.

The only thing that actually worked and I was able to place an order for a gift certificate was by changing the Yes, Skip Shipping Address, to No, Normal Shipping Rules, which treats the item as a regular product. I'm inclined to believe something isn't working when shipping isn't required, but I have no idea where to even begin to check.

Log (private values replaced with XXXXXXXXX):
[18-Feb-2020 11:24:08 America/Detroit] Request URI: /index.php?main_page=checkout_process, IP address: XXXXXXX
#1 trigger_error() called at [/includes/classes/db/mysql/query_factory.php:171]
#2 queryFactory->show_error() called at [/includes/classes/db/mysql/query_factory.php:143]
#3 queryFactory->set_error() called at [/includes/classes/db/mysql/query_factory.php:270]
#4 queryFactory->Execute() called at [/includes/functions/functions_general.php:952]
#5 zen_db_perform() called at [/includes/classes/order.php:695]
#6 order->create() called at [/includes/modules/checkout_process.php:95]
#7 require(/includes/modules/checkout_process.php) called at [/includes/modules/pages/checkout_process/header_php.php:14]
#8 require(/includes/modules/pages/checkout_process/header_php.php) called at [/home/koolwedd/public_html/index.php:36]
--> PHP Fatal error: 1366:Incorrect integer value: '' for column `koolwedd_TEMP`.`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 ('1', 'XXXXXXXX', '', 'XXXXXXXX', '', 'XXXXXXX', 'XXXXX', 'XXXXXX', 'United States', '0000000000', 'XXXXXXX', '2', ' ', '', '', '', '', '', '', '', '', 'XXXX', '', 'XXXXXXX', '', 'XXXXXXX', 'XXXX', 'XX', 'United States', '2', 'Check/Money Order', 'moneyorder', 'Free Shipping-', 'free', '', '', '', '', '', now(), '1', '100', '0', 'USD', '1.000000', 'XXXXXXX') ==> (as called by) /includes/functions/functions_general.php on line 952 <== in /includes/classes/db/mysql/query_factory.php on line 171.

Has anyone seen this behavior before? Any clue where the problem could be and how can it be solved?

lat9 has released a 2.3.0 version that might address the GC problem.

Is your store's /includes/classes/order.php the as-shipped zc156c version? I just tried placing a GC-only order (with and without OPC) and had no issue.

Quote:

---

Originally Posted by dbltoe

lat9 has released a 2.3.0 version that might address the GC problem.

---

I thought it would be an OPC issue, but when I tested with OPC disabled and just the classic template the behavior was still present. I'll download and install the new version you suggest, but I don't believe OPC is the problem.

Quote:

---

Originally Posted by lat9

Is your store's /includes/classes/order.php the as-shipped zc156c version? I just tried placing a GC-only order (with and without OPC) and had no issue.

---

Yes, identical.
With or without OPC I cannot get the purchase of a GC to go through. The only way it goes through is if I change the product from "Yes, Skip Shipping Address" to "No, Shipping Address Required"

The underlying issue is that the order-processing, which for some unknown reason is different on your site, has set the delivery_address_format_id field to '' (an empty string) which is an invalid decimal value. That, in conjunction with your webhost having defaulted the MySQL mode to more strict settings is what's leading to that error.

Since you're running zc156c, you can see that MySQL Mode setting at the top of the display rendered by your admin's Tools->Server/Version Info.

You can work-around the issue by creating a .php file containing:

Code:

---

<?php
/**
 * @package install
 * @copyright Copyright 2003-2018 Zen Cart Development Team
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: Drbyte Sun Jan 7 22:37:45 2018 -0500 Modified in v1.5.6 $
 *
 */

/**
 * Optionally set a MySQL mode during installation
 * Ref: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
 */
define('DB_MYSQL_MODE', 'TRADITIONAL');
//define('DB_MYSQL_MODE', 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO');

---

... in both the storefront and admin main directories, e.g. [/admin]/includes/extra_datafiles/mysql_mode_control.php.

I'll note, though, that there might be webhosts that disallow that override.

Quote:

---

Originally Posted by lat9

The underlying issue is that the order-processing, which for some unknown reason is different on your site, has set the delivery_address_format_id field to '' (an empty string) which is an invalid decimal value. That, in conjunction with your webhost having defaulted the MySQL mode to more strict settings is what's leading to that error.

Since you're running zc156c, you can see that MySQL Mode setting at the top of the display rendered by your admin's Tools->Server/Version Info.

---

Thanks Cindy!
I'll implement the fix and report back.

I never had an issue with GC before this version of ZC. This one went completely over my head.
Question, How is the order processing different on my site? Is this something I could have inadvertently caused?
I checked the database and delivery_address_format_id is set to 0 which is what the installation file also has (I keep copies of the files I download). Is it not supposed to be 0? If I modify the default value could that possibly solve the issue? Just trying to learn =)

Quote:

---

Originally Posted by lat9

The underlying issue is that the order-processing, which for some unknown reason is different on your site, has set the delivery_address_format_id field to '' (an empty string) which is an invalid decimal value. That, in conjunction with your webhost having defaulted the MySQL mode to more strict settings is what's leading to that error.

Since you're running zc156c, you can see that MySQL Mode setting at the top of the display rendered by your admin's Tools->Server/Version Info.

You can work-around the issue by creating a .php file containing:

Code:

---

<?php
/**
 * @package install
 * @copyright Copyright 2003-2018 Zen Cart Development Team
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: Drbyte Sun Jan 7 22:37:45 2018 -0500 Modified in v1.5.6 $
 *
 */

/**
 * Optionally set a MySQL mode during installation
 * Ref: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
 */
define('DB_MYSQL_MODE', 'TRADITIONAL');
//define('DB_MYSQL_MODE', 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO');

---

... in both the storefront and admin main directories, e.g. [/admin]/includes/extra_datafiles/mysql_mode_control.php.

I'll note, though, that there might be webhosts that disallow that override.

---

Unfortunately the patch didn't work :no::cry:

Quote:

---

Originally Posted by mvstudio

Unfortunately the patch didn't work :no::cry:

---

When you view your admin's Tools->Server/Version Info, what shows in that upper area?

Quote:

---

Originally Posted by mvstudio

Thanks Cindy!
I'll implement the fix and report back.

I never had an issue with GC before this version of ZC. This one went completely over my head.
Question, How is the order processing different on my site? Is this something I could have inadvertently caused?
I checked the database and delivery_address_format_id is set to 0 which is what the installation file also has (I keep copies of the files I download). Is it not supposed to be 0? If I modify the default value could that possibly solve the issue? Just trying to learn =)

---

The default value should be an integer 0 (as you see in your database). The issue is that not only PHP but also MySQL implementations are getting more strict, so you can no longer store '' (an empty string) and have it be magically converted to a 0 when the database field is set to contain an integer value.