Re: [Gusdev-gusdev] everything works except commit!

[Michael S. <msa...@pc...>]

Michael--

You'll probably need to grant select privileges on the sequence to 
whatever user (or users) login.  This is dependent on your instance of 
GUS, but for example:

grant select on dots.similarity_SQ to DOTS;

would allow the user DOTS to select on this sequence.

--Mike

On Apr 19, 2004, at 2:09 PM, MICHAEL LUCHTAN wrote:

> Michael-
> Just a little bit of sql help left I think.  I took your suggestion 
> below,
> logged in as sysdba executed the commands below.  Now, a regular user
> can't see the table.  I.E. I get the following error message when 
> running
> the plugin:
> DBD::Oracle::db prepare failed: ORA-00942: table or view does not exist
> (DBD ERROR: OCIStmtExecute/Describe) at
> /var/local/gus_home/lib/perl/GUS/Common/Plugin/LoadBlastSimFast.pm line
> 346, <GEN0> line 1651269.
> Which is in reference to the sql statement:
> select dots.similarity_SQ.NEXTVAL from DUAL
>
> Running sqlplus confirms that a regular user will get that error.  
> Running
> sqlplus as sysdba shows that it is there.
>
> How can I grant permission for folks to view /increase this sequence?
>
> Michael Luchtan
> http://www.cs.uga.edu/~luchtan
>
>
> On Mon, 19 Apr 2004, Michael Saffitz wrote:
>
>>
>> Hi Michael,
>>
>> Your sequences are out of sync with the corresponding tables.  You
>> should definitely continue to use the sequences (you'll avoid many
>> problems down the road), but you'll need to alter sequences so that
>> they start with the proper value.  For example, for the similarity
>> table:
>>
>> (Get the maximum value of the primary key:)
>>
>> select max(SIMILARITY_ID) from dots.similarity;
>>
>> You must then drop and recreate the sequence, setting the sequence to
>> start with the value you got in the query above, plus 5 or so to be
>> safe.  Let's say this is 5067:
>>
>> drop sequence dots.similarity_SQ;
>> create sequence dots.similarity_SQ START WITH 5073;
>>
>> Using the count(*) as the primary key generation is not supported and
>> is likely to produced unexpected results.
>>
>> --Mike
>> CBIL DBA
>>
>> On Apr 19, 2004, at 11:58 AM, MICHAEL LUCHTAN wrote:
>>
>>> Hello everyone-
>>> I have been attempting to load blast similarities into GUS with the
>>> plugin
>>> LoadBlastSimFast.pm after already loading some data in with the 
>>> plugin
>>> LoadBlastSimilarityPK.pm (which was slow compared with the Fasst
>>> version).
>>> Well, the first thing that LoadBlastSimFast.pm does is start
>>> complaining
>>> about PrimaryKey uniqueness constraints being violated.  It used some
>>> fancy Oracle SQL that I didn't understand:
>>> select dots.similarity_SQ.NEXTVAL from DUAL
>>> Which by debugging I found out started at one, and increased each 
>>> time
>>> we
>>> tested the plugin, regardless of commit not being turned on.
>>> Naturally this crashed, because
>>> we already had about 3,000 entries in the dots.similarity table and
>>> we already had a sequence_id with value 1.  There
>>> was likewise a problem with the dots.similarityspan table.
>>>
>>> Since I only having a workingman's knowledge of OracleSQL, I switched
>>> it
>>> so that it starts with
>>> select count(*) from dots.similarity
>>> And increases this by one for each entry.  Likewise for
>>> dots.similarityspan.  This seemed to fix the problem, and the plugin
>>> works
>>> great on each file tested, claims to have entered all the correct 
>>> data,
>>> and exits gracefully.  Yet when I run the same command line with
>>> --commit
>>> appended to the end, we start getting these Primary Key constraints
>>> violated errors again.
>>>
>>> Perhaps I do not understand what happens with commit.  I thought that
>>> these kind of errors would be caught with a regular run without 
>>> commit
>>> turned on.
>>> Any help?
>>>
>>>
>>>
>>>
>>> Michael Luchtan
>>> http://www.cs.uga.edu/~luchtan
>>>
>>>
>>>
>>>
>>>
>>> -------------------------------------------------------
>>> This SF.Net email is sponsored by: IBM Linux Tutorials
>>> Free Linux tutorial presented by Daniel Robbins, President and CEO of
>>> GenToo technologies. Learn everything from fundamentals to system
>>> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
>>> _______________________________________________
>>> Gusdev-gusdev mailing list
>>> Gus...@li...
>>> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>>
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by: IBM Linux Tutorials
>> Free Linux tutorial presented by Daniel Robbins, President and CEO of
>> GenToo technologies. Learn everything from fundamentals to system
>> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
>> _______________________________________________
>> Gusdev-gusdev mailing list
>> Gus...@li...
>> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>>