| Russian Version |
Often this looks like:
$php phpconf2009_3.php
string(26) "MySQL server has gone away"
Code:
$cat phpconf2009_3.php
<?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 I point you to the cause of the error I want to show some system variables of the MySQL server. These are variables responsible for the 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 |
+----------------------------+-------+
You can see their description below:
connect_timeout
How much time to wait answer from the MySQL server before returning error about server does not answer.
interactive_timeout
How much time to wait activity from the interactive client before killing the connection.
wait_timeout
How much time to wait activity from any client before killing the connection. If client is interactive and value of interactive_timeout is different from wait_timeout interactive_timeout will be used.
net_read_timeout
How much time to wait answer from client which reads from MySQL server. For example when client sent SELECT query and is reading its result.
net_write_timeout
How much time to wait answer from client which writes to the server. For example if client is sending query.
In the example above cause of the problem was we were waitning too much time for small wait_timeout which we set before:
mysql_query('set wait_timeout=1');
this is value of wait_timeout set for the example script.
sleep(3);
waiting time: 3>1, therefore this is not surprise what we got error.
Of course I used sleep(3) only to show how to get such error. In case of real application you need to examine pieces of code where such delay can be created by code without using sleep or other similar functions.
In case of similar errors check other timeouts, because they can affect application as well.
Method #16: check value of wait_timeout and other timeouts if you get error "MySQL server has gone away"
Also I want to inform you about you don't always get message "MySQL server has gone away" in case of small timeout. You can also get message "Lost connection to MySQL server at 'reading authorization packet'". Most often such a message inform about problems with small connect_timeout.
I could not find net with errors sufficient to repeat the problem, therefore I used debugger.
Lets start PHP under gdb:
$gdb php
Set breakpoint in MySQL C API function:
(gdb) b wait_for_data
Breakpoint 1 at 0x4337a: file client.c, line 190.
Set name of file with PHP script:
(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 PHP stops we should wait 11 seconds, because this is default value of connect_timeout(10) + 1, then run command c (continue) in gdb.
(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 the result we got error "Lost connection to MySQL server at 'reading authorization packet'" which shows connect_timeout is too small for such net waiting time.
What to do?
Problems with connect_timeout frequently show problems with net used between MySQL client and server or the box where MySQL server is running is overloaded.
Therefore after you checked if increasing timeout solves the problem try to find and solve real cause of such behavior if this is possible.
Don't increase connect_timeout without real need: nobody will like to wait too much!
Method #17: check value of connect_timeout in case of error "Lost connection to MySQL server at 'reading authorization packet'"
| Back | Content | Forward |
|
Author 2010 Sveta Smirnova COPYRIGHT © 2010 S.Smirnova and S. Lasunov sveta_at_js-client_dot_com |