Error: handles can"t be shared between threads

Hi,

I am using mod_perl 2.0.0 under Windows 2000, with perl 5.8.6, Apache
2.0.54, and Apache::DBI version 0.96.

I want to create a module that uses DBI.pm for connecting to the MySQL
database, then using that module in all other modules that might need a
connection.

Here is the module I have tried:

package Site::MySQL;

use strict;
use DBI ();

sub dbh {
return DBI->connect("DBI:mysql:database=test;host=localhost", "root", undef,
{RaiseError => 0, PrintError => 1, AutoCommit => 1});
}
1;

I get the $dbh in other modules using:
my $dbh = Site::MySQL::dbh();

If I run the programs in command line, they work fine, but if I run them
using mod_perl, it gives an error in the log file:

[Thu May 19 20:36:01 2005] [error] DBD::mysql::db prepare failed: handle 2
is owned by thread 225321c not current thread 17cde94 (handles can't be
shared between threads and your driver may need a CLONE method added) at
e:/web/presa3/modules/Presa/Categories.pm line 34.\n

I have tried to use and not to use Apache::DBI but with the same results.

I don't have any idea where could be the problem. Is the module that uses
DBI bad? Isn't a good idea to create a functional module instead of an OOP
one?
Or... what can I try to make them work?

Thank you.

Teddy

Re: Error: handles can"t be shared between threads

On Thursday 19 May 2005 1:47 pm, Octavian Rasnita wrote:
> [Thu May 19 20:36:01 2005] [error] DBD::mysql::db prepare failed: handle 2
> is owned by thread 225321c not current thread 17cde94 (handles can't be
> shared between threads and your driver may need a CLONE method added) at
> e:/web/presa3/modules/Presa/Categories.pm line 34.\n

Are you opening database handles during server startup, in a startup.pl or a
BEGIN block inside a module called from startup.pl?

- Perrin

Re: Error: handles can"t be shared between threads

From: "Perrin Harkins"

> On Thursday 19 May 2005 1:47 pm, Octavian Rasnita wrote:
> > [Thu May 19 20:36:01 2005] [error] DBD::mysql::db prepare failed: handle
2
> > is owned by thread 225321c not current thread 17cde94 (handles can't be
> > shared between threads and your driver may need a CLONE method added) at
> > e:/web/presa3/modules/Presa/Categories.pm line 34.\n
>
> Are you opening database handles during server startup, in a startup.pl or
a
> BEGIN block inside a module called from startup.pl?
>
> - Perrin

I have put the following lines in a startup.pl file which is included for
all virtualhosts (but I have a single virtual host):

use Apache::DBI ();
Apache::DBI->connect_on_init('DBI:mysql:database=test', 'root', undef,
{PrintError => 1, RaiseError => 0, AutoCommit => 1});

Then I have called the modules that use DBI in a second "preload.pl" program
which is included just in my virtual host, using:

use Site::Module1 ();
use Site::Module2 ();
....

I have noticed that if I comment out the lines from the second file, the
site works fine, but I am not sure if I won't have problems after a certain
time.
It seems that I am not allowed to launch the modules at server startup.

Thanks.

Teddy

Re: Error: handles can"t be shared between threads

On Fri, 2005-05-20 at 04:32 +0300, Octavian Rasnita wrote:
> I have put the following lines in a startup.pl file which is included for
> all virtualhosts (but I have a single virtual host):
>
> use Apache::DBI ();
> Apache::DBI->connect_on_init('DBI:mysql:database=test', 'root', undef,
> {PrintError => 1, RaiseError => 0, AutoCommit => 1});
>
> Then I have called the modules that use DBI in a second "preload.pl" program
> which is included just in my virtual host, using:
>
> use Site::Module1 ();
> use Site::Module2 ();

What's going on in there? Are you using Class::DBI, by any chance?

> I have noticed that if I comment out the lines from the second file, the
> site works fine, but I am not sure if I won't have problems after a certain
> time.
> It seems that I am not allowed to launch the modules at server startup.

It just means that those modules open database handles when you load
them and cache those handles in globals or closures. That will always
cause problems. You need to make them postpone opening the handles, or
stop keeping cached copies of the handles.

- Perrin

Re: Error: handles can"t be shared between threads

Hi,

From: "Perrin Harkins"
> > I have put the following lines in a startup.pl file which is included
for
> > all virtualhosts (but I have a single virtual host):
> >
> > use Apache::DBI ();
> > Apache::DBI->connect_on_init('DBI:mysql:database=test', 'root', undef,
> > {PrintError => 1, RaiseError => 0, AutoCommit => 1});
> >
> > Then I have called the modules that use DBI in a second "preload.pl"
program
> > which is included just in my virtual host, using:
> >
> > use Site::Module1 ();
> > use Site::Module2 ();
>
> What's going on in there? Are you using Class::DBI, by any chance?
>

No, I have never used Class::DBI in any module.

> > I have noticed that if I comment out the lines from the second file, the
> > site works fine, but I am not sure if I won't have problems after a
certain
> > time.
> > It seems that I am not allowed to launch the modules at server startup.
>
> It just means that those modules open database handles when you load
> them and cache those handles in globals or closures. That will always
> cause problems. You need to make them postpone opening the handles, or
> stop keeping cached copies of the handles.
>

Ok, it seems that I am closer to find the problem.
I could postpone opening them by not loading them at server startup, but I
think a better idea would be to stop keeping cached copies of the handles.
However, I have no idea how to do that.

In all the modules I have put something like:

package Site::Module1;

use strict;
use Site::MySQL (); #The module that connects to MySQL
my $dbh = Site::MySQL::dbh();

#Then the subroutines follow... and they use the $dbh.

What should I do?

Put "use Site::MySQL" and "my $dbh = Site::MySQL::dbh()" inside of
subroutines?
Close the database connection after each request using $dbh->disconnect() or
$dbh->close() or other way?

Thank you.

Teddy

Re: Error: handles can"t be shared between threads

On Friday 20 May 2005 4:27 pm, Octavian Rasnita wrote:
> In all the modules I have put something like:
>
> package Site::Module1;
>
> use strict;
> use Site::MySQL (); #The module that connects to MySQL
> my $dbh = Site::MySQL::dbh();

You are creating a closure when you use that $dbh in your subs. This is very
bad, for multiple reasons. In addition to the problem you already see, this
will prevent Apache::DBI from reconnecting if your MySQL connection times
out. It will also break the safety rollback handler which you would need if
you ever decide to use transactions.

> What should I do?
>
> Put "use Site::MySQL" and "my $dbh = Site::MySQL::dbh()" inside of
> subroutines?

You only need the "use" once at the top, but you should put the call to get
the dbh at the beginning of each sub. Apache::DBI will intercept the connect
calls and give you back a cached handle.

- Perrin