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
