If you want to debug the issue of "MySQL server has gone away", you can reproduce it with below steps:
Modify configuration file:
- sudo vi /etc/mysql/my.cnf
Make below changes:
- [mysqld]
- wait_timeout = 30
- interactive_timeout = 30
Restart the service:
- sudo /etc/init.d/mysql restart
Write below PHP codes:
- $link = mysql_connect('127.0.0.1', 'root', 'root');
- if (!$link) {
- die('Could not connect: ' . mysql_error());
- }
- echo 'Connected successfully';
- sleep(31);
- $result = mysql_query('show variables;');
- if (!$result) {
- die('Invalid query: ' . mysql_error());
- }
- while ($row = mysql_fetch_assoc($result)) {
- var_dump($row);
- }
- mysql_free_result($result);
- mysql_close($link);
- ?>
Run:
- $ php mysql.php
- Connected successfully
- Invalid query: MySQL server has gone away
Or wait for 30 seconds in command line mode:
- mysql> select variables like '%timeout';
- ERROR 2006 (HY000): MySQL server has gone away
- No connection. Trying to reconnect...
- Connection id: 40
- Current database: *** NONE ***
Now you can do what you want to do. For example add mysql_ping to let it restart itself.
- function get_conn() {
- $conn = mysql_connect('127.0.0.1', 'root', 'root');
- if (!$conn) {
- die('Could not connect: ' . mysql_error() . '\n');
- }
- return $conn;
- }
- $conn = get_conn();
- sleep(31);
- if (!mysql_ping($conn)) {
- mysql_close($conn);
- $conn = get_conn();
- echo 'Reconnect\n';
- }
- $result = mysql_query('show variables;');
- if (!$result) {
- die('Invalid query: ' . mysql_error());
- }
- while ($row = mysql_fetch_assoc($result)) {
- var_dump($row);
- }
- mysql_free_result($result);
- mysql_close($conn);
- ?>
If you are using C/C++, you can use below method to let mysql_ping restart itself after establishing the connection.
- char mysql_reconnect = 1;
- mysql_options(mysql->conn, MYSQL_OPT_RECONNECT, (char *)&mysql_reconnect);