间歇性MySQL服务器已经走了错误 [英] intermittent MySQL server has gone away error

问题描述

我一直在抓我的头，想弄清楚是什么导致我的脚本间歇性错误。错误是：SQLSTATE [HY000]：一般错误：2006 MySQL服务器已经走了。

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.

我的脚本是一个curl的函数的一部分，从JSON响应，然后将其写入表。我会说，80％的时间，它工作正常，然后其他20％我得到服务器走了错误。我没有能够确定任何导致它错误出来的趋势，它只是似乎是随机的。任何想法为什么我可能会得到这个错误？感谢您查看此信息

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");

推荐答案

这可能是因为您的CURL要求那么mysql连接超时

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

或者
1）为CURL设置一个请求超时，因此它会在错误时更快死亡（CURLOPT_CONNECTTIMEOUT只用于连接 - CURLOPT_TIMEOUT用于请求的总长度，如果服务器没有及时响应，它将停止）
2）打开mysql空闲超时，以防止服务器断开连接，不发送查询

3）检测错误并自动重新连接到mysql

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和interactive_timeout是您关心的两个

wait_timeout and interactive_timeout are the two you care about

这篇关于间歇性MySQL服务器已经走了错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！