当前位置： 文江博客话题详情

$_SERVER['remote_port'] 会影响MySQL查询成功吗？

发布于 2024-12-01 02:48:45 字数 6494 浏览 1 评论 0原文

我在电子商务网站上遇到了一个非常奇怪的问题：一些（但不是全部）更新订单状态的查询由于某种神秘的原因而失败。

客户成功付款后，支付网关会调用在线商店中的 php 脚本，该脚本应更新相应订单的状态（从“待处理”到“已确认”）。

这在某些情况下有效，但在其他情况下则无效。我注意到 order_status_update() 在从特定 IP（和相应端口）而不是从另一个 IP 调用回调 URL 时起作用的模式。

我已经检查了代码并使用大量日志记录对其进行了检测，并且所有内容都检查到了查询级别，但在一种情况下它有效，而在另一种情况下它失败。

我能看到的唯一区别是：

  1. 调用回调脚本的服务器的IP、
  2. 调用回调脚本的服务器的远程端口。

据我所知，调用的 $_SERVER['REMOTE_ADDR'] 不应该对任何执行产生影响（当然，除非脚本明确地执行了相应的操作，但事实并非如此），所以我想知道是否$_SERVER['REMOTE_PORT'] 可能会对 MySQL/数据库产生影响，导致查询可能失败？

当脚本/查询成功时，REMOTE_PORT 为 18436，当失败时，REMOTE_PORT 为 49495。

我已附加两个订单的日志记录，第一个成功，然后第二个失败 - 您可以看到它们实际上是相同的（除了 order_id 和 mdate，如预期的那样）

何时起作用：

nab_notify - calling order_status_update with d[]=:Array\n(\n    [order_id] => 2860\n    [order_status] => C\n    [notify_customer] => Y\n)\n
ps_order::order_status_update - d[]=Array\n(\n    [order_id] => 2860\n    [order_status] => C\n    [notify_customer] => Y\n)\n
ps_order::order_status_update - curr_order_status:   order_status:C   notify_customer:1
ps_order::order_status_update - UPDATE vm_orders (1) w/fields[]=Array\n(\n    [order_status] => C\n    [mdate] => 1314093024\n)\n
ps_order::order_status_update - UPDATE vm_orders (2) order_id:[2860]
ps_order::order_status_update - UPDATE vm_orders (3) QUERY:[UPDATE `#__vm_orders` SET `order_status` = 'C',\n`mdate` = '1314093024'\nWHERE order_id='2860']
ps_order::order_status_update - UPDATE vm_orders (4) qresult:[1]

何时不起作用：

nab_notify - calling order_status_update with d[]=:Array\n(\n    [order_id] => 2863\n    [order_status] => C\n    [notify_customer] => Y\n)\n
ps_order::order_status_update - d[]=Array\n(\n    [order_id] => 2863\n    [order_status] => C\n    [notify_customer] => Y\n)\n
ps_order::order_status_update - curr_order_status:   order_status:C   notify_customer:1
ps_order::order_status_update - UPDATE vm_orders (1) w/fields[]=Array\n(\n    [order_status] => C\n    [mdate] => 1314137858\n)\n
ps_order::order_status_update - UPDATE vm_orders (2) order_id:[2863]
ps_order::order_status_update - UPDATE vm_orders (3) QUERY:[UPDATE `#__vm_orders` SET `order_status` = 'C',\n`mdate` = '1314137858'\nWHERE order_id='2863']
ps_order::order_status_update - UPDATE vm_orders (4) qresult:[0]

给定“相同”查询，有人可以解释为什么一个会失败而另一个会成功吗？

谢谢， Eric

更新 1： 我添加了查询级别日志记录并发现了一些错误，日志显示：

ERROR 2006 - MySQL 服务器已消失

...但奇怪的是，不适用于其中的所有查询这次特别的会议（你猜这很令人鼓舞？）。我在痕迹中留下了换行符，以防它们有方向。

我经过几次搜索和其他论坛帖子后，我检查了 php 设置，看看

max_execution_time = 60
max_input_time = 60

哪个时间应该足够？

奇怪的是，这种情况似乎只在从特定 IP 发出对特定 URL 的请求时才会发生（这次与其他订单状态未更新时的 IP 相同）。

2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT `order_number`, `user_id`, `order_subtotal`,
                `order_total`, `order_currency`, `order_tax`, 'order_status', 
                `order_shipping_tax`, `coupon_discount`, `order_discount`
            FROM `jos_vm_orders` 
            WHERE `order_id`='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT `order_number`, `user_id`, `order_subtotal`,
                `order_total`, `order_currency`, `order_tax`, 'order_status', 
                `order_shipping_tax`, `coupon_discount`, `order_discount`
            FROM `jos_vm_orders` 
            WHERE `order_id`='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT user_id,order_total,order_status FROM jos_vm_orders WHERE jos_vm_orders.order_id='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT user_id,order_total,order_status FROM jos_vm_orders WHERE jos_vm_orders.order_id='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=UPDATE `jos_vm_orders` SET `order_status` = 'C',
