[Gmod-gbrowse] Fwd: loading scripts not working

[Asma Riyaz]

---------- Forwarded message ----------
From: Asma Riyaz <***@gmail.com>
Date: Mon, Oct 6, 2014 at 12:55 PM
Subject: loading scripts not working
To: ***@scottcain.net, gmod-***@lists.sourceforge.ne


Hi,

I am trying to load gff3 files for Tribolium using bp_seqfeature_load.pl
which fails with this error:

Transaction aborted because DBD::mysql::st execute failed: Table
'tribolium.locationlist' doesn't exist at
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm line 1571, <GEN0>
line 22208.

-------------------- EXCEPTION --------------------
MSG: should have a primary_id
STACK Bio::DB::SeqFeature::Store::DBI::mysql::_add_SeqFeature
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm:603
STACK Bio::DB::SeqFeature::Store::add_SeqFeature
/usr/share/perl5/Bio/DB/SeqFeature/Store.pm:1525
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree_in_tables
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:726
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:699
STACK Bio::DB::SeqFeature::Store::GFF3Loader::finish_load
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:343
STACK Bio::DB::SeqFeature::Store::Loader::load_fh
/usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:354
STACK Bio::DB::SeqFeature::Store::Loader::load
/usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:243
STACK toplevel bp_seqfeature_load.pl:258

Here is the command I use:
perl bp_seqfeature_load.pl -d tribolium -a DBI::mysql --user <user>
--password <password> <list of gff3 files>


I have also tried using bp_bulk_load_gff.pl to load data:

"my" variable $AUTH masks earlier declaration in same scope at
bp_bulk_load_gff.pl line 656.
"my" variable $db masks earlier declaration in same scope at
bp_bulk_load_gff.pl line 666.
This operation will delete all existing data in database dbi:mysql:test.
Continue? Y

159000 features parsed in 0.08s...
You are loading a Bio::DB::GFF database with GFF3 formatted data.
While this will likely work fine, the Bio::DB::GFF schema does not
always faithfully capture the complexity represented in GFF3 files.
Unless you have a specific reason for using Bio::DB::GFF, we suggest
that you use a Bio::DB::SeqFeature::Store database and its corresponding
loader, bp_seqfeature_load.pl.
Total parse time 13.70sin 0.09s...
Loading feature data and analyzing tables. You may see RDBMS messages
here...
ERROR 1045 (28000) at line 1: Access denied for user 'tomoyasulab'@'localhost'
(using password: YES)
Total load time 0.00s
done...
Analyzing/optimizing tables. You will see database messages...
Optimization time time 0.00s
FAILURE: Please see standard error for details

Here is command I used:
perl bp_bulk_load_gff.pl -d tribolium --user <user> --password <password>
<list of gff3 files>.

In mysql I issued the following commands:

CREATE DATABASE tribolium;
grant select on tribolium.* to ***@localhost;
grant all privileges on tribolium.* to <user>@localhost;

What am I missing and how can I make this work?

Thanks,
Asma

[Asma Riyaz]

I tried -c too while running this file:

perl bp_seqfeature_load.pl -d dbi:mysql:userdata_tribolium -u www-data -p
<password> -c [list of gff3 files]

This time it is an access denied error:

-------------------- EXCEPTION --------------------
MSG: Access denied for user 'www-data'@'localhost' (using password: YES)
STACK Bio::DB::SeqFeature::Store::DBI::mysql::init
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm:217
STACK Bio::DB::SeqFeature::Store::new
/usr/share/perl5/Bio/DB/SeqFeature/Store.pm:388
STACK toplevel bp_seqfeature_load.pl:224

Here is how I created an empty database and granted privileges:
CREATE DATABASE userdata_tribolium;
CREATE USER 'www-data'@'localhost' IDENTIFIED BY <password>;
use userdata_tribolium;
GRANT ALL PRIVILEGES ON `userdata_tribolium.*` TO 'www-data'@'localhost';

Any suggestions?

Thanks
Asma

Hi Asma,
Looking at your `bp_seqfeature_load.pl` command, it seems to be missing
the `-c` (--create) flag. This is required for the very first time you load
a GFF3 file into a database that has not yet been initialized (the schema
has not be created).
For all subsequent loads, you can skip the `-c` parameter, if your plan is
to perform incremental loads.
http://search.cpan.org/~cjfields/BioPerl-1.6.924/scripts/Bio-DB-SeqFeature-Store/bp_seqfeature_load.pl
Hope this helps!
Best,
Vivek
---------- Forwarded message ----------
Date: Mon, Oct 6, 2014 at 12:55 PM
Subject: loading scripts not working
Hi,
I am trying to load gff3 files for Tribolium using bp_seqfeature_load.pl
Transaction aborted because DBD::mysql::st execute failed: Table
'tribolium.locationlist' doesn't exist at
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm line 1571, <GEN0>
line 22208.
-------------------- EXCEPTION --------------------
MSG: should have a primary_id
STACK Bio::DB::SeqFeature::Store::DBI::mysql::_add_SeqFeature
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm:603
STACK Bio::DB::SeqFeature::Store::add_SeqFeature
/usr/share/perl5/Bio/DB/SeqFeature/Store.pm:1525
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree_in_tables
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:726
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:699
STACK Bio::DB::SeqFeature::Store::GFF3Loader::finish_load
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:343
STACK Bio::DB::SeqFeature::Store::Loader::load_fh
/usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:354
STACK Bio::DB::SeqFeature::Store::Loader::load
/usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:243
STACK toplevel bp_seqfeature_load.pl:258
perl bp_seqfeature_load.pl -d tribolium -a DBI::mysql --user <user>
--password <password> <list of gff3 files>
"my" variable $AUTH masks earlier declaration in same scope at
bp_bulk_load_gff.pl line 656.
"my" variable $db masks earlier declaration in same scope at
bp_bulk_load_gff.pl line 666.
This operation will delete all existing data in database dbi:mysql:test.
Continue? Y
159000 features parsed in 0.08s...
You are loading a Bio::DB::GFF database with GFF3 formatted data.
While this will likely work fine, the Bio::DB::GFF schema does not
always faithfully capture the complexity represented in GFF3 files.
Unless you have a specific reason for using Bio::DB::GFF, we suggest
that you use a Bio::DB::SeqFeature::Store database and its corresponding
loader, bp_seqfeature_load.pl.
Total parse time 13.70sin 0.09s...
Loading feature data and analyzing tables. You may see RDBMS messages
here...
(using password: YES)
Total load time 0.00s
done...
Analyzing/optimizing tables. You will see database messages...
Optimization time time 0.00s
FAILURE: Please see standard error for details
perl bp_bulk_load_gff.pl -d tribolium --user <user> --password <password>
<list of gff3 files>.
CREATE DATABASE tribolium;
What am I missing and how can I make this work?
Thanks,
Asma
------------------------------------------------------------------------------
Slashdot TV. Videos for Nerds. Stuff that Matters.
http://pubads.g.doubleclick.net/gampad/clk?id=160591471&iu=/4140/ostg.clktrk_______________________________________________
Gmod-gbrowse mailing list
https://lists.sourceforge.net/lists/listinfo/gmod-gbrowse

[Scott Cain]

Hi Asma,

I just sent you an email telling you to add the -c flag, but it didn't make
it to the list since the email address was spelled wrong, but I guess no
harm, since you already did that anyway.

My recollection of the permission scheme in MySQL is that you have to grant
permission to bulk load files has to be given explicitly (that is, it isn't
part of "ALL", which is admittedly somewhat confusing). I think the syntax
is something like GRANT FILE TO ...user... File permission is global, so
you don't grant it for a specific database.

Scott

