Re: [Gusdev-gusdev] Problem with ga registration/update - Primary key constraint

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

We had the same problem last month here. We posted comments on this on
the Wiki.

I wrote a script to reset all sequences based on the primary key's max
value. It's available at:
http://mango.ctegd.uga.edu/jkissingLab/download/gus_oracle_seqfix.pl.gz

Best regards,
Pablo

On Thu, 2004-07-22 at 15:52, Arnaud Kerhornou wrote:
> John Iodice wrote:
> 
> >Maybe the cause was a twofold problem:
> >
> >1. The select against the sequence.nextval failed because of database
> >permission problems, and
> >2. The application is coded to populate the new primary key with
> >maxval+1 in case of (1)
> >
> >I have seen this series of events while using plugins to populate GUS
> >tables.
> >
> >It seems that you could test (1) by starting up sqlplus as the user that
> >ran the "ga" and typing "select <sequence-name>.nextval from dual".
> >
> >  
> >
> we did this and we noticed that any sequence.nextval returned 1. I think 
> that's why we got the same message than Thomas, "DBD::Oracle::st execute 
> failed: ORA-00001: unique constraint (CORE.PK_ALGORITHMIMPLEMENTATION) 
> violated". We didn't get any permission issues.
> 
> To fix this we have reset the sequences to a high number to make sure 
> they don't clash with already existing PK values. Well it's a temporary 
> hack !
> 
> Arnaud
> 
> >On Thu, 2004-07-22 at 15:08, Arnaud Kerhornou wrote:
> >  
> >
> >>Hi
> >>
> >>The same problem ocurred to me recently. When we checked the Oracle 
> >>sequences, they were all set to 1 though there were some rows in some 
> >>tables. I don't know why that has happened.
> >>
> >>Does anyone have an idea why the sequences could be reset to 1 ?
> >>
> >>Arnaud
> >>
> >>Thomas Otto wrote:
> >>
> >>    
> >>
> >>>Folks,
> >>>
> >>>I found my error:
> >>>The problem was, that the sequences of the tables in the oracle didn't 
> >>>display the realy primarykey number. I do not know why, but I will 
> >>>have an eye on it.
> >>>I modify the 'lastnumber', and know it seems to work out.
> >>>
> >>>Cheers,
> >>>Thomas
> >>>Thomas Otto wrote:
> >>>
> >>>      
> >>>
> >>>>Hi,
> >>>>
> >>>>I did an update via cvs, rebuild the stuff, worked more or less fine.
> >>>>
> >>>>But this the ga wanted to be registered another time:
> >>>>USER ERROR: GUS::PluginMgr::GusApplication revision 1.52 has not been 
> >>>>registered.
> >>>>Please use 'ga +meta --commit'
> >>>>
> >>>>The error of this command see below. There is a conflict of the 
> >>>>primary key...
> >>>>So I changed the cvs version and the MD5 checksum in the table.
> >>>>
> >>>>But I have to do this for all the plugins - no fun...
> >>>>
> >>>>Who can help me?
> >>>>
> >>>>Thomas
> >>>>
> >>>>
> >>>>ERROR: of ga + meta --commit
> >>>>DBD::Oracle::st execute failed: ORA-00001: unique constraint 
> >>>>(CORE.PK_ALGORITHMIMPLEMENTATION) violated (DBD ERROR: 
> >>>>OCIStmtExecute) at 
> >>>>/home/oracle/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 145.
> >>>>
> >>>>SQL ERROR!! involving
> >>>>
> >>>>   INSERT INTO Core.AlgorithmImplementation (  other_read, 
> >>>>group_write, cvs_revision, algorithm_id, group_read, cvs_tag, 
> >>>>user_read, row_user_id, description, algorithm_implementation_id, 
> >>>>other_write, modification_date, row_project_id, 
> >>>>row_alg_invocation_id, executable_md5, executable, row_group_id, 
> >>>>user_write )
> >>>>   VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, ?, ?, ?, ?, 
> >>>>?, ? ) Values: 1, 1, 1.52, 2, 1,  , 1, 1, update for GUS 3.0, 22, 0, 
> >>>>1, 1, c1212c2753960e177d29b40d5a313f59, 
> >>>>GUS::PluginMgr::GusApplication, 1, 1 at 
> >>>>/home/oracle/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 185
> >>>>       
> >>>>GUS::ObjRelP::DbiDbHandle::death('GUS::ObjRelP::DbiDbHandle=HASH(0x8691088)','\x{a} 
> >>>>SQL ERROR!! involving\x{a} \x{a}    INSERT INTO 
> >>>>Core.AlgorithmImple...') called at 
> >>>>/home/oracle/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 148
> >>>>       
> >>>>GUS::ObjRelP::DbiDbHandle::sqlExec('GUS::ObjRelP::DbiDbHandle=HASH(0x8691088)','GUS::ObjRelP::DbiDbHandle::st=HASH(0x87d1e30)','ARRAY(0x87d1ef0)','\x{a}    
> >>>>INSERT INTO Core.AlgorithmImplementation (  other_read, ...') called 
> >>>>at /home/oracle/gus_home/lib/perl/GUS/ObjRelP/DbiRow.pm line 681
> >>>>       
> >>>>GUS::ObjRelP::DbiRow::quote_and_insert('GUS::Model::Core::AlgorithmImplementation=HASH(0x870d390)','HASH(0x86f1b28)') 
> >>>>called at /home/oracle/gus_home/lib/perl/GUS/ObjRelP/DbiRow.pm line 628
> >>>>       
> >>>>GUS::ObjRelP::DbiRow::insert('GUS::Model::Core::AlgorithmImplementation=HASH(0x870d390)') 
> >>>>called at /home/oracle/gus_home/lib/perl/GUS/Model/GusRow.pm line 1677
> >>>>       
> >>>>GUS::Model::GusRow::submit('GUS::Model::Core::AlgorithmImplementation=HASH(0x870d390)','undef',1) 
> >>>>called at /home/oracle/gus_home/lib/perl/GUS/Model/GusRow.pm line 1764
> >>>>       
> >>>>GUS::Model::GusRow::submitChildrenInClass('GUS::Model::Core::Algorithm=HASH(0x8690ff8)','GUS::Model::Core::AlgorithmImplementation') 
> >>>>called at /home/oracle/gus_home/lib/perl/GUS/Model/GusRow.pm line 1745
> >>>>       
> >>>>GUS::Model::GusRow::submitAllChildren('GUS::Model::Core::Algorithm=HASH(0x8690ff8)') 
> >>>>called at /home/oracle/gus_home/lib/perl/GUS/Model/GusRow.pm line 1684
> >>>>       
> >>>>GUS::Model::GusRow::submit('GUS::Model::Core::Algorithm=HASH(0x8690ff8)') 
> >>>>called at 
> >>>>/home/oracle/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 361
> >>>>       
> >>>>GUS::PluginMgr::GusApplication::doMajorMode_Meta('GUS::PluginMgr::GusApplication=HASH(0x804d00c)','undef') 
> >>>>called at 
> >>>>/home/oracle/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 289
> >>>>       
> >>>>GUS::PluginMgr::GusApplication::doMajorMode('GUS::PluginMgr::GusApplication=HASH(0x804d00c)','undef') 
> >>>>called at 
> >>>>/home/oracle/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 198
> >>>>       
> >>>>GUS::PluginMgr::GusApplication::parseAndRun('GUS::PluginMgr::GusApplication=HASH(0x804d00c)','ARRAY(0x8060684)') 
> >>>>called at /home/oracle/gus_home/bin/ga line 11
> >>>>
> >>>>
> >>>>
> >>>>ERROR of Submitrow:
> >>>>DBD::Oracle::st execute failed: ORA-00001: unique constraint 
> >>>>(CORE.PK_ALGORITHMIMPLEMENTATION) violated (DBD ERROR: 
> >>>>OCIStmtExecute) at 
> >>>>/home/oracle/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 145.
> >>>>
> >>>>SQL ERROR!! involving
> >>>>
> >>>>   INSERT INTO Core.AlgorithmImplementation (  other_read, 
> >>>>group_write, cvs_revision, algorithm_id, group_read, cvs_tag, 
> >>>>user_read, row_user_id, description, algorithm_implementation_id, 
> >>>>other_write, modification_date, row_project_id, 
> >>>>row_alg_invocation_id, executable_md5, executable, row_group_id, 
> >>>>user_write )
> >>>>   VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, ?, ?, ?, ?, 
> >>>>?, ? ) Values: 1, 1, 1.9, 3, 1,  , 1, 1, make consistent with GUS 
> >>>>3.0, 24, 0, 1, 1, 60470c8cf30c7cea218fcbbb48840d97, 
> >>>>GUS::Common::Plugin::SubmitRow, 1, 1 at 
> >>>>/home/oracle/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 185
> >>>>
> >>>>
> >>>>
> >>>>-------------------------------------------------------
> >>>>This SF.Net email is sponsored by BEA Weblogic Workshop
> >>>>FREE Java Enterprise J2EE developer tools!
> >>>>Get your free copy of BEA WebLogic Workshop 8.1 today.
> >>>>http://ads.osdn.com/?ad_id=4721&alloc_id=10040&op=click
> >>>>_______________________________________________
> >>>>Gusdev-gusdev mailing list
> >>>>Gus...@li...
> >>>>https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
> >>>>
> >>>>        
> >>>>
> >>>
> >>>-------------------------------------------------------
> >>>This SF.Net email is sponsored by BEA Weblogic Workshop
> >>>FREE Java Enterprise J2EE developer tools!
> >>>Get your free copy of BEA WebLogic Workshop 8.1 today.
> >>>http://ads.osdn.com/?ad_id=4721&alloc_id=10040&op=click
> >>>_______________________________________________
> >>>Gusdev-gusdev mailing list
> >>>Gus...@li...
> >>>https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
> >>>
> >>>      
> >>>
> >>-------------------------------------------------------
> >>This SF.Net email is sponsored by BEA Weblogic Workshop
> >>FREE Java Enterprise J2EE developer tools!
> >>Get your free copy of BEA WebLogic Workshop 8.1 today.
> >>http://ads.osdn.com/?ad_id=4721&alloc_id=10040&op=click
> >>_______________________________________________
> >>Gusdev-gusdev mailing list
> >>Gus...@li...
> >>https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
> >>    
> >>
> >
> >  
> >
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by BEA Weblogic Workshop
> FREE Java Enterprise J2EE developer tools!
> Get your free copy of BEA WebLogic Workshop 8.1 today.
> http://ads.osdn.com/?ad_id=4721&alloc_id=10040&op=click
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
-- 
-----------------------------
Pablo Nascimento Mendes
Research Scholar
Kissinger Lab
Department of Genetics
University of Georgia
C210 Life Sciences Bldg.
Athens, Georgia 30602
Phone:706 542-1447
E-mail: pa...@ug...