[Gmod-help] using gmod_bulk_load_gff3.pl in a multiuser environment

[Scott Cain]

Hi Cornel,

You absolutely cannot use the bulk loader by more than one user on the
same database at a time.  It will fail horribly, and not just because
of temporary tables/files: the bulk loader also gets the values of
table primary keys and increments them inside the loader, but not the
database, while it is running.  If another process comes along and
tries to do the same thing, it will reuse primary keys and then fail
to load the data because of that.  It is a fairly fundamental design
issue to make the loader faster (if it had to update the sequences in
the database it would really slow it down a lot).

It may be possible to make a much slower loader that is multiuser
friendly, but if I were doing that, I would design it from the ground
up to use either Class::DBI, because that has been done before, but
isn't used much (at all?) any more, or DBIx::Class, because it is a
more modern ORM system.  In the meantime, the best you can do is force
the processes into a queue.

Scott


On Mon, Aug 31, 2009 at 10:25 AM, Ghiban, Cornel<ghiban at cshl.edu> wrote:
> Hi all,
>
> I'm building a web application that uses this script to load various analyses results into
> a chado database. We often notice the loading fails and it wasn't difficult to see why. Since gmod_bulk_load_gff3.pl takes a few good seconds to execute, a second or third instance of this script will fail to work properly, if the first run is still running.
>
> This is because of the temporary tables.
>
> The first script will create a series of temporary tables, meanwhile a 2nd script tries to do the same, but it fails - it may still continue to run, I'm not sure, but then the 1st script finishes (drops the temporary tables) and the 2nd script can't do it's job.
>
> Maybe the temporary objects' name should contain a random string (unique per run), that would prevent name clashes. Could this be easily done? Or is there another solution?
>
> Also in the web environment, Bio::GMOD::DB::Adapter->file_handles fails to create the temp files, unless I change the TEMPLATE to "/tmp/chado-$key-XXXX".
>
> Thanks,
> Cornel
>
>



-- 
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     216-392-3087
Ontario Institute for Cancer Research