>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