mysql server dying

Here is the scenario:

2 machines, machine 1 is running a script
and machine is the db server.

the script is doing massive inserts/updates (in a
transaction) into the db when all of a sudden the
db server dies (it shut off somehow). the db being
powered off caused the script to hang. it hung for
a while and i eventually had to kill -9 it. what im
wondering is, is there some sort of timeout value i
can play with? what i would like to do is set a timeout,
and if the timeout is reached at any point then DBI
should issue some sort of error msg that i can then
parse inside the $Dbh->ErrorHandler sub. I know there
is a timeout value for connecting to a db but that
doesnt help me in this case.

Thanks

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: mysql server dying

--=-quud5UzGBZgQ6zEUnqTQ
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Thu, 2004-04-15 at 13:22, mayuran wrote:
> Here is the scenario:
>=20
> 2 machines, machine 1 is running a script
> and machine is the db server.
>=20
> the script is doing massive inserts/updates (in a
> transaction) into the db when all of a sudden the
> db server dies (it shut off somehow). the db being
> powered off caused the script to hang. it hung for
> a while and i eventually had to kill -9 it. what im
> wondering is, is there some sort of timeout value i
> can play with? what i would like to do is set a timeout,
> and if the timeout is reached at any point then DBI
> should issue some sort of error msg that i can then
> parse inside the $Dbh->ErrorHandler sub. I know there
> is a timeout value for connecting to a db but that
> doesnt help me in this case.

You could set an alarm (from 'perldoc -f alarm'):

eval {
local $SIG{ALRM} =3D sub { die "alarm\n" }; # NB: \n required
alarm $timeout;
do_sql_query_that_might_be_unplugged();
alarm 0;
};
if ($@) {
die unless $@ eq "alarm\n"; # propagate unexpected errors
# timed out
} else {
# didn't
}


--=20

|- Garth Webb -|
|- garth@zappos.com -|

--=-quud5UzGBZgQ6zEUnqTQ
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQBAfvzMZJKKSGXZQTcRAvZrAKCxyAHD8E4dqXIiUjlMbEWUNP6FrwCg iDab
35fTp5LCU9gve8pIDiZ6WJk=
=KHFJ
-----END PGP SIGNATURE-----

--=-quud5UzGBZgQ6zEUnqTQ--

Re: mysql server dying

--=-quud5UzGBZgQ6zEUnqTQ
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Thu, 2004-04-15 at 13:22, mayuran wrote:
> Here is the scenario:
>=20
> 2 machines, machine 1 is running a script
> and machine is the db server.
>=20
> the script is doing massive inserts/updates (in a
> transaction) into the db when all of a sudden the
> db server dies (it shut off somehow). the db being
> powered off caused the script to hang. it hung for
> a while and i eventually had to kill -9 it. what im
> wondering is, is there some sort of timeout value i
> can play with? what i would like to do is set a timeout,
> and if the timeout is reached at any point then DBI
> should issue some sort of error msg that i can then
> parse inside the $Dbh->ErrorHandler sub. I know there
> is a timeout value for connecting to a db but that
> doesnt help me in this case.

You could set an alarm (from 'perldoc -f alarm'):

eval {
local $SIG{ALRM} =3D sub { die "alarm\n" }; # NB: \n required
alarm $timeout;
do_sql_query_that_might_be_unplugged();
alarm 0;
};
if ($@) {
die unless $@ eq "alarm\n"; # propagate unexpected errors
# timed out
} else {
# didn't
}


--=20

|- Garth Webb -|
|- garth@zappos.com -|

--=-quud5UzGBZgQ6zEUnqTQ
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQBAfvzMZJKKSGXZQTcRAvZrAKCxyAHD8E4dqXIiUjlMbEWUNP6FrwCg iDab
35fTp5LCU9gve8pIDiZ6WJk=
=KHFJ
-----END PGP SIGNATURE-----

--=-quud5UzGBZgQ6zEUnqTQ--

Re: mysql server dying

On Thu, 15 Apr 2004, mayuran wrote:

> Here is the scenario:
>
> 2 machines, machine 1 is running a script
> and machine is the db server.
>
> the script is doing massive inserts/updates (in a
> transaction) into the db when all of a sudden the
> db server dies (it shut off somehow).

What is causing the db to die? Does it segfault, exit, hang?
> the db being
> powered off caused the script to hang. it hung for

Is there something that is causing your script to block on the socket?
It could still be expecting to read/send data over the socket, and if you
did not get a FIN/RST the script that the system is on will think that it
is still connected.

Try turning on logging server-side, and see what is going on with the db
server.

> a while and i eventually had to kill -9 it. what im

And a SIGTERM did not cause the process to exit?

> wondering is, is there some sort of timeout value i
> can play with? what i would like to do is set a timeout,

Not right now. But your real problem is that your server is going away.
Fix that and you should not have to worry about the timeouts.


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: mysql server dying

On Thu, 15 Apr 2004, mayuran wrote:

> Here is the scenario:
>
> 2 machines, machine 1 is running a script
> and machine is the db server.
>
> the script is doing massive inserts/updates (in a
> transaction) into the db when all of a sudden the
> db server dies (it shut off somehow).

What is causing the db to die? Does it segfault, exit, hang?
> the db being
> powered off caused the script to hang. it hung for

Is there something that is causing your script to block on the socket?
It could still be expecting to read/send data over the socket, and if you
did not get a FIN/RST the script that the system is on will think that it
is still connected.

Try turning on logging server-side, and see what is going on with the db
server.

> a while and i eventually had to kill -9 it. what im

And a SIGTERM did not cause the process to exit?

> wondering is, is there some sort of timeout value i
> can play with? what i would like to do is set a timeout,

Not right now. But your real problem is that your server is going away.
Fix that and you should not have to worry about the timeouts.


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: mysql server dying

On Thu, 15 Apr 2004, Garth Webb wrote:

> On Thu, 2004-04-15 at 13:22, mayuran wrote:
> > a while and i eventually had to kill -9 it. what im
> > wondering is, is there some sort of timeout value i
> > can play with? what i would like to do is set a timeout,
> > and if the timeout is reached at any point then DBI
> > should issue some sort of error msg that i can then
> > parse inside the $Dbh->ErrorHandler sub. I know there
> > is a timeout value for connecting to a db but that
> > doesnt help me in this case.
>
> >
> You could set an alarm (from 'perldoc -f alarm'):

I wonder if a SIGALRM would help if it takes a SIGKILL to term the
process.


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: mysql server dying

On Thu, 15 Apr 2004, Garth Webb wrote:

> On Thu, 2004-04-15 at 13:22, mayuran wrote:
> > a while and i eventually had to kill -9 it. what im
> > wondering is, is there some sort of timeout value i
> > can play with? what i would like to do is set a timeout,
> > and if the timeout is reached at any point then DBI
> > should issue some sort of error msg that i can then
> > parse inside the $Dbh->ErrorHandler sub. I know there
> > is a timeout value for connecting to a db but that
> > doesnt help me in this case.
>
> >
> You could set an alarm (from 'perldoc -f alarm'):

I wonder if a SIGALRM would help if it takes a SIGKILL to term the
process.


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: mysql server dying

Rudy Lippan wrote:
> On Thu, 15 Apr 2004, mayuran wrote:
>> all of a sudden the db server dies (it shut off somehow).
> What is causing the db to die? Does it segfault, exit, hang?

It sounds like the server just powered down suddenly. Is that right,
mayuran? The whole machine died, not just the mysqld process?

>> the db being powered off caused the script to hang.
> Is there something that is causing your script to block on the socket?
> It could still be expecting to read/send data over the socket, and if you
> did not get a FIN/RST the script that the system is on will think that it
> is still connected.

Yes, it sounds like the TCP session is hanging because the remote host
just disappeared from the net without warning.

> Try turning on logging server-side, and see what is going on with the db
> server.
>> a while and i eventually had to kill -9 it.
> And a SIGTERM did not cause the process to exit?

IIRC, that's because the process is still waiting to close the TCP
connection.

>> is there some sort of timeout value i
>> can play with? what i would like to do is set a timeout,
> Not right now. But your real problem is that your server is going away.
> Fix that and you should not have to worry about the timeouts.

The TCP keepalive timeout can be varied but that affects everything on
the machine AFAIK so is not something to be done lightly. I suppose
it's correct to say that the problem will be 'solved' if the server
doesn't go away, but servers going away without warning is a fact of
life in distributed systems and IMHO all applications should protect
themselves against it at the lowest level they can (i.e. detect it in
the application's comms modules).

Cheers, Dave


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: mysql server dying

Rudy Lippan wrote:
> On Thu, 15 Apr 2004, mayuran wrote:
>> all of a sudden the db server dies (it shut off somehow).
> What is causing the db to die? Does it segfault, exit, hang?

It sounds like the server just powered down suddenly. Is that right,
mayuran? The whole machine died, not just the mysqld process?

>> the db being powered off caused the script to hang.
> Is there something that is causing your script to block on the socket?
> It could still be expecting to read/send data over the socket, and if you
> did not get a FIN/RST the script that the system is on will think that it
> is still connected.

Yes, it sounds like the TCP session is hanging because the remote host
just disappeared from the net without warning.

> Try turning on logging server-side, and see what is going on with the db
> server.
>> a while and i eventually had to kill -9 it.
> And a SIGTERM did not cause the process to exit?

IIRC, that's because the process is still waiting to close the TCP
connection.

>> is there some sort of timeout value i
>> can play with? what i would like to do is set a timeout,
> Not right now. But your real problem is that your server is going away.
> Fix that and you should not have to worry about the timeouts.

The TCP keepalive timeout can be varied but that affects everything on
the machine AFAIK so is not something to be done lightly. I suppose
it's correct to say that the problem will be 'solved' if the server
doesn't go away, but servers going away without warning is a fact of
life in distributed systems and IMHO all applications should protect
themselves against it at the lowest level they can (i.e. detect it in
the application's comms modules).

Cheers, Dave


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org