Minion Worker & DBI

[Yves]

I'm trying to implement a Minon worker that needs access to a database via DBI.

Here is the code that I use :

use Mojolicious::Lite;

plugin Minion => { SQLite => 'sqlite:test.db' };

helper dbh => sub {
      my $dbh = DBI->connect("dbi:Oracle:somedb", 'someuser', 'somepasswd');
      return $dbh;
};

app->minion->add_task( mytask => \&mytask );

sub mytest {
   
    my ($job, $arg) = @_;
       
    my $dbh = $job->app->dbh;
    my ($time) = $dbh->selectrow_array('select sysdate from dual');    
    $job->finish('documentation generation done');
       
}
app->start;

The problem that I have, is that the connection to the database is closed/reopened on every task execution, which is very slow, and not the behavior that I expect.

Using the same dbh helper on a Mojolicious app (not within minon task) works fine and opens one connection per worker.

I've already tried various setups with DBI's InactiveDestroy parameter or using DBIx::Connector but no success so far.

[Dan Book]

Your $dbh is only returned from the helper and not stored anywhere, so it disconnects as soon as it goes out of scope. Try declaring `my $dbh` in the global scope or using the `state` feature (roughly equivalent). A Mojo::SQLite object would be preferable to store over the dbh itself. See an example here: https://metacpan.org/source/DBOOK/Mojo-SQLite-0.021/examples/blog/lib/Blog.pm

--
You received this message because you are subscribed to the Google Groups "Mojolicious" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mojolicious...@googlegroups.com.
To post to this group, send email to mojol...@googlegroups.com.
Visit this group at https://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.

[Yves]

I've already tried using the state feature, and the result is the same unfortunately, the connection is lost after every task execution.

helper dbh => sub { state $dbh = DBI->connect("dbi:Oracle:somedb", 'someuser', 'somepasswd') };

I used very similar code on my Mojo web app and it works as expected, but this seems to be related to the fact that it's running as a Minion workers :

./my_app.pl minion worker -j 2

I've also tried the DBD::mysql driver, but still no success with this :

#!/bin/env perl

use Mojolicious::Lite;

plugin Minion => { SQLite => 'sqlite:test.db' };

helper dbh => sub { state $dbh = DBI->connect("DBI:mysql:database=test;host=localhost", "test", "test") };

 
app->minion->add_task( mytask => \&mytask );

sub mytask {    
    my ($job, $arg) = @_;

    my $dbh = $job->app->dbh;

    $dbh->selectrow_array('SELECT NOW()');

    $job->finish('documentation generation done');      
}
app->start;

Yves

[Joel Berger]

Minion tasks are performed in forks to protect the integrity of the worker process. Once the task is forked (or indeed after any fork) there must be a new connection to the database, this is the only possible way to do anything sane. 

[Dan Book]

This would be handled by storing a Mojo::SQLite object instead of the dbh, or a DBIx::Connector object as you mentioned before.

--

[Yves]

Ok this makes it much clearer.

For this particular use, I have a lots of small tasks which require DB connection, but having a reconnection overhead for each task is just not acceptable.

If it's the only possible way maybe Minon is not the right tool.

I'm a bit disapointed because except for this problem, it would be an excellent solution.

Thanks

[Yves]

According to the DBIx::Connector's documentation, if the process has been forked, it will always generate a new connection which is consistent with what I get when I tested it.

I understand that connections cannot be shared between forked processes, but I thought there would be a solution to have a pool of connections and then having the forked processes use them (one at a time). Is this even possible ?

Maybe it's not Mojo/Minon specific, but I was curious if anybody else had a good (or at least better) solution.