What causes THIS error?

Printable View

2 Dec 2023, 10:58 PM
Jeff_Mash

What causes THIS error?

I see this a bunch of times, and can't figure out what causes it. The IP address is always registered to PayPal.

Code:

[02-Dec-2023 13:47:12 America/Los_Angeles] Request URI: /store/ipn_main_handler.php, IP address: 173.0.81.65, Language id 1 #1 trigger_error() called at [/home/domain/public_html/store/includes/classes/db/mysql/query_factory.php:667] #2 queryFactory->show_error() called at [/home/domain/public_html/store/includes/classes/db/mysql/query_factory.php:634] #3 queryFactory->set_error() called at [/home/domain/public_html/store/includes/classes/db/mysql/query_factory.php:275] #4 queryFactory->Execute() called at [/home/domain/public_html/store/includes/modules/shipping/freeoptions.php:104] #5 freeoptions->update_status() called at [/home/domain/public_html/store/includes/modules/shipping/freeoptions.php:85] #6 freeoptions->__construct() called at [/home/domain/public_html/store/includes/classes/shipping.php:91] #7 shipping->__construct() called at [/home/domain/public_html/store/ipn_main_handler.php:318] --> PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY zone_id' at line 5 :: SELECT zone_id FROM zen_zones_to_geo_zones WHERE geo_zone_id = 2 AND zone_country_id = ORDER BY zone_id ==> (as called by) /home/domain/public_html/store/includes/modules/shipping/freeoptions.php on line 104 <== in /home/domain/public_html/store/includes/classes/db/mysql/query_factory.php on line 667.
2 Dec 2023, 11:19 PM
Jeff_Mash

Re: What causes THIS error?

I might see WHY this is happening, but not sure.

We have a FREE SHIPPING zone. Inside of that zone, we defined two geographical areas to offer free shipping:

- Puerto Rico
- United States

However, the value for ZONE_ID is NULL for the United States:

Screenshot: https://i.postimg.cc/65WHVj7Q/screenshot-979.png

Could that be causing the error in the logs?

When we pull up the definitions for that zone, the only thing I can see is that "United States" doesn't allow us to select "All Zones". Just says "Please Select" and only offers individual states to choose from. But we want ALL the states to have the free shipping.

Screenshot: https://i.postimg.cc/8cB0pxj2/screenshot-978.png
2 Dec 2023, 11:31 PM
Jeff_Mash

Re: What causes THIS error?

Or maybe it means someone is checking out from a country that we don't allow? I guess what throws me for a loop is that the IP is registered to PayPal, so I don't know why this error is being flagged. The part of the code that is executing the SQL statement is:

SELECT zone_id
FROM " . TABLE_ZONES_TO_GEO_ZONES . "
WHERE geo_zone_id = " . (int)MODULE_SHIPPING_FREEOPTIONS_ZONE . "
AND zone_country_id = " . $order->delivery['country']['id'] . "
ORDER BY zone_id"

So I'm guessing that something incorrect is being used for the Country ID?
3 Dec 2023, 02:02 AM
Jeff_Mash

Re: What causes THIS error?

I think the problem is that $order->delivery['country']['id'] is EMPTY (blank).

Is there any reason why the ipn_main_handler.php would be logging an IP address from PayPal and the $order->delivery['country']['id'] is blank?

I am afraid this might be causing customers to not place orders due to some misconfiguration.
3 Dec 2023, 11:07 AM
lat9

Re: What causes THIS error?

Quote:

Originally Posted by Jeff_Mash

I might see WHY this is happening, but not sure.

We have a FREE SHIPPING zone. Inside of that zone, we defined two geographical areas to offer free shipping:

- Puerto Rico
- United States

However, the value for ZONE_ID is NULL for the United States:

Screenshot: https://i.postimg.cc/65WHVj7Q/screenshot-979.png

Could that be causing the error in the logs?

When we pull up the definitions for that zone, the only thing I can see is that "United States" doesn't allow us to select "All Zones". Just says "Please Select" and only offers individual states to choose from. But we want ALL the states to have the free shipping.

