>Description:
With the current series of MySQL RDBMS (tested with 3.23.49 on Debian
Linux 3.0 running a 2.4.19-pre10 kernel) connecting to a remote
database can be problematic since MySQL doesn't handle very well
situations where the remote database has become unavailable.
To be more precise, issuing a mysql_query() when the remote database
has become unavailable (due to routing problems, physical cabling,
firewall filtering, etc) will cause the execution to remain in
mysql_query() for a undetermined amount of time. Strace'ing shows the
process trying to read() from the MySQL socket (here fd = 3) which
will hang, see below.
20:33:21 write(1, "entered oer_debug()\n", 20) = 20
20:33:21 write(1, "reconnect()\n", 12) = 12
20:33:21 fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
20:33:21 read(3, 0x8090d68, 8192) = -1 EAGAIN (Resource temporarily unavailable)
20:33:21 fcntl64(3, F_SETFL, O_RDWR) = 0
20:33:21 write(3, "H\0\0\0\3INSERT INTO oer_output VALU"..., 76) = 76
20:33:21 read(3,
What happens in that code is that always when a debug message is
written to stdout it is also written to the database. After "entered
oer_debug()" and "reconnect()" would follow the writing of
"reconnect()" to the database. The writing seems to have succeeded (?)
but the read() following it will block, see below.
20:33:21 read(3, 0x8090d68, 4) = -1 ETIMEDOUT (Connection timed out)
20:50:55 shutdown(3, 2 /* send and receive */) = -1 ENOTCONN (Transport endpoint is
not connected)
20:50:55 close(3) = 0
20:50:55 write(1, "leaving oer_debug()\n", 20) = 20
I was actually patient enough to wait for it to timeout. Seems that
read() will timeout in about 17,5 minutes which is awfully close to
the TCP timeout of 15 minutes. If you are wondering, mysql_query()
will return -1 and not 2006 or 2013 as one would expect.
What will happen next is that the next call to mysql_query() will make
MySQL try to connect to the remote database which again will timeout
in a undetermined amount of time.
Now, before you suggest I should be using mysql_options() to set the
connect timeout, I am. It doesn't seem to help in this context. I had
the timeout set to 10 seconds and it took 17,5 minutes for read() to
timeout and 3 minutes for connect() to timeout.
What I would like to see in MySQL is the ability to control how MySQL
reacts to a database connection becoming unavailable or at least to be
able to detect it. What I would also like to see is a timeout option
for queries (set a timeout for how long the query is allowed to take).
Thank you in advance for your past/present/future efforts.
>How-To-Repeat:
Startup your MySQL program, let it connect to the remote database and
filter the traffic with a firewall. The next mysql_query() will hang.
>Fix:
>Submitter-Id: <submitter ID>
>Originator:
>Organization:
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis: mysql_query() hangs if remote database becomes unavailable
>Severity:
>Priority:
>Category: mysql
>Class:
>Release: mysql-3.23.49 (Source distribution)
>Environment:
System: Linux irc1 2.4.19-pre10 #1 Sat Jun 8 03:00:02 EEST 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jun 8 02:24 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Apr 28 12:57 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2390922 Apr 28 12:58 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 28 12:58 /usr/lib/libc.so
-rw-r--r-- 1 root root 726660 Mar 24 06:56 /usr/lib/libc-client.so.2001
Configure command: ./configure --prefix=/usr --exec-prefix=/usr
--libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql
--localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info
--mandir=/usr/share/man --enable-shared --with-libwrap --enable-assembler
--with-berkeley-db --with-innodb --enable-static --enable-shared --enable-local-infile
--with-raid --enable-thread-safe-client --without-readline
--with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql
--without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
