Gone away wont go away!

[HuggieBear]

Evening,

 

I'm getting an error stating that 'MySQL server has gone away'.

 

I'm using MySQL 5.0.67 and I'm only executing a very small query.  I've checked out the possible causes at the MySQL Website but I can't make much sense of it.

 

The tables that I'm dealing with are small and don't have much data in.  The data I'm inserting is very small too.

 

<?php

// Connect to the server
$db = mysql_connect('server', 'username', 'password');
if (!$db) {
   die('Could not connect: ' . mysql_error());
}

// Select the database
$active_db = mysql_select_db('my_database', $db);
if (!$active_db) {
   die("Could not use $database : " . mysql_error());
}

// Query the database for all out of date feeds (table contains 13 rows)
$sql = "SELECT merchant, feed_url, feed_filename FROM cp_feeds WHERE unix_timestamp(now()) - unix_timestamp(last_collected) > 86400";
if (!$results = mysql_query($sql, $db)){
   die ("Unable to execute query: $sql" . mysql_error());
}

// Update all out of date feeds
$ood_feeds = mysql_num_rows($results);
logProgress('INFO ', "$ood_feeds feeds require updating");

if ($ood_feeds > 0){

   // Prepare the filepath
   $file_path = '/home/path/private/';

   // Retrieve each feed and store it locally
   while ($feed = mysql_fetch_array($results, MYSQL_ASSOC)){
      logProgress('INFO', "Getting feed from {$feed['merchant']}");

      $contents = file_get_contents($feed['feed_url']);
      $filename = $file_path . $feed['feed_filename'];
      if (!file_put_contents($filename, $contents)){
         logProgress('WARN', "Unable to write $filename");
      }
      else {
         logProgress('INFO', "Feed written to $filename");

         // Updates a single row in the 13 row table
         $sql = "UPDATE cp_feeds SET last_collected = now() WHERE merchant = '{$feed['merchant']}'";
         $result = mysql_query($sql, $db);
      }
   }
   logProgress('INFO', 'All feeds updated');
}

// Log the progress of the script
function logProgress ($level, $message){
   global $db;
   // Log table currently contains about 70 rows
   $sql = "INSERT INTO cp_log VALUES (now(), '$level', '$message')";
   if (!mysql_query($sql, $db)){
      die("Unable to insert logging info into the database: " . mysql_error());
   }
   if ($level == "FATAL"){
      die("A fatal error occured, check log for details");
   }
}

?>

 

It's the inserting of log info that's failing.  Incidentally, I've changed the script so that it only gets one file now.  It's about 5MB and takes around 45 seconds.  Is it this gap that's causing the error?

 

The log table info is:

CREATE TABLE `cp_log` (
`msg_time` datetime NOT NULL,
`msg_level` varchar(6) NOT NULL,
`msg` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

Thanks

Rich

[fenway]

Gap?

[HuggieBear]

Yeah, the 45 second wait between writing this to the database:

logProgress('INFO', "Getting feed from {$feed['merchant']}");

 

and then writing this to the database:

logProgress('INFO', "Feed written to $filename");

 

This is where I retreive a remote file and write it locally ready to be parsed at a later date.

 

It's the second write that's causing the error.

 

Regards

Rich

[dreamwest]

I've checked out the possible causes at the MySQL Website but I can't make much sense of it.

 

Mysql docs are crap compared to php docs, at least beginners can understand php docs.

 

Personally i learnt mysql syntax through text books and not through mysql website

[fenway]

What is the timeout?

[HuggieBear]

Do you mean the max_execution_time in PHP?  If so then it's set to 180.

 

Cheers

Rich

[fenway]

No, there's a timeout for the mysql connection, too....

[HuggieBear]

Oh ok, I didn't realise that.  Do you know where I can find it?

[fenway]

I'm fairly sure the mysql refman page on this topic describes where to find it... I don't recall.

[HuggieBear]

Thanks for the pointer. You're right, it's mysql_options().

 

I don't think that PHP has an option to call this API function though.

[fenway]

What about mysql.connect_timeout in your php.ini file?