homepage Welcome to WebmasterWorld Guest from 54.145.209.80
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
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

5+ Year Member



 
Msg#: 4601583 posted 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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4601583 posted 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

5+ Year Member



 
Msg#: 4601583 posted 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

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



 
Msg#: 4601583 posted 9:25 pm on Aug 13, 2013 (gmt 0)

Why not just use
mysqldump?
penders

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



 
Msg#: 4601583 posted 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