duanlujiaji10335 2017-01-09 06:32
浏览 45
已采纳

是什么导致这个PHP脚本旨在备份大型mysql表以耗尽内存?

I wrote this script which is designed to backup MySQL tables selectively from PHP while reliably keeping the data types intact.

The problem is that recently, it has started to give me a memory exhaustion error, even though I am clearing the data on each cycle through the loop.

My code is as follows (i placed a comment showing the line numbers from the error as this is a much larger script - this is the entire backup portion. the $file param is actually a path but i have been too lazy to rename it):

public static function backup($tables, $file)
{
    $return       = ''; // LINE 1221
    $file_written = false;

    //get all of the tables
    if ($tables == '*') {
        $tables = array(); // LINE 1226
        $result = mysqli_query(self::$cn, 'SHOW TABLES');
        while ($row = mysqli_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }

    // Initialize File
    $filename = $file . 'db-backup-' . time() . '-' . (md5(implode(',', $tables))) . '.sql';
    echo $filename; die(); // placed this here so I could see
                           // if it was running out of memory above
                           // and this echos out fine, so the problem
                           // is somewhere below.
    $handle   = fopen($filename, 'w');
    fclose($handle);
    $handle = fopen($filename, 'a');

    //cycle through
    foreach ($tables as $table) {
        if ($table <> "") {
            $result = mysqli_query(self::$cn, 'SELECT * FROM `' . $table . '`');
            if ($result) {
                $num_fields = mysqli_num_fields($result);

                $row2 = mysqli_fetch_row(mysqli_query(self::$cn, 'SHOW CREATE TABLE `' . $table . '`'));
                if ($row2) {
                    $file_written = true;
                    $return .= 'DROP TABLE IF EXISTS `' . $table . '`;';
                    $return .= "

" . $row2[1] . ";

";

                    $field_flags = false;
                    fwrite($handle, $return);
                    $return = '';

                    for ($i = 0; $i < $num_fields; $i++) {
                        while ($row = mysqli_fetch_row($result)) {

                            if ($field_flags == false) {
                                for ($j = 0; $j < $num_fields; $j++) {
                                    $tmp = mysqli_fetch_field_direct($result, $j);
                                    if (strpos($tmp->flags, 'NOT_NULL') !== false) {
                                        $field_flags[$j] = false;
                                    } else {
                                        $field_flags[$j] = true;
                                    }
                                }
                            }

                            // var_dump($field_flags); die();
                            $return .= 'INSERT INTO `' . $table . '` VALUES(';
                            for ($j = 0; $j < $num_fields; $j++) {
                                $row[$j] = addslashes($row[$j]);
                                $row[$j] = preg_replace("/
/", "\
", $row[$j]);
                                if (isset($row[$j])) {
                                    if ($row[$j] <> '') {
                                        $return .= '"' . $row[$j] . '"';
                                    } else {
                                        if ($field_flags[$j]) {
                                            $return .= "NULL";
                                        } else {
                                            $return .= '""';
                                        }
                                    }
                                } else {
                                    if ($field_flags[$j]) {
                                        $return .= "NULL";
                                    } else {
                                        $return .= '""';
                                    }
                                }
                                if ($j < ($num_fields - 1)) {
                                    $return .= ',';
                                }
                            }
                            $return .= ");
";
                            fwrite($handle, $return);
                            $return = '';
                        }
                    }
                    $return .= "


";
                    fwrite($handle, $return);
                    $return = '';
                }
            }
            mysqli_free_result($result);
        }
    }
    fclose($handle);

    //save file
    if ($file_written) {
        return $filename;
    } else {
        if (file_exists($filename)) {
            unlink($filename);
        }
        return false;
    }
}

I wrote this about 7 years ago, and haven't had the time to rewrite this in PDO or fully assess whether this conversion would be of any benefit.

The message I am getting is as follows (line 31 is just calling the method):

PHP Fatal error:  Allowed memory size of 268435456 bytes exhausted (tried to allocate 32 bytes) in d:\server\htdocs\backup.php on line 1221, referer: http://example.com/index.html
PHP Stack trace:, referer: http://example.com/index.html
PHP   1. {main}() d:\server\htdocs\backup.php:0, referer: http://example.com/index.html
PHP   2. mysqli_db::backup() d:\server\htdocs\backup.php:31, referer: http://example.com/index.html
PHP   3. mysqli_fetch_field_direct() d:\server\htdocs\backup.php:1221, referer: http://example.com/index.html
PHP Fatal error:  Allowed memory size of 268435456 bytes exhausted (tried to allocate 32 bytes) in d:\server\htdocs\backup.php on line 1226, referer: http://example.com/index.html
  • 写回答

1条回答 默认 最新

  • douxiajia6309 2017-01-09 08:28
    关注

    Most likely it's the resultset, due to mysqlnd driver (this is my article about PDO, but it's applicable for mysqli as well).

    To solve, you have to add MYSQLI_USE_RESULT parameter to mysqli_query(),

    $result = mysqli_query(self::$cn, 'SELECT * FROM `' . $table . '`', MYSQLI_USE_RESULT);
    

    Also, move your SHOW CREATE TABLE query above SELECT, as you cannot run other queries while an unbuffered query is active.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 我想通过python处理sybase IQ库的一些数据,但是不知道sybase IQ库怎么连接,之前连接都是用linux上的 dbisql
  • ¥15 安装llama-cpp-python==0.1.83失败
  • ¥20 管道轴向耦合水击问题
  • ¥60 补全networkx TODO部分。
  • ¥15 有内推吗,云计算linux运维方向
  • ¥30 sort cuteSV.vcf by bcftools用IGV可视化出现报错
  • ¥100 SOS!对STK中导出的天体图像进行质心提取有没有人做过啊
  • ¥15 python 欧式距离
  • ¥15 运行qteasy报错
  • ¥15 遗传算法解决有工序顺序约束的大规模FJSP问题