kudra has asked for the wisdom of the Perl Monks concerning the following question:

I've got a question which I hope someone can answer. The problem has been solved, but I'm curious about why the solution was neccessary.

Note: All examples are pseudocode.

I have been working on an application which runs in the fast CGI environment. Much of the functionality has been put in modules. There are a number of variables which need to be shared by the modules in a single program run, but which must be distinct each time the application is run. One of these variables is a DBI handler, which I was passing to the constructors of two different modules:

my $one = new One(dbh => $dbh, ...); my $two = new Two(dbh => $dbh, ...);
Module One parses some files, begins a transaction, and calls routines in module Two to insert some data. The commit is done in module One. The handle is disconnected in the destruction of Two.

This did not work. It was almost as if One and Two had different connections, because when I added a commit to Two, the data was committed. Yet I did not get any warnings about the handle in Two being destroyed instead of disconnected. In short, it didn't act like a new handler, nor did it act like an old one.

I tried again to make sure that One and Two got the same handler. I changed the code to:

my $one = new One(dbh => \$dbh, ...); my $two = new Two(dbh => \$dbh, ...);
This worked.

My question is: why did I need to explicitly pass this by reference?

Replies are listed 'Best First'.
Re: Why was it neccessary to pass a DBI handler by reference?
by tilly (Archbishop) on Jan 28, 2004 at 17:02 UTC
    What you have described shouldn't happen. Therefore either there is a bug that I don't know about, or your description is incomplete in some important particular.

    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.

Re: Why was it neccessary to pass a DBI handler by reference?
by BrowserUk (Patriarch) on Jan 28, 2004 at 17:29 UTC

    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....

    Examine what is said, not who speaks.
    "Efficiency is intelligent laziness." -David Dunham
    "Think for yourself!" - Abigail
    Timing (and a little luck) are everything!

      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.
Re: Why was it neccessary to pass a DBI handler by reference?
by mpeppler (Vicar) on Jan 28, 2004 at 16:38 UTC
    It almost feels as if the reference count got screwed up, and caused the handle not to get destroyed as it should (although I don't really see how that is possible). Did you try to run this with DBI->trace(3) to see what was going on?


      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. :)

Re: Why was it neccessary to pass a DBI handler by reference?
by Trimbach (Curate) on Jan 28, 2004 at 18:04 UTC
    Is it possible that that code that you passed $dbh into wasn't accidentally de-referencing $dbh somewhere? If $dbh was being inappropriately dereferenced in the One and Two packages (for example) then that would explain why passing a reference to a reference would fix it. It may not have anything to do with DBI at all.

    Gary Blackburn
    Trained Killer

      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.
Re: Why was it neccessary to pass a DBI handler by reference?
by Rhandom (Curate) on Jan 28, 2004 at 19:55 UTC
    Your references are fine - the Database is the problem. Using Oracle we had a similar issue. We kind of narrowed it down to the following:
    Create a $dbh.
    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.
    Alternate possibility goes something like the parent signals that he is done and database closes the door on the child. Child tries to use the connection but the door is already closed - so the child closes its own $dbh.

    There is lot of magic in DBI. There is a lot going on in the specific driver for your database. I'd be very careful about forking and trying to make you're own persistent connections or sharing the $dbh around. In the end we found a way to disassociate the file descriptors so that the closing signals wouldn't be sent. Kind of a hack fix really.
    my @a=qw(random brilliant braindead); print $a[rand(@a)];
      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)];
Re: Why was it neccessary to pass a DBI handler by reference?
by derby (Abbot) on Jan 28, 2004 at 17:13 UTC
    Just changing to \$dbh will not work without other changes. So the big question is are you sure you just changed the new call and the new method (to properly de-reference the reference to $dbh)? Were there possibly any other changes?

      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.
Re: Why was it neccessary to pass a DBI handler by reference?
by neuroball (Pilgrim) on Jan 28, 2004 at 15:54 UTC

    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.


      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(); 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;
      And 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;

      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...


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


        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";

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


        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)
Re: Why was it neccessary to pass a DBI handler by reference?
by Anonymous Monk on Jan 30, 2004 at 18:40 UTC

    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();

    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.