Bug #14057 mysql_ping() handles TCP and UNIX socket connections in different ways
Submitted: 16 Oct 2005 11:20 Modified: 27 Feb 2006 19:56
Reporter: Gleb Paharenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.14, 4.1.16-BK OS:Linux (Linux (Fedora 4))
Assigned to: Magnus Blåudd

[16 Oct 2005 11:20] Gleb Paharenko
Description:
mysql_ping() doesn't properly work with TCP connection (through the UNIX socket it works fine)

How to repeat:
First this problem has appeared on the list at:
  http://lists.mysql.com/mysql/190441

The test program:

#include<stdio.h>
#include<mysql/mysql.h>
int main()
{
        MYSQL mysql;
        char c;

        int i;
  printf("Begin\n");
        mysql_init(&mysql);
        mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"mysql");
        if (!mysql_real_connect(&mysql,"127.0.0.1"

                                                ,"root"

                                                ,""

                                                ,"test"

                                                ,0

                                                ,NULL

                                                ,0))
        {
            fprintf(stderr, "Failed to connect to database: Error: %s\n",
                                  mysql_error(&mysql));
        }
        mysql.reconnect = 1;
        printf("Restart MySQL server and press Enter...\n");
        c=getchar();
  (void) putc ('\n', stdout);
        for(i=1; i<4; i++)
        {
                if( mysql_ping(&mysql) != 0)
                {
                        printf("Error: %s\n", mysql_error(&mysql));
                }
                else
                {
                        printf("Ok!\n");
                }
        }

}

The output is:

Begin
Restart MySQL server and press Enter...

Error: Lost connection to MySQL server during query
Ok!
Ok!

If I change the host from 127.0.0.1 to localhost - everything works fine:

Begin
Restart MySQL server and press Enter...

Ok!
Ok!
Ok!

So the problem that mysql_ping doesn't work with TCP connection properly (host=127.0.0.1).

Additional info:

>Server: /usr/bin/mysqladmin  Ver 8.41 Distrib 4.1.14, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          4.1.14-standard-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 55 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 11  Flush tables: 1  Open tabl
es: 0  Queries per second avg: 0.018
>C compiler:    2.95.3
>C++ compiler:  2.95.3
>Environment:
        <machine, os, target, libraries (multiple lines)>
System: Linux love 2.6.13-1.1526_FC4 #1 Wed Sep 28 19:15:10 EDT 2005 i686 i686 i
386 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using built-in specs.
Target: i386-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/u
sr/share/info --enable-shared --enable-threads=posix --enable-checking=release -
-with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-
libgcj-multifile --enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gt
k --with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre --host=i386-redhat-li
nux
Thread model: posix

gcc version 4.0.1 20050727 (Red Hat 4.0.1-5)
Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce'  CXX='g
cc'  CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce                -felide-constr
uctors -fno-exceptions -fno-rtti                  '  LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx  1 root root 13 Авг 29 18:38 /lib/libc.so.6 -> libc-2.3.5.so
-rwxr-xr-x  1 root root 1485672 Авг 15 17:34 /lib/libc-2.3.5.so
-rw-r--r--  1 root root 2516100 Авг 15 17:18 /usr/lib/libc.a
-rw-r--r--  1 root root 238 Авг 15 16:36 /usr/lib/libc.so
Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-st
atic' '--with-client-ldflags=-all-static' '--with-comment=MySQL Community Editio
n - Standard (GPL)' '--with-server-suffix=-standard' '--without-embedded-server'
 '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--
enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-uni
x-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=com
plex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysco
nfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/
usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-
thread-safe-client' '--with-readline' 'CC=gcc' 'CFLAGS=-O2 -mcpu=i486 -fno-stren
gth-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce                 -felid
e-constructors -fno-exceptions -fno-rtti                  ' 'CXX=gcc'

Suggested fix:
Fix this issue,  ask for any additional info. If you're unable to reproduce this bug (though
it is quite reproducible for me and another guy on the list) give some advice where should I dig
in MySQL source code for this problem.
[16 Oct 2005 12:16] Valerii Kravchuk
Thank you for a problem report. It is easily repeatable. I was able to repeat the behaviour you described on latest 4.1.16-BK. The program built from 14057.c (will attach the file), gave me the following:

