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 Abaqus打不开cae文件怎么办?
  • ¥20 双系统开机引导中windows系统消失问题?
  • ¥15 小程序准备上线,软件开发公司需要提供哪些资料给甲方
  • ¥15 关于生产日期批次退货退款,库存回退的问题
  • ¥15 手机应用的时间可以修改吗
  • ¥15 docker 运行OPEN-webui异常
  • ¥15 麒麟系统如何删除光盘刻录痕迹
  • ¥15 recipe通过gem协议传的是什么
  • ¥15 TS2307: Cannot find module 'cc'.
  • ¥15 100小时学会sap 书上pp章节5.22,标准成本计算逻辑?