[SOLVED] Extend MySql Connection time...

[Maq]

I have a script that scans through over 338,000 records of text.  I get a fatal error:

 

Fatal error: Maximum execution time of 900 seconds exceeded in...

 

Is there any way to override this connection time?

[JohnM1983]

set_time_limit (0)

 

http://uk3.php.net/set_time_limit

 

~JohnM

[Mchl]

You could also perhaps tweak your query so that it returned results sooner...

[Maq]

Thanks for you responses but I found a solution to my problem.

 

I used:

 

ini_set('max_execution_time', '1500');

 

This approach changes the execution time only for the session, then restores the default time when the execution is complete.

[Mchl]

Tweaking query to get result sooner is always good

[Maq]

I'm sorry Mchl, what do you mean by tweaking query?  Could you give an example?

[Mchl]

For example checking if indexes could be used.

There's whole section about it manual

 

http://dev.mysql.com/doc/refman/5.0/en/query-speed.html

[Maq]

Oh ok.  Yes I know what you're talking about.  I have actually indexed and done some other things to optimize my query.  The fact is that I'm scanning and updating 338,000 records, that are each a couple paragraphs long.  Thanks.

[Mchl]

Ok then.

More than 900s is pretty tough... :/

[PFMaBiSmAd]

I'm scanning and updating

If you are doing that using some (slow) php code in a loop with SELECT/UPDATE statements, it is likely it can be accomplished 5-10 faster just using queries. Post your code and an example of what you are updating.

[Maq]

The goal is to find all email addresses and eliminate the @ sign and everything after it.

 

 

Code:

 

$connection = mysql_connect($host, $user_name, $password) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");

ini_set('max_execution_time', '15000');

$inc = 1000;
$j=0;
$start = 0;
$finish = $inc;

echo "Starting email replacement... 
";

