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

Dear monks,
I am attempting to create a multi-threaded script, which will perform operations on mysql_database. Unfortunately I am having a problem with sharing the mysql connection object. I found DBIx::Threaded modules, which supposedly does exactly what I need (establish pool of connections, and with each database operation draw from the pool), but I can't get it working, more precisely, if I use it as I would use regular DBI module, I get error 'Can't call method "dbix_threaded_start" without a package or object reference', after the prepare line. Is there any special wayt to return the statement handle object. 
#!/usr/bin/perl
use Net::DNS;
use DBI;
use 5.10.0;
use threads;
use threads::shared;
use Date::Calc qw(Delta_Days);
use Net::DNS::Sendmail;
use DBIx::Threaded;
#use diagnostics;

$| = 1;

my $thread_no = shift;
my $host = "localhost";
my $database = "test";

my $user = "root";
my $password = "";

my (@date) = (localtime)[5,4,3];
$date[1]++;
$date[0] += 1900;


my $dbh_t = DBIx::Threaded->connect("DBI:mysql:database=$database;host
+=$host", $user, $password);
DBIx::Threaded->dbix_threaded_create_pool(10);

my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host", $use
+r, $password);

my $sth = $dbh->prepare("SELECT id,domain,DATE(created) FROM domain_li
+st");
$sth->execute();

my $counter = 0;
 
while (my @row = $sth->fetchrow_array())
{
    my $id = $row[0];
    my $domain = $row[1];
    my $created = $row[2];
    next unless $row[0..3];
    my $thr = threads->create(mx_lookup,$id,$domain,$created) unless $
+counter >= $thread_no;
    $thr->detach && $counter++ if $thr;
}
$dbh_t->close_all();
$dbh->disconnect();

sub mx_lookup {
    my $id = shift;
    my $domain = shift;
    my $date_c = shift;
    
    my @date_c = split /-/, $date_c;
    my $delta = Delta_Days(@date_c,@date);
    
if ($delta <= 30)
{
my $sth = $dbh_t->dbix_threaded_start_prepare("select max(id),domain_i
+d,changed_from,changed_to,date from mx_history where domain_id=$id");
#this is where the script fails
$sth->dbix_threaded_start();
        
my @row = $sth->fetchrow_array();
my $last_mx = $row[3];
$sth->finish();
}
}

[download]
(I cut down the code, so only the relevant pieces are included... Thanks for any help clone4

Replies are listed 'Best First'.
Re: Threads and multiple DBI connections
by BrowserUk (Patriarch) on Nov 01, 2010 at 16:30 UTC 
    my $dbh_t = DBIx::Threaded->connect("DBI:mysql:database=$database;host
+=$host", $
+user, $password);
DBIx::Threaded->dbix_threaded_create_pool(10);

my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host", $use
+r, $passwo
+rd);

my $sth = $dbh->prepare("SELECT id,domain,DATE(created) FROM domain_li
+st");
$sth->execute();

    [download]

    I've never used DBIx::Threaded, so this is guesswork. But from a quick squint at the synosis, I suspect that you shouldn't be creating and using both a threaded handle and a non-threaded handle.

    Try getting rid of the second connect, and use $dbh_t wherever you would normally use $dbh (Or just rename the former to the latter>)

    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]
[d/l]
[select]
      Thanks for the reply, but I've already tried that. The problem is that $dbh->dbix_threaded_start_prepare won't return the statement object handle, so $sth ends up being empty. The author in the doc doesn't say how to obtain the statement object handle, and this is the normal way of doing it...

      Any other ideas welcomed!
[reply]
        The problem is that $dbh->dbix_threaded_start_prepare won't return the statement object handle, so $sth ends up being empty.

        I agree that the documentation is very confusing.

        The $dbh->dbix_threaded_start_* calls don't return handles, they return immediately with IDs.

        You then have call $rc = $h->dbix_threaded_wait( $id ); to wait for the thing you started to complete.

        So, to asynchonously prepare a statement, you'd do (something like; untested): 

        my $prepID = $dbh->dbix_threaded_start_prepare( $sql, ... );
...
## you can do other stuff here
...
my $sth = $dbh->dbix_threaded_wait($id);

        [download]

        But you don't need to use the asynchronous calls for everything. You could just use the normal dbi calls for some things.

        Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
        "Science is about questioning the status quo. Questioning authority".
        In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom