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

[MICHAEL L. <lu...@cs...>]

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
>