`mdate` = '1314240914'
WHERE order_id='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=UPDATE `jos_vm_orders` SET `order_status` = 'C',
`mdate` = '1314240914'
WHERE order_id='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=INSERT INTO `jos_vm_order_history` (`order_id`,
`order_status_code`,
`date_added`,
`customer_notified`,
`comments`) VALUES (
'2868'
,'C'
,'2011-08-25 12:25:14'
,'1'
,''
)
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=INSERT INTO `jos_vm_order_history` (`order_id`,
`order_status_code`,
`date_added`,
`customer_notified`,
`comments`) VALUES (
'2868'
,'C'
,'2011-08-25 12:25:14'
,'1'
,''
)
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT order_item_id, event_reg_id FROM jos_vm_order_item WHERE order_id=2868
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT order_item_id, event_reg_id FROM jos_vm_order_item WHERE order_id=2868
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT id FROM jos_menu WHERE link='index.php?option=com_virtuemart' AND published=1
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT id FROM jos_menu WHERE link='index.php?option=com_virtuemart' AND published=1
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT vendor_name,contact_email FROM jos_vm_vendor WHERE vendor_id='1'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT vendor_name,contact_email FROM jos_vm_vendor WHERE vendor_id='1'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT first_name,last_name,user_email,order_status_name,country,order_currency FROM jos_vm_order_user_info,jos_vm_orders,jos_vm_order_status WHERE jos_vm_orders.order_id = '2868' AND jos_vm_orders.user_id = jos_vm_order_user_info.user_id AND jos_vm_orders.order_id = jos_vm_order_user_info.order_id AND order_status = order_status_code
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT first_name,last_name,user_email,order_status_name,country,order_currency FROM jos_vm_order_user_info,jos_vm_orders,jos_vm_order_status WHERE jos_vm_orders.order_id = '2868' AND jos_vm_orders.user_id = jos_vm_order_user_info.user_id AND jos_vm_orders.order_id = jos_vm_order_user_info.order_id AND order_status = order_status_code
原文

I'm experiencing a very strange issue with an e-commerce website: some queries, but not all, to update order's statuses are failing for some mysterious reason.

Upon a successful payment by a customer, the payment gateway calls a php-script in the online shop that should update the appropriate order's status (from 'pending' to 'confirmed').

This works in certain cases, but not others. I noticed the pattern that order_status_update() works when the callback URL is called from a particular IP (and corresponding port), but not from another.

I've gone through the code and instrumented it with a bunch of logging, and everything checked out down to the query level, but in one case it works and the other case it fails.

The only difference I can see is:

  1. the IP of server calling the callback script,
  2. the remote port of the server calling the callback script.

