What is causing this php script designed to backup large mysql tables to run out of memory?

Question

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 .= "\n\n" . $row2[1] . ";\n\n";

                    $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("/\n/", "\\n", $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 .= ");\n";
                            fwrite($handle, $return);
                            $return = '';
                        }
                    }
                    $return .= "\n\n\n";
                    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

Answer 1

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.