syncdb fails in Oracle when orphaned sequences exist

[elamrtus]

when syncdb is run against oracle, it creates one table and crashes, throwing an exception:

Creating table auth_permission
Traceback (most recent call last):
  File "./manage.py", line 11, in <module>
    execute_manager(settings)
  File "/usr/lib/python2.5/site-packages/django/core/management/__init__.py", line 272, in execute_manager
    utility.execute()
  File "/usr/lib/python2.5/site-packages/django/core/management/__init__.py", line 219, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/lib/python2.5/site-packages/django/core/management/base.py", line 72, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/lib/python2.5/site-packages/django/core/management/base.py", line 86, in execute
    output = self.handle(*args, **options)
  File "/usr/lib/python2.5/site-packages/django/core/management/base.py", line 168, in handle
    return self.handle_noargs(**options)
  File "/usr/lib/python2.5/site-packages/django/core/management/commands/syncdb.py", line 76, in handle_noargs
    cursor.execute(statement)
  File "/usr/lib/python2.5/site-packages/django/db/backends/util.py", line 18, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python2.5/site-packages/django/db/backends/oracle/base.py", line 299, in execute
    return Database.Cursor.execute(self, query, params)
cx_Oracle.DatabaseError: ORA-00955: name is already used by an existing object

This happens for every table

Then, when all tables are created, i run it again and get:

Traceback (most recent call last):
  File "./manage.py", line 11, in <module>
    execute_manager(settings)
  File "/usr/lib/python2.5/site-packages/django/core/management/__init__.py", line 272, in execute_manager
    utility.execute()
  File "/usr/lib/python2.5/site-packages/django/core/management/__init__.py", line 219, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/lib/python2.5/site-packages/django/core/management/base.py", line 72, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/lib/python2.5/site-packages/django/core/management/base.py", line 86, in execute
    output = self.handle(*args, **options)
  File "/usr/lib/python2.5/site-packages/django/core/management/base.py", line 168, in handle
    return self.handle_noargs(**options)
  File "/usr/lib/python2.5/site-packages/django/core/management/commands/syncdb.py", line 97, in handle_noargs
    emit_post_sync_signal(created_models, verbosity, interactive)
  File "/usr/lib/python2.5/site-packages/django/core/management/sql.py", line 491, in emit_post_sync_signal
    verbosity=verbosity, interactive=interactive)
  File "/usr/lib/python2.5/site-packages/django/dispatch/dispatcher.py", line 360, in send
    **named
  File "/usr/lib/python2.5/site-packages/django/dispatch/robustapply.py", line 47, in robustApply
    return receiver(*arguments, **named)
  File "/usr/lib/python2.5/site-packages/django/contrib/auth/management.py", line 26, in create_permissions
    ctype = ContentType.objects.get_for_model(klass)
  File "/usr/lib/python2.5/site-packages/django/contrib/contenttypes/models.py", line 28, in get_for_model
    defaults = {'name': smart_unicode(opts.verbose_name_raw)},
  File "/usr/lib/python2.5/site-packages/django/db/models/manager.py", line 85, in get_or_create
    return self.get_query_set().get_or_create(**kwargs)
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 226, in get_or_create
    obj.save()
  File "/usr/lib/python2.5/site-packages/django/db/models/base.py", line 272, in save
    self.save_base()
  File "/usr/lib/python2.5/site-packages/django/db/models/base.py", line 329, in save_base
    result = manager._insert(values, return_id=update_pk)
  File "/usr/lib/python2.5/site-packages/django/db/models/manager.py", line 127, in _insert
    return insert_query(self.model, values, **kwargs)
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 729, in insert_query
    return query.execute_sql(return_id)
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/subqueries.py", line 300, in execute_sql
    cursor = super(InsertQuery, self).execute_sql(None)
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py", line 1466, in execute_sql
    cursor.execute(sql, params)
  File "/usr/lib/python2.5/site-packages/django/db/backends/util.py", line 18, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python2.5/site-packages/django/db/backends/oracle/base.py", line 299, in execute
    return Database.Cursor.execute(self, query, params)
cx_Oracle.DatabaseError: ORA-01400: cannot insert NULL into ("ORACLE"."DJANGO_CONTENT_TYPE"."ID")

[elamrtus]

this happened because i already had run the syncdb, and found and error, then deleted the tables and ran it again. I had not deleted the "sequences" and the ORA-00955: name is already used by an existing object means that it is trying to create a sequence that already existed.

[Erin Kelly]

The root of the problem here is that the management code is structured to only introspect whether the table exists, not whether the table, sequence, and trigger all exist. In other backends, this is generally fine, because one or more of the following are true:

• The sequence/trigger is unnecessary.
• The sequence is automatically created or dropped when the table is created or dropped.
• If an error occurs during syncdb, the transaction is rolled back. This doesn't work in Oracle, since DDL can't be rolled back.

[cmarshal]

I've replaced the CREATE SEQUENCE statement with a PL/SQL block that checks for existence before creating. If someone's gone and done manual DDL (which causes this condition), I think its safer to reuse the pre-existing sequence than to drop it and re-create.

[Matt Boersma]

(In [8522]) Fixed #7628 -- Oracle backend won't try to recreate existing sequences during syncdb. Thanks, cmarshal.

[Jacob]

Milestone 1.0 deleted