Post by Asma Riyaz
perl bp_seqfeature_load.pl -d dbi:mysql:userdata_tribolium -u www-data -p
<password> -c [list of gff3 files]
-------------------- EXCEPTION --------------------
STACK Bio::DB::SeqFeature::Store::DBI::mysql::init
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm:217
STACK Bio::DB::SeqFeature::Store::new
/usr/share/perl5/Bio/DB/SeqFeature/Store.pm:388
STACK toplevel bp_seqfeature_load.pl:224
CREATE DATABASE userdata_tribolium;
use userdata_tribolium;
Any suggestions?
Thanks
Asma

Hi Asma,
Looking at your `bp_seqfeature_load.pl` command, it seems to be missing
the `-c` (--create) flag. This is required for the very first time you load
a GFF3 file into a database that has not yet been initialized (the schema
has not be created).
For all subsequent loads, you can skip the `-c` parameter, if your plan
is to perform incremental loads.
http://search.cpan.org/~cjfields/BioPerl-1.6.924/scripts/Bio-DB-SeqFeature-Store/bp_seqfeature_load.pl
Hope this helps!
Best,
Vivek
---------- Forwarded message ----------
Date: Mon, Oct 6, 2014 at 12:55 PM
Subject: loading scripts not working
Hi,
I am trying to load gff3 files for Tribolium using bp_seqfeature_load.pl
Transaction aborted because DBD::mysql::st execute failed: Table
'tribolium.locationlist' doesn't exist at
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm line 1571, <GEN0>
line 22208.
-------------------- EXCEPTION --------------------
MSG: should have a primary_id
STACK Bio::DB::SeqFeature::Store::DBI::mysql::_add_SeqFeature
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm:603
STACK Bio::DB::SeqFeature::Store::add_SeqFeature
/usr/share/perl5/Bio/DB/SeqFeature/Store.pm:1525
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree_in_tables
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:726
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:699
STACK Bio::DB::SeqFeature::Store::GFF3Loader::finish_load
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:343
STACK Bio::DB::SeqFeature::Store::Loader::load_fh
/usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:354
STACK Bio::DB::SeqFeature::Store::Loader::load
/usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:243
STACK toplevel bp_seqfeature_load.pl:258
perl bp_seqfeature_load.pl -d tribolium -a DBI::mysql --user <user>
--password <password> <list of gff3 files>
"my" variable $AUTH masks earlier declaration in same scope at
bp_bulk_load_gff.pl line 656.
"my" variable $db masks earlier declaration in same scope at
bp_bulk_load_gff.pl line 666.
This operation will delete all existing data in database dbi:mysql:test.
Continue? Y
159000 features parsed in 0.08s...
You are loading a Bio::DB::GFF database with GFF3 formatted data.
While this will likely work fine, the Bio::DB::GFF schema does not
always faithfully capture the complexity represented in GFF3 files.
Unless you have a specific reason for using Bio::DB::GFF, we suggest
that you use a Bio::DB::SeqFeature::Store database and its corresponding
loader, bp_seqfeature_load.pl.
Total parse time 13.70sin 0.09s...
Loading feature data and analyzing tables. You may see RDBMS messages here...
(using password: YES)
Total load time 0.00s
done...
Analyzing/optimizing tables. You will see database messages...
Optimization time time 0.00s
FAILURE: Please see standard error for details
perl bp_bulk_load_gff.pl -d tribolium --user <user> --password
<password> <list of gff3 files>.
CREATE DATABASE tribolium;
What am I missing and how can I make this work?
Thanks,
Asma
------------------------------------------------------------------------------
Slashdot TV. Videos for Nerds. Stuff that Matters.
http://pubads.g.doubleclick.net/gampad/clk?id=160591471&iu=/4140/ostg.clktrk_______________________________________________
Gmod-gbrowse mailing list
https://lists.sourceforge.net/lists/listinfo/gmod-gbrowse

[Asma Riyaz]

