4

I have been scratching my head trying to figure out what is causing an intermittent error in my script. The error is: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away.

My script below is part of a function that does a curl, gets some values from a JSON response, and then writes them to a table. I'd say 80% of the time it works fine and then the other 20% i get the server gone away error. I haven't been able to identify any trends that causes it to error out, it just seems to be random. Any ideas why i might be getting this error? thanks for checking this out

    ...
    //post via cURL 
    $ch = curl_init( $url );
    $timeout = 500;
    curl_setopt( $ch, CURLOPT_POST, 1);
    curl_setopt( $ch, CURLOPT_POSTFIELDS, $myvars);
    curl_setopt( $ch, CURLOPT_FOLLOWLOCATION, 1);
    curl_setopt( $ch, CURLOPT_CONNECTTIMEOUT, $timeout);
    curl_setopt( $ch, CURLOPT_HEADER, 0);
    curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
    $this->response = curl_exec( $ch );
    $this->json_decoded = json_decode($this->response);
    $this->full = print_r($this->json_decoded, true);
    $client_leadid = $this->json_decoded->Parameters->lead_id;
    $client_status = $this->json_decoded->Status;   
    $length = curl_getinfo($ch);
    curl_close($ch);

    //record in DB
    $insert = $this->full.' | '.$url.' | '.$myvars.' | '.$this->date . ' | Time Taken: '.$length['total_time'];
    $db->exec("UPDATE table SET client_resp = '$insert' WHERE global_id = '$this->leadid' LIMIT 1");
    $db->exec("UPDATE table SET client_leadid = '$client_leadid' WHERE global_id = '$this->leadid' LIMIT 1");
rob melino
  • 781
  • 2
  • 10
  • 18
  • What can you read in the MySQL error log? – Jocelyn Aug 27 '12 at 21:27
  • 4
    You'll probably want to go down the causes checklist found [here](http://dev.mysql.com/doc/refman/5.0/en/gone-away.html). – Josh Aug 27 '12 at 21:27
  • 1
    You can reduce the number of queries by 50% which might have a positive outcome with the renadom character you face. Happy debugging, because what you shared so far is not a question but an invitation to guess. What happens with the network connections when is works / stops to work. Have you monitored these? What about the error log? What about the server load? And actually, please provide the queries in plain text, not PHP code. – hakre Aug 27 '12 at 22:08
  • Server has gone away: The server has gone away. (Or, cannot be reached for some reason.) – uınbɐɥs Aug 27 '12 at 22:17
  • The server is probably hiding from your [SQL injection bug ridden code](http://bobby-tables.com/php). Please learn how to use placeholders before you hurt yourself. – tadman Aug 28 '12 at 00:50
  • 1
    @tadman - yes i know, still new to PDO and will fix this! Thanks for pointing it out – rob melino Aug 28 '12 at 05:35
  • @Josh - thanks for the checklist, i am working through each to try to nail down the issue though i think it is probably due to the slow curl response – rob melino Aug 28 '12 at 05:36

1 Answers1

4

This is probably happening because your CURL request is taking longer then the mysql connection timeout

either 1) set a request-timeout for CURL so it dies sooner on errors (CURLOPT_CONNECTTIMEOUT is only for connections- CURLOPT_TIMEOUT is for the overall length of the request and it will stop if the server doesn't respond in time) 2) turn up the mysql idle timeouts to prevent the server from disconnecting you for not sending queries
3) detect the error and automatically reconnect to mysql

mysql> show variables like "%timeout%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 28800 |
| net_read_timeout         | 30    |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)

wait_timeout and interactive_timeout are the two you care about

msEmmaMays
  • 1,073
  • 7
  • 7
  • Thank you, I have increased the mysql timeouts and am waiting to see if that fixes the issue. I wasn't aware of these settings so thanks for the tip! – rob melino Aug 28 '12 at 05:35
  • you suggested 'detect the error and automatically reconnect' Could you offer some guidance on how to do this? would i do it using try {...} catch (PDOException $err) { [reconnect_here] }? What would i put in place of [reconnect_here]? Thanks for your help... – rob melino Aug 28 '12 at 06:31
  • Rob- it depends on your code.. If you have your own DB class with a query() function in it- you would trap the error, reconnect and then call query() again with the exact same parameters to retry (returning the results directly- think recursion) If you are writing the DB queries directly in your code, you're just going to have to do it the hard way (reconnect + rerun the query in the try block... but make sure you catch the error if the server is still down on the second attempt) - Not that i would recommend nesting try {} catch{} blocks if you can help it – msEmmaMays Aug 28 '12 at 19:35
  • eww- that was spaced out nicely - I guess you can't put empty lines in comments! sorry.... – msEmmaMays Aug 28 '12 at 19:39
  • Thanks Robert, i have a db class query in an include file. So, all i ended up doing was including it after the cURL function forcing it to reconnect. So far this seems to be going the trick! thanks again for all of your help – rob melino Aug 28 '12 at 20:05