Hi Guys!
I am using a backup script which takes a dump of a MySQL database. Now when a run the script, I get the below error. It shouldn’t be a problem as there’s only 2500 rows in the database. Does anyone know why this might happen and any potential fixes?
Is it MySQL giving me this error?
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 11445236 bytes) in /home/username/public_html/r_backups/mysqldump.class.php on line 163
Thanks in advance.
That is a PHP error. You tried to put too much data into temporary memory for the execution frame.
Without seeing the script, cant offer anything more than that.
Ok, so there’s too much data for PHP to handle? Would it be better if I flushed the output every few seconds?
Depends on your setup, but why not have PHP execute mysqldump on the system, rather than storing the result?
The answer to this is that it will be installed on multiple client systems and therefore the permissions do not always allow mysql dump to be executed.
So what would be the next best solution?
Cheers
My first impulse is to say, if this is a single-table pull, stagger the queries to pull parts of the table at a time, write the result out, dump the result, and pull the next block/table.
If you’re pulling multiple tables, pull one table at a time. At least, it will reduce your trouble to a certain table.
Here’s the MySQL class i’m using. As you can see i’m pulling multiple tables as I need to backup the entire database. Do you know which section I would need to modify?
<?php
/**
* Dump data from MySQL database
*
* @name MySQLDump
* @author Marcus Vinícius <mv@cidademais.com.br>
* @version 1.1 2005-06-01
* @example
*
* $dump = new MySQLDump();
* print $dump->dumpDatabase("mydb");
*
*/
class MySQLDump {
/**
* Dump data and structure from MySQL database
*
* @param string $database
* @return string
*/
function dumpDatabase($database) {
// Set content-type and charset
header('Content-Type: text/html; charset=iso-8859-1');
// Connect to database
$db = @mysql_select_db($database);
if (!empty($db)) {
// Get all table names from database
$c = 0;
$result = mysql_list_tables($database);
for($x = 0; $x < mysql_num_rows($result); $x++) {
$table = mysql_tablename($result, $x);
if (!empty($table)) {
$arr_tables[$c] = mysql_tablename($result, $x);
$c++;
}
}
// List tables
$dump = '';
for ($y = 0; $y < count($arr_tables); $y++){
// DB Table name
$table = $arr_tables[$y];
// Structure Header
$structure .= "-- \
";
$structure .= "-- Table structure for table `{$table}` \
";
$structure .= "-- \
\
";
// Dump Structure
$structure .= "DROP TABLE IF EXISTS `{$table}`; \
";
$structure .= "CREATE TABLE `{$table}` (\
";
$result = mysql_db_query($database, "SHOW FIELDS FROM `{$table}`");
while($row = mysql_fetch_object($result)) {
$structure .= " `{$row->Field}` {$row->Type}";
$structure .= (!empty($row->Default)) ? " DEFAULT '{$row->Default}'" : false;
$structure .= ($row->Null != "YES") ? " NOT NULL" : false;
$structure .= (!empty($row->Extra)) ? " {$row->Extra}" : false;
$structure .= ",\
";
}
$structure = ereg_replace(",\
$", "", $structure);
// Save all Column Indexes in array
unset($index);
$result = mysql_db_query($database, "SHOW KEYS FROM `{$table}`");
while($row = mysql_fetch_object($result)) {
if (($row->Key_name == 'PRIMARY') AND ($row->Index_type == 'BTREE')) {
$index['PRIMARY'][$row->Key_name] = $row->Column_name;
}
if (($row->Key_name != 'PRIMARY') AND ($row->Non_unique == '0') AND ($row->Index_type == 'BTREE')) {
$index['UNIQUE'][$row->Key_name] = $row->Column_name;
}
if (($row->Key_name != 'PRIMARY') AND ($row->Non_unique == '1') AND ($row->Index_type == 'BTREE')) {
$index['INDEX'][$row->Key_name] = $row->Column_name;
}
if (($row->Key_name != 'PRIMARY') AND ($row->Non_unique == '1') AND ($row->Index_type == 'FULLTEXT')) {
$index['FULLTEXT'][$row->Key_name] = $row->Column_name;
}
}
// Return all Column Indexes of array
if (is_array($index)) {
foreach ($index as $xy => $columns) {
$structure .= ",\
";
$c = 0;
foreach ($columns as $column_key => $column_name) {
$c++;
$structure .= ($xy == "PRIMARY") ? " PRIMARY KEY (`{$column_name}`)" : false;
$structure .= ($xy == "UNIQUE") ? " UNIQUE KEY `{$column_key}` (`{$column_name}`)" : false;
$structure .= ($xy == "INDEX") ? " KEY `{$column_key}` (`{$column_name}`)" : false;
$structure .= ($xy == "FULLTEXT") ? " FULLTEXT `{$column_key}` (`{$column_name}`)" : false;
$structure .= ($c < (count($index[$xy]))) ? ",\
" : false;
}
}
}
$structure .= "\
);\
\
";
// Header
$structure .= "-- \
";
$structure .= "-- Dumping data for table `$table` \
";
$structure .= "-- \
\
";
// Dump data
unset($data);
$result = mysql_query("SELECT * FROM `$table`");
$num_rows = mysql_num_rows($result);
$num_fields = mysql_num_fields($result);
for ($i = 0; $i < $num_rows; $i++) {
$row = mysql_fetch_object($result);
$data .= "INSERT INTO `$table` (";
// Field names
for ($x = 0; $x < $num_fields; $x++) {
$field_name = mysql_field_name($result, $x);
$data .= "`{$field_name}`";
$data .= ($x < ($num_fields - 1)) ? ", " : false;
}
$data .= ") VALUES (";
// Values
for ($x = 0; $x < $num_fields; $x++) {
$field_name = mysql_field_name($result, $x);
$data .= "'" . str_replace('\\"', '"', mysql_escape_string($row->$field_name)) . "'";
$data .= ($x < ($num_fields - 1)) ? ", " : false;
}
$data.= ");\
";
}
$data.= "\
";
$dump .= $structure . $data;
$dump .= "-- --------------------------------------------------------\
\
";
}
return $dump;
}
}
}
?>
Thanks in advance.
My suggestion would be that at the end of your for loop you output $dump to a file, and empty that variable.