Apache 在字符串连接循环中崩溃

发布于 2024-11-02 01:44:38 字数 1256 浏览 5 评论 0原文

我有一个脚本,可以打开一个巨大的 XLSX 文件并读取 3000 行数据,并将其保存到二维数组中。在 Apache 崩溃的所有地方中,它是在构建 MySQL 查询的简单循环中发生的。我知道这一点,因为如果我从应用程序中删除以下几行,它就会毫无问题地运行:

$query = "INSERT INTO `map.lmds.dots` VALUES";

foreach($data as $i => $row)
{
    $id = $row["Abonnementsid"];
    $eier = $row["Eier"];
    $status = $row["Status"];

    if($i !== 0) $query .= "\n,";

    $query .= "('$id', '$eier', '$status', '0', '0')";
}

echo $query;

我看不出代码有什么问题。

我正在使用 PHPExcel 和 dBug.php

为什么这个脚本会导致 Apache 崩溃?


编辑: 也许我应该详细说明崩溃的含义。我的意思是经典的 Windows“程序已停止工作”:


编辑:< /strong> 受其中一个答案启发的另一次尝试。 Apache 仍然崩溃:

$query = "INSERT INTO `map.lmds.dots` VALUES";
$records = array();

foreach($data as $i => &$row)
{
    $id = $row["Abonnementsid"];
    $eier = $row["Eier"];
    $status = $row["Status"];

    $records[] = "('$id', '$eier', '$status', '0', '0')";
}

echo $query . implode(",", $records);

编辑:我已经进一步缩小了范围。一旦我添加 foreach 循环,Apache 就会崩溃。

foreach($data as $i => $row) {};

I have a script that opens a huge XLSX file and reads 3000 rows of data, saving it to a two dimensional array. Of all places for Apache to crash, it does so in a simple loop that builds a MySQL query. I know this because if I remove the following lines from my application, it runs without issue:

$query = "INSERT INTO `map.lmds.dots` VALUES";

foreach($data as $i => $row)
{
    $id = $row["Abonnementsid"];
    $eier = $row["Eier"];
    $status = $row["Status"];

    if($i !== 0) $query .= "\n,";

    $query .= "('$id', '$eier', '$status', '0', '0')";
}

echo $query;

I can't see a thing wrong with the code.

I'm using PHPExcel and dBug.php

Why is this script crashing Apache?


EDIT: Perhaps I should elaborate on what I mean by crash. I mean a classic Windows "Program has stopped working":


EDIT: Another attempt inspired by one of the answers. Apache still crashes:

$query = "INSERT INTO `map.lmds.dots` VALUES";
$records = array();

foreach($data as $i => &$row)
{
    $id = $row["Abonnementsid"];
    $eier = $row["Eier"];
    $status = $row["Status"];

    $records[] = "('$id', '$eier', '$status', '0', '0')";
}

echo $query . implode(",", $records);

EDIT: I have narrowed it down further. As soon as I add a foreach loop, Apache crashes.

foreach($data as $i => $row) {};

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

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

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

z祗昰~ 2024-11-09 01:44:38

正如其他受访者所说,这很可能是内存问题,您应该检查 Apache 错误日志和 PHP 错误日志以获取更多信息。

假设这是一个内存问题,我建议您更改代码,以便在 foreach 循环内执行多个插入语句,而不是将整个内容存储在一个大字符串中并将其一次性发送到数据库。当然,这意味着您要对数据库进行 3000 多次调用,而不仅仅是一次,我希望这会慢一些,您可以通过使用准备好的语句来缓解这个问题,该语句应该会更高效。如果这仍然太慢,请尝试更改循环,以便仅在循环中每 N 次调用数据库。

Like the others respondents have said this is most likely a memory issue, you should check both your Apache error logs and your PHP error logs for more info.

Assuming this is a memory problem, I suggest you change your code so that you execute multiple insert statements inside the foreach loop rather than storing the whole thing in a big string and sending it to the database all at once. Of course, this means that you're making 3000+ calls to the database rather than just one, I'd expect this to be a bit slower, you can mitigate this problem by using a prepared statement which should be a bit more efficient. If this is still too slow, try changing your loop so that you only call the database every N times round the loop.