Screenshot: https://i.postimg.cc/8cB0pxj2/screenshot-978.png

That NULL country_id for the United States is absolutely what's causing that invalid SQL to be generated.

The "All Zones" selection, albeit not extremely intuitive, is an alias of the "Please Select" selection. That is, choose "Please Select" to choose all zones for a specific country.
3 Dec 2023, 02:43 PM
mc12345678

Re: What causes THIS error?

It seems to me, that the issue may get resolved if the sql query value for the country id were cast to an integer. At least in that way the query would have proper formatting. It doesn't mean that the query would pull the proper data. Such a change would be:

Code:

SELECT zone_id FROM " . TABLE_ZONES_TO_GEO_ZONES . " WHERE geo_zone_id = " . (int)MODULE_SHIPPING_FREEOPTIONS_ZONE . " AND zone_country_id = " . (int)$order->delivery['country']['id'] . " ORDER BY zone_id"
3 Dec 2023, 02:53 PM
lat9

Re: What causes THIS error?

Quote:

Originally Posted by mc12345678

It seems to me, that the issue may get resolved if the sql query value for the country id were cast to an integer. At least in that way the query would have proper formatting. It doesn't mean that the query would pull the proper data. Such a change would be:

Code:

SELECT zone_id FROM " . TABLE_ZONES_TO_GEO_ZONES . " WHERE geo_zone_id = " . (int)MODULE_SHIPPING_FREEOPTIONS_ZONE . " AND zone_country_id = " . (int)$order->delivery['country']['id'] . " ORDER BY zone_id"

While that would provide a work-around for the issue, that's not a correction since that value should be set so we still need to determine the root-cause of the issue.
3 Dec 2023, 03:05 PM
mc12345678

Re: What causes THIS error?

Quote:

Originally Posted by lat9

While that would provide a work-around for the issue, that's not a correction since that value should be set so we still need to determine the root-cause of the issue.

Sure, allows sales to properly continue while other research continues about the fact that a default selection results in what had been reported as any of such values like a NULL, an empty quote or a value of zero such as false not otherwise forced to be displayed as a zero.
3 Dec 2023, 03:25 PM
lat9

Re: What causes THIS error?

Quote:

Originally Posted by mc12345678

Sure, allows sales to properly continue while other research continues about the fact that a default selection results in what had been reported as any of such values like a NULL, an empty quote or a value of zero such as false not otherwise forced to be displayed as a zero.

Again, the keyword is work-around.
3 Dec 2023, 08:06 PM
Jeff_Mash

Re: What causes THIS error?

I really appreciate BOTH your feedback on this. :)

I just typecast that line by putting (int) in front of the $order->delivery['country']['id'] variable.

Cindy, you mentioned that "Please Select" is an alias of the "All Zones." I do have that set up, even though the database has NULL in the zone_id.

Here, let me see if I can illustrate my settings to help pinpoint this issue.

Under Modules -> Shipping -> Free Shipping options, I have my "Shipping Zone" set to "Free Shipping Areas."

Under Zones Definitions, I created a "Free Shipping Areas" zone. That zone only has two entries:
- Puerto Rico (All Zones)
- United States (All Zone)

Screenshot: https://i.postimg.cc/QtCfSWnz/screenshot-980.png

If I click to EDIT the Puerto Rico entry, I can see that "All Zones" is an option for the zone: https://i.postimg.cc/j5y4MQ4Z/screenshot-981.png

If I click to EDIT the United States entry, it only says "Please Select": https://i.postimg.cc/vBXc84YG/screenshot-982.png

This "Free Shipping Areas" zone has an ID of 2.

When I go to the database and run a query (SELECT * FROM `zen_zones_to_geo_zones` WHERE `geo_zone_id` = 2; ), this is what I see: https://i.postimg.cc/RVwqqpVP/screenshot-983.png

As you can see, the zone_id for the United States is NULL. If that is causing the issue, should I just change it to "0" like the Puerto Rico entry?
3 Dec 2023, 08:14 PM
Jeff_Mash

Re: What causes THIS error?

Again, I should add, every time I see this error in the logs, the IP address always seems to be registered to PayPal.

Here are the two I've recently seen:

- 66.211.170.66
- 173.0.81.140

Just mentioning that in case it helps shed light on the error.
3 Dec 2023, 08:27 PM
lat9

Re: What causes THIS error?

Quote:

Originally Posted by Jeff_Mash

...
When I go to the database and run a query (SELECT * FROM `zen_zones_to_geo_zones` WHERE `geo_zone_id` = 2; ), this is what I see: https://i.postimg.cc/RVwqqpVP/screenshot-983.png

As you can see, the zone_id for the United States is NULL. If that is causing the issue, should I just change it to "0" like the Puerto Rico entry?

Jeff, you've got that bass-ackwards; the zone_id for the US (country id 223) is 0 (for all) while the one for Puerto Rico is NULL (since it doesn't have any defined states/provinces, aka zones).
3 Dec 2023, 09:18 PM
Jeff_Mash

Re: What causes THIS error?

Quote:

Originally Posted by lat9

Jeff, you've got that bass-ackwards; the zone_id for the US (country id 223) is 0 (for all) while the one for Puerto Rico is NULL (since it doesn't have any defined states/provinces, aka zones).

LOL - I'm really good at walking bass-ackwards! Thanks for clearing that up!

I guess we wait and see if typecasting the variable as an (int) will help remove the error.

My main concern was that somehow, these errors indicated customers who were trying to checkout and not able to. We get complaints all the time about having trouble checking out, and I couldn't figure out if these errors were related.

The fact that all the IP's are related to PayPal made me wonder if this was a checkout related issue, and if I could be losing sales.
14 Dec 2023, 02:47 AM
mc12345678

Re: What causes THIS error?

Quote:

Originally Posted by Jeff_Mash

I really appreciate BOTH your feedback on this. :)

I just typecast that line by putting (int) in front of the $order->delivery['country']['id'] variable.

Cindy, you mentioned that "Please Select" is an alias of the "All Zones." I do have that set up, even though the database has NULL in the zone_id.

Here, let me see if I can illustrate my settings to help pinpoint this issue.

Under Modules -> Shipping -> Free Shipping options, I have my "Shipping Zone" set to "Free Shipping Areas."

Under Zones Definitions, I created a "Free Shipping Areas" zone. That zone only has two entries:
- Puerto Rico (All Zones)
- United States (All Zone)

Screenshot: https://i.postimg.cc/QtCfSWnz/screenshot-980.png

If I click to EDIT the Puerto Rico entry, I can see that "All Zones" is an option for the zone: https://i.postimg.cc/j5y4MQ4Z/screenshot-981.png

If I click to EDIT the United States entry, it only says "Please Select": https://i.postimg.cc/vBXc84YG/screenshot-982.png

This "Free Shipping Areas" zone has an ID of 2.

When I go to the database and run a query (SELECT * FROM `zen_zones_to_geo_zones` WHERE `geo_zone_id` = 2; ), this is what I see: https://i.postimg.cc/RVwqqpVP/screenshot-983.png

As you can see, the zone_id for the United States is NULL. If that is causing the issue, should I just change it to "0" like the Puerto Rico entry?

Quote:

Originally Posted by lat9

Jeff, you've got that bass-ackwards; the zone_id for the US (country id 223) is 0 (for all) while the one for Puerto Rico is NULL (since it doesn't have any defined states/provinces, aka zones).

Quote:

Originally Posted by Jeff_Mash

LOL - I'm really good at walking bass-ackwards! Thanks for clearing that up!

I guess we wait and see if typecasting the variable as an (int) will help remove the error.

My main concern was that somehow, these errors indicated customers who were trying to checkout and not able to. We get complaints all the time about having trouble checking out, and I couldn't figure out if these errors were related.

The fact that all the IP's are related to PayPal made me wonder if this was a checkout related issue, and if I could be losing sales.

Note that while looking at that window with united states selected and the drop down selection of "please select", selecting the update button will change that database value from 0 to null and that it would not return to a zero basically until the record was deleted or hard pressed to be a zero.