Oracle with sequence for primary key and that sequence out of sync

[Scott Koranda]

Hello,

I am using SQLAlchemy 0.9.4 with Python 2.6.6 on RHEL 6.5 with
Oracle 11.2.0.3.

My simple application queries an LDAP directory to find
particular records and then reflects the information into an
Oracle table. The application is designed to keep the database
table version of the information in sync with the LDAP
directory as the system of record.

Here is some pseudocode that shows the basic outline of what I
am doing:

class AdminGroup(Base):
__tablename__ = 'admin_groups'

id = Column(Integer, Sequence('seq_admin_groups_id'), primary_key = True)
dn = Column(String)
name = Column(String)
display_name = Column(String)
primary_mail = Column(String)

def __repr__(self):
return "<AdminGroup(dn='%s',name='%s',display_name='%s',primary_mail='%s')>"
% (
self.dn, self.name, self.display_name, self.primary_mail)


db_engine = create_engine(...)
SessionClass = sessionmaker(bind = db_engine)

# loop every N minutes
session = SessionClass()

ldap_groups = ldap_connection.search_ext_s(...)

for dn, attr_dict in ldap_groups:

# see if group is represented in database table
admin_group = session.query(AdminGroup).filter(AdminGroup.dn
== "%s" % dn).first()

if admin_group:
# group exists so see if any updates needed

else:
# create the group in the database table
admin_group = AdminGroup(dn = dn, name = name, ...)
session.add(admin_group)

# done with all groups so commit and sleep until next iteration
session.commit()

Note that the class AdminGroup() is written to use the
sequence seq_admin_groups_id to generate the primary key.

I created the sequence in the Oracle database using sqlplus
and the command:

CREATE SEQUENCE seq_admin_groups_id START WITH 1 INCREMENT BY 1;
COMMIT;

I then ran the Python code and it performed as I expected,
creating roughly 500 rows in the table. It continued to run
fine for a few days, creating and deleting rows now and then.

After a few days the code began throwing this exception:

2014-05-30 16:32:24,337 ERROR Master: Caught database
exception while provisioning: (raised as a result of
Query-invoked autoflush; consider using a session.no_autoflush
block if this flush is occuring prematurely) (IntegrityError)
ORA-00001: unique constraint (MYSCHEMA.SYS_C0015706) violated
'INSERT INTO admin_groups (id, dn, name, display_name,
primary_mail) VALUES (seq_admin_groups_id.nextval, :dn, :name,
:display_name, :primary_mail) RETURNING admin_groups.id
INTO :ret_0' {'dn':
'cn=somename,ou=groups,dc=bc,dc=edu',
'primary_mail': 'some...@my.edu',
'display_name': 'somename', 'name':
'somename', 'ret_0': <cx_Oracle.NUMBER with value None>}

I investigated and found that the sequence seq_admin_groups_id
was now at the value 68 after having been used previously to
insert rows with IDs in the 500s.

I stopped the code and used sqlplus to change the sequence
back to a value in the high 500s. I then restarted the code
and the exception no longer occurred.

I am unable to explain how the sequence seq_admin_groups_id
went from in the 500s and working fine to suddenly being 68.

The only place in the Python code where the sequence is used
explicitly is in the definition of the AdminGroup() class.

I would be grateful for any insights on how the sequence might
have become out of sync or anything I can change in the code
to prevent it from happening again.

Thank you for your consideration.

Scott

P.S. I should add that I am using cx_Oracle and the connection
string

oracle+cx_oracle://user:password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=somehost)(PORT=1521)))(CONNECT_DATA=(SID=MYSID)))

[Michael Bayer]

On Jun 2, 2014, at 1:17 PM, Scott Koranda <skor...@gmail.com> wrote:

>
> I investigated and found that the sequence seq_admin_groups_id
> was now at the value 68 after having been used previously to
> insert rows with IDs in the 500s.
>
> I stopped the code and used sqlplus to change the sequence
> back to a value in the high 500s. I then restarted the code
> and the exception no longer occurred.
>
> I am unable to explain how the sequence seq_admin_groups_id
> went from in the 500s and working fine to suddenly being 68.
>
> The only place in the Python code where the sequence is used
> explicitly is in the definition of the AdminGroup() class.
>
> I would be grateful for any insights on how the sequence might
> have become out of sync or anything I can change in the code
> to prevent it from happening again.

there’s nothing on the Python library side that could do that, someone had to have run an ALTER SEQUENCE on the database side in order for that to happen.

[Ofir Herzas]

Sorry to barge in, but I'm having the exact same issue and I'm pretty sure no one altered the sequence manually.

I'm using sqlalchemy 0.8, python 2.7.6, rhel 6.5, oracle 10g, cx_oracle with the same connection string as above

This issue started just recently after running ok for more than a thousand times.

I should also say that my application is installed at several customers, and I have this issue only at the one using Oracle.

[Mike Bayer]

On 6/25/14, 5:50 AM, Ofir Herzas wrote:

Sorry to barge in, but I'm having the exact same issue and I'm pretty sure no one altered the sequence manually.

I'm using sqlalchemy 0.8, python 2.7.6, rhel 6.5, oracle 10g, cx_oracle with the same connection string as above

This issue started just recently after running ok for more than a thousand times.

I should also say that my application is installed at several customers, and I have this issue only at the one using Oracle.

I'm open to possibilities for how this could happen, short of the "ALTER SEQUENCE" command being emitted, in which case feel free to grep SQLAlchemy's codebase for this clause (it's not there).   From my vantage point, if there is actually an issue that is implicitly making this happen outside of application code, it would have to be on the driver or server side somehow.

On Tuesday, June 3, 2014 1:18:42 AM UTC+3, Michael Bayer wrote:

On Jun 2, 2014, at 1:17 PM, Scott Koranda <skor...@gmail.com> wrote:

>
> I investigated and found that the sequence seq_admin_groups_id
> was now at the value 68 after having been used previously to
> insert rows with IDs in the 500s.
>
> I stopped the code and used sqlplus to change the sequence
> back to a value in the high 500s. I then restarted the code
> and the exception no longer occurred.
>
> I am unable to explain how the sequence seq_admin_groups_id
> went from in the 500s and working fine to suddenly being 68.
>
> The only place in the Python code where the sequence is used
> explicitly is in the definition of the AdminGroup() class.
>
> I would be grateful for any insights on how the sequence might
> have become out of sync or anything I can change in the code
> to prevent it from happening again.

there’s nothing on the Python library side that could do that, someone had to have run an ALTER SEQUENCE on the database side in order for that to happen.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Ofir Herzas]

Didn't say it was your responsibility, just thought you should know there might be an issue there.

I'll modify the sequence manually and get back to it should the problem persist.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/4iAInBP6iyI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Jonathan Vanasco]

On Monday, June 2, 2014 6:18:42 PM UTC-4, Michael Bayer wrote:

there’s nothing on the Python library side that could do that, someone had to have run an ALTER SEQUENCE on the database side in order for that to happen.

I don't even think you could use ALTER SEQUENCE in this situation.  I "fondly" remember Oracle sequences needing to be dropped and re-added with a lot of NEXTVALs... or using chunks of PL/SQL scripts to otherwise modify the sequence.

Doing a quick look online, it seems like things still work the same way.  

If I were in this situation, I would enable a lot of query and connection logging, and then comb through the logs to see what was happening.    For a variety of reasons, this doesn't seem like it should be possible.

Is it possible that some records were being inserted with a numeric id that was originally generated by .nextval() in your app, but has since been incremented only in Python ?