---------- Forwarded message ----------
From: Asma Riyaz <***@gmail.com>
Date: Mon, Oct 6, 2014 at 4:15 PM
Subject: Re: loading scripts not working
To: Scott Cain <***@scottcain.net>
Cc: "Gbrowse (E-mail)" <gmod-***@lists.sourceforge.ne>


Hi,

I have added the '-c' flag and ending in an access denied error (my
previous mail has the STACK error and the steps I took to initialize the
database)

Thanks
Asma

Hi Asma,
Let's stick with bp_seqfeature_load.pl, since that is the loader for a
Bio:DB::SeqFeature::Store database, which is much better to use with GFF3
data. The bp_bulk_load_gff.pl script works with Bio:DB::GFF databases,
and works better with GFF2 data.
The message about a table not existing is the clue to the problem: the
command you provided lacks the "create" flag to create the database tables
before loading the data, so you should add "-c" or "--create" to the
seqfeature_load command. Be careful with this flag though, because it will
wipe out any data that is already in the database, so you should use it
only the first time you are loading data into the database. If you want to
load data after the initial load, be sure to drop the create flag.
Scott

Post by Asma Riyaz
Hi,
I am trying to load gff3 files for Tribolium using bp_seqfeature_load.pl
Transaction aborted because DBD::mysql::st execute failed: Table
'tribolium.locationlist' doesn't exist at
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm line 1571, <GEN0>
line 22208.
-------------------- EXCEPTION --------------------
MSG: should have a primary_id
STACK Bio::DB::SeqFeature::Store::DBI::mysql::_add_SeqFeature
/usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm:603
STACK Bio::DB::SeqFeature::Store::add_SeqFeature
/usr/share/perl5/Bio/DB/SeqFeature/Store.pm:1525
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree_in_tables
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:726
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:699
STACK Bio::DB::SeqFeature::Store::GFF3Loader::finish_load
/usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:343
STACK Bio::DB::SeqFeature::Store::Loader::load_fh
/usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:354
STACK Bio::DB::SeqFeature::Store::Loader::load
/usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:243
STACK toplevel bp_seqfeature_load.pl:258
perl bp_seqfeature_load.pl -d tribolium -a DBI::mysql --user <user>
--password <password> <list of gff3 files>
"my" variable $AUTH masks earlier declaration in same scope at
bp_bulk_load_gff.pl line 656.
"my" variable $db masks earlier declaration in same scope at
bp_bulk_load_gff.pl line 666.
This operation will delete all existing data in database dbi:mysql:test.
Continue? Y
159000 features parsed in 0.08s...
You are loading a Bio::DB::GFF database with GFF3 formatted data.
While this will likely work fine, the Bio::DB::GFF schema does not
always faithfully capture the complexity represented in GFF3 files.
Unless you have a specific reason for using Bio::DB::GFF, we suggest
that you use a Bio::DB::SeqFeature::Store database and its corresponding
loader, bp_seqfeature_load.pl.
Total parse time 13.70sin 0.09s...
Loading feature data and analyzing tables. You may see RDBMS messages
here...
(using password: YES)
Total load time 0.00s
done...
Analyzing/optimizing tables. You will see database messages...
Optimization time time 0.00s
FAILURE: Please see standard error for details
perl bp_bulk_load_gff.pl -d tribolium --user <user> --password
<password> <list of gff3 files>.
CREATE DATABASE tribolium;
What am I missing and how can I make this work?
Thanks,
Asma

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

[Krishnakumar, Vivek]

Hi Asma,

Looking at your `bp_seqfeature_load.pl` command, it seems to be missing the `-c` (--create) flag. This is required for the very first time you load a GFF3 file into a database that has not yet been initialized (the schema has not be created).
For all subsequent loads, you can skip the `-c` parameter, if your plan is to perform incremental loads.

Please refer to the documentation available here: http://search.cpan.org/~cjfields/BioPerl-1.6.924/scripts/Bio-DB-SeqFeature-Store/bp_seqfeature_load.pl

Hope this helps!

