Using syncdb with Oracle using synonyms causes ORA-00955.

[ash@…]

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

[Karen Tracey]

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

[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.

[Erin Kelly]

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.

[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.

[Malcolm Tredinnick]

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.

[Anssi Kääriäinen]

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.

[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