MySQL server has gone away (Err no 2006)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
RussellEngland
Forum Newbie
Posts: 8
Joined: Thu Nov 11, 2010 3:58 pm

MySQL server has gone away (Err no 2006)

Post by RussellEngland »

I'm geocoding some addresses using Googles api. Its on a test server so no-one else is on there but me.

At random points, sometimes 2 or 3 records, sometimes 50+ records I get a error number 2006, "MySQL server has gone away" error.

Is there a setting in php I need to do?

Code: Select all

<?php

require("phpsqlajax_dbinfo.php");

// Opens a connection to a MySQL server
$connection = mysql_connect("localhost", $username, $password);
if (!$connection) {
  die("Not connected : " . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die("Can\'t use db : " . mysql_error());
}

mysql_query("SET NAMES 'utf8'");

// Select all the rows in the markers table
$query = "SELECT * FROM mymarkers WHERE updated = 0 LIMIT 2500";
$rows = mysql_query($query);
if (!$rows) {
  die("Invalid query: " . mysql_error());
}


// Google maps api key http://code.google.com/apis/maps/signup.html
$key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
$base_url = "http://maps.googleapis.com/maps/api/geocode/json?key=" . $key . "&sensor=false";

// Initialize delay in geocode speed
$delay = (86400000000/2500); // microseconds in a day divided by maximum requests per day (2500)


// Iterate through the rows, geocoding each address
while ($row = @mysql_fetch_assoc($rows)) 
{
	$mycount = 0;
	$finished = false;

	$itemid = $row["itemid"];
	$region = strtolower(substr($row["field_countrycode"],-2));
	
        // Pick out the address fields
	$fields = array();
	$mycount = 0;
	foreach( $row as $field )
	{

		if (($mycount>=2) && ($mycount<=7) && ($field!='') && ($field!=null))
		{
			$fields[] = html_entity_decode(unicodetohtml($field));  // convert \u to chars and &xxx; to the character
		}
		$mycount++;
	}
	$fields[] = $row['countryname'];
	
	$mycount = 0;	
	$maxfield = count($fields)-1;	
	while (!$finished && $mycount<=$maxfield)
	{
		$address = "";
		// Keep trying a reduced version of the address, ie. full address first, then city+state, then state etc.
		for ($field=$mycount; $field<=$maxfield; $field++)
		{
			if ( ($field==0) || ($fields[$field]!=$fields[$field-1]) )
			{
				$address .= $fields[$field] . " ";
			}
		}
		
                // What are we searching for
		echo " Search : " . $address ." region : " . $region . " Search count : " . $mycount .  <br/>";
	
		$mycount++;
		
               // Get the json
		$request_url = $base_url . "&address=" . urlencode($address) . "&region=" . urlencode($region);
		
		if ($address!='')
		{
			// Wait for it...
			usleep($delay);
				
			$json_contents = file_get_contents($request_url,0,null,null);
			$json = json_decode($json_contents);
		
			echo "Search status : " . $json->status . "<br/>";		
		
			switch ($json->status)
			{
				case "ZERO_RESULTS":
					// try again
					$finished=false;
					break;
				case "OVER_QUERY_LIMIT":
					// try again
					$finished=false;
					$delay += 100000;
					$mycount--;
					break;
				case "REQUEST_DENIED":
					// try again
					$finished=true;
					break;
				case "INVALID_REQUEST":
					// try again
					$finished=true;
					break;
				case "OK":
					$finished=true;
					break;
			}
		}

	}

	$location = $json->results[0]->formatted_address;
	$geo_lat = $json->results[0]->geometry->location->lat;
	$geo_long = $json->results[0]->geometry->location->lng;
	$accuracy = $json->results[0]->geometry->location_type; // Accuracy

	// update the marker
	$query = sprintf( "UPDATE mymarkers SET location='%s', geo_lat='%s', geo_long='%s', accuracy='%s', updated=1 where itemid=%s",
				addslashes($location),
				addslashes($geo_lat),
				addslashes($geo_long),
				addslashes($accuracy),
				$itemid );
	$result = mysql_query($query);
	if (!$result) {
		die("Invalid query: " . mysql_error());
	}	

	echo "Found : " . $location . " lat : " . $geo_lat . " lng : " . $geo_long . " accuracy : " . $accuracy . '<br/>';
	
}

	function unicodetohtml($str) 
	{
		return (preg_replace("/\\\\u([0-9a-f]{3,4})/i", "&#x\\1;", $str));
	}
?>
btop
Forum Newbie
Posts: 2
Joined: Wed Dec 08, 2010 3:12 pm

Re: MySQL server has gone away (Err no 2006)

Post by btop »

Could be a bunch of different things - MySQL has some info on it http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
RussellEngland
Forum Newbie
Posts: 8
Joined: Thu Nov 11, 2010 3:58 pm

Re: MySQL server has gone away (Err no 2006)

Post by RussellEngland »

Yey! Got it to work...

I had this message from my host
The reason this can happen is the Mysql kill job runs every two minutes, while the PHP kill job runs every 3 minutes. So technically mysql can run for 2 minutes, 25 seconds, and php can technically run for 3 minutes, 25 seconds. And the jobs will rarely run at the same time.
Which explains why it goes away at random.

I tried to use ini_set("mysqli.reconnect","1") but my host doesn't allow it.

Then the simplest of solutions

Code: Select all

	if (!$mysqli->ping()) 
	{
		echo ("Ping Error: " . $mysqli->errorno . " : " . $mysqli->error . '<br/>');
		if (!$mysqli->real_connect('localhost', $username, $password, $database)) {
			die('Connect Error (' . mysqli_connect_errno() . ') '
					. mysqli_connect_error());
		}		
		else
		{
			echo 'Reconnected successfully!!<br/>';
		}
	}	
Works perfectly

This is the final code

Code: Select all

<?php
$key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
$base_url = "http://maps.googleapis.com/maps/api/geocode/json?key=" . $key . "&sensor=false";

// Initialize delay in geocode speed
$delay = (86400000000/2500); // microseconds in a day divided by maximum requests per day (2500)

echo "Reconnect : " . ini_get('mysqli.reconnect') . '<br/>';
if (ini_set('mysqli.reconnect', '1'))
{
	echo 'Reconnection set<br/>';
}
else
{
	echo 'reconnection not set <br/>';
}

require("phpsqlajax_dbinfo.php");

$mysqli = mysqli_init();
if (!$mysqli) {
    die('mysqli_init failed');
}

if (!$mysqli->options(MYSQLI_INIT_COMMAND, "SET NAMES 'utf8'")) {
    die('Setting MYSQLI_INIT_COMMAND failed');
}

if (!$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 60)) {
    die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}

if (!$mysqli->real_connect('localhost', $username, $password, $database)) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}


$mysqli->query("SET NAMES 'utf8'");

// Select all the rows in the markers table
$query = "SELECT * FROM mymarkers WHERE updated = 0 ORDER BY countryname LIMIT 1000;"; 
if (!$rows = $mysqli->query($query))
{
  	die("Invalid query: " .$mysqli->errno . ' : ' .$mysqli->error);
}

// Iterate through the rows, geocoding each address
while ($row = $rows->fetch_assoc()) 
{
	$mycount = 0;
	$finished = false;

	$itemid = $row["itemid"];
	$region = strtolower(substr($row["field_countrycode"],-2));
	
	$fields = array();
	$mycount = 0;
	foreach( $row as $field )
	{
		if (($mycount>=2) && ($mycount<=7) && ($field!='') && ($field!=null))
		{
			$fields[] = html_entity_decode(unicodetohtml($field));  // convert \u to chars and &xxx; to the character
		}
		$mycount++;
	}
	$fields[] = $row['countryname'];
	
	
	// print_r($row);
	/// convert ampersands and \u?
	
	$mycount = 0;	
	$maxfield = count($fields)-1;	
	while (!$finished && $mycount<=$maxfield)
	{
		$address = "";
		
		for ($field=$mycount; $field<=$maxfield; $field++)
		{
			if ( ($field==0) || ($fields[$field]!=$fields[$field-1]) )
			{
				$address .= $fields[$field] . " ";
			}
		}
		

		$mycount++;
		echo "Search : " . $address ." region : " . $region . " Search attempt : " . $mycount . "<br/>";
		
		
		$request_url = $base_url . "&address=" . urlencode($address) . "&region=" . urlencode($region);
		
		
		
		
		if ($address!='')
		{
			// Wait for it...
			usleep($delay);
				
			$json_contents = file_get_contents($request_url,0,null,null);
			$json = json_decode($json_contents);
		
			echo "Search status : " . $json->status . "<br/>";		
		
			switch ($json->status)
			{
				case "ZERO_RESULTS":
					// try again
					$finished=false;
					break;
				case "OVER_QUERY_LIMIT":
					// try again
					$finished=false;
					$delay += 100000;
					$mycount--;
					break;
				case "REQUEST_DENIED":
					// try again
					$finished=true;
					break;
				case "INVALID_REQUEST":
					// try again
					$finished=true;
					break;
				case "OK":
					$finished=true;
					break;
			}
		}

	}
	// echo $json_contents;
	// echo $json;

	$location = $json->results[0]->formatted_address;
	$geo_lat = $json->results[0]->geometry->location->lat;
	$geo_long = $json->results[0]->geometry->location->lng;
	$accuracy = $json->results[0]->geometry->location_type; // Accuracy

	echo "Found : " . $location . " lat : " . $geo_lat . " lng : " . $geo_long . " accuracy : " . $accuracy . '<br/><br/>';

	// Select all the rows in the markers table
	$query = sprintf( "UPDATE mymarkers SET location='%s', geo_lat='%s', geo_long='%s', accuracy='%s', updated=1 where itemid=%s limit 1",
				addslashes($location),
				addslashes($geo_lat),
				addslashes($geo_long),
				addslashes($accuracy),
				$itemid );
	echo $query . '<br/>';
	
	if (!$mysqli->ping()) 
	{
		echo ("Ping Error: " . $mysqli->errorno . " : " . $mysqli->error . '<br/>');
		if (!$mysqli->real_connect('localhost', $username, $password, $database)) {
			die('Connect Error (' . mysqli_connect_errno() . ') '
					. mysqli_connect_error());
		}		
		else
		{
			echo 'Reconnected successfully!!<br/>';
		}
	}	

	if (! $mysqli->query($query) ) {
		die("Invalid query: " .$mysqli->errno . ' : ' .$mysqli->error);
	}	
	
}
$rows->close();

mysqli_close($mysqli);


	function unicodetohtml($str) 
	{
		return (preg_replace("/\\\\u([0-9a-f]{3,4})/i", "&#x\\1;", $str));
	}


?>
Post Reply