Welcome to WebmasterWorld Guest from 54.156.92.243

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL export script arbitrarily stops

     
6:37 pm on Aug 13, 2013 (gmt 0)

Full Member

5+ Year Member

joined:Mar 22, 2008
posts: 317
votes: 0


I am trying to create a cronned PHP script to backup the entire contents of the database on a daily basis. The output is saved into an SQL file that could be imported back into the database server in the event we need to recover from a database loss.

The script loops through each table as follows:
1. generates a CREATE TABLE statement
2. loops through each row and generates an INSERT statement.

For testing purposes, I placed a LIMIT 0,20 on each table so that I could verify that the queries were being properly generated. After successful testing, I removed the LIMIT so that all records would be backed up. That's when it stopped working.

The script executes properly up to a certain point, when it suddenly stops without giving any error messages. It always stops at the same point, just before the INSERT phase of a particular table. Initially, I figured it may have been a memory or timeout error, so I added a set_time_limit(), ini_set(memory_limit), and ignore_user_abort(true).

Unfortunately, the script still continues to end randomly. I have tried executing it from the browser and from the command line with the exact same result.


<?php
ob_start();
error_reporting(E_ALL);
ignore_user_abort(TRUE);
ini_set('memory_limit','256M');

$con= mysqli_connect(*********);

$tableList = array();
$res = mysqli_query($con,"SHOW TABLES");
while($cRow = mysqli_fetch_array($res)) {
$tableList[] = $cRow[0];
}

echo "-- QUOTE TOOL BACKUP $now\n\n";
foreach($tableList as $table) {

echo "\n-- ".strtoupper($table)." ---------------- \n\n";

echo "CREATE TABLE `$table` (\n";
$lines='';
$q=mysqli_query($con,"DESCRIBE $table");
while($row=mysqli_fetch_array($q)) {
$lines.= "\t`".$row['Field']."` ";
$lines.= $row['Type'];
if($row['Null']=="NO") { $lines.= " NOT NULL"; } else { $lines.= " NULL"; }
if($row['Default']!='') { $lines.= " default '".$row['Default']."'"; }
if($row['Extra']!='') { $lines.= " ".$row['Extra']; }
$lines.= ",\n";
}
$q=mysqli_query($con,"DESCRIBE $table");
while($row=mysqli_fetch_array($q)) {
if($row['Key']!='') {
$lines.="\t";
if($row['Key']=='PRI') { $lines.= "PRIMARY KEY (`".$row['Field']."`)"; }
elseif($row['Key']=='UNI') { $lines.= "UNIQUE KEY (`".$row['Field']."`)"; }
elseif($row['Key']=='MUL') { $lines.= "KEY (`".$row['Field']."`)"; }
$lines.= ",\n";
}
}
echo substr($lines,0,-2);
echo "\n);\n\n";

$q=mysqli_query($con,"SELECT * FROM $table");
if(mysqli_num_rows($q)>=1) {
echo "INSERT INTO $table (";
$fields='';
$q=mysqli_query($con,"DESCRIBE $table");
while($row=mysqli_fetch_array($q)) {
$fields.=$row['Field'].", ";
}
echo substr($fields, 0,-2);
echo ") VALUES \n";

$v='';
$q=mysqli_query($con,"SELECT * FROM $table");
$cols=mysqli_num_fields($q);
while($row=mysqli_fetch_array($q)) {
set_time_limit(200);
$v.= "\t(";
$values='';
$i=0;
while($i<$cols) {
$values.="'".$row[$i]."', ";
$i++;
}
$v.= substr($values,0,-2);
$v.="),\n";
}
echo substr($v,0,-2).';';
}
echo "\n";
}

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 4);

echo "\n\n-- END. ----------- \n\n";

$content = ob_get_clean();

$file = fopen("backups/2013-08-13 14_36_00.sql","w+");
fwrite($file,$content);
fclose($file);

echo "BACKUP FILE SUCCESSFULLY CREATED.";
?>
7:40 pm on Aug 13, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 15, 2003
posts:942
votes: 27


If you're on a shared host, the host may have (in fact, probably) set a hard script execution time limit that you cannot override. This is a common cause of the problem you have.
8:00 pm on Aug 13, 2013 (gmt 0)

Full Member

5+ Year Member

joined:Mar 22, 2008
posts: 317
votes: 0


It's actually hosted here locally at our office. As far as anyone knows, there isn't a hard set time limit.
9:25 pm on Aug 13, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


Why not just use
mysqldump
?
10:46 pm on Aug 14, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2006
posts: 3123
votes: 0


Yes, I was also wondering why not use mysqldump?

But I am also rather curious as to the way you have written your script... why are you using the output buffer and effectively holding the entire dump in memory before writing it out? Why not open a file for appending and write to that as you go (or at least in chunks)?

You state that the script "always stops at the same point", but in the next paragraph you state, "the script still continues to end randomly"? Is it at the same point or is it random?
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members