mysql_query() hangs if remote database becomes unavailable

Hi!

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

>>>>> "equ" == equ writes:

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.



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

------------------------------------------------------------ ---------
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 bugs-thread12234@lists.mysql.com
To unsubscribe, e-mail

Re: mysql_query() hangs if remote database becomes unavailable

Hi, sorry for the delay. I also went on vacation (Hello
Santorini/Greece!).

I have followed up the discussion in your absence with Gelu Gogancea
but there seems to be a language barrier somewhere so we didn't
understand each other. See my comments below.

On Sun, 28 Jul 2002, Michael Widenius wrote:

> In your test, what did you do with the remote database.
> (Just curious).

I firewalled the out direction (OUTPUT) to the remote database on the
server which was running the MySQL client.

> 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?

Well it wasn't because I didn't remove the firewall rule, once I did
MySQL indeed reconnected fine when the next mysql_query() was issued,
no problem there.

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

Yes, that was my error. Sorry about that. My code now detects a
"server went away" situation and deals with it properly.

> 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 ?

I believe it is in connect(). Maybe I have a error somewhere in my
code but I haven't had much success altering the connection timeout
after mysql_init().

-- clip --
unsigned int timeout;

timeout = (mystate->current_server) ? mystate->current_server->pingfrequency >> 1 : 60;
mysql_options(&mystate->mysqldb.mysqldbconn, MYSQL_OPT_CONNECT_TIMEOUT, (char *) &timeout);
-- clip --

With that piece of code the connect timeout seems to be always around
3 minutes.

> 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.

That would certainly be required at least for the IRC case where ~17
minutes is a long time for a channel without a bot/bots.

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

True...

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

Hear hear. Would it be possible to get the timeout for read() in the
3.23.xx series? Aren't we just talking about a socket option and/or
additional timer/alarm functionality to the existing ones in net.c?

> Regards,
> Monty
> CTO of MySQL AB

Thank you plenty for your time & efforts.


------------------------------------------------------------ ---------
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 bugs-thread12279@lists.mysql.com
To unsubscribe, e-mail

Re: mysql_query() hangs if remote database becomes unavailable

Hi ,

----- Original Message -----
From: "EQU"
To: "Michael Widenius"
Cc: "MySQL buglist"
Sent: Sunday, August 04, 2002 9:40 PM
Subject: Re: mysql_query() hangs if remote database becomes unavailable


>
> I have followed up the discussion in your absence with Gelu Gogancea
> but there seems to be a language barrier somewhere so we didn't
> understand each other.
Unfortunately for me...it's true and i apologize.

Regards,

Gelu




------------------------------------------------------------ ---------
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 bugs-thread12282@lists.mysql.com
To unsubscribe, e-mail

Re: mysql_query() hangs if remote database becomes unavailable

Hi!

>>>>> "equ" == equ writes:



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 ?

equ> I believe it is in connect(). Maybe I have a error somewhere in my
equ> code but I haven't had much success altering the connection timeout
equ> after mysql_init().

equ> -- clip --
equ> unsigned int timeout;

equ> timeout = (mystate->current_server) ? mystate->current_server->pingfrequency >> 1 : 60;
equ> mysql_options(&mystate->mysqldb.mysqldbconn, MYSQL_OPT_CONNECT_TIMEOUT, (char *) &timeout);
equ> -- clip --

equ> With that piece of code the connect timeout seems to be always around
equ> 3 minutes.

Is the above done one a working connections or a new connection for
which you are going to call mysql_real_connect() ?

You should be able to find out what' wrong by compiling MySQL with
--debug, link your application with the new client library and do
MYSQL_DEBUG=d:t:O,\tmp\client.trace ; export MYSQL_BUG
before you start your application.

If you now get a timeout, the trace file \tmp\client.trace should
contain enough information for you to find the real reason for the timeout.



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

equ> Hear hear. Would it be possible to get the timeout for read() in the
equ> 3.23.xx series? Aren't we just talking about a socket option and/or
equ> additional timer/alarm functionality to the existing ones in net.c?

We don't have any timer alarms for the client server code.
I don't know how to do this portable with socket options...
If you could figure this out (and test it) we could consider adding
this to the MySQL standard source code..

Regards,
Monty



------------------------------------------------------------ ---------
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 bugs-thread12356@lists.mysql.com
To unsubscribe, e-mail