reading large remote file causes error

kidsleep

I'm working on a script that uses the "ip to country" files. Everything works great, except that when reading the files(via the file() function), I get a "server returned unknown error" error whenever I DON'T echo something foreach line of the file. If I DO echo something, ANYTHING, it works perfectly. Anyone have any ideas on this and how to fix it?

bubblenut

Sounds a little odd, could you post some code?
Thanks
Bubble

kidsleep

Sorry for not replying sooner, my keyboard went kaput. Here is the relevent code, the line echo("$b_ip<br>") is the line that must be in for the script to work. I'm thinking it may just timeout before all the files can be read(there are ~59k of rows to be inserted) I tried again just now and got a "cannot display page" error yet it was able to insert 6,210 rows before script failure.

Thanks for the reply.

$file[0]="ftp://ftp.apnic.net/public/apnic/stats/apnic/delegated-apnic-latest"; 
$file[1]="ftp://ftp.apnic.net/public/stats/ripe-ncc/delegated-ripencc-latest";
$file[2]="ftp://ftp.apnic.net/public/stats/arin/delegated-arin-latest";
$file[3]="ftp://ftp.lacnic.net/pub/stats/lacnic/delegated-lacnic-latest";


foreach($file as $key=>$val)	{

if(!@$array=file($val))		{

	die("<strong>$val</strong> is not available");


}



foreach($array as $fp=>$line)		{


	if(strpos($line,"ipv4") && !strpos($line,"*"))		{

		$temp=explode("|",$line);

		$b_ip = sprintf("%u", ip2long($temp[3]));
		$e_ip=$b_ip+($temp[4]-1);
		$country=$temp[1];

		$query="SELECT id FROM country_list WHERE code='$country'";

		$result=mysql_query($query);

		if(!mysql_num_rows($result))		{

			$row[0]=0;
		}

		else	{

			$row=mysql_fetch_row($result);


		}


			$query="INSERT INTO ip(ip_from,ip_to,code)VALUES('$b_ip','$e_ip','$row[0]')";

			$result=mysql_query($query) or die(mysql_error());

			//echo("$b_ip<br>");


	}

}

bubblenut

Sounds like either a memory or time maxout at the server end. Have a look as [man]memory_get_usage()[/man] to check how the memory usage is doing at points in the script. If you haven't changed your php.ini the maxout for memory is 8Mb per thread, I'd strongly suggest against changing this in the php.ini as if you get too many threads using too much memory you can make apache fall over (happened a few weeks ago to myself when I made a little booboo on my searchresults page, didn't show up untill the whole thing went poo), you can set the memory on a per-script basis with [man]ini_set()[/man] on "memory_limit" but if that can be avoided I think it would be best (no point in using up resources that aren't needed 😉) I'm not quite sure how PHP handles it's garbage collection but it may be file that's causing the problem. If it turns out it is a memory issue we may want to look into using [man]fopen()[/man] and it's siblings instead but we'll come to that if we need to.
The other problem I mentioned it may be is the time limit. Not much you can do to avoid this one other than streamlining which, again, there's not much point in getting into until we know what the problem is as the only method's I can think of would eat up a more memory.
To check the time you'll need to do a little profiling using the [man]microtime()[/man] function and dotting timers around the place to find out what's taking the most time. Also check your max_execution_time in php.ini, it is very straight forward to set this using [man]set_time_limit()[/man]

The other end is the client side, it may simply be that your browser is set up to return a fail if it doesn't recieve anything for more than a certain amount of time, basically it's assuming that something has gone wrong because it hasn't heard anything from the server in such a long time. Only way around this is to change the settings in your browser. This looks as though it is a maintenance script though. Prehapse it would be better fitted as a cron job or have it fired off in the background by a script you call and then email you the results.

Do a bit of memory and time profiling so we have a better idea of where the problem lies and get back.

HTH
Bubble

maqmus

I tried to use the ip-to-country database using foreach() and couldn't make it work. I got all the time memory problems (bubblenut was right!). Tried then with for() and it worked fine.

kidsleep

Thanks maqmus, I'll experiment with using for. bubblenut, how would you streamline this script? I've thought of importing one file and then using redirection to rerun the script to import another file etc... But I'd rather keep it within one script. But I'm always willing to learn.

Weedpacket

One thing you could perhaps do is read one line of the remote file (using [man]fgets[/man] etc., instead of file()), process that line, read the next line, process that, and so on. Then you'll never have to have the entire file in memory at once.

bubblenut

One thing that jumps out is the SELECT id FROM country_list WHERE code='$country' every iteration of the loop.
There are only about 280 or so countrys in the world (let's say 300 for arguments sake), this is not a huge amount of data. It would almost certainly be quicker (unless there's only a couple of elements in each of the files) to download the entire country list into an array and then access the id's through the array soemthing like the following

$file[0]="ftp://ftp.apnic.net/public/apnic/stats/apnic/delegated-apnic-latest"; 
$file[1]="ftp://ftp.apnic.net/public/stats/ripe-ncc/delegated-ripencc-latest";
$file[2]="ftp://ftp.apnic.net/public/stats/arin/delegated-arin-latest";
$file[3]="ftp://ftp.lacnic.net/pub/stats/lacnic/delegated-lacnic-latest";

//Get the country data
$query="SELECT id, code FROM country_list";
$res=mysql_query($query);
while($row=mysql_fetch_array($res,MYSQL_ASSOC)) {
  $country_list[$row['code']]=$row['id'];
}

foreach($file as $key=>$val)    {

if(!@$array=file($val))        {

    die("<strong>$val</strong> is not available");


}

foreach($array as $fp=>$line) {
	  if(strpos($line, 'ipv4') && !strpos($line,'*')) {
		  $temp=explode('|',$line);
			$b_ip=sprintf('%u',ip2long($temp[3]));
			$e_ip=$b_ip+($temp[4]-1);
			if(array_key_exists($temp[1],$country_list)) {
			  $country=$country_list[$temp[1]];
			} else {
			  $country=0;
			}

			$query="INSERT INTO ip(ip_from, ip_to, code) VALUES ('$b_ip','$e_ip','$country')";
			$result=mysql_query($query) or die(mysql_error());

    //echo("$b_ip<br>");


    }

}

Now, there's one more thing which may speed things up a little. DB queries are going to be the bottlekneck here (unless you've got a slow network connection but there's not a lot we can do about that) so optimization is going to come in the form of reducing the number of MySQL queries (as we already have done by removing the country query from the loop. We can also reduce the number of times the INSERT query runs, bu this will be a little bit more complex.
We can combine multiple inserts into one query with the following syntax.

INSERT INTO table 
(col1, col2) 
VALUES 
('var11','var12'), 
('var21','var22'), 
('var31','var32);

The only problem is that MySQL chokes if you try to pass it a query that's too big and I'm not sure what this size is, I've hit it before but I can't for the life of me remember what it was. I'd suggest doing it every 50 or so iterations to be on the safe side (I am 100% certain this will go through without a problem). If you have loads of data which needs to go in you could run some tests but try to make it failsafe because in order to find out far you can go you will need to test it to breaking point.
Good Luck
Bubble