内存限制用完 [英] Memory limit exhausted
问题描述
在运行将大数据导入mysql的PHP脚本时,遇到以下错误:
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
我之前已经在我的PHP INI文件中分配了256M的内存限制.到目前为止,这种记忆已经足以应付我的便笺,到目前为止,我还没有遇到任何问题.我已导入文件大小为1MB和2.84MB,同时将内存限制保持为256MB.当我运行大约1MB的文本文件时,发生此错误.
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.
我假设错误所指向的内存是PHP内存限制.可以设置其他内存限制吗?对于解决此问题的一些建议,我将不胜感激.非常感谢.
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.
新修改
...
...
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);
我不希望开始一个新问题,因为它仍然在这个问题上. 我已经解决了内存耗尽"问题,但是现在遇到了一个mySQL查询错误,这使我的生活变得十分痛苦,我需要帮助.
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
这是echo $ query的结果:
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
我已经注意到,查询未解析输入数据中的最后一个变量"* 67".我不确定它是否以*.开头,因此无法识别.
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 *.
推荐答案
一次插入一行或将它们分批插入,例如一次插入50行...
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));
这篇关于内存限制用完的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!