Begin
MySQL is up and running
Restart MySQL and hit enter (reconnect: 1)...

Result of ping: 1
Error: Lost connection to MySQL server during query
Ping 2 Ok!
Ping 3 Ok!

with 127.0.0.1, and three 'Ok!' when working through socket (with localhost). But I do not understand, why is it a bug. According to the documentation (http://dev.mysql.com/doc/refman/4.1/en/mysql-ping.html):

"A non-zero return does not indicate whether the MySQL server itself is down; the connection might be broken for other reasons such as network problems."

So, the server is working, and we just recconect to it by using mysql_ping(), but connection was really broken when we shutted down the server (I did it using "mysqladmin -uroot shuwdown". While socket file still exists even after shutting down the server.

I think, it is at most a documentation request, the small difference in behaviour that should be described with a simple example like yours. But not a bug. What do you think?
[16 Oct 2005 12:17] Valerii Kravchuk
Test case as a separate file.

Attachment: 14057.c (text/x-csrc), 991 bytes.

[16 Oct 2005 13:36] Lefteris Tsintjelis
This is intresting, the documentation indeed mentions that mysql_ping
indicates "Zero if the connection to the server is alive" and not that
"zero if the server is alive", as I *naturally expected* also, so it
is kind of tricky this one. I would expect the same behavior with Unix
sockets or TCP sockets though. By restarting the server the connection
breaks so I guess the first ping restores that broken connection and
therefor returns with a non zero value since the connection was not
alive at that moment.

However, since reconnect was set, I would *very naturally expect* to
try and reconnect first and then return with a 0 value if everything is
OK. This would definetly make much more sence and make mysql_ping ()
much easier to use, instead of now making an extra mysql_ping () again
to see if the first one restored the connection or not and it would
definetly save some unessasary timeouts in case the server remains
down.
[16 Oct 2005 16:08] Gleb Paharenko
Hi!

This makes sence. mysql_ping reports that connection is broken and then tries to
reconnect again. Now, I agree that mysql_ping should report about the broken connection,
in case we in the middle of some transaction (after reconnection we potentially lose table locks, user variables, session  variables ). But this should be reported when we're using a Unix socket as well. So TCP part is working fine, but the Unix socket part could produce problems. And ,all in all, the different behavior should be ment in the documentation.
[17 Oct 2005 7:55] Sergei Golubchik
This looks like a duplicate of http://bugs.mysql.com/bug.php?id=2845
Though mysql_ping can have a workaround for this - unlike mysql_query(), ping packet is always safe to send again, so mysql_ping can reconnect where mysql_query cannot.
[19 Oct 2005 6:07] Lefteris Tsintjelis
I think there may be another issue with the timeouts here. In case the connection is completly lost and there is no response whatsoever from the server then mysql_ping must wait for 60 secs before it exits. If the connection was broken during a query, which is most likely and the time such a broken connection will be detected, then that is another 60 secs before we get an error and if we want to make sure that the connection is down and send a second ping then the total goes up to 3 minutes minimum before we can safely tell that the connection is broken. The default timing cost for all this is pretty high.
[9 Feb 2006 13:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2362
[9 Feb 2006 14:18] Magnus Blåudd
This is related to bug#15348
[16 Feb 2006 12:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2727
[24 Feb 2006 8:10] Magnus Blåudd
Pushed a fix to 5.0.19 and 5.1.8 that makes 'mysql_ping' retry if "reconnect" flag is set and it get error CR_SERVER_LOST in the first attempt to ping the server.
[27 Feb 2006 19:56] Mike Hillyer
Documented in 5.0.19 and 5.1.8 changelogs:

 <listitem>
        <para>
          The <function>mysql_ping</function> function will now retry 
          if  the <literal>reconnect</literal>
          flag is set and error <literal>CR_SERVER_LOST</literal> is
          encountered during the first attempt to ping the
          server. (Bug #14057)
        </para>
      </listitem>
[1 Mar 2006 15:36] Armin Schöffmann
Seems to be connected with bug http://bugs.mysql.com/bug.php?id=927/
[4 Mar 2006 16:06] Armin Schöffmann
typo:
http://bugs.mysql.com/bug.php?id=9271/