[Bioperl-l] Building biosql database errors

[Hilmar Lapp]

This could have multiple reasons. Generally speaking, in an ideal world 
the unique key constraint is on the tuple (name, ontology), and given 
the error message this seems to be the constraint you're violating, 
because

	- you may have loaded GO before and did not specify --lookup or 
similar options that let the script deal with pre-existing content to 
be updated according to one of different policies (check out the 
load_ontology.pl POD for a more elaborate discussion of update options)

	- the term that appears to violate the constraint exists also as an 
obsoleted term with the same name, but a different GO identifier, and 
you did not choose to ignore and delete obsoleted terms

The latter is particularly nasty and a reflection of the fact that the 
world we live in is not ideal. If you are going to always purge 
existing terms from the database and then reload GO then you can keep 
the unique key constraint the way it is, and just need to make sure 
that this strategy is reflected in the options (--noobsolete). The 
downside of doing so (and of using --delobsolete, too) is that deleting 
the terms will remove their associations to bioentries and features as 
well, i.e., if any bioentry or feature was annotated with either any GO 
term (if reload from scratch) or a GO term that is being obsoleted (if 
using --delobsolete) then obviously you lose that annotation when 
deleting the term(s). If you'll reload those associations right 
afterwards, then there's no problem with this.

Alternatively, if you want to keep GO in the database and then update 
it with a new release, then apart from choosing what to do with terms 
that are obsolete (see the load_ontology.pl POD for the choices you 
have) you need to change the unique key constraint to the tuple of 
(name, ontology, is_obsolete). This should be a commented-out option in 
the schema DDL file.

Hth,

	-hilmar

On Tuesday, February 10, 2004, at 09:02  AM, Sean Davis wrote:

> I am trying to build a biosql database on a mysql database.  I have 
> mysql
> and biosql schema running and can successfully load some data, but for 
> a
> proportion of the data when loading ontology or locuslink, I get the
> following (many times).  Am I doing something wrong, or is this to be
> expected?  I would just push on with --safe (as given below), but 
> clearly
> part of the data is not loaded correctly after looking at the result.  
> I
> have the same problem when loading locuslink.  Any input is 
> appreciated.
>
> Thanks,
> Sean
>
>
> % perl ../../../bioperl-db/scripts/biosql/load_ontology.pl --safe 
> --fmtargs
> "-defs_file,GO.defs" --dbuser sdavis --dbpass mic2222 --namespace "Gene
> Ontology" --format goflat component.ontology.2004-02-01
> process.ontology.2004-02-01 function.ontology.2004-02-01
>
> -------------------- WARNING ---------------------
> MSG: insert in Bio::DB::BioSQL::TermAdaptor (driver) failed, values 
> were
> ("GO:0042597","periplasmic space","The region between the inner
> (cytoplasmic) and outer membrane (Gram-negative bacteria) or inner 
> membrane
> and cell wall (fungi).","") FKs (84)
> Duplicate entry 'periplasmic space-84' for key 2
> ---------------------------------------------------
> Could not store term relationship (periplasmic space (sensu
> Fungi),IS_A,periplasmic space):
>
> ------------- EXCEPTION  -------------
> MSG: create: object (Bio::Ontology::GOterm) failed to insert or to be 
> found
> by unique key
> STACK Bio::DB::BioSQL::BasePersistenceAdaptor::create
> /Library/Perl/5.8.1/Bio/DB/BioSQL/BasePersistenceAdaptor.pm:207
> STACK Bio::DB::Persistent::PersistentObject::create
> /Library/Perl/5.8.1/Bio/DB/Persistent/PersistentObject.pm:243
> STACK Bio::DB::BioSQL::BasePersistenceAdaptor::create
> /Library/Perl/5.8.1/Bio/DB/BioSQL/BasePersistenceAdaptor.pm:170
> STACK Bio::DB::Persistent::PersistentObject::create
> /Library/Perl/5.8.1/Bio/DB/Persistent/PersistentObject.pm:243
> STACK (eval) ../../../bioperl-db/scripts/biosql/load_ontology.pl:548
> STACK toplevel ../../../bioperl-db/scripts/biosql/load_ontology.pl:547
>
> --------------------------------------
>
> _______________________________________________
> Bioperl-l mailing list
> Bioperl-l at portal.open-bio.org
> http://portal.open-bio.org/mailman/listinfo/bioperl-l
>
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------