Timeout 1

[TommyIndigo]

I'm running a script that cycles through a lengthy loop. Within the loop, various text files are parsed and a SQL Server db record is appended. I'm opening the db connection before the loop, and closing it afterwards.

This program works flawlessly with a small amount of test data, but now in production there is much more data to cycle through.

The program is terminating early in the browser without any error messages. I'm on a W2K server, and tried increasing any of the IIS timeouts I could find...with no luck.

I have heavy logging to try to see if it a programmatic problem...but there are no issues, other than time. However, I'm not getting an explicit timeout error message.

Not sure if this is happening from the script itself, IIS, or SQL Server (again, no errors). Any ideas?? I have a deadline of Friday at 5 pm and am desperate.

Thanks,
TOM

 

[KevinADC]

check the server error log if possible for any clues.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[TommyIndigo]

I have seen from other posts that you cannot change the timeout via your Perl script on a Windows machine. Is there any workaround for this??

I am setting the timeouts in IIS (website properties)...but those changes don't seem to help. Are there other timeout settings I may be missing?

Kevin - thanks for your reply. I just spent 2 hours trying to read the .etl files that the Performance Logs dump. Still havent' found a utility to read these things. So, I couldn't find any log data to assist with this.

 

[KevinADC]

any chance you can try running that script from the command prompt and see if it produces output or completes running? Do you have warnings enabled in the perl script? (-w on the shebang line or as "use warnings;")

------------------------------------------
- Kevin, perl coder unexceptional!

 

[stevexff]

Have you tried opening the DB connection after the loop, i.e loop-parse-open-insert-close? This would at least eliminate the DB connection from the timeout, no?

Also, "lengthy" could mean anything. How long is it taking, and is there anything else we could do to speed it up?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[TommyIndigo]

Kevin - I can't run it from the Command Line, because it is using CGI objects....but I do have it returning progress to the browser as it loops. And I have "use warnings" on.

It is running fine, with no errors or warnings....but it just stops prematurely. Seems to be at the 5 minute point.

If I alter it to go through fewer records, it completes properly....so it seems to be a timeout.

Steve - I actually tried putting the connection within the loop per your suggestion, with the same results. I think that at least rules out a DSN or DB timeout?

Here is a copy of one of the subs that times out on me:

sub refresh_change_requests
{

db_connect();

$dbh->do("DROP TABLE change_requests");

$dbh->do("CREATE TABLE change_requests (
	project_id SMALLINT,
	cr_id SMALLINT,
	brief_description VARCHAR(1000),
	originator_id SMALLINT,	
	date_originated VARCHAR(50),
	type VARCHAR(50),
	detailed_description VARCHAR(8000),
	requirements_change VARCHAR(25),
	severity VARCHAR(25),
	priority VARCHAR(25),
	status VARCHAR(50),
	assignee_id SMALLINT,
	date_due VARCHAR(50),
	disposition_notes VARCHAR(5000),
	number_of_defects SMALLINT,
	phase_originated VARCHAR(50),
	phase_detected VARCHAR(50),
	phase_removed VARCHAR(50),
	time_to_fix VARCHAR(25),
	root_cause VARCHAR(1000)	
	)");

LOOP:foreach my $project(@QMvalidProjects)
{
	my $project_path 		= 	$project->param('ProjectDir');
	my $project_control_number 	= 	$project->param('ControlNumber');
	my $cr_path		=	$project_path."/projrecs/cr";
	my $cr_path_full		=	$cr_path."/data.txt";
	unless (-e $cr_path_full){ next LOOP;}
	
	print "<BR>EXAMINING PROJECT $project_control_number CHANGE REQUESTS";
	
	$obj				= 	pragmasys::Data::CGI->new(path =>$cr_path);

	my $all_cr_data	= $obj->fetch(all => 1);

	$obj = undef;


	$sth = $dbh->prepare("INSERT INTO change_requests(
		project_id,
		cr_id,
		brief_description,
		originator_id,	
		date_originated,
		type,
		detailed_description,
		requirements_change,
		severity,
		priority,
		status,
		assignee_id,
		date_due,
		disposition_notes,
		number_of_defects,
		phase_originated,
		phase_detected,
		phase_removed,
		time_to_fix,
		root_cause		
		) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

	foreach my $record (@$all_cr_data)
	{

		my $cr_id	 			= $record->param('ControlNumber');
		my $brief_description			= $record->param('ChangeStatement');
		my $originator_id			= convertStringToZero($record->param('OriginatorName'));	
		my $date_originated			= translateTime($record->param('CreateDate'));
		my $type				= $record->param('Type');
		my $detailed_description		= $record->param('Narrative');
		my $requirements_change			= $record->param('ReqChange');
		my $severity				= $record->param('Severity');
		my $priority				= $record->param('Priority');
		my $status				= $record->param('ResolutionStatus');
		my $assignee_id				= convertStringToZero($record->param('AssigneeName'));
		my $date_due				= translateTime($record->param('DueDate'));
		my $disposition_notes			= $record->param('DispositionNotes');
		my $number_of_defects			= $record->param('NumberOfDefects');
		my $phase_originated			= $record->param('PhaseOriginated');
		my $phase_detected			= $record->param('PhaseDiscovered');
		my $phase_removed			= $record->param('PhaseRemoved');
		my $time_to_fix				= $record->param('TimeToFix');
		my $root_cause				= $record->param('RootCauseDesc');

		
		$sth->execute( 
			$project_control_number, 
			$cr_id, 
			$brief_description,
			$originator_id,	
			$date_originated,
			$type,
			$detailed_description,
			$requirements_change,
			$severity,
			$priority,
			$status,
			$assignee_id,
			$date_due,
			$disposition_notes,
			$number_of_defects,
			$phase_originated,
			$phase_detected,
			$phase_removed,
			$time_to_fix,
			$root_cause) or die "Choked on PROJECT: $project_control_number  , CR: $cr_id";

	}



}
	db_disconnect();
	$all_cr_data = undef;
	print "<BR>COMPLETED CR DUMP";

}
# END REFRESH CHANGE REQUESTS

 

[TommyIndigo]

For all it's worth, I found the following at

http://www.cosmicscripts.com/servers/timeout.html:

Timeout.pm - Script Control for IIS:
Although IIS does have setting to control the lifetime of CGI scripts, it doesn't always work. The solution is very straight forward using a simple little script that we provide. In IIS you can set individual ASP timeouts per site. With this script you can do the same for CGI Perl scripts. If you are running Windows2000 we highly recommend that you use this module as there is a problem with the IIS CGI timeout setting when a script goes into a certain kind of loop.

I'm going to play with this after hours tonight. Anyone have any experience with this?

 

[TommyIndigo]

I found the answer!!

This is actually an IIS issue, but many Perl folks may experience this timeout problem if using Windows servers.

The solution is to use a utility available from Microsoft to modify the Metabase (this is NOT available via the IIS GUI!!). The utility is downloadable from MS.

The ASP timeout in IIS is NOT the Perl timeout!!! The "CGITimeout" property for the server must be set ONLY from this Metabase utility (or the command line). The property may be modified for the whole server, or down to the individual script.

Here is a link to an IIS-related site that gives many examples:

http://www.iis-resources.com/modules/AMS/print.php?storyid=509

Thanks to Kevin and Steve for replying with suggestions.

 

[KevinADC]

Good info to know, sounds like a candidtate for a FAQ.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[MillerH]

Another way to deal with this is to isolate any time intensive operations from the webserver. I personally do this by having a daemon to handle anything that will take longer than a fraction of a second.

It's pretty easy to predict those things that are going to take a long time. Basically anything that has lots of computation, database, or network activities. Then, if necessary, let the webserver be able to tell the status of the job.

- Miller

 

[stevexff]

Tommy

I'd be inclined to put a COMMIT in after you've created the table. With my DBA head on, DROP-CREATE-INSERT-sh*tloads-of-data seems like a really bad idea from a rollback buffer perpective...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]