Best,
Vivek

On Oct 6, 2014, at 1:14 PM, Asma Riyaz <***@gmail.com<mailto:***@gmail.com>> wrote:


---------- Forwarded message ----------
From: Asma Riyaz <***@gmail.com<mailto:***@gmail.com>>
Date: Mon, Oct 6, 2014 at 12:55 PM
Subject: loading scripts not working
To: ***@scottcain.net<mailto:***@scottcain.net>, gmod-***@lists.sourceforge.ne<mailto:gmod-***@lists.sourceforge.ne>


Hi,

I am trying to load gff3 files for Tribolium using bp_seqfeature_load.pl<http://bp_seqfeature_load.pl/> which fails with this error:

Transaction aborted because DBD::mysql::st execute failed: Table 'tribolium.locationlist' doesn't exist at /usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm<http://mysql.pm/> line 1571, <GEN0> line 22208.

-------------------- EXCEPTION --------------------
MSG: should have a primary_id
STACK Bio::DB::SeqFeature::Store::DBI::mysql::_add_SeqFeature /usr/share/perl5/Bio/DB/SeqFeature/Store/DBI/mysql.pm:603<http://mysql.pm:603/>
STACK Bio::DB::SeqFeature::Store::add_SeqFeature /usr/share/perl5/Bio/DB/SeqFeature/Store.pm:1525
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree_in_tables /usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:726
STACK Bio::DB::SeqFeature::Store::GFF3Loader::build_object_tree /usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:699
STACK Bio::DB::SeqFeature::Store::GFF3Loader::finish_load /usr/share/perl5/Bio/DB/SeqFeature/Store/GFF3Loader.pm:343
STACK Bio::DB::SeqFeature::Store::Loader::load_fh /usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:354
STACK Bio::DB::SeqFeature::Store::Loader::load /usr/share/perl5/Bio/DB/SeqFeature/Store/Loader.pm:243
STACK toplevel bp_seqfeature_load.pl:258<http://bp_seqfeature_load.pl:258/>

Here is the command I use:
perl bp_seqfeature_load.pl<http://bp_seqfeature_load.pl/> -d tribolium -a DBI::mysql --user <user> --password <password> <list of gff3 files>


I have also tried using bp_bulk_load_gff.pl<http://bp_bulk_load_gff.pl/> to load data:

"my" variable $AUTH masks earlier declaration in same scope at bp_bulk_load_gff.pl<http://bp_bulk_load_gff.pl/> line 656.
"my" variable $db masks earlier declaration in same scope at bp_bulk_load_gff.pl<http://bp_bulk_load_gff.pl/> line 666.
This operation will delete all existing data in database dbi:mysql:test. Continue? Y

159000 features parsed in 0.08s...
You are loading a Bio::DB::GFF database with GFF3 formatted data.
While this will likely work fine, the Bio::DB::GFF schema does not
always faithfully capture the complexity represented in GFF3 files.
Unless you have a specific reason for using Bio::DB::GFF, we suggest
that you use a Bio::DB::SeqFeature::Store database and its corresponding
loader, bp_seqfeature_load.pl<http://bp_seqfeature_load.pl/>.
Total parse time 13.70sin 0.09s...
Loading feature data and analyzing tables. You may see RDBMS messages here...
ERROR 1045 (28000) at line 1: Access denied for user 'tomoyasulab'@'localhost' (using password: YES)
Total load time 0.00s
done...
Analyzing/optimizing tables. You will see database messages...
Optimization time time 0.00s
FAILURE: Please see standard error for details

Here is command I used:
perl bp_bulk_load_gff.pl<http://bp_bulk_load_gff.pl/> -d tribolium --user <user> --password <password> <list of gff3 files>.

In mysql I issued the following commands:

CREATE DATABASE tribolium;
grant select on tribolium.* to ***@localhost;
grant all privileges on tribolium.* to <user>@localhost;

What am I missing and how can I make this work?

Thanks,
Asma