homepage Welcome to WebmasterWorld Guest from 54.167.185.110
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL export script arbitrarily stops
webfoo




msg:4601585
 6:37 pm on Aug 13, 2013 (gmt 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.";
?>

 

rainborick




msg:4601599
 7:40 pm on Aug 13, 2013 (gmt 0)

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.

webfoo




msg:4601607
 8:00 pm on Aug 13, 2013 (gmt 0)

It's actually hosted here locally at our office. As far as anyone knows, there isn't a hard set time limit.

DrDoc




msg:4601629
 9:25 pm on Aug 13, 2013 (gmt 0)

Why not just use
mysqldump?
penders




msg:4602001
 10:46 pm on Aug 14, 2013 (gmt 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?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved