Menu
▾
▴
Re: [Gusdev-gusdev] Load GOOntology error--
|
From: Dave B. <db...@pc...> - 2003-05-16 16:27:43
|
Hi Michael and folks,
This is the result of a known bug in the database schema. Right now,
there is a non-nullable foreign key in SRes.GoTerm that points back to
SRes.GoTerm itself (the foreign key being ancestor_go_term).
I believe I submitted a request for a schema change, but until that change
is implemented the temporary solution is a hack; when loading the root
term of the GO Ontology, I just set the ancestor_go_term to 1, so that it
temporarily points to whatever entry is in SRes.GoTerm with an ID of 1.
Then I immediately update the root entry to (correctly) point to itself as
the ancestor_go_term.
However, since you are loading the Ontology for the first time, you
obviously won't have any entry with an ID of 1; that is the cause of the
error message you are getting.
I think the only solution to this is to make the column ancestor_go_term
nullable. After that, you will have to take out the line in the plugin
that does that does the hack of setting the ancestor_go_term to 1, which
is shown here:
(line 500 of LoadGoOntology.pm):
my $ontologyGoTerm = GUS::Model::SRes::GOTerm->new({
go_id => $rootGoId,
external_database_release_id => $extDbRelId,
source_id => $rootEntry->getId(),
name => $rootEntry->getName(), #just the name
definition => $rootEntry->getName(),
minimum_level => 0,
maximum_level => 0,
number_of_levels => 1,
ancestor_go_term_id => $tempAncestorId, #remove this line
} );
After reading some of the previous email traffic on the gus-dev list, I'm
not sure if you have permissions to make these modifications. I can make
them for you and resubmit them to cvs, but I am hesitant to make this
change permanent until we have the schema change to make the column
non-nullable instituted in our copy of GUS. This brings up some of the
larger questions about how schema changes to GUS are ensured to remain
consistent among all copies of GUS. I'm sure there's a protocol; while I'm
not familiar with it I'm sure other people on this list are and could
help you out implementing all of this.
Glad you got the plugin running; did you end up passing in the external
database ids as command line parameters or did you figure out what was
causing the previous problem some other way?
Dave
On Fri, 16 May 2003, MICHAEL LUCHTAN wrote:
> Dave-
> Check it out:
> [luchtan@mango install]$ ga GUS::GOPredict::Plugin::LoadGoOntology
> --create_release --file_path=/home/gusdev/gus3.0-checkouts/GO
> --id_file=/home/luchtan/GOLOG --verbose
> Reading properties from /home/gus_home/config/GUS-PluginMgr.prop
> Reading properties from /home/luchtan/gus.properties
> DBD::Oracle::db do failed: ORA-30019: Illegal rollback Segment operation
> in Automatic Undo mode (DBD ERROR: OCIStmtExecute) at
> /home/gus_home/lib/perl/GUS/ObjRelP/DbiDatabase.pm line 149.
> DBD::Oracle::db do failed: ORA-30019: Illegal rollback Segment operation
> in Automatic Undo mode (DBD ERROR: OCIStmtExecute) at
> /home/gus_home/lib/perl/GUS/ObjRelP/DbiDatabase.pm line 149.
> executing Select external_database_id from sres.externaldatabase where
> name = ? GO Function
> executing Select external_database_id from sres.externaldatabase where
> name = ? GO Component
> executing Select external_database_id from sres.externaldatabase where
> name = ? GO Process
> Thu May 15 23:59:40 2003 loading all .ontology files in
> /home/gusdev/gus3.0-checkouts/GO in preparation for parsing
> Thu May 15 23:59:40 2003 parsing all .ontology files in preparation
> for inserting into database
> Thu May 15 23:59:49 2003 parsing finished; loading ontology into
> database
>
>
> prepareAndExecute: select external_database_release_id
> from sres.externalDatabaseRelease
> where version = '2.690' and external_database_id = 92
>
> GO:0003673, 1, 174, 1, 0, 0, Gene_Ontology)
> DBD::Oracle::st execute failed: ORA-02291: integrity constraint
> (SRES.GOTERM_FK02) violated - parent key not found (DBD ERROR:
> OCIStmtExecute) at /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line
> 144.
>
>
> sqlExec:
> UPDATE Core.AlgorithmInvocation
> SET
> end_time = SYSDATE,
> row_alg_invocation_id = ?,
> modification_date = SYSDATE
> WHERE algorithm_invocation_id = ?
> bindValues (174, 174)
> DbiHandle:sqlExec:insert succeeded 1 row(s)
>
> SQL ERROR!! involving
>
> INSERT INTO SRes.GOTerm ( row_user_id, go_id, user_write,
> group_write, ancestor_go_term_id, row_project_id, name,
> external_database_release_id, group_read, minimum_level, row_group_id,
> go_term_id, other_read, source_id, modification_date, user_read,
> row_alg_invocation_id, number_of_levels, maximum_level, other_write,
> definition )
> VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, ?, ?,
> ?, ?, ?, ? )
> Values: 6, GO:0003673, 1, 1, 1, 2, Gene_Ontology, 93, 1, 0, 3, 3, 1,
> GO:0003673, 1, 174, 1, 0, 0, Gene_Ontology at
> /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 184
>
> GUS::ObjRelP::DbiDbHandle::death('GUS::ObjRelP::DbiDbHandle=HASH(0x86af264)',
> '^J SQL ERROR!! involving^J ^J INSERT INTO SRes.GOTerm ( row_use...')
> called at /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 147
>
> GUS::ObjRelP::DbiDbHandle::sqlExec('GUS::ObjRelP::DbiDbHandle=HASH(0x86af264)',
> 'GUS::ObjRelP::DbiDbHandle::st=HASH(0x8bc753c)', 'ARRAY(0x8bc75c0)', '^J
> INSERT INTO SRes.GOTerm ( row_user_id, go_id, user_write, ...') called at
> /home/gus_home/lib/perl/GUS/ObjRelP/DbiRow.pm line 674
>
> GUS::ObjRelP::DbiRow::quote_and_insert('GUS::Model::SRes::GOTerm=HASH(0x890e1e0)',
> 'HASH(0x890dc40)') called at /home/gus_home/lib/perl/GUS/ObjRelP/DbiRow.pm
> line 621
>
> GUS::ObjRelP::DbiRow::insert('GUS::Model::SRes::GOTerm=HASH(0x890e1e0)')
> called at /home/gus_home/lib/perl/GUS/Model/GusRow.pm line 1677
>
> GUS::Model::GusRow::submit('GUS::Model::SRes::GOTerm=HASH(0x890e1e0)')
> called at /home/gus_home/lib/perl/GUS/GOPredict/Plugin/LoadGoOntology.pm
> line 512
>
> GUS::GOPredict::Plugin::LoadGoOntology::__makeOntologyRoot('GUS::GOPredict::Plugin::LoadGoOntology=HASH(0x84bf9b8)',
> 'HASH(0x8e7c514)', 'GO:0003673', 93) called at
> /home/gus_home/lib/perl/GUS/GOPredict/Plugin/LoadGoOntology.pm line 437
>
> GUS::GOPredict::Plugin::LoadGoOntology::__makeRoots('GUS::GOPredict::Plugin::LoadGoOntology=HASH(0x84bf9b8)',
> 'HASH(0x8e7c514)', 'CBIL::Bio::GeneOntologyParser::Store=HASH(0x8b26f04)',
> 93, undef, 'FileHandle=GLOB(0x88c801c)') called at
> /home/gus_home/lib/perl/GUS/GOPredict/Plugin/LoadGoOntology.pm line 195
>
> GUS::GOPredict::Plugin::LoadGoOntology::__load_ontology('GUS::GOPredict::Plugin::LoadGoOntology=HASH(0x84bf9b8)',
> 'CBIL::Bio::GeneOntologyParser::Parser=HASH(0x882ce64)') called at
> /home/gus_home/lib/perl/GUS/GOPredict/Plugin/LoadGoOntology.pm line 144
>
> GUS::GOPredict::Plugin::LoadGoOntology::run('GUS::GOPredict::Plugin::LoadGoOntology=HASH(0x84bf9b8)',
> 'HASH(0x882ce34)') called at
> /home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 389
> eval {...} called at
> /home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 385
>
> GUS::PluginMgr::GusApplication::doMajorMode_Run('GUS::PluginMgr::GusApplication=HASH(0x80fbb0c)',
> 'GUS::GOPredict::Plugin::LoadGoOntology') called at
> /home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 284
>
> GUS::PluginMgr::GusApplication::doMajorMode('GUS::PluginMgr::GusApplication=HASH(0x80fbb0c)',
> 'GUS::GOPredict::Plugin::LoadGoOntology') called at
> /home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 193
>
> GUS::PluginMgr::GusApplication::parseAndRun('GUS::PluginMgr::GusApplication=HASH(0x80fbb0c)',
> 'ARRAY(0x8105130)') called at /home/gus_home/bin/ga line 11
>
>
> Looks like an sql error, but I'm not sure what to do about it. Do any of
> the rror messages give you any leads?
>
>
> Michael Luchtan
> http://www.cs.uga.edu/~luchtan
>
>
> On Thu, 15 May 2003, Dave Barkan wrote:
>
> > Hey Michael,
> >
> > I still think that the error is an sql error. It does say
> >
> > > Can't call method "execute" without a package or object reference at
> > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 78.
> >
> > but my experience is that that message is printed out when the sql
> > statement is not what it expects. The fact that it also says
> >
> > prepareAndExecute: select external_database_release_id
> > > from sres.externalDatabaseRelease
> > > where version = '2.690' and external_database_id =
> > > DBD::Oracle::db prepare failed: ORA-00921: unexpected end of SQL command
> >
> > and doesn't print out the external_database_id in the statement is why I
> > am focusing on the external database issue. Here's what I did so we can
> > narrow it down:
> >
> > 1. I added an error statement if the plugin tries to run this query and
> > can't find the database id; that way we can be sure about the problem.
> >
> > 2. I added three command line arguments: function_db_id, process_db_id,
> > and component_db_id. You can pass these in to specify the external
> > database ids of the respective branches, thus avoiding the
> > programming hack of hardcoded entries in the database.
> >
> > To rerun, just do the following:
> >
> > 1. cvs update LoadGoOntology to get the version I just committed.
> > 2. run 'build GUS/GOPredict install -append' to build the plugin.
> > 3. run the command 'ga +update GUS::GOPredict::Plugin::LoadGoOntology
> > --commit'
> >
> > (this sequence of commands generally has to be done whenever changes have
> > been made to a plugin, especially those to the command line arguments).
> >
> > Try that, run it with the --verbose flag set, and let me know what you
> > see.
> >
> > Dave
> >
> >
> >
> > On Thu, 15 May 2003, MICHAEL LUCHTAN wrote:
> >
> > > Dave-
> > >
> > > Yes, we ran the provided queries, and they are in the table. Also, those
> > > files are there as downloaded from the ftp site that you provided.
> > > Again, here is a snapshot of where it failed(which seems more like a
> > > compiler error than an sql error):
> > >
> > > sqlExec:
> > > INSERT INTO Core.AlgorithmParam ( row_user_id, user_write,
> > > group_write, is_default, order_num, row_project_id,
> > > algorithm_invocation_id, group_read, string_value, row_group_id,
> > > other_read, modification_date, user_read, row_alg_invocation_id,
> > > algorithm_param_id, algorithm_param_key_id, other_write )
> > > VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, '', ?, ?, SYSDATE, ?, ?, ?, ?, ? )
> > > bindValues (6, 1, 1, 0, 0, 2, 169, 1, 3, 1, 1, 169, 1740, 304, 0)
> > > DbiHandle:sqlExec:insert succeeded 1 row(s)
> > > Thu May 15 03:01:35 2003 loading all .ontology files in
> > > /home/gusdev/gus3.0-checkouts/GO in preparation for parsing
> > > Thu May 15 03:01:35 2003 parsing all .ontology files in preparation
> > > for inserting into database
> > > Thu May 15 03:01:43 2003 parsing finished; loading ontology into
> > > database
> > >
> > >
> > > prepareAndExecute: select external_database_release_id
> > > from sres.externalDatabaseRelease
> > > where version = '2.690' and external_database_id =
> > > DBD::Oracle::db prepare failed: ORA-00921: unexpected end of SQL command
> > > (DBD ERROR: OCIStmtExecute/Describe) at
> > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 77.
> > > prepareAndExecute FAILED:
> > > GUS::ObjRelP::DbiDbHandle=HASH(0x81d2074)->errstr
> > >
> > >
> > > sqlExec:
> > > UPDATE Core.AlgorithmInvocation
> > > SET
> > > end_time = SYSDATE,
> > > row_alg_invocation_id = ?,
> > > modification_date = SYSDATE
> > > WHERE algorithm_invocation_id = ?
> > > bindValues (169, 169)
> > > DbiHandle:sqlExec:insert succeeded 1 row(s)
> > > Can't call method "execute" without a package or object reference at
> > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 78.
> > >
> > >
> > > Thanks,
> > >
> > > Michael Luchtan
> > > http://www.cs.uga.edu/~luchtan
> > >
> > >
> > > On Thu, 15 May 2003, Dave Barkan wrote:
> > >
> > > > Hey Michael,
> > > >
> > > > Alright, I'm still not sure it isn't an issue with an external database
> > > > entry. Try these two things.
> > > >
> > > > 1.Run the query 'select external_database_id from sres.externaldatabase
> > > > where name = ?'
> > > >
> > > > and substitute 'GO Function,' GO Process,' 'GO Component' for each ?.
> > > >
> > > > 2. Make sure that the ontology files you are loading from are of the form
> > > > 'branch.ontology' where branch can be one of 'process', 'function', or
> > > > 'component.' These are the names of the files when they are downloaded
> > > > from the GO site.
> > > >
> > > > The reason I want you to give the query a shot is that the plugin runs the
> > > > same query to get the id's of those databases. Then it turns around and
> > > > uses those ids in the query that is failing:
> > > >
> > > > >
> > > > > prepareAndExecute: select external_database_release_id
> > > > > from sres.externalDatabaseRelease
> > > > > where version = '2.690' and external_database_id =
> > > > > DBD::Oracle::db prepare failed: ORA-00921: unexpected end of SQL command
> > > >
> > > > Sorry if this is overkill to try to figure this one out but I guess that's
> > > > the disadvantage of debugging across state lines!
> > > >
> > > > dave
> > > >
> > > >
> > > >
> > > >
> > > > > (DBD ERROR: OCIStmtExecute/Describe) at
> > > > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 77.
> > > > > prepareAndExecute FAILED:
> > > > > GUS::ObjRelP::DbiDbHandle=HASH(0x81d2084)->errstr
> > > > >
> > > > >
> > > > > sqlExec:
> > > > > UPDATE Core.AlgorithmInvocation
> > > > > SET
> > > > > end_time = SYSDATE,
> > > > > row_alg_invocation_id = ?,
> > > > > modification_date = SYSDATE
> > > > > WHERE algorithm_invocation_id = ?
> > > > > bindValues (167, 167)
> > > > > DbiHandle:sqlExec:insert succeeded 1 row(s)
> > > > > Can't call method "execute" without a package or object reference at
> > > > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 78.
> > > > >
> > > > >
> > > > >
> > > > > Michael Luchtan
> > > > > http://www.cs.uga.edu/~luchtan
> > > > >
> > > > >
> > > > > On Thu, 15 May 2003, Dave Barkan wrote:
> > > > >
> > > > > > Hey Michael,
> > > > > >
> > > > > > Can you run it again with the --verbose flag set, and resend the output?
> > > > > > That will probably help me figure out the error much easier. Thanks,
> > > > > >
> > > > > > Dave
> > > > > >
> > > > > > On Thu, 15 May 2003, MICHAEL LUCHTAN wrote:
> > > > > >
> > > > > > > Hello Dave-
> > > > > > > Here is the error:
> > > > > > >
> > > > > > > [luchtan@mango gbparserFailures]$ ga
> > > > > > > GUS::GOPredict::Plugin::LoadGoOntology --create_release
> > > > > > > --file_path=/home/gusdev/gus3.0-checkouts/GO --id_file=/home/luchtan/GOLOG
> > > > > > >
> > > > > > > Reading properties from /home/gus_home/config/GUS-PluginMgr.prop
> > > > > > > Reading properties from /home/luchtan/gus.properties
> > > > > > > DBD::Oracle::db do failed: ORA-30019: Illegal rollback Segment operation
> > > > > > > in Automatic Undo mode (DBD ERROR: OCIStmtExecute) at
> > > > > > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDatabase.pm line 149.
> > > > > > > DBD::Oracle::db do failed: ORA-30019: Illegal rollback Segment operation
> > > > > > > in Automatic Undo mode (DBD ERROR: OCIStmtExecute) at
> > > > > > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDatabase.pm line 149.
> > > > > > > DBD::Oracle::db do failed: ORA-30019: Illegal rollback Segment operation
> > > > > > > in Automatic Undo mode (DBD ERROR: OCIStmtExecute) at
> > > > > > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDatabase.pm line 149.
> > > > > > > DBD::Oracle::db do failed: ORA-30019: Illegal rollback Segment operation
> > > > > > > in Automatic Undo mode (DBD ERROR: OCIStmtExecute) at
> > > > > > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDatabase.pm line 149.
> > > > > > > Thu May 15 00:48:36 2003 loading all .ontology files in
> > > > > > > /home/gusdev/gus3.0-checkouts/GO in preparation for parsing
> > > > > > > Thu May 15 00:48:36 2003 parsing all .ontology files in preparation
> > > > > > > for inserting into database
> > > > > > > Thu May 15 00:48:44 2003 parsing finished; loading ontology into
> > > > > > > database
> > > > > > > DBD::Oracle::db prepare failed: ORA-00921: unexpected end of SQL command
> > > > > > > (DBD ERROR: OCIStmtExecute/Describe) at
> > > > > > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 77.
> > > > > > > prepareAndExecute FAILED:
> > > > > > > GUS::ObjRelP::DbiDbHandle=HASH(0x81d2150)->errstr
> > > > > > > Can't call method "execute" without a package or object reference at
> > > > > > > /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 78.
> > > > > > >
> > > > > > > Some kind of referencing error? Thanks,
> > > > > > >
> > > > > > > Michael Luchtan
> > > > > > > http://www.cs.uga.edu/~luchtan
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > -------------------------------------------------------
> > > > > > > Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara
> > > > > > > The only event dedicated to issues related to Linux enterprise solutions
> > > > > > > www.enterpriselinuxforum.com
> > > > > > >
> > > > > > > _______________________________________________
> > > > > > > Gusdev-gusdev mailing list
> > > > > > > Gus...@li...
> > > > > > > https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
>
|
