| Bug #9271 | mysql_ping reports wrong result after mysql_kill | ||
|---|---|---|---|
| Submitted: | 18 Mar 2005 11:12 | Modified: | 21 Apr 2006 17:33 |
| Reporter: | Armin Schöffmann (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.10 | OS: | Windows (win xp sp2 de) |
| Assigned to: | Magnus Blåudd | CPU Architecture: | Any |
[22 Mar 2005 22:47]
MySQL Verification Team
I think you did a typo in your first test case:
result 0, the connection hasn't been reestablished
# I got 1 == the connection hasn't been reestablished
# should be 0 if the reconnect was successfully
call 'mysql_ping again'
result 1, connection has been restored.
# I got 0 == connection has been restored.
The MYSQL struct has a member reconnect, then in
your first mysql_ping(..) it tries to reconnect like the
Manual says:
Checks whether the connection to the server is working. If it has gone down, an automatic reconnection is attempted.
Look how mysqladmin does using that reconnect member:
case ADMIN_PING:
mysql->reconnect=0; /* We want to know of reconnects */
if (!mysql_ping(mysql))
{
if (option_silent < 2)
puts("mysqld is alive");
}
else
{
if (mysql_errno(mysql) == CR_SERVER_GONE_ERROR)
{
mysql->reconnect=1;
if (!mysql_ping(mysql))
puts("connection was down, but mysqld is now alive");
}
else
{
my_printf_error(0,"mysqld doesn't answer to ping, error: '%s'",
MYF(ME_BELL),mysql_error(mysql));
return -1;
}
}
mysql->reconnect=1; /* Automatic reconnect is default */
break;
[23 Mar 2005 0:04]
Armin Schöffmann
i repeated testcase #1: same result: 0: on 1st mysql_ping(...) call 1: on 2nd call the reconnect happens during the 2nd call. when i set MYSQL.reconnect=0, the results are the same, with the exception that no reconnect appears after the 2nd call. (as exspected)
[23 Mar 2005 0:39]
MySQL Verification Team
Notice from the Manual:
22.2.3.44. mysql_ping()
int mysql_ping(MYSQL *mysql)
Description
Checks whether the connection to the server is working. If it has gone down, an automatic reconnection is attempted.
This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary.
Return Values
Zero if the server is alive. Non-zero if an error occurred.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I got the below output from the small code at the bottom:
C:\temp>test01
First MYSQL Connected with the Server: 4.1.10-nt
Second MYSQL Connected with the Server: 4.1.10-nt
For First MySQL reconnect automatic is on
First mysql_ping() returned: 1
Second mysql_ping() returned: 0
#include <stdio.h>
#include <string.h>
#include <my_global.h>
#include <m_ctype.h>
#include <m_string.h>
#include <mysql.h>
int main()
{
unsigned long my_pid;
int rt_ping, rt_ping2;
my_bool rt_reconnect;
MYSQL *MySQL;
MYSQL *MySQL2;
if ( (MySQL = mysql_init((MYSQL*) 0)) &&
mysql_real_connect( MySQL,"localhost","root",
"", "mysql", 0, NULL, 0 ))
printf("First MYSQL Connected with the Server: %s\n",
mysql_get_server_info(MySQL));
else
{
printf("First MYSQL Failed to connect with the server\n");
mysql_close( MySQL );
return 1;
}
if ( (MySQL2 = mysql_init((MYSQL*) 0)) &&
mysql_real_connect( MySQL2,"localhost","root",
"", "mysql", 0, NULL, 0 ))
printf("Second MYSQL Connected with the Server: %s\n",
mysql_get_server_info(MySQL2));
else
{
printf("Failed to connect with the server\n");
mysql_close( MySQL );
return 1;
}
my_pid = mysql_thread_id(MySQL);
mysql_kill(MySQL,my_pid);
rt_reconnect = MySQL->reconnect;
rt_ping = mysql_ping(MySQL);
rt_ping2 = mysql_ping(MySQL);
if ( rt_reconnect )
printf("For First MySQL reconnect automatic is on\n");
printf("First mysql_ping() returned: %d\n", rt_ping);
printf("Second mysql_ping() returned: %d\n", rt_ping2);
mysql_close(MySQL);
mysql_close(MySQL2);
return 0;
}
[23 Mar 2005 1:35]
Armin Schöffmann
I narrowed down the problem:
The described behaviour seems to show up only, when you kill the mysql-connection from within another process (not a different app-thread): I tried with MySQLAdministrator:
Output from your (modified) test-app (source code below)
G:\Projekte\MySQLPingTest\Debug>mysqlpingtest
First MYSQL Connected with the Server: 4.1.10a-nt-max
Kill connection 34 from another process (e.g. MySQL Administrator)
and press <enter>.
For First MySQL reconnect automatic is on
First mysql_ping() returned: 0
Second mysql_ping() returned: 1
G:\Projekte\MySQLPingTest\Debug>
#include "stdafx.h"
#include <stdio.h>
#include <string.h>
#include <my_global.h>
#include <m_ctype.h>
#include <m_string.h>
#include <mysql.h>
int main()
{
MYSQL *MySQL;
int rt_ping, rt_ping2;
my_bool rt_reconnect;
if ( (MySQL = mysql_init((MYSQL*) 0)) &&
mysql_real_connect( MySQL,"localhost","root",
"", "mysql", 0, NULL, 0 ))
printf("First MYSQL Connected with the Server: %s\n",
mysql_get_server_info(MySQL));
else
{
printf("First MYSQL Failed to connect with the server\n");
mysql_close( MySQL );
return 1;
}
printf("Kill connection %d from another process (e.g. MySQL Administrator)\nand press <enter>.\n",mysql_thread_id(MySQL));
fgetc(stdin);
rt_reconnect = MySQL->reconnect;
rt_ping = mysql_ping(MySQL);
rt_ping2 = mysql_ping(MySQL);
if ( rt_reconnect )
printf("For First MySQL reconnect automatic is on\n");
printf("First mysql_ping() returned: %d\n", rt_ping);
printf("Second mysql_ping() returned: %d\n", rt_ping2);
mysql_close(MySQL);
return 0;
}
[23 Mar 2005 22:03]
Jim Winstead
There still appears to be confusion about what the return value of mysql_ping() means. If it returns 0, it means the server is up. If it returns anything else, it means the server is down. It does not return a particular value to indicate that a reconnect was done. If the reconnect flag on the MYSQL struct is set to 0, no reconnect will be done. If it is set to any other value, a reconnect will automatically be done by mysql_ping(), and any other method that accesses the server. In MySQL 4.1, the reconnect flag is on by default. In MySQL 5.0, it is off by default.
[23 Mar 2005 23:59]
Armin Schöffmann
Jim,Miguel, in all reported cases, the server was up at calltime of mysql_ping, but neither was the result consistent between repeated calls nor was it 0 in every case. Before calling mysql_ping we simply killed the connection either by mysql_kill from within the same process/thread (example of miguel) or by killing the connection-thread from within another process (my example).The only case where mysql_ping seems to follow exactly the docs is when you restart mysqld before the call. Each of the 2 cases, showed a different result from mysql_ping().
[28 Mar 2005 19:52]
MySQL Verification Team
I don't think this is a bug, but need more clarification in our documentation how it works i.e: the member reconnect needs to be documented. I suggest you to use a code like mysqladmin has.
[1 Mar 2006 15:35]
Armin Schöffmann
Seems to be connected with bug http://bugs.mysql.com/bug.php?id=14057/
[21 Apr 2006 11:01]
Magnus Blåudd
This problem has been fixed in version 5.0 of MySQL. I would suggest using that version or continur with the workarund to ping twice that you already have implemented. The error code returned depend on _when_ we can detect if the server has closed the connection. If it can be detected before anything is sent to the server, we will get error 2006 which basiucally means the server has disconnected/we couldn't write to the socket. The reconnect mechanism will then kick in and we can connect to the server again and send the message. But if the disconnection can't be detected before we have sent the message to the server, it's not possible to say iit was because the server diconnected ot didn't answer, or answered but we didn't get the reponse. And thus the reconnect will not kick in. In 5.0 the disconnection should be detected before we send the packet to the server - at least in the case when the server has closed the connection in a nice way. Alse see bug#2845 which is kind of the mother bug.
[21 Apr 2006 12:22]
Armin Schöffmann
Magnus, Just verified with our client-code against 5.0.20 with libmysqld 5.0.20 testcase #1 SERVER START/STOP: ------------------------------------------------------------- 1 active TCP/IP-connection (127.0.0.1:3306) - stopping the server by 'net stop mysql' (normal shutdown) - mysql_ping reports an error (correct) - restarting the server by 'net start mysql' - mysql_ping reports OK connection is alive after 1st mysql_ping testcase #2 SERVER RESTART: ------------------------------------------------------------- 1 active TCP/IP-connection (127.0.0.1:3306) - stopping the server by 'net stop mysql' (normal shutdown) - restarting the server by 'net start mysql' - mysql_ping reports OK connection is alive after 1st mysql_ping testcase #3 CONNECTION KILL: ------------------------------------------------------------- 2 active TCP/IP-connections (127.0.0.1:3306) - killing connection 1 by SQL-cmd 'KILL 1;' - mysql_ping on killed connection reports OK connection is alive after 1st mysql_ping. testcase #4 SERVER CRASH: ------------------------------------------------------------- 1 active TCP/IP-connection (127.0.0.1:3306) - killing the server through the task-manager (abnormal shutdown) - restarting the server by 'net start mysql' - mysql_ping reports OK connection is alive after 1st mysql_ping Nice - mysql_ping seems to work now as exspected. Thanks for your efforts.
[21 Apr 2006 17:33]
Magnus Blåudd
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Use 5.0.20 or later.
Description: mysql_ping returns wrong/ambiguous results upon connection-abort (mysql_kill) or server-restart. How to repeat: case #1 open connection #1 with 'mysql_real_connect' open connection #2 and abort connection #1 with mysql_kill with the mysql-handle of connection #1 call 'mysql_ping': result 0, the connection hasn't been reestablished call 'mysql_ping again' result 1, connection has been restored. the behaviour changes, if you issue a query on the killed connection before the first call of mysql_ping(): result will be '1' and a reconnect has taken place within the call (as exspected). case 2: open a connection with 'mysql_real_connect' restart the server call 'mysql_ping': result 0, the connection has been reestablished -> you don't get aware, that a disconnect has taken place mysql_ping reports correct results under the following condition: the connection has died due to a connection wait_timeout: result 1, reconnect took place imho: mysql_ping should: try to reconnect a lost connection, report, if a reconnection-attempt took place and if it was successful report if the connection is healthy and no action had to be taken. Suggested fix: currently, we use the following code (in our client app) to work around the described behaviour: SQLRETURN CDatabaseMySQL::PingConnection(void) { // check vitality of the database-connection // update connection-id (=thread_id) and connection-options // upon reconnect if(thread_id){ CCritLock lock(&critical_connection); int error=0; for (int i=2;i--;){ if(mysql_ping(hdb.hmysql)) error++; } if(error<2){ // update current thread_id thread_id=mysql_thread_id(hdb.hmysql); // set connection specific variables SetConnectionOptions(); return SQL_SUCCESS; } } return SQL_ERROR; }