Reproduce "MySQL server has gone away" in PHP

13,042 0
Summary

Developers can reproduce the "MySQL server has gone away" error by configuring MySQL's `wait_timeout` and `interactive_timeout` to a short duration, like 30 seconds, in `my.cnf` and restarting the service. A PHP script can then connect, pause for longer than the set timeout, and attempt a subsequent query, which will fail with the specified error. To address this, a robust solution involves using `mysql_ping` in PHP to verify the connection's status and re-establish it if it has dropped before executing further queries. C/C++ applications can achieve similar automatic reconnection behavior by setting the `MYSQL_OPT_RECONNECT` option.

If you want to debug the issue of "MySQL server has gone away", you can reproduce it with below steps:

Modify configuration file:

  1. sudo vi /etc/mysql/my.cnf 

Make below changes:

  1. [mysqld]  
  2. wait_timeout = 30  
  3. interactive_timeout = 30 

Restart the service:

  1. sudo /etc/init.d/mysql restart 

Write below PHP codes:

  2. $link = mysql_connect('127.0.0.1', 'root', 'root');  
  3. if (!$link) {  
  4.     die('Could not connect: ' . mysql_error());  
  5. }  
  6. echo 'Connected successfully';  
  7.   
  8. sleep(31);  
  9. $result = mysql_query('show variables;');  
  10. if (!$result) {  
  11.     die('Invalid query: ' . mysql_error());  
  12. }  
  13. while ($row = mysql_fetch_assoc($result)) {  
  14.     var_dump($row);  
  15. }  
  16. mysql_free_result($result);  
  17.   
  18. mysql_close($link);  
  19. ?> 

Run:

  1. $ php mysql.php   
  2. Connected successfully  
  3. Invalid query: MySQL server has gone away 

Or wait for 30 seconds in command line mode:

  1. mysql> select variables like '%timeout';  
  2. ERROR 2006 (HY000): MySQL server has gone away  
  3. No connection. Trying to reconnect...  
  4. Connection id:    40  
  5. Current database: *** NONE *** 

Now you can do what you want to do. For example add mysql_ping to let it restart itself.

  2. function get_conn() {  
  3.     $conn = mysql_connect('127.0.0.1', 'root', 'root');  
  4.     if (!$conn) {  
  5.         die('Could not connect: ' . mysql_error() . '\n');  
  6.     }  
  7.     return $conn;  
  8. }  
  9.   
  10. $conn = get_conn();  
  11.   
  12. sleep(31);  
  13. if (!mysql_ping($conn)) {  
  14.     mysql_close($conn);  
  15.     $conn = get_conn();  
  16.     echo 'Reconnect\n';  
  17. }  
  18.   
  19. $result = mysql_query('show variables;');  
  20. if (!$result) {  
  21.     die('Invalid query: ' . mysql_error());  
  22. }  
  23. while ($row = mysql_fetch_assoc($result)) {  
  24.     var_dump($row);  
  25. }  
  26. mysql_free_result($result);  
  27.   
  28. mysql_close($conn);  
  29. ?> 

If you are using C/C++, you can use below method to let mysql_ping restart itself after establishing the connection.

  1. char mysql_reconnect = 1;   
  2. mysql_options(mysql->conn, MYSQL_OPT_RECONNECT, (char *)&mysql_reconnect); 

Source : http://blog.csdn.net/ffb/article/details/8792009

MYSQL DEBUG RMYSQL SERVER HAS GONE AWAY

  RELATED

  COMMENTS

0

No comment for this article.