情栀口红 2024-11-09 01:44:38

在允许的执行时间内,字符串连接量和所涉及的字符串数据量可能太多而无法一次处理。

您可以尝试仅收集数组中的值并将它们放在最后:

$query = "INSERT INTO `map.lmds.dots` VALUES";
$records = array();
foreach($data as $i => $row) {
    $records[] = "('".mysql_real_escape_string($row["Abonnementsid"])."', '".mysql_real_escape_string($row["Eier"])."', '".mysql_real_escape_string($row["Status"])."', '0', '0')";
}
$query .= implode("\n,", $records);

或者以块的形式插入记录:

$query = "INSERT INTO `map.lmds.dots` VALUES";
$records = array();
foreach($data as $i => $row) {
    $records[] = "('".mysql_real_escape_string($row["Abonnementsid"])."', '".mysql_real_escape_string($row["Eier"])."', '".mysql_real_escape_string($row["Status"])."', '0', '0')";
    if ($i % 1000 === 999) {
        mysql_query($query . implode("\n,", $records));
        $records = array();
    }
}
if (!empty($records)) {
    mysql_query($query . implode("\n,", $records));
}

也可以参考 foreach 以避免创建数组的内部副本:

foreach($data as $i => &$row) {
    // …
}

The amount of string concatenation and the amount of string data involved could be too much to handle at once during the permitted execution time.

You could try to just collect the values in an array and put them together at the end:

$query = "INSERT INTO `map.lmds.dots` VALUES";
$records = array();
foreach($data as $i => $row) {
    $records[] = "('".mysql_real_escape_string($row["Abonnementsid"])."', '".mysql_real_escape_string($row["Eier"])."', '".mysql_real_escape_string($row["Status"])."', '0', '0')";
}
$query .= implode("\n,", $records);

Or insert the records in chunks:

$query = "INSERT INTO `map.lmds.dots` VALUES";
$records = array();
foreach($data as $i => $row) {
    $records[] = "('".mysql_real_escape_string($row["Abonnementsid"])."', '".mysql_real_escape_string($row["Eier"])."', '".mysql_real_escape_string($row["Status"])."', '0', '0')";
    if ($i % 1000 === 999) {
        mysql_query($query . implode("\n,", $records));
        $records = array();
    }
}
if (!empty($records)) {
    mysql_query($query . implode("\n,", $records));
}

Also try it with reference in foreach to avoid that an internal copy of the array is made:

foreach($data as $i => &$row) {
    // …
}
水水月牙 2024-11-09 01:44:38

这听起来确实像是内存问题。也许它与构建 SQL 查询的循环无关。这可能与之前读取“非常”大的文件有关。并且循环将内存使用量推向极限。读取文件后是否尝试释放内存?

您可以使用 memory_get_peak_usage() 和 memory_get_usage() 获取有关消耗内存的更多信息。

如果这不能解决您的问题。安装 Xdebug 或 Zend Debugger 等调试器并进行一些分析。

This does sounds like a memory issue. Maybe it has nothing to do with the loop building the SQL query. It could be related to reading the "very" large file before that. And the loop pushing memory usage over the limit. Did you try freeing up memory after reading the file?

You can use memory_get_peak_usage() and memory_get_usage() to get some more info about consumed memory.

If that doesn't solve your issue. Install a debugger like Xdebug or Zend Debugger and do some profiling.

梓梦 2024-11-09 01:44:38

好吧,事实证明,将 PHP 从 5.3.1 更新到 5.3.5 就解决了这个问题。我仍然不知道是什么导致 PHP 崩溃,但我想我的 PHP 可能只是被破坏了,需要重新安装。

Alright, it turns out that updating PHP from 5.3.1 to 5.3.5 made the problem go away. I still have no idea as to what made PHP crash in the first place, but I suppose my PHP could simply have been broken and in need of a reinstall.

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