Lecture
Often it looks like this:
$php phpconf2009_3.php
string(26) "MySQL server has gone away"
Code:
$cat phpconf2009_3.php
mysql_connect('127.0.0.1:3351', 'root', '');
mysql_select_db('test');
mysql_query('set wait_timeout=1');
$query = 'SELECT 1';
sleep(3);
$result = mysql_query($query);
if (0 != mysql_errno())
var_dump(mysql_error());
else
while ($row = mysql_fetch_row($result))
var_dump($row);
mysql_close();
?>
Before pointing out the cause of the error, I would like to draw your attention to several system MySQL server variables. These are variables responsible for timeout:
mysql> show variables like '%timeout%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| connect_timeout | 10 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| wait_timeout | 28800 |
+----------------------------+-------+
Below are their descriptions:
connect_timeout
How long to wait for a server response before giving an error that the server is not responding.
interactive_timeout
How long to wait for activity from an interactive client before breaking the connection.
wait_timeout
How long to wait for activity from any client before breaking the connection. If the client is interactive and the value of interactive_timeout differs from wait_timeout, then the value interactive_timeout will be used.
net_read_timeout
How long the MySQL server will wait for a response from the reading client. For example, the client sent a SELECT query and reads the response from the server.
net_write_timeout
How long the MySQL server will wait for a response from the recording client. For example, a client sends a request for processing.
In the example above, the problem was that we waited too long for the preset small wait_timeout:
mysql_query('set wait_timeout=1');
set wait_timeout.
sleep(3);
waiting time: 3> 1, so it is not surprising that we received an error.
Naturally sleep (3) was added for clarity only. In the case of a real application, you should pay attention to the places where such a delay can be caused including code without using sleep and similar functions.
Also check other timeouts in case of similar errors.
Reception №16: check the value of wait_timeout and other timeout-s if you encounter the error "MySQL server has gone away"
I would also like to draw your attention to the fact that you will not always get the message "MySQL server has gone away" in case of too little timeout. It can also be the message “Lost connection to MySQL server at 'reading authorization packet'”. Most often such a message indicates a problem with connect_timeout.
For example, I could not find a network with sufficient interruptions to repeat the problem, so I used the debugger.
Run the PHP interpreter under the gdb debugger:
$gdb php
Set breakpoint in the MySQL C API client function:
(gdb) b wait_for_data
Breakpoint 1 at 0x4337a: file client.c, line 190.
Pass the file name with the PHP script to the interpreter:
(gdb) set args phpconf2009_3.php
Run the program:
(gdb) run
Starting program: /usr/local/bin/php phpconf2009_3.php
Reading symbols for shared libraries .+.................................................................++.++ done
Breakpoint 1 at 0x204e435: file client.c, line 1861.
Reading symbols for shared libraries . done
Breakpoint 1, wait_for_data (fd=6, timeout=60) at client.c:195
195 ufds.fd= fd; client.c:1861
(gdb)
When the interpreter stops, we wait 11 seconds, which is 1 second more than the default connect_timeout value, then we enter the command with (continue).
(gdb) c
Continuing.
PHP Warning: mysql_connect(): Lost connection to MySQL server at 'reading authorization packet', system error: 0 in /Users/apple/Documents/www_project/MySQL/Conferences/phpconf2009_3.php on line 2
Warning: mysql_connect(): Lost connection to MySQL server at 'reading authorization packet', system error: 0 in /Users/apple/Documents/www_project/MySQL/Conferences/phpconf2009_3.php on line 2
As a result, they got the error "Lost connection to the MySQL server at 'reading authorization packet'", which indicates that connect_timeout is too small
What to do?
Problems with connect_timeout most often indicate either unstable network operation between the client and the MySQL server, or that the machine running the MySQL server is overloaded.
Therefore, after you check whether the increase in timeout helps, try to find and eliminate the real cause of this behavior, if possible.
Do not increase connect_timeout unnecessarily: nobody will like to wait too long!
Reception №17: check the connect_timeout value in case of the error “Lost connection to MySQL server at 'reading authorization packet'”
Comments
To leave a comment
Databases - Error detection methods in SQL application
Terms: Databases - Error detection methods in SQL application