Memory limit exhausted

Question

While running a PHP script that imports large data into mysql, I encountered the following error:

memory exhausted near ''$GPGGA','130735.000','60.15751','N','24.74303','E','1','05','1.6','8.7','M','35' at line 1

I have before now, assigned a memory limit of 256M in my PHP INI file. This memory has so far been sufficient for my sripts and I have experienced no issues till now. I have imported files of size 1MB and 2.84MB while keeping the memory limit at 256MB. THis error occured when I runned a text file of about 1MB.

I have assumed the memory the error is refering to is the PHP memory limit. Could there be any other memory limits to be set? I would appreciate some suggestions in resolving this problem. Many thanks.

New edits

...
...
if (is_array($inFile)) {

foreach($inFile as $inFileName) {

    $newFilePath = $upload_directory."/".$inFileName;

    if($inFileName != "")

    $handle = fopen($newFilePath,"r");
    $numlines =1;

    $query = "INSERT INTO gga_raw_data(device_id, gga_date,  nmea, gga_time, latitude, north, longitude, east, fixed_quality, no_of_satelites, hdop, altitude, meters, height_of_geoid, metres, check_sum, test_case_id) values";
    while (($data = fgetcsv($handle)) !== FALSE)
    {
        $numlines++;

        $myDay=substr($data[1], 0,2);
        $myMonth=substr($data[1], 2,2);
        $myYear=substr($data[1], 4,2);

        $gga_date = convertToNiceDate($myDay, $myMonth, 2000+$myYear);

        $longitude = convertToDegrees($data[6]);
        $latitude  = convertToDegrees($data[4]);


        if (!isset($data[16])) {
            $data[16] = '';
        }

        $query .="('$data[0]','$gga_date','$data[2]','$data[3]','$latitude','$data[5]','$longitude','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]', '$data[15]', '$data[16])',";


    }

    $query = substr($query, 0,-1);

I do not wish to start a new question since it is still on this issue. I have resovled the " memory exhaustion" issue but now have a a mySQL query error has made my life quite miserable and I need help.

This is a sample of my input data from a text file:

1,070610,$GPGGA,080323.460,6013.45368,N,02445.28396,E,1,04,4.6,18.3,M,35.0,M,,*67
1,070610,$GPGGA,080327.000,6013.44424,N,02445.31214,E,1,05,1.9,30.8,M,35.0,M,,*66

This is the result from echo $query:

INSERT INTO gga_raw_data(device_id, gga_date, nmea, gga_time, latitude, north, longitude, east, fixed_quality, no_of_satelites, hdop, altitude, meters, height_of_geoid, metres,check_sum) values('1','2010,06,07','$GPGGA','080323.460','60.22423','N','24.75473','E','1','04','4.6','18.3','M','35.0','M','')('1','2010,06,07','$GPGGA','080327.000','60.22407','N','24.7552','E','1','05','1.9','30.8','M','35.0','M','')
)('1','2010,06,07','$GPGGA','053416.000','0','','0','','0','','','','','','','')('1','2010,06,07','$GPGGA','053431.000','0','','0','','0','','','','','','','')('1','2010,06,07','$GPGGA','062929.000','0','','0','','0','','','','','','','')('1','2010,06,07','$GPGGA','080250.000','0','','0','','0','','','','','','','')('1','2010,06,07','$GPGGA','080305.000','0','','0','','0','','','','','','','')('1','2010,06,07','$GPGGA','080305.000','0','','0','','0','','','','','','','')('1','2010,06,07','$GPGGA','080320.000','0','','0','','0','','','','','','','')


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('1','2010,06,07','$GPGGA','080327.000','60.22407','N','24.7552','E','1','05','1' at line 1

I have come to notice that, the last variable "*67" from the input data is not being parsed by the query. I am not sure if it is not being recognised since it begins with *.

Please show more context and maybe some code. Where does that error message come from exactly? It is not PHP's default "out of memory" message, it sounds more like it's coming from mySQL — Pekka, Nov 11 '10 at 12:27
The script am running contains a query which runs ones but carries out many insertions using a while loop. Another addition is just 2 functions to covert date and 2 fields. Peharps you are right.It should be coming from mySQL — ibiangalex, Nov 11 '10 at 12:31
I am running a wampserver but I have no clue of what you mean if you can show some example. — ibiangalex, Nov 11 '10 at 14:21
By cramming every set of values into one massive INSERT statement, if your file you're reading from is large, it's quite possible that $query is getting insanely large. symcbean is correct below - if you run the inserts in smaller batches, you should be fine. — Ross Snyder, Nov 11 '10 at 14:26
I get the point but am having a problem with running the stuff as Symcbean suggested. The variable $query is given error as undefined am not sure what else it wants here and then mysql is erring, issuing this:check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1". I am trying to figure out this 2 issues. Can't even find the erring hanging quote — ibiangalex, Nov 11 '10 at 14:45

score 2 · Accepted Answer · answered Nov 11 '10 at 13:40

2

Do the inserts one row at a time or batch them into, say 50 rows at a time...

$gen_query=$query; // keep $query as the stub

while (($data = fgetcsv($handle)) !== FALSE)
{
    $numlines++;

    ...
    $gen_query .="('$data[0]','$gga_date','$data[2]','$data[3]','$latitude','$data[5]','$longitude','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]', '$data[15]', '$data[16])',";
   if ($numlines % 50 == 0) {
      mysql_query(substr($gen_query,0,-1));
      $gen_query=$query;
   }
}
if ($gen_query!=$query) mysql_query(substr($gen_query,0,-1));

answered Nov 11 '10 at 13:40

symcbean

47,736
6
59
94

So you could do a thing like that! I give it a shot and see what happens. thanks. – ibiangalex Nov 11 '10 at 14:04
I applied your suggestion but am having two errors: The $query is undefined it says and the mySQl syntax error which says "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1" what could be the problem? – ibiangalex Nov 11 '10 at 14:19

score 0 · Answer 2 · answered Nov 11 '10 at 12:31

0

There are other limits you can set in php.ini like max post size, max upload size, etc.... But that error looks like it's due to something else. Are you constructing really large objects [in php, most likely arrays] after you read the uploaded file ? I had a similar issue last night and all I had to do was increase my memory limit in php.ini.

When I've had this issue previously with no option to increase the memory limit, I had to rewrite my code so that so many objects were not stored in memory at the same time. Can you post some code ?

answered Nov 11 '10 at 12:31

sjobe

2,817
3
24
32

Yes am constructing arrays in this form in a query:$query .="('$data[0]','$gga_date','$data[2]','$data[3]','$latitude','$data[5]','$longitude','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]', '$data[15]', '$data[16])',"; – ibiangalex Nov 11 '10 at 13:02
Sorry how do I post you code? There is something I never really don't use for formatting code i.e code that span inline. I dont quite get it but i paste the code here – ibiangalex Nov 11 '10 at 13:05
Just did that. I have left out the part that handles the upload. I have them all one script. thanks – ibiangalex Nov 11 '10 at 13:18
The more I look at it, the more I think it's from mysql.. http://stackoverflow.com/questions/1178736/mysql-maximum-memory-usage might be helpful. Good luck! – sjobe Nov 11 '10 at 14:28
Hello sjobe, I have resolved the memory stuff but now have a syntax error which am not able to figure out why. I have edited my question to add the error in case anybody could offer some help. – ibiangalex Nov 12 '10 at 07:36

Memory limit exhausted

2 Answers2

Linked