Hi!

Sorry for the late reply but I have been on vacation.

equ> I am the programmer of the oer+MySQL IRC bot (http://oer.equnet.org). equ> With the current series of MySQL RDBMS (tested with 3.23.49 on Debian equ> Linux 3.0 running a 2.4.19-pre10 kernel) connecting to a remote equ> database can be problematic since MySQL doesn't handle very well equ> situations where the remote database has become unavailable.

<cut>

equ> What happens in that code is that always when a debug message is equ> written to stdout it is also written to the database. After "entered equ> oer_debug()" and "reconnect()" would follow the writing of equ> "reconnect()" to the database. The writing seems to have succeeded (?) equ> but the read() following it will block, see below.

equ> 20:33:21 read(3, 0x8090d68, 4) = -1 ETIMEDOUT (Connection timed
out) equ> 20:50:55 shutdown(3, 2 /* send and receive */) = -1 ENOTCONN (Transport
endpoint is not connected) equ> 20:50:55 close(3) = 0 equ> 20:50:55 write(1, "leaving oer_debug()\n", 20) = 20

In your test, what did you do with the remote database. (Just curious). It should have been up when MySQL did a reconnect and then you did something to it so that it would not answer to requests?

equ> I was actually patient enough to wait for it to timeout. Seems that equ> read() will timeout in about 17,5 minutes which is awfully close to equ> the TCP timeout of 15 minutes. If you are wondering, mysql_query() equ> will return -1 and not 2006 or 2013 as one would expect.

mysql_real_query() is supposed to return -1 on error. You can find the error code with mysql_errno(MYSQL *).

equ> What will happen next is that the next call to mysql_query() will make equ> MySQL try to connect to the remote database which again will timeout equ> in a undetermined amount of time.

Will the timeout happen in the connect() or in the read ?

Currently we have only timeout's on connect. It should not be that hard to extend the client protocol to have timeouts on reads, but we haven't done that.

equ> Now, before you suggest I should be using mysql_options() to set the equ> connect timeout, I am. It doesn't seem to help in this context. I had equ> the timeout set to 10 seconds and it took 17,5 minutes for read() to equ> timeout and 3 minutes for connect() to timeout.

equ> What I would like to see in MySQL is the ability to control how MySQL equ> reacts to a database connection becoming unavailable or at least to be equ> able to detect it. What I would also like to see is a timeout option equ> for queries (set a timeout for how long the query is allowed to take).

The timeout for queries is a bit cumbersome as the query could be an update query which you would not like to get killed.

We have plans to support automatic termination of long SELECT queries in the server in 4.1 or 5.0.

Regards, Monty CTO of MySQL AB