I am having major problems loading a 20+MB comma-delimited file into MySQL. Initially the loader was timing out, so I fixed that with periodic use of set_time_limit(). Now, even after doing everything I can imagine, the system aborts with an OUT-OF-MEMORY message. Tracking it I have found with each block of rows added the memory usage (determined with memory_get_usage() ) is climbing and I can find nothing to free that memory. Anyone have some options this old man hasn't thought of?
Memory usage:
Loading data from ResFile050506.txt. Memory usage at beginning is 398168
Memory used: 8618824 after extension request; added 4500
Memory used: 24891536 after extension request; added 13500
Fatal error: Allowed memory size of 26214400 bytes exhausted (tried to allocate 4065 bytes)
Code:
<?php
// .......... //
/* field seperators for strings ($strdiv) and numerics ($numdiv) */
$strdiv = "\",";
$numdiv = ",";
$breakpoint = $load_order[$sequence]["breaks"];
/* counters to track how many records were read from file ($readin) and how many were successfully loaded ($added) */
$added=0;
$prevadded=0;
$readin=0;
if ($datfp = fopen($loadfile, "r")) { /* open the DATA file for loading */
/* load strategy for this operation if flush-and-fill so delete everything in the table first */
$nConnID = opendb();
@mysql_query("DELETE FROM ".$load_order[$sequence]["table"]." WHERE 1", $nConnID) or die("ERROR: ".mysql_error());
@mysql_query("ALTER TABLE ".$load_order[$sequence]["table"]." PACK_KEYS=0 CHECKSUM=0 DELAY_KEY_WRITE=0 AUTO_INCREMENT=1", $nConnID) or die("ERROR: ".mysql_error());
closedb($nConnID);
/* now we're ready to start loading from this file */
while (!feof($datfp) ) {
$data = fgets($datfp); /* get one record from the buffer. fgets() will pull up to the \n character that terminates each line. */
$ptr=0; /* ready for the records. set the pointer, PTR, to the starting point. */
$length = strlen($data);
$record = substr($data, 0, $length);
$readin++; /* got a data record, so count it as read */
$i=0; /* used to traverse the NAMES array for the fields */
/* now walk the string and extract the variables to load into the data load array, $vars */
while ( $ptr < $length ) {
if ( substr($record, $ptr, 1) == "\"" ) { /* it's a string var find the closing quotes to end the field value */
$ptr++;
/* move past the first quote mark */
if ($i == $lastelement) { $end = $length; }else{ $end = strpos ( $record, $strdiv, $ptr ); }
if ( $ptr == $end ) { /* no value in field, so load empty variable */
$var = "";
$ptr = $ptr + 2;
}else{ /* has a value, so load it character per time */
$var = "";
for ($j=$ptr; $j < $end; $j++) { $var .= substr($record, $j, 1); }
$ptr = $end+2;
}
}else{ // not a string, so numerics' terminal character is the comma
if ($i == $lastelement) { $end = $length; }else{ $end = strpos ( $record, $numdiv, $ptr ); }
$var = "";
for ($j=$ptr; $j < $end; $j++) { $var .= substr($record, $j, 1); }
$ptr = $end+1;
}
/* we have the value in $var, so clean the data and save it to the load array */
$vars[$names[$i]] = prep_inputs($var);
$i++; /* this will move to the next array variable to get */
}
if ( ($added % $breakpoint) == 0 AND ($added > ($prevadded+10)) ) {
/* need to check that progress is still being made before extending time limits */
set_time_limit (120);
$prevadded = $added;
}else{ break; }
$update = date("Y-m-d H:i:s", time()); /* set the update value for those tables that use last update dates */
/* this record is loaded into the load array and ready to hit the database */
$recadded = load_record($vars, $update, $load_order[$sequence]["table"]);
$added += $recadded;
unset($vars);
unset($data);
} /* end of data file load */
fclose($datfp);
} /* end of data file open */
// __clean up and select next action options __ //
?>
