in reply to DBI DESTROY problem

Hello skef,

Welcome to the community. Well I do not see something extremely wrong with your code. So I have a few minor suggestions and possible problem solutions.

I was looking online and maybe this is the reason/solution to your problem AutoInactiveDestroy

Taken from the documentation:

Type: boolean, inherited

The "InactiveDestroy" attribute, described above, needs to be explicitly set in the child process after a fork(). This is a problem if the code that performs the fork() is not under your control, perhaps in a third-party module. Use AutoInactiveDestroy to get around this situation.

If set true, the DESTROY method will check the process id of the handle and, if different from the current process id, it will set the InactiveDestroy attribute.

This is the example it's designed to deal with:

my $dbh = DBI->connect(...);
    some_code_that_forks(); # Perhaps without your knowledge
    # Child process dies, destroying the inherited dbh
    $dbh->do(...); # Breaks because parent $dbh is now broken

[download]

Caution: The AutoInactiveDestroy attribute was added in DBI 1.614.

So in case you want to add this feature you need to verify/update your version.

Second Objervation: From Programming with the DBI By Alligator Descartes & Tim Bunce:

What happens if you don't explicitly disconnect? Since DBI handles are references to Perl objects, Perl's own garbage collector will move in and sweep up any object trash you leave lying around. It does that by calling the object's DESTROY method when there are no longer any references to the object held by your script, or when Perl is exiting.

Third Objervation: Personally I do not like Manual Error Checking so I do it automatically by setting $h->{PrintError} = 1; and $h->{RaiseError} = 1;. You can read on the same reference about Automatic Versus Manual Error Checking.

Taken all this in consideration a sample script written in MySQL but with minor modifications should work also for SQL.

I have added functions to check if the DB or the TABLE exist else create them.

I am also using conf.ini file just in case I want to connect to multiple devices so I can use a for loop. Personally I found it extremely useful in case you do not like it just import your data straight.

conf.ini

[MySQL]
user=username
pass=password
host=hostname
port=3306
db=database_name
table=table_name

[download]

MySQL.pl Script

#!/usr/bin/perl
use DBI;
use strict;
use warnings;
use Data::Dumper;
use Config::Simple;

#package LSPDB;

$|=1; #flush every time the program

my $path = 'conf.ini';
my %config = ();
my $checkExist = "";

