Fatal error: Allowed memory size of 947912704 bytes exhausted

Topics: User Forum
Feb 24, 2014 at 7:33 PM
Error:

Fatal error: Allowed memory size of 947912704 bytes exhausted (tried to allocate 385 bytes) in /var/www/pricing/phpexcel/Classes/PHPExcel/CachedObjectStorage/PHPTemp.php on line 65

My source:

<?
/** Error reporting */
error_reporting(E_ALL);

ini_set('memory_limit', '5000M');

/** Include path **/
ini_set('include_path', ini_get('include_path').';phpexcel/Classes/');

/** PHPExcel */
include 'phpexcel/Classes/PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'phpexcel/Classes/PHPExcel/Writer/Excel2007.php';

// Initiate cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '5GB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

// Create new PHPExcel object
//echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Set properties
//echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Minerva - Pricing");
$objPHPExcel->getProperties()->setLastModifiedBy("Minerva - Pricing");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

// Add some data
//echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', utf8_encode('des_equipe_venda'));
$objPHPExcel->getActiveSheet()->SetCellValue('B1', utf8_encode('nova_equipe_venda'));   
$objPHPExcel->getActiveSheet()->SetCellValue('C1', utf8_encode('nom_pessoa_empresa'));
$objPHPExcel->getActiveSheet()->SetCellValue('D1', utf8_encode('ano'));
$objPHPExcel->getActiveSheet()->SetCellValue('E1', utf8_encode('semana'));
$objPHPExcel->getActiveSheet()->SetCellValue('F1', utf8_encode('des_categoria_comercial'));
$objPHPExcel->getActiveSheet()->SetCellValue('G1', utf8_encode('des_grupo_corte'));
$objPHPExcel->getActiveSheet()->SetCellValue('H1', utf8_encode('des_corte_comercial'));
$objPHPExcel->getActiveSheet()->SetCellValue('I1', utf8_encode('cod_item'));
$objPHPExcel->getActiveSheet()->SetCellValue('J1', utf8_encode('des_item'));
$objPHPExcel->getActiveSheet()->SetCellValue('K1', utf8_encode('qtd_item'));
$objPHPExcel->getActiveSheet()->SetCellValue('L1', utf8_encode('valor_un_nota'));
$objPHPExcel->getActiveSheet()->SetCellValue('M1', utf8_encode('valor_total_nota'));
$objPHPExcel->getActiveSheet()->SetCellValue('N1', utf8_encode('valor_un_tabela'));
$objPHPExcel->getActiveSheet()->SetCellValue('O1', utf8_encode('valor_total_tabela'));
$objPHPExcel->getActiveSheet()->SetCellValue('P1', utf8_encode('valor_un_custo'));
$objPHPExcel->getActiveSheet()->SetCellValue('Q1', utf8_encode('valor_total_custo'));
$objPHPExcel->getActiveSheet()->SetCellValue('R1', utf8_encode('dif_valor_item_nota_mnrv'));
$objPHPExcel->getActiveSheet()->SetCellValue('S1', utf8_encode('valor_dif_tabela_abaixo'));
$objPHPExcel->getActiveSheet()->SetCellValue('T1', utf8_encode('valor_dif_tabela_acima'));

//PREENCHE XLSX
$vendas = mssql_query("EXEC [dbpricing].[dbo].[spr_relatorio_base_real_venda_3_anual] '$data_inicio', '$data_fim 23:59:59', NULL;");

$r = 2;
while ( $row = mssql_fetch_assoc($vendas) ) {
    $row = array_map('utf8_encode', $row);  
    $objPHPExcel->getActiveSheet()->fromArray($row, NULL, 'A'.$r++);
}

// Rename sheet
//echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Base real de vendas');

// Save Excel 2007 file
//echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
}
?>

Can someone please explain what may be going on...

Thanks
Coordinator
Feb 24, 2014 at 8:55 PM
Edited Feb 24, 2014 at 8:56 PM
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '5GB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
You're seriously telling PHPExcel to use 5GB of physical memory before it starts to use file caching? Especially when you have less than 1GB of physical memory allocated to any PHP process?
Feb 25, 2014 at 10:37 AM
what do you recommend in this case?
Coordinator
Feb 25, 2014 at 11:45 AM
Edited Feb 25, 2014 at 11:45 AM
I recommend setting memoryCacheSize to a value a lot lower than the amount of physical memory you have available, like perhaps 32M
Feb 25, 2014 at 1:30 PM
Edited Feb 25, 2014 at 1:31 PM
Hello,

I set the php.ini to memory_limit "1000M" and memoryCacheSize to "32MB".

See error now:

Fatal error: Allowed memory size of 947912704 bytes exhausted (tried to allocate 346 bytes) in / var / www / pricing / PHPExcel / Classes / PHPExcel / CachedObjectStorage / PHPTemp.php on line 65

My query is returning about 491275 lines. Is it related?

What is the recommended setting in this case?