To the best of my knowledge, the $_SERVER['REMOTE_ADDR'] of a call should not make a difference to anything executing (unless of course the script explicitly does something accordingly, which it doesn't), so I'm wondering if the $_SERVER['REMOTE_PORT'] could have an effect on a MySQL/database that would cause a query to potentially fail?

When the script/query is successful, the REMOTE_PORT is 18436, when it fails the REMOTE_PORT is 49495.

I've attached the logging from the two orders, the first is successful, then second failed -- you can see they are effectively identical (except for the order_id and mdate, as expected)

When it works:

nab_notify - calling order_status_update with d[]=:Array\n(\n    [order_id] => 2860\n    [order_status] => C\n    [notify_customer] => Y\n)\n
ps_order::order_status_update - d[]=Array\n(\n    [order_id] => 2860\n    [order_status] => C\n    [notify_customer] => Y\n)\n
ps_order::order_status_update - curr_order_status:   order_status:C   notify_customer:1
ps_order::order_status_update - UPDATE vm_orders (1) w/fields[]=Array\n(\n    [order_status] => C\n    [mdate] => 1314093024\n)\n
ps_order::order_status_update - UPDATE vm_orders (2) order_id:[2860]
ps_order::order_status_update - UPDATE vm_orders (3) QUERY:[UPDATE `#__vm_orders` SET `order_status` = 'C',\n`mdate` = '1314093024'\nWHERE order_id='2860']
ps_order::order_status_update - UPDATE vm_orders (4) qresult:[1]

When it doesn't work:

nab_notify - calling order_status_update with d[]=:Array\n(\n    [order_id] => 2863\n    [order_status] => C\n    [notify_customer] => Y\n)\n
ps_order::order_status_update - d[]=Array\n(\n    [order_id] => 2863\n    [order_status] => C\n    [notify_customer] => Y\n)\n
ps_order::order_status_update - curr_order_status:   order_status:C   notify_customer:1
ps_order::order_status_update - UPDATE vm_orders (1) w/fields[]=Array\n(\n    [order_status] => C\n    [mdate] => 1314137858\n)\n
ps_order::order_status_update - UPDATE vm_orders (2) order_id:[2863]
ps_order::order_status_update - UPDATE vm_orders (3) QUERY:[UPDATE `#__vm_orders` SET `order_status` = 'C',\n`mdate` = '1314137858'\nWHERE order_id='2863']
ps_order::order_status_update - UPDATE vm_orders (4) qresult:[0]

Given the "same" query, can someone shed some light on why one would fail whereas another would succeed?

Thanks,
Eric

UPDATE 1: I added query level logging and uncovered some errors, the logs reveal:

ERROR 2006 - MySQL server has gone away

... but strangely not for all queries within this particular session (guess that's encouraging?). I left the line-breaks in the traces just in case they have a bearing.

I after a couple searches and other forum posts, I checked the php settings and see

max_execution_time = 60
max_input_time = 60

which should be sufficient time?

What is strange is that this only seems to occur when a request to particular URL is done from a specific IP (the same IP this time as when the other orders statuses were not updated).

2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT `order_number`, `user_id`, `order_subtotal`,
                `order_total`, `order_currency`, `order_tax`, 'order_status', 
                `order_shipping_tax`, `coupon_discount`, `order_discount`
            FROM `jos_vm_orders` 
            WHERE `order_id`='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT `order_number`, `user_id`, `order_subtotal`,
                `order_total`, `order_currency`, `order_tax`, 'order_status', 
                `order_shipping_tax`, `coupon_discount`, `order_discount`
            FROM `jos_vm_orders` 
            WHERE `order_id`='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT user_id,order_total,order_status FROM jos_vm_orders WHERE jos_vm_orders.order_id='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT user_id,order_total,order_status FROM jos_vm_orders WHERE jos_vm_orders.order_id='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=UPDATE `jos_vm_orders` SET `order_status` = 'C',
`mdate` = '1314240914'
WHERE order_id='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=UPDATE `jos_vm_orders` SET `order_status` = 'C',
`mdate` = '1314240914'
WHERE order_id='2868'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=INSERT INTO `jos_vm_order_history` (`order_id`,
`order_status_code`,
`date_added`,
`customer_notified`,
`comments`) VALUES (
'2868'
,'C'
,'2011-08-25 12:25:14'
,'1'
,''
)
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=INSERT INTO `jos_vm_order_history` (`order_id`,
`order_status_code`,
`date_added`,
`customer_notified`,
`comments`) VALUES (
'2868'
,'C'
,'2011-08-25 12:25:14'
,'1'
,''
)
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT order_item_id, event_reg_id FROM jos_vm_order_item WHERE order_id=2868
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT order_item_id, event_reg_id FROM jos_vm_order_item WHERE order_id=2868
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT id FROM jos_menu WHERE link='index.php?option=com_virtuemart' AND published=1
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT id FROM jos_menu WHERE link='index.php?option=com_virtuemart' AND published=1
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT vendor_name,contact_email FROM jos_vm_vendor WHERE vendor_id='1'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT vendor_name,contact_email FROM jos_vm_vendor WHERE vendor_id='1'
2011-08-25  02:54:48    -   JDatabaseMySQL::query - SQL=SELECT first_name,last_name,user_email,order_status_name,country,order_currency FROM jos_vm_order_user_info,jos_vm_orders,jos_vm_order_status WHERE jos_vm_orders.order_id = '2868' AND jos_vm_orders.user_id = jos_vm_order_user_info.user_id AND jos_vm_orders.order_id = jos_vm_order_user_info.order_id AND order_status = order_status_code
2011-08-25  02:54:48    -   JDatabaseMySQL::query - !!!! ERROR !!!! 2006 - MySQL server has gone away SQL=SELECT first_name,last_name,user_email,order_status_name,country,order_currency FROM jos_vm_order_user_info,jos_vm_orders,jos_vm_order_status WHERE jos_vm_orders.order_id = '2868' AND jos_vm_orders.user_id = jos_vm_order_user_info.user_id AND jos_vm_orders.order_id = jos_vm_order_user_info.order_id AND order_status = order_status_code

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。
列表为空，暂无数据

关于作者

小清晰的声音

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

游缘惊梦

文章 0 评论 0

小兔几

文章 0 评论 0

Glik

文章 0 评论 0

生生漫

文章 0 评论 0

Luxian

文章 0 评论 0

Champion-Ming

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文