Opened 5 years ago

Closed 2 years ago

Last modified 17 months ago

#13565 closed Bug (wontfix)

Using syncdb with Oracle using synonyms causes ORA-00955.

Reported by: ash@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Normal Keywords: oracle
Cc: ikelly, mboersma Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When working with legacy databases and Django, Oracle has a nice feature to alias a table to match what Django thinks it should be. (Which is absolutely needed when the table you're referencing is in another user's schema.)

Unfortunately, the table creation SQL doesn't detect the alias, and sees the table as missing. When it tries to create it, you get a ORA-00955 error. (Name is already used by an existing object).

Creating table users_foo
Traceback (most recent call last):
  File "./manage.py", line 11, in <module>
    execute_manager(settings)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2-py2.6.egg/django/core/management/__init__.py", line 438, in execute_manager
    utility.execute()
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2-py2.6.egg/django/core/management/__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2-py2.6.egg/django/core/management/base.py", line 191, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2-py2.6.egg/django/core/management/base.py", line 218, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2-py2.6.egg/django/core/management/base.py", line 347, in handle
    return self.handle_noargs(**options)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2-py2.6.egg/django/core/management/commands/syncdb.py", line 95, in handle_noargs
    cursor.execute(statement)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2-py2.6.egg/django/db/backends/util.py", line 15, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2-py2.6.egg/django/db/backends/oracle/base.py", line 507, in execute
    return self.cursor.execute(query, self._param_generator(params))
django.db.utils.DatabaseError: ORA-00955: name is already used by an existing object

Attachments (1)

13565.diff (730 bytes) - added by ikelly 5 years ago.

Download all attachments as: .zip

Change History (11)

comment:1 follow-up: Changed 5 years ago by kmtracey

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

This sounds like a good use case for the model Meta managed=False option?

comment:2 in reply to: ↑ 1 Changed 5 years ago by anonymous

Replying to kmtracey:

This sounds like a good use case for the model Meta managed=False option?

Yes, setting it as not managed is a viable work around. But it would be nice if one didn't have to do that to a bunch of tables that really already exist. Other DB backends are able to detect existing tables fine, making this an Oracle-specific oddity.

Alternatively, just change syncdb to catch and ignore the ORA-00955 error and continue on, as the error can only mean that the item already exists, and syncdb is supposed to ignore existing tables.

comment:3 Changed 5 years ago by ikelly

  • Cc ikelly mboersma added

comment:4 Changed 5 years ago by lrekucki

  • Keywords oracle added

Changed 5 years ago by ikelly

comment:5 follow-up: Changed 5 years ago by ikelly

  • Has patch set
  • Needs tests set
  • Triage Stage changed from Unreviewed to Design decision needed

The patch that I just uploaded fixes this issue by allowing the introspection layer to recognize views and synonyms as existing tables, which is probably the right way to go about it. However, this also breaks commands like reset and sqlclear since Django now tries to drop those tables automatically and fails. That makes me question whether this is really something we want to do.

Comparing the behavior of the other backends, sqlite3 does the same thing as oracle -- syncdb fails with an error if a defined model is implemented as a view. Mysql runs syncdb successfully but fails on reset or sqlclear. I didn't test postgresql because it doesn't seem to be correctly installed on our test server just now.

Ultimately, I think that marking the affected models as unmanaged should not be viewed as a "workaround" in this circumstance, but as the correct thing to do.

comment:6 in reply to: ↑ 5 Changed 5 years ago by dragonpaw

Replying to ikelly:

The patch that I just uploaded fixes this issue by allowing the introspection layer to recognize views and synonyms as existing tables, which is probably the right way to go about it. However, this also breaks commands like reset and sqlclear since Django now tries to drop those tables automatically and fails. That makes me question whether this is really something we want to do.

Maybe the best solution is to have the existence of a managed table as a synonym be a clearly unsupported combination with a proper error message for it, instead of the 'object exists' one you get now. Something like:

Error: Table xxxxxx already exists as a synonym, but is set as managed. Either remove the synonym, or set 'managed = False' in the Meta.

comment:7 Changed 4 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:8 Changed 4 years ago by mtredinnick

  • Easy pickings unset
  • UI/UX unset

Ian, the concerns you have about your patch seem to rule it out. We shouldn't be in a position to accidentally nuke existing data. Ever. This isn't identical to the case where something like the PostgreSQL backend detects it has already created a table it would otherwise create anyway. In this case, the table's lifecycle is outside of Django... it's exactly the use case for Meta.managed=False: tell Django not to deal with that table.

The argument that this requires an extra line of code when working with a legacy database that cannot be changed and where the table name can't be set in Django doesn't warrant risking destroying external data/tables in my view. It's an edge-case with a solution that already exists.

I would vote not to add this feature request.

comment:9 Changed 2 years ago by akaariai

  • Resolution set to wontfix
  • Status changed from new to closed

My vote is managed=False, too. You will want to have this very likely for other purposes (no truncation by test runner for example). Closing as wontfix.

comment:10 Changed 17 months ago by manelclos@…

Another way to skip table creation is to create a database router and return False for some models or apps. For example:

    def allow_syncdb(self, db, model):
        if model._meta.app_label in ('auth', 'contenttypes', 'sessions', 'django_cas'):
            return False
Note: See TracTickets for help on using tickets.
Back to Top