sub mysql {

    Config::Simple->import_from("".$path."", \%config)
    or die Config::Simple->error();

    my $dbh = DBI->connect("dbi:mysql::".$config{'MySQL.host'}.":".$co
+nfig{'MySQL.port'}."", 
               "".$config{'MySQL.user'}."",
               "".$config{'MySQL.pass'}."",
               { 'PrintError' => 1, 'RaiseError' => 1 , 'AutoInactiveD
+estroy' => 1 } 
    ) or die "Could not connect to ". $config{'MySQL.host'} .": ". $DB
+I::errstr ."\n";

    my $databases = $dbh->do("SHOW DATABASES LIKE '".$config{'MySQL.db
+'}."'") 
    or die "Error: " .dbh->errstr. "\n";

    if ($databases eq 1) {
    printf "Database: ". $config{'MySQL.db'} ." exists not creating: "
+. $config{'MySQL.db'} ."\n";
    }
    else {
    printf "Database: ". $config{'MySQL.db'} ." does not exist creatin
+g: ". $config{'MySQL.db'} ."\n";
    
    $checkExist = $dbh->do("CREATE DATABASE IF NOT EXISTS `".$config{'
+MySQL.db'}."`")
        or die "Could not create the: ".$config{'MySQL.db'}." error: "
+. $dbh->errstr ."\n";

    } # End of else

    $dbh->do("USE ".$config{'MySQL.db'}."")
    or die "Error: " .dbh->errstr. "\n";

    my $tables = $dbh->do("SHOW TABLES FROM `".$config{'MySQL.db'}."` 
+WHERE Tables_in_".$config{'MySQL.db'}." LIKE '".$config{'MySQL.table'
+}."'") or die "Error: " .dbh->errstr. "\n";

    if ($tables eq 1) {
    printf "Table: ".$config{'MySQL.table'}." exists not creating: ".$
+config{'MySQL.table'}."\n";
    }

    else {
    printf "Table: ".$config{'MySQL.table'}." does not exist creating:
+ ".$config{'MySQL.table'}."\n";

    $checkExist = $dbh->prepare("CREATE TABLE IF NOT EXISTS `".$config
+{'MySQL.table'}."` (
                    `id` int(11) NOT NULL AUTO_INCREMENT,
                    `UnixTime` int(11) NOT NULL,
                    `losses` int(11) NOT NULL,
                    PRIMARY KEY (`id`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREM
+ENT=1 ;");

    if (!$checkExist->execute()) {
        die "Error: ". $checkExist->errstr ."\n";
    }

    } # End of else

    my $range = 50;
    my $minimum = 100;

    my $random_number = int(rand($range)) + $minimum;
    my $time = time();

    my $losses = $time . ' ' . $random_number;

    $checkExist = $dbh->prepare("INSERT IGNORE INTO `".$config{'MySQL.
+table'}."` (`UnixTime`, `losses`) VALUES ('".$time."','".$random_numb
+er."') ");

    if (!$checkExist->execute()) {
    die "Error: ". $checkExist->errstr ."\n";
    }

    my $statement = "SELECT * FROM `".$config{'MySQL.table'}."` WHERE 
+1";

    my $hash_ref = $dbh->selectall_hashref($statement, 'id');

=arrayref
    # get array of id and name pairs:
    my $ary_ref = $dbh->selectcol_arrayref($statement, { Columns=>[1,3
+] });
    my %hash = @$ary_ref; # build hash from key-value pairs so $hash{$
+id} => name

    print Dumper(\%hash);
=cut
    
=count rows
    $checkExist = $dbh->prepare("SELECT COUNT(*) FROM `".$config{'MySQ
+L.table'}."`");
    
    if (!$checkExist->execute()) {
    die "Error: ". $checkExist->errstr ."\n";
    }

    my ($crows) = $dbh->selectrow_array($checkExist);
    print $crows . "\n";
=cut

    $checkExist->finish();

    $dbh->disconnect()
    or warn "Error disconnecting: $DBI::errstr\n";

    return $hash_ref;

} # End of mysql sub

my $output_ref = mysql();

print Dumper($output_ref);

__END__

First Round:

Database: Speed does not exist creating: Speed
Table: Data does not exist creating: Data
$VAR1 = {
          '1' => {
                   'UnixTime' => '1413048302',
                   'losses' => '102',
                   'id' => '1'
                 }
        };

Second Round:

$VAR1 = {
          '1' => {
                   'UnixTime' => '1413048302',
                   'losses' => '102',
                   'id' => '1'
                 },
          '2' => {
                   'losses' => '135',
                   'UnixTime' => '1413048306',
                   'id' => '2'
                 }
        };

[download]

Hope this helps and solves your problem.

Seeking for Perl wisdom...on the process of learning...not there...yet!

Replies are listed 'Best First'.
Re^2: DBI DESTROY problem
by skef (Novice) on Oct 11, 2014 at 20:09 UTC

    Thanks for the welcome and reply!

    Unfortunately, InactiveDestroy doesn't fix the problem, and AutoInactiveDestroy seems only to be applicable to situations where the process is forking (and, in any case, doesn't fix the problem).

    The test code I posted was the shortest I could come up with that generated the double error, so it doesn't really capture the use case. My actual code runs in the context of a Catalyst web server, and I'm throwing the exception to get the error message and the location in the code up to the request handler code so that it can be prettied up and sent back to the browser. So while I could give up on that and put a die every call, that handling would end up being much more explicit, not less (especially because of the added code to dig out the last SQL statement).

[reply]

In Section Seekers of Perl Wisdom