mysql_connect_timeout problem when using times less than 1 sec

------=_Part_20526_6494833.1159027366697
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

The DBI module when connecting to a mysql database seams to only support
integer values for the mysql_connect_timeout if you use say 0.5 secs as the
timeout value it defaults to 3secs. Does anyone know a way round this
problem as i need a very short timeout in my application.

for example

#does not work and defaults to 3 secs timeout
$dbh = DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=
0.5","username","password");

#does work and timesout after 1 sec
$dbh =
DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=1","username","password");

Thanks

Duncan

------=_Part_20526_6494833.1159027366697--

Re: mysql_connect_timeout problem when using times less than 1 sec

Without looking at the source code of DBD::mysql, I would quess that the
mysql_connect_timeout parameter is an integer. 0.5 is rounded down to 0,
which is the same as not specifying mysql_connect_timeout at all. If you
are on a Unix/POSIX System, alarm() and Signals may help.

Alexander

On 23.09.2006 18:02, Duncan Wren wrote:
> The DBI module when connecting to a mysql database seams to only support
> integer values for the mysql_connect_timeout if you use say 0.5 secs
> as the
> timeout value it defaults to 3secs. Does anyone know a way round this
> problem as i need a very short timeout in my application.
>
> for example
>
> #does not work and defaults to 3 secs timeout
> $dbh =
> DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=
> 0.5","username","password");
>
> #does work and timesout after 1 sec
> $dbh =
> DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=1","username","password");
>
>
> Thanks
>
> Duncan
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: mysql_connect_timeout problem when using times less than 1 sec

------=_Part_26548_30536683.1159093433066
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I have looked at the code for DBD::mysql and DBI.pm but cannot workout where
the timeout is implemented or where the default is specified. Does anyone
know where this logic is implemented? I would rather make make the change in
the module that currently implements the time out code than add some logic
to add timeout in my application code.

Duncan

On 24/09/06, Alexander Foken wrote:
>
> Without looking at the source code of DBD::mysql, I would quess that the
> mysql_connect_timeout parameter is an integer. 0.5 is rounded down to 0,
> which is the same as not specifying mysql_connect_timeout at all. If you
> are on a Unix/POSIX System, alarm() and Signals may help.
>
> Alexander
>
> On 23.09.2006 18:02, Duncan Wren wrote:
> > The DBI module when connecting to a mysql database seams to only support
> > integer values for the mysql_connect_timeout if you use say 0.5 secs
> > as the
> > timeout value it defaults to 3secs. Does anyone know a way round this
> > problem as i need a very short timeout in my application.
> >
> > for example
> >
> > #does not work and defaults to 3 secs timeout
> > $dbh =
> > DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=
> > 0.5","username","password");
> >
> > #does work and timesout after 1 sec
> > $dbh =
> >
> DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=1","username","password");
> >
> >
> > Thanks
> >
> > Duncan
> >
>
>
> --
> Alexander Foken
> mailto:alexander@foken.de http://www.foken.de/alexander/
>
>

------=_Part_26548_30536683.1159093433066--

Re: mysql_connect_timeout problem when using times less than 1 sec

http://search.cpan.org/src/CAPTTOFU/DBD-mysql-3.0007/dbdimp. c line 1309
and following, the value is converted to an int, then passed to
mysql_options(), which is a part of the mysql API. (Ignore the cast to
const char *, that's just how the mysql_options() API call is defined.)
Looking at http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html ,
there IS NO WAY to specify a timeout of less than one second.

It seems that you have to live with a minimum timeout of one second, as
alarm() also has only seconds resolution. The only way for a timeout of
less than one second is a combination of fork(), select(), kill() and a
custom signal handler, which is really, really ugly.

Let's try to solve the problem in an other way: Why do you need such a
short timeout?

Alexander

On 24.09.2006 12:23, Duncan Wren wrote:
> I have looked at the code for DBD::mysql and DBI.pm but cannot workout
> where
> the timeout is implemented or where the default is specified. Does anyone
> know where this logic is implemented? I would rather make make the
> change in
> the module that currently implements the time out code than add some
> logic
> to add timeout in my application code.
>
> Duncan
>
> On 24/09/06, Alexander Foken wrote:
>>
>> Without looking at the source code of DBD::mysql, I would quess that the
>> mysql_connect_timeout parameter is an integer. 0.5 is rounded down to 0,
>> which is the same as not specifying mysql_connect_timeout at all. If you
>> are on a Unix/POSIX System, alarm() and Signals may help.
>>
>> Alexander
>>
>> On 23.09.2006 18:02, Duncan Wren wrote:
>> > The DBI module when connecting to a mysql database seams to only
>> support
>> > integer values for the mysql_connect_timeout if you use say 0.5 secs
>> > as the
>> > timeout value it defaults to 3secs. Does anyone know a way round this
>> > problem as i need a very short timeout in my application.
>> >
>> > for example
>> >
>> > #does not work and defaults to 3 secs timeout
>> > $dbh =
>> > DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=
>> > 0.5","username","password");
>> >
>> > #does work and timesout after 1 sec
>> > $dbh =
>> >
>> DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=1","username","password");
>>
>> >
>> >
>> > Thanks
>> >
>> > Duncan
>> >
>>
>>
>> --
>> Alexander Foken
>> mailto:alexander@foken.de http://www.foken.de/alexander/
>>
>>
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: mysql_connect_timeout problem when using times less than 1 sec

------=_Part_26964_16096651.1159099948218
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I need a very short time out as the application i'm using has mutiple
connects to the db per page so if the dbhost is down i need it timeout
quickly to ensure the page is returned quickly to the user. Otherwise the
page hangs for several secs.

The only solution i can see is ensuring that each page only has 1 connection
to the db (i know it should be like this anyway, but this is old code). As 1
x 1sec timeout is acceptable but 10 x 1 sec timeout is not.

Your correct that a combo of select/fork/kill will end up very messy so that
is not really an option.

Thanks again for you help Alexander

On 24/09/06, Alexander Foken wrote:
>
> http://search.cpan.org/src/CAPTTOFU/DBD-mysql-3.0007/dbdimp. c line 1309
> and following, the value is converted to an int, then passed to
> mysql_options(), which is a part of the mysql API. (Ignore the cast to
> const char *, that's just how the mysql_options() API call is defined.)
> Looking at http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html ,
> there IS NO WAY to specify a timeout of less than one second.
>
> It seems that you have to live with a minimum timeout of one second, as
> alarm() also has only seconds resolution. The only way for a timeout of
> less than one second is a combination of fork(), select(), kill() and a
> custom signal handler, which is really, really ugly.
>
> Let's try to solve the problem in an other way: Why do you need such a
> short timeout?
>
> Alexander
>
> On 24.09.2006 12:23, Duncan Wren wrote:
> > I have looked at the code for DBD::mysql and DBI.pm but cannot workout
> > where
> > the timeout is implemented or where the default is specified. Does
> anyone
> > know where this logic is implemented? I would rather make make the
> > change in
> > the module that currently implements the time out code than add some
> > logic
> > to add timeout in my application code.
> >
> > Duncan
> >
> > On 24/09/06, Alexander Foken wrote:
> >>
> >> Without looking at the source code of DBD::mysql, I would quess that
> the
> >> mysql_connect_timeout parameter is an integer. 0.5 is rounded down to
> 0,
> >> which is the same as not specifying mysql_connect_timeout at all. If
> you
> >> are on a Unix/POSIX System, alarm() and Signals may help.
> >>
> >> Alexander
> >>
> >> On 23.09.2006 18:02, Duncan Wren wrote:
> >> > The DBI module when connecting to a mysql database seams to only
> >> support
> >> > integer values for the mysql_connect_timeout if you use say 0.5 secs
> >> > as the
> >> > timeout value it defaults to 3secs. Does anyone know a way round this
> >> > problem as i need a very short timeout in my application.
> >> >
> >> > for example
> >> >
> >> > #does not work and defaults to 3 secs timeout
> >> > $dbh =
> >> > DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=
> >> > 0.5","username","password");
> >> >
> >> > #does work and timesout after 1 sec
> >> > $dbh =
> >> >
> >>
> DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=1","username","password");
> >>
> >> >
> >> >
> >> > Thanks
> >> >
> >> > Duncan
> >> >
> >>
> >>
> >> --
> >> Alexander Foken
> >> mailto:alexander@foken.de http://www.foken.de/alexander/
> >>
> >>
> >
>
>
> --
> Alexander Foken
> mailto:alexander@foken.de http://www.foken.de/alexander/
>
>

------=_Part_26964_16096651.1159099948218--

Re: mysql_connect_timeout problem when using times less than 1 sec

On 24.09.2006 14:12, Duncan Wren wrote:
> I need a very short time out as the application i'm using has mutiple
> connects to the db per page so if the dbhost is down i need it timeout
> quickly to ensure the page is returned quickly to the user. Otherwise the
> page hangs for several secs.

Abort if the first connection in the page times out after 1 or 2 seconds
and assume the server is down instead of trying to connect again:

# start of page
my $dbh=DBI->connect(... mysql_connect_timeout => 1);
return unless $dbh; # Server is down, the assuming the following
connects will also fail
# or, if you need an Exception:
# die "Can't connect to server" unless $dbh;

my $sth=$dbh->prepare ...

my $dbh2=DBI->connect(... mysql_connect_timeout => 1);
return unless $dbh2;

and so on

Maximum timeout is one second, with this code.


Alexander

>
> The only solution i can see is ensuring that each page only has 1
> connection
> to the db (i know it should be like this anyway, but this is old
> code). As 1
> x 1sec timeout is acceptable but 10 x 1 sec timeout is not.
>
> Your correct that a combo of select/fork/kill will end up very messy
> so that
> is not really an option.
>
> Thanks again for you help Alexander
>
> On 24/09/06, Alexander Foken wrote:
>>
>> http://search.cpan.org/src/CAPTTOFU/DBD-mysql-3.0007/dbdimp. c line 1309
>> and following, the value is converted to an int, then passed to
>> mysql_options(), which is a part of the mysql API. (Ignore the cast to
>> const char *, that's just how the mysql_options() API call is defined.)
>> Looking at http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html ,
>> there IS NO WAY to specify a timeout of less than one second.
>>
>> It seems that you have to live with a minimum timeout of one second, as
>> alarm() also has only seconds resolution. The only way for a timeout of
>> less than one second is a combination of fork(), select(), kill() and a
>> custom signal handler, which is really, really ugly.
>>
>> Let's try to solve the problem in an other way: Why do you need such a
>> short timeout?
>>
>> Alexander
>>
>> On 24.09.2006 12:23, Duncan Wren wrote:
>> > I have looked at the code for DBD::mysql and DBI.pm but cannot workout
>> > where
>> > the timeout is implemented or where the default is specified. Does
>> anyone
>> > know where this logic is implemented? I would rather make make the
>> > change in
>> > the module that currently implements the time out code than add some
>> > logic
>> > to add timeout in my application code.
>> >
>> > Duncan
>> >
>> > On 24/09/06, Alexander Foken wrote:
>> >>
>> >> Without looking at the source code of DBD::mysql, I would quess that
>> the
>> >> mysql_connect_timeout parameter is an integer. 0.5 is rounded down to
>> 0,
>> >> which is the same as not specifying mysql_connect_timeout at all. If
>> you
>> >> are on a Unix/POSIX System, alarm() and Signals may help.
>> >>
>> >> Alexander
>> >>
>> >> On 23.09.2006 18:02, Duncan Wren wrote:
>> >> > The DBI module when connecting to a mysql database seams to only
>> >> support
>> >> > integer values for the mysql_connect_timeout if you use say 0.5
>> secs
>> >> > as the
>> >> > timeout value it defaults to 3secs. Does anyone know a way round
>> this
>> >> > problem as i need a very short timeout in my application.
>> >> >
>> >> > for example
>> >> >
>> >> > #does not work and defaults to 3 secs timeout
>> >> > $dbh =
>> >> > DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=
>> >> > 0.5","username","password");
>> >> >
>> >> > #does work and timesout after 1 sec
>> >> > $dbh =
>> >> >
>> >>
>> DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=1","username","password");
>>
>> >>
>> >> >
>> >> >
>> >> > Thanks
>> >> >
>> >> > Duncan
>> >> >
>> >>
>> >>
>> >> --
>> >> Alexander Foken
>> >> mailto:alexander@foken.de http://www.foken.de/alexander/
>> >>
>> >>
>> >
>>
>>
>> --
>> Alexander Foken
>> mailto:alexander@foken.de http://www.foken.de/alexander/
>>
>>
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: mysql_connect_timeout problem when using times less than 1 sec

Or a completely different way: Timeout the entire page generation, not
just the mysql connections:

## following roughly http://perldoc.perl.org/functions/alarm.html
my $page;
eval {
local $SIG{ALRM}=sub { die "TIMEOUT\n" };
alarm(3); # 3 seconds for a page build
$page=build_page(...);
alarm(0);
};
if ($@) {
if ($@=~/^TIMEOUT/) {
send_timeout_error_page();
} else {
send_fatal_error_page($@);
} else {
send_page($page);
}

Note that you need a system where alarm() and signals work as expected,
i.e. Unix/POSIX.

Alexander





On 24.09.2006 14:12, Duncan Wren wrote:
> I need a very short time out as the application i'm using has mutiple
> connects to the db per page so if the dbhost is down i need it timeout
> quickly to ensure the page is returned quickly to the user. Otherwise the
> page hangs for several secs.
>
> The only solution i can see is ensuring that each page only has 1
> connection
> to the db (i know it should be like this anyway, but this is old
> code). As 1
> x 1sec timeout is acceptable but 10 x 1 sec timeout is not.
>
> Your correct that a combo of select/fork/kill will end up very messy
> so that
> is not really an option.
>
> Thanks again for you help Alexander
>
> On 24/09/06, Alexander Foken wrote:
>>
>> http://search.cpan.org/src/CAPTTOFU/DBD-mysql-3.0007/dbdimp. c line 1309
>> and following, the value is converted to an int, then passed to
>> mysql_options(), which is a part of the mysql API. (Ignore the cast to
>> const char *, that's just how the mysql_options() API call is defined.)
>> Looking at http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html ,
>> there IS NO WAY to specify a timeout of less than one second.
>>
>> It seems that you have to live with a minimum timeout of one second, as
>> alarm() also has only seconds resolution. The only way for a timeout of
>> less than one second is a combination of fork(), select(), kill() and a
>> custom signal handler, which is really, really ugly.
>>
>> Let's try to solve the problem in an other way: Why do you need such a
>> short timeout?
>>
>> Alexander
>>
>> On 24.09.2006 12:23, Duncan Wren wrote:
>> > I have looked at the code for DBD::mysql and DBI.pm but cannot workout
>> > where
>> > the timeout is implemented or where the default is specified. Does
>> anyone
>> > know where this logic is implemented? I would rather make make the
>> > change in
>> > the module that currently implements the time out code than add some
>> > logic
>> > to add timeout in my application code.
>> >
>> > Duncan
>> >
>> > On 24/09/06, Alexander Foken wrote:
>> >>
>> >> Without looking at the source code of DBD::mysql, I would quess that
>> the
>> >> mysql_connect_timeout parameter is an integer. 0.5 is rounded down to
>> 0,
>> >> which is the same as not specifying mysql_connect_timeout at all. If
>> you
>> >> are on a Unix/POSIX System, alarm() and Signals may help.
>> >>
>> >> Alexander
>> >>
>> >> On 23.09.2006 18:02, Duncan Wren wrote:
>> >> > The DBI module when connecting to a mysql database seams to only
>> >> support
>> >> > integer values for the mysql_connect_timeout if you use say 0.5
>> secs
>> >> > as the
>> >> > timeout value it defaults to 3secs. Does anyone know a way round
>> this
>> >> > problem as i need a very short timeout in my application.
>> >> >
>> >> > for example
>> >> >
>> >> > #does not work and defaults to 3 secs timeout
>> >> > $dbh =
>> >> > DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=
>> >> > 0.5","username","password");
>> >> >
>> >> > #does work and timesout after 1 sec
>> >> > $dbh =
>> >> >
>> >>
>> DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=1","username","password");
>>
>> >>
>> >> >
>> >> >
>> >> > Thanks
>> >> >
>> >> > Duncan
>> >> >
>> >>
>> >>
>> >> --
>> >> Alexander Foken
>> >> mailto:alexander@foken.de http://www.foken.de/alexander/
>> >>
>> >>
>> >
>>
>>
>> --
>> Alexander Foken
>> mailto:alexander@foken.de http://www.foken.de/alexander/
>>
>>
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: mysql_connect_timeout problem when using times less than 1 sec

Alexander Foken wrote:
> http://search.cpan.org/src/CAPTTOFU/DBD-mysql-3.0007/dbdimp. c line 1309
> and following, the value is converted to an int, then passed to
> mysql_options(), which is a part of the mysql API. (Ignore the cast to
> const char *, that's just how the mysql_options() API call is defined.)
> Looking at http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html ,
> there IS NO WAY to specify a timeout of less than one second.
>
> It seems that you have to live with a minimum timeout of one second, as
> alarm() also has only seconds resolution. The only way for a timeout of
> less than one second is a combination of fork(), select(), kill() and a
> custom signal handler, which is really, really ugly.

With Time::HiRes and Sys::Sigaction you could do something like:

use Time::HiRes qw (ualarm);
use Sys::SigAction qw (set_sig_handler);

eval {
my h$ = set_sig_handler('ALRM', sub{die;},
{mask=>[qw(INT ALRM)],safe=>0);

# set alarm to 500ms
ualarm(500_000)

# other stuff

# reset alarm
ualarm(0);
}

I used something similar in some test code which worked as expected but
I never used it in production.

> Let's try to solve the problem in an other way: Why do you need such a
> short timeout?
>
> Alexander
>
> On 24.09.2006 12:23, Duncan Wren wrote:
>> I have looked at the code for DBD::mysql and DBI.pm but cannot workout
>> where
>> the timeout is implemented or where the default is specified. Does anyone
>> know where this logic is implemented? I would rather make make the
>> change in
>> the module that currently implements the time out code than add some
>> logic
>> to add timeout in my application code.
>>
>> Duncan
>>
>> On 24/09/06, Alexander Foken wrote:
>>>
>>> Without looking at the source code of DBD::mysql, I would quess that the
>>> mysql_connect_timeout parameter is an integer. 0.5 is rounded down to 0,
>>> which is the same as not specifying mysql_connect_timeout at all. If you
>>> are on a Unix/POSIX System, alarm() and Signals may help.
>>>
>>> Alexander
>>>
>>> On 23.09.2006 18:02, Duncan Wren wrote:
>>> > The DBI module when connecting to a mysql database seams to only
>>> support
>>> > integer values for the mysql_connect_timeout if you use say 0.5 secs
>>> > as the
>>> > timeout value it defaults to 3secs. Does anyone know a way round this
>>> > problem as i need a very short timeout in my application.
>>> >
>>> > for example
>>> >
>>> > #does not work and defaults to 3 secs timeout
>>> > $dbh =
>>> > DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=
>>> > 0.5","username","password");
>>> >
>>> > #does work and timesout after 1 sec
>>> > $dbh =
>>> >
>>> DBI->connect("DBI:mysql:db_name:db_host:3306;mysql_connect_t imeout=1","username","password");
>>>
>>> >
>>> >
>>> > Thanks
>>> >
>>> > Duncan
>>> >
>>>
>>>
>>> --
>>> Alexander Foken
>>> mailto:alexander@foken.de http://www.foken.de/alexander/
>>>
>>>
>>
>
>


--
Stephen Carville
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602