1
I’m having a hard time with my back imagem when pulling data in Mysql large volume and a single line has string very large that is column imagem (Registration has around 6,000-10,000 per shipment).
The column imagem has data from a compressed file copy in ZIP and converted into base64() (Print example below).
Stream: Loads Files (csv or txt) and pdf if there is, while uploading files is sent to database, make copy of the files csv/txt and pdf, compact in ZIP of pdf, converts into base64() of the archive ZIP and finally creates file XML to be sent via SOAP.
In other words, in the file creation part XML I am getting memory error even setting to 600MB in function ini_set().
Fatal error: Allowed memory size of 629145600 bytes exhausted (tried to allocate 507512456 bytes) in /home/techgame/public_html/tools-Cra/conf/Function.php on line 80
The 80 line error ends in the part endwhile; well likely related to Mysql.
<?php
while($col = mysqli_fetch_array($titulo)):
$xml .= '<tr t01="1" t02="555" t03="" t04="" t05="" t06="" t07="" t08="00000-000" t09="CUIABA" t10="SP" t11="'.$col['nossonumero'].'" t12="'.$col['especie'].'" t13="'.$col['numerotitulo'].'" t14="0" t15="'.$col['imagem'].'" t16="'.$rows.'"/>';
$rows++;
endwhile;
?>
Follow the XML Layout when it is mounting before creating XML file.
Note: Using hosting, has 1GB of RAM.
<?php
function layoutXML($conn,$value){
$comarca = mysqli_query($conn,"SELECT comarca FROM remessa WHERE sequencial = '{$value}' GROUP BY comarca");
$remessa = mysqli_query($conn,"SELECT * FROM sequencial WHERE sequencial='{$value}'");
$remessa = mysqli_fetch_array($remessa);
$xml = '<?xml version="1.0" encoding="ISO-8859-1"?><remessa><nome_arquivo> '.substr($remessa["arquivo_ref"],-12).' </nome_arquivo>';
ini_set('default_socket_timeout',1000);
ini_set('memory_limit','600M');
while($municipio = mysqli_fetch_array($comarca)):
$titulo = mysqli_query($conn,"SELECT * FROM remessa WHERE sequencial='{$value}' AND comarca='".$municipio['comarca']."'");
$xml .= '<comarca CodMun="'.comarca($municipio[0]).'"><hd h01="0" h02="555" h03="555" h04="'.data($remessa["data"]).'" h05="'.$value.'" h06="" h07="" h08="'.mysqli_num_rows($titulo).'" h09="0" h10="043" h11="'.comarca($municipio[0]).'" h12="" h13="1" />';
$rows = 2;
while($col = mysqli_fetch_array($titulo)):
$xml .= '<tr t01="1" t02="555" t03="" t04="" t05="" t06="" t07="" t08="00000-000" t09="CUIABA" t10="SP" t11="'.$col['nossonumero'].'" t12="'.$col['especie'].'" t13="'.$col['numerotitulo'].'" t14="0" t15="'.$col['imagem'].'" t16="'.$rows.'"/>';
$rows++;
endwhile;
$soma = mysqli_query($conn,"SELECT SUM(saldo) total FROM remessa WHERE sequencial='{$value}' AND comarca='".$municipio['0']."'");
$soma = mysqli_fetch_array($soma);
$xml .= '<tl t01="9" t02="555" t03="" t04="'.data($remessa["data"]).'" t05="'.(mysqli_num_rows($titulo)*3).'" t06="'.number_format($soma['total'],2,'.','').'" t07="" t08="'.$rows.'"/></comarca>';
endwhile;
ini_set('default_socket_timeout',60);
ini_set('memory_limit','128M');
$xml .= '</remessa>';
return $xml;
}
function createXML($xml,$path){
$fp = fopen($path,"w+");
fwrite($fp,$xml);
fclose($fp);
}
createXML(layoutXML($conn,"5000"),"bkp/xml/nome_arquivo.xml");
?>
Instead of fopen and fwrite why not try file_put_contents (inside the looping)? So you wouldn’t need to leave the $xml variable with the immense content that causes this memory overflow. Another solution would be to use Generator https://www.php.net/manual/en/language.generators.overview.php
– Marcos Xavier
This does not seem to have to do with Mysql, but with PHP itself and its string concatenation.
– Inkeliz
@Marcosxavier Thanks for the information, I used your suggestion
file_put_contents()within looping, I was successful. When testing sending via SOAP I was unsuccessful when file is too large (around 450-500MB). Without image is around 10MB, this successful sending. I usedfile_get_contents().– Augusto Junior
@Inkeliz Yes you are right, it is not related to Mysql. With information from Marcos Xavier I ended up understanding the problem (E gaining more experience tbm rs) than the variable
$xmlwas filling memory or is generating burst as the boy says. Anyway thank you for the information.– Augusto Junior
Show. I highly recommend the understanding/use of generators
– Marcos Xavier
@Marcosxavier Thanks again for talking about Enerator subject. I found very useful the function
yield.– Augusto Junior
@Augustojunior, if you have found a solution to the question you can add it. Perhaps, you can change the title of the question to "how to concatenate large strings" (or similar), since this represents the problem better than "Mysql large volume". Sure, if you want.
– Inkeliz
@Inkeliz edited friend. Thanks for guiding.
– Augusto Junior