Opened 14 years ago

Closed 12 years ago

Last modified 11 years 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: Erin Kelly, Matt Boersma 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 Erin Kelly 14 years ago.

Download all attachments as: .zip

Change History (11)

comment:1 by Karen Tracey, 14 years ago

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

in reply to:  1 comment:2 by anonymous, 14 years ago

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 by Erin Kelly, 14 years ago

Cc: Erin Kelly Matt Boersma added

comment:4 by Łukasz Rekucki, 14 years ago

Keywords: oracle added

by Erin Kelly, 14 years ago

Attachment: 13565.diff added

comment:5 by Erin Kelly, 14 years ago

Has patch: set
Needs tests: set
Triage Stage: UnreviewedDesign 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.

in reply to:  5 comment:6 by dragonpaw, 14 years ago

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 by Julien Phalip, 14 years ago

Severity: Normal
Type: Bug

comment:8 by Malcolm Tredinnick, 13 years ago

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 by Anssi Kääriäinen, 12 years ago

Resolution: wontfix
Status: newclosed

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 by manelclos@…, 11 years ago

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