while($j {
queryCall($start, $finish);
$start += $inc;
$finish += $inc;
$j++;
echo $start . " - " . $finish . "
";
}

function queryCall($start, $finish)
{
$pattern = "/\b[^@\s]+@\S+?\.[a-z]+\.?[a-z]+\b/i";
$t=0;
$results = mysql_query("SELECT Body_, MessageID_ FROM messages_ LIMIT " . $start . " , " . $finish . "");
while ($data = mysql_fetch_array($results)) { 

	preg_match_all($pattern, $data['Body_'], $matches);
	$theData = "";
	$theID = 0;
	$i=0;
	$theData = $data['Body_'];
	$theID = $data['MessageID_'];
	$t++;
	foreach($matches[0] as $value)
	{
		if($i>0) { $theData = $theData2;}
		$pieces = explode("@", $value);
		$theData = str_replace($value, $pieces[0], $theData);
		$theData2 = $theData;
		$i++;
	}
$theData = mysql_real_escape_string($theData);
mysql_query("UPDATE messages_ SET Body_ = '" . $theData . "' WHERE MessageID_ = " . $theID . "");
}		
}

?>

 

Example:

 

Contact was made 

-----Original Message-----
From: Tim M [mailto:timm017@yahoo.com] 
Sent: Wednesday, August 25, 2004 10:31 AM
To: Pearson Owners Discussion List
Subject: test

test

---

---
You are currently subscribed to pearson as: tim@yahoo.com.
To unsubscribe send a blank email to leave-pearson-196315R

[PFMaBiSmAd]

Here is a slightly modified version of your queryCall function that eliminates unnecessary steps (tested) -

<?php
function queryCall($start, $finish)
{
$pattern = "/\b[^@\s]+@\S+?\.[a-z]+\.?[a-z]+\b/i";
$t=0;
$results = mysql_query("SELECT Body_, MessageID_ FROM messages_ LIMIT " . $start . " , " . $finish . "");
while ($data = mysql_fetch_array($results)) { 

	preg_match_all($pattern, $data['Body_'], $matches); // finds all email addresses
//		$theData = ""; // not necessary as this is set to $data[...] later
//		$theID = 0; // not necessary as this is set to $data[..] later
//		$i=0; // not necessary
	$theData = $data['Body_']; // copy of original body
//		$theID = $data['MessageID_']; // copy of id - not necessary, just use $data[..] in the UPDATE query
//		$t++; // not used

	// loop over each 
	foreach($matches[0] as $value)
	{
//			if($i>0) { $theData = $theData2;} // executed after the first pass - not necessary
		$pieces = explode("@", $value); // explode each mail address
		$theData = str_replace($value, $pieces[0], $theData); // replace the email address with just the "name" part of the email address
//			$theData2 = $theData; // save the results as "$theData2" - not necessary
//			$i++; // not necessary
	}
$theData = mysql_real_escape_string($theData);
mysql_query("UPDATE messages_ SET Body_ = '" . $theData . "' WHERE MessageID_ = " . $data['MessageID_'] . "");
}		
}
?>

 

And I believe that someone with more time could figure out the sql needed to replace all of the code with a single UPDATE query.

[Mchl]

 

And I believe that someone with more time could figure out the sql needed to replace all of the code with a single UPDATE query.

 

I don't think MySQL's UPDATE allows for that.

 

Another thing that could speed up things, would be to change the storage engine to InnoDB and then do all the UPDATEs within a single transaction (AUTOCOMMIT OFF)

 

[edit]

 

And I think that using mysqli extension's prepared statements for UPDATE could also save some time.

[Maq]

Thanks for the revised code.  I will implement and test it but doesn't this line of code only get you only the last replaced email.

 

$theData = str_replace($value, $pieces[0], $theData);

 

For example, if I had, "blah blah blah tim@yahoo.com blah blah blah george@aol.com"

 

$theData would have the string, "blah blah blah tim@yahoo.com blah blah blah george" and tim@yahoo.com would still remain.

 

 

 

[PFMaBiSmAd]

The preg_match_all() returns an array of all the email addresses. So, the foreach() loop takes care of more than one in a message.

 

I was thinking the sql Regular Expressions could do more than just find if an email address was present. So, it does appear that some php code is necessary to process the information.

[PFMaBiSmAd]

Someone with some time could probably come up with a preg_replace() so that the code would end up looking like -

<?php
function queryCall($start, $finish)
{
$pattern = "/\b[^@\s]+@\S+?\.[a-z]+\.?[a-z]+\b/i";
$results = mysql_query("SELECT Body_, MessageID_ FROM messages_ LIMIT " . $start . " , " . $finish . "");
while ($data = mysql_fetch_array($results)) { 
    $data['Body_'] = preg_replace(?????);
    $data['Body_'] = mysql_real_escape_string($data['Body_']);
    mysql_query("UPDATE messages_ SET Body_ = '" . $data['Body_'] . "' WHERE MessageID_ = " . $data['MessageID_'] . "");
    }
}
?>

[Maq]

I was thinking the sql Regular Expressions could do more than just find if an email address was present. So, it does appear that some php code is necessary to process the information.

 

I briefly read some tutorials on "preg_match_all()" and don't remember anything more than making an array of matches but I could have easily overlooked it.  Anyway this is the code that makes up for it to store the string after each replacement...

 

if($i>0) { $theData = $theData2;}
		$pieces = explode("@", $value);
		$theData = str_replace($value, $pieces[0], $theData);
		$theData2 = $theData;

 

Well this needs to be done by today so I just decided to run my original version.  This is only a one time deal.  But if I need something like this in the future I will definitely implement your suggestions and revised code. 

 

My script has been running for about 20 minutes and only has 100,000 records complete... :-\

[Maq]

Someone with some time could probably come up with a preg_replace() so that the code would end up looking like -

 

Hmm good I idea.  Unfortunately I'm not a SQL guru and at the moment do not have time to investigate this problem.  I kind of just want to get it done.  When I have time I will definitely look into this and, if I come up with something, I will post it here for all to see. 

[PFMaBiSmAd]

The array of matches returned by preg_match_all would be like the following -

 

$matches[0][0] = "tim@yahoo.com";

$matches[0][1] = "george@aol.com";

 

The foreach() loop iterates over all of them.

 

if($i>0) { $theData = $theData2;}
....
$theData2 = $theData;

 

That code is not accomplishing anything. The results are in $theData. Putting a copy into $theData2 and then putting that back into $theData is just wasting time and memory.

 

To get the maximum performance out of the code I posted, you can replace all occurrences of $theData with $data['Body_']. This will save the time and memory involved in coping $data['Body_'] into $theData (I left the code as is for clarity.)

[fenway]

Look here for the sql-only version -- though I haven't used it much.