1,105,594 Community Members

Fatal error: Allowed memory size of 134217728 bytes exhausted

Member Avatar
54uydf
Junior Poster
136 posts since Sep 2009
Reputation Points: 1 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi, I'm getting this error:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 576 bytes) in 'path' on line 207
what I'm doing is getting an excel file with about 8000+ rows and trying to move them into a table in my DB.
a FOR loop is putting data from each row of the excel file into a class vars c,f,g..,m. and then calls this insert() func (for every row). here's a part of the code and line 207 is highlighted. how can I fix this error?

function insert() {
	$sql = "INSERT INTO grades VALUES ( '".$this->c."','".
		$this->f."','".$this->g."','".
		$this->h."','".$this->i."','".
		$this->j."','".$this->k."','".
		$this->l."','".$this->m."' )";
	$dblink = null;
	try	{
		[B]$dblink = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);[/B]		
		mysql_select_db(DB_NAME,$dblink);
		mysql_set_charset('utf8', $dblink) or die ('Error changing charset to hebrew');
	} catch(Exception $ex) {
		echo "Could not connect to " . DB_HOST . ":" . DB_NAME . "\n";
		echo "Error: " . $ex->message;
		exit;
	}
		
	$retid = mysql_query($sql,$dblink) or die(mysql_error());
	if (!$retid) { 
		echo( mysql_error()); 
	}
	
	if(is_resource($dblink)) {		
		mysql_close($dblink);
	}		
	return $retid;	
}

I can PM the whole class code if that is necessary.

thank you

Member Avatar
pritaeas
mod_pritaeas
11,315 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 156 [?]
Moderator
Featured
Sponsor
 
0
 

You keep opening and closing the mysql connection. Try opening it before your loop and closing it when you are done.

Member Avatar
54uydf
Junior Poster
136 posts since Sep 2009
Reputation Points: 1 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

alright, I did that but now I'm getting the same error only from different script.
it's a PHPExcel script, and it's too long to post here..I'll post the function with the line that gives error..but I don't know what I can change here, have no idea where is this function is called from even.. is there a way to just allow more memory? don't know if it's a good idea, but I gotta make this thing work somehow

/**
	 * Get cell at a specific coordinate by using numeric cell coordinates
	 *
	 * @param	string $pColumn		Numeric column coordinate of the cell
	 * @param	string $pRow		Numeric row coordinate of the cell
	 * @return	PHPExcel_Cell		Cell that was found
	 */
	public function getCellByColumnAndRow($pColumn = 0, $pRow = 1)
	{
		$columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
		$coordinate = $columnLetter . $pRow;

		if (!$this->_cellCollection->isDataSet($coordinate)) {
			[B]$cell = $this->_cellCollection->addCacheData($coordinate, new PHPExcel_Cell($columnLetter, $pRow, null, PHPExcel_Cell_DataType::TYPE_NULL, $this));[/B]			$this->_cellCollectionIsSorted = false;

			if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < $pColumn)
				$this->_cachedHighestColumn = $columnLetter;

			$this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow);

			return $cell;
		}

		return $this->_cellCollection->getCacheData($coordinate);
	}
Member Avatar
cwarn23
Occupation: Genius
3,028 posts since Sep 2007
Reputation Points: 387 [?]
Q&As Helped to Solve: 281 [?]
Skill Endorsements: 15 [?]
Team Colleague
Featured
 
1
 

No wonder why. You php configuration is limited to 13M of data. Your excel file is probably 5 or 10M. I would link you to some recourses on how to fix it but again happygeek has told me not to do so. So I can only say what the problem is and not give paragraphs of detailed descriptions on things that cause the problem and how to solve it along with side effects on some servers as well as warnings as per usage.

Member Avatar
54uydf
Junior Poster
136 posts since Sep 2009
Reputation Points: 1 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

my file is only 601 KB, has 4 sheets, and about 2000 rows on each sheet..

Question Answered as of 2 Years Ago by pritaeas and cwarn23
Member Avatar
mschroeder
Work Harder
655 posts since Jul 2008
Reputation Points: 251 [?]
Q&As Helped to Solve: 134 [?]
Skill Endorsements: 8 [?]
Team Colleague
 
0
 

Your memory limit is set to 128MB not 13MB. In my experience with PHPExcel, you can expect between 1 - 10KB of memory usage on average per cell. Formulas and formatting causing even larger memory footprints.

While it will require converting your workbook into 4 csv files, you will then be able to iterate over the individual files by line and your memory footprint will be tiny.

  • Create one database connection.
  • Open the file by creating an instance of an SplFileObject
  • Set the READ_CSV flag (http://www.php.net/manual/en/splfileobject.setflags.php)
  • Set the CSV Controls to match your file's delimiter and enclosure (http://www.php.net/manual/en/splfileobject.setcsvcontrol.php)
  • Iterate over the files, line by line (see the example on the setCsvControl manual page).
  • Execute an insert for each line OR build a large insert statement and execute only 1 query.
    • Executing an insert for each line, will allow you to use prepared statements and will use less memory, but will take longer to execute
    • Executing one large query will make using prepared statements more difficult, will use more memory, but will execute faster.

If this is a big project and you really must work with Excel files, than I would suggest budgeting the money for the excellent LibXL, http://www.libxl.com/home.html This library also has a native php extension https://github.com/iliaal/php_excel

Member Avatar
54uydf
Junior Poster
136 posts since Sep 2009
Reputation Points: 1 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

thank you, I haven't heard of this library before, eitherway I've been working for few months with phpexcel and it's sort of too late to change the code or the library..but good to know for next time..which I hope never comes lol
no more working with excel for me please!

Member Avatar
digital-ether
Nearly a Posting Virtuoso
1,264 posts since Sep 2005
Reputation Points: 399 [?]
Q&As Helped to Solve: 102 [?]
Skill Endorsements: 11 [?]
Team Colleague
 
0
 

Do you have more memory you that you can increase your PHP memory limit to? Edit PHP.ini.

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: