I'd initially guess that the description is incomplete because bona fide Perl bugs are fairly rare. And I'd look in particular at how you clear variables since what you are describing could easily be explained by something, somewhere, holding on to a reference to $dbh so that DESTROY didn't get called when you expected it to.

I agree that it shouldn't happen.

I was mostly curious as to whether anyone had observed something like this in DBI before. That doesn't seem to be the case, so I agree the bug is most likely in my code.

Therefore, I will take the advice of tilly and Abigail and put my curiosity on hold until I have time to make a test case to demonstrate the problem.

This is an off-the-wall guess. Have you, or could you, re-try the original code outside of the fast CGI environment?

Knowing nothing about fast CGI, I read CGI::Fast POD (which may or may not be related) and saw that it is a "persistant environment" that

Each time there's a new request, CGI::Fast returns a CGI object to your loop. The rest of the time your script waits in the call to new(). When the server requests that your script be terminated, new() will return undef. You can of course exit earlier if you choose. A new version of the script will be respawned to take its place...

The possibility struck me that this is involved somehow. It seems possible that the "spawning" process might be duplicating the parameters and causing the loss of magic somewhere. And that by passing a reference, it means that it is the reference that is duplicated, pointing to the existing blessed $dbh, and so the magic is retained?

Like I say, pure speculation....

Thank you for your suggestion. I did, however, run the application in an ordinary CGI environment when I noticed the problem. This did not lead to any change.

Michael

I did, but the results weren't very useful to me because 1) I have never looked at the output much before and don't know how to read it; and 2) the code is part of a project, and so the tracelog that was generated was 690 lines long.

When the deadline is less pressing I hope to have time to product a test case that demonstrates the problem, but I know I won't be given time for it now that I've come up with a solution (the project is quite urgent). I'm just curious now! As I stated, it's not a real problem anymore, but I would like to know why. :)

I don't think that happened, because if I somehow managed to dereference it, the first method I tried to call on it would give an error. I also couldn't have fixed the problem by adding the reference and an extra dereference (which I failed to mention having done), because there would still have been the double dereference.

Create a $dbh.
Fork.
Child disassociates from the parent.
Parent exits.
Child's $dbh hopefully stays alive.
Parent's $dbh goes out of scope.
Parent's $dbh sends close signal to database.
Database sends signal that the connection was closed.
Child's $dbh gets signal that the connection was closed.
Child's $dbh closes.

Interesting, but I'm not forking. By that I mean that I'm neither explicitly forking, nor am I creating the handle outside of the fastcgi run loop--the handle should be created anew for each connection. The handle is then shared between various pieces of code which need to make use of it, within the scope of a single program-run.

The problem is not the database, it's that you're doing VERY BAD THINGS and expecting that it should work.

You can't fork with an open database connection and expect it to work like that with any database, any operating system, any programming language. How would the database know you did that? How would the database driver know you did that?

s/you're/your/ - Bad Grammer! Bad!
my @a=qw(random brilliant braindead); print $a[rand(@a)];

I made the following changes:

• call to $one->new sends \$dbh instead of $dbh.
• One::new expects \$dbh instead of $dbh, and dereferences it.
That's it. I didn't expect it to work, and only tried it because the symptom seemed to be that of a variable passed by value, not by reference. The fact that it did work has left me bewildered, and is the reason I started this thread.

It's one of the good/bad things about perl. You get what you ask for.

When you passed your handler just through the variable, I.e. $dbh, you passed the scalar value of $dbh to the hash.

When you passed your handler through a reference, i.e. \$dbh, you passed the memory position of your handler to the hash

So, in the first case you have a scalar value, which is in no way connected to the handler, and in the second case you have a memory address which gives you access to the handler.

It's the strange thing about references. I always make a note in my mind that my finger, which points at the moon, isn't the moon. Yet, if you followed the direction my finger is pointing at, you would surely find the moon.

Sorry when the finger/moon analogy is a bit fuzzy.

/oliver/

Don't worry, your moon analogy is fine, but I already know the difference between a reference and a scalar value. My question has more to do with the peculiarities of DBI and why it doesn't act like I expect.

See this small example program:

use warnings;
use strict;
use Foo;
use Bar;

my $foo = new Foo;
my $bar = new Bar($foo);

$foo->x("from program");
print "(app) Memory: $foo, X: ".$foo->x()."\n";
$bar->show();
[download]

Foo.pm isn't very interesting:

package Foo;

sub new {
    my $name = shift;
    bless { x => 'from Foo' }, $name;
}

sub x {
    my ($self, $arg) = @_;
    $$self{x} = $arg if ($arg);
    return $$self{x};
}

1;
[download]

And Bar.pm just stores some foo:

package Bar;

sub new {
    my $name = shift;
    my $foo = shift;

    bless { foo => $foo }, $name;
}

sub show {
    my $self = shift;
    my $foo = $$self{foo};
    print "(bar) Memory: $foo, X: ".$foo->x()."\n";
}

1;
[download]

This example is almost exactly like what I wanted to do, except that I'm using a makeshift module instead of DBI.

The results of the memory test will of course depend upon the execution, but both times the memory location is the same. That wouldn't happen after I 'forced a copy' by changing one of the values in $foo if I didn't have a reference.

I hope this clarifies my question.

Okay, now I understand your problem... but I have to say that I am totally out of my depth.

I will put my head into some pods and see what happens...

Nothing to see here but an emberassed monk... go on...

/oliver/

Hans: Not ze problem!

That doesn't make any sense to me. \$dhb is also a scalar value, and a database handler is already a reference.

Abigail

Hm... following the logic of the above (if I am not mistaken): I am (a) wrong, and (b) kudra needed to have a reference to a reference???

Not to be a pain in your neck, but could you explain a bit more what you meant? For example, take the following code:

#!/usr/local/bin/perl

use warnings;
use diagnostics;
use strict;

my $x = "Just another scalar";
my $y = \$x;

print $x."\n";
print $y."\n";
[download]

Am i totally of base with this??? Or am I missing something very basic?

/oliver/

Hans: Not ze problem!

As far as I remember, \$dbh is not a scalar. It is a reference, not a scalar. If you print a reference, it doesn't know what to do so it prints out the address value, it doesn't mean it is a scalar. (Of course I may be wrong but I'm pretty sure I am not)

Following your description, I made an example to try to duplicate the behavior you were describing.

However, it does not do what you say. It seems to work fine.

#!/usr/bin/perl -w
use DBI;
use strict;
package One;

sub new {
    my $class = shift;
    my $self = bless {}, $class;
    my $dbh = shift || return undef;
    $self->{dbh} = $dbh;
    return $self;
}

sub execute {
    my $self = shift;
    my $query = shift;
    my $sth= $self->{dbh}->prepare($query);
    my $rows = $sth->execute or die $DBI::errstr;;
    print "$rows rows affected\n";
}

sub begin_work{
    my $self = shift;
    $self->{dbh}->begin_work;
}

sub commit{
    my $self = shift;
    $self->{dbh}->commit;
}

package Two;

sub new {
    my $class = shift;
    my $self = bless {}, $class;
    my $dbh = shift || return undef;
    $self->{dbh} = $dbh;
    return $self;
}

sub execute {
    my $self = shift;
    my $query = shift;
    my $sth= $self->{dbh}->prepare($query);
    my $rows = $sth->execute or die $DBI::errstr;;
    print "$rows rows affected\n";
}

package main;

my $dbh= DBI->connect("dbi:SQLite:dbname","","",{RaiseError => 1,AutoC
+ommit=>1})
#my $dbh = DBI->connect("DBI:mysql:dbname",
#    'user', 'password', {RaiseError => 1, AutoCommit=>1})
    or die "can't connect\n";

$dbh->do(qq{delete from table1 where t2id =5});
$dbh->do(qq{delete from table2 where t2id =5});
my $query = qq{ insert into table1 values (5,"xxx") };
my $one = new One $dbh or die "can't create One\n";
my $two = new Two $dbh or die "can't create Two\n";
$one->begin_work();     # transaction starts in module One
$one->execute($query);  # transaction continues in module One
$query = qq{insert into table2 values(11,"yyy", 5)};
$two->execute($query);  # transaction continues in module Two
$one->commit();         # transaction ends in module One
$dbh->disconnect();
[download]

I tried this example with two different DBD drivers (MySQL and SQLite) and it worked as I expected. The transaction is processed correctly. Try to add a call to $dbh->rollback() after the first execute, and it will cancel the insertion without problems. It means that the transaction is really split across packages.