reconnect to database was: SIG{"PIPE"}

As I mentioned earlier on some crazy hosting server my script is terminated
by SIG{PIPE}. Thanks for all who help me to find the reason, but now I need
to resolve found reason.
On the hosting a MySQL server is running but is limited to keep connection
max to 10 seconds. Idea of my script is this:

1) connect to database server ($dbh is "our" variable)
2) define sql commands e.g.:
our $findit=$dbh->prepare("select max(myid) from table";
our $storeit=$dbh->prepare("insert into table set myid=?, text=?");
3) download some web page using LWP module
4) parse data
5) store data to tables e.g.
$storeit->execute($myid,$sometext);

Points 3 and 4 can take more then 10 seconds and in this case MySQL server
disconnect me and at point 5 I will be killed by SIGPIPE. So I thinked up a
routine to connect to MySQL server again if needed, but this not work and I
still get SIGPIPE.
I define
our $testdb=$dbh->prepare("select 1");
and sub recon()

sub recon
{
my $ok=1;
$testdb->execute() or $ok=0;
unless($ok)
{
$dbh=DBI->connect("DBI:mysql:$db:$dbhost",$dbuser,$dbpasswor d);
}
}

Have somebody some idea how to resolve it? I not need to do something like
this
connect db
prepare sql command
execute sql
disconnect
do some non-db operations
connect to db
....

I use about 30 different sql commands and I thought that is good idea to
define all command in one sub and use execute() commands in script only.

--

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)

Re: reconnect to database was: SIG{"PIPE"}

"Petr Vileta" wrote in
news:febkd1$1n9f$1@ns.felk.cvut.cz:

> On the hosting a MySQL server is running but is limited to keep
> connection max to 10 seconds. Idea of my script is this:
>
> 1) connect to database server ($dbh is "our" variable)
> 2) define sql commands e.g.:
> our $findit=$dbh->prepare("select max(myid) from table";
> our $storeit=$dbh->prepare("insert into table set myid=?,
> text=?");

I am guessing you are using integer primary keys. In general, this is a
bad idea. (E.g., this method will allow you to insert duplicates that
differ only in the meaningless integer key.)

It is even a worse idea to try to generate to integer ids yourself using
your method.

Use a meaningful primary key. I am not going to discuss this any further
as this is not a SQL group.


> 3) download some web page using LWP module
> 4) parse data
> 5) store data to tables e.g.
> $storeit->execute($myid,$sometext);

It seems obvious to me that the script should wait until it actually
needs to access the database to connect to the database.

....

> I define
> our $testdb=$dbh->prepare("select 1");
> and sub recon()
>
> sub recon
> {
> my $ok=1;
> $testdb->execute() or $ok=0;
> unless($ok)
> {
> $dbh=DBI->connect("DBI:mysql:$db:$dbhost",$dbuser,$dbpasswor d);
> }
> }
>
> Have somebody some idea how to resolve it? I not need to do something
> like this
> connect db
> prepare sql command
> execute sql
> disconnect
> do some non-db operations
> connect to db
> ...
>
> I use about 30 different sql commands and I thought that is good idea
> to define all command in one sub and use execute() commands in script
> only.

I smell premature optimization. I would go ahead and attempt to
communicate with the database and if that fails just attempt to re-
connect. So:

* Download web page
* Parse web page
* Connect to database and store data

Sinan


--
A. Sinan Unur <1usa@llenroc.ude.invalid>
(remove .invalid and reverse each component for email address)
clpmisc guidelines: