Opened 6 years ago

Last modified 10 months ago

#12529 new Bug

manage.py syncdb doesn't check tables by using mangled names with Oracle backend

Reported by: jtiai Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: syncdb oracle inspectdb
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

With Oracle syncdb doesn't use mangled names when comparing tablenames to ones existing in database thus trying to create tables that already exists. This leads to

Creating table ssp_service_category_translation
Traceback (most recent call last):
  File "./manage.py", line 19, in <module>
    execute_manager(settings)
  File "../../django-trunk/django/core/management/__init__.py", line 439, in execute_manager
    utility.execute()
  File "../../django-trunk/django/core/management/__init__.py", line 380, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "../../django-trunk/django/core/management/base.py", line 195, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "../../django-trunk/django/core/management/base.py", line 222, in execute
    output = self.handle(*args, **options)
  File "../../django-trunk/django/core/management/base.py", line 351, in handle
    return self.handle_noargs(**options)
  File "../../django-trunk/django/core/management/commands/syncdb.py", line 91, in handle_noargs
    cursor.execute(statement)
  File "../../django-trunk/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "../../django-trunk/django/db/backends/oracle/base.py", line 487, in execute
    raise e
cx_Oracle.DatabaseError: ORA-00955: name is already used by an existing object

Root cause for this is using connection.introspection.table_name_converter() in syncdb main loop which in case of oracle returns just lowercase name and not properly mangled name that would match existing table in database.

Change History (10)

comment:1 Changed 6 years ago by jtiai

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

This happens only when model has db_table defined in Meta:

class ThisIsLongModelThatDoesNotFail(models.Model):
    pass

class ThisFails(models.Model):
    class Meta:
        db_table = 'very_long_table_name_that_will_fail'

Note: This also happens in pre-multidb codebase.

comment:2 Changed 6 years ago by jtiai

It seems that django/db/models/options.py around L105 uses tablename truncate only when table name is not provided.

I think truncate should happen regardless is it provided or not to make models work regardless of DB.

Currently this only is Oracle problem since it's only db backend that has limitation on names.

comment:3 Changed 6 years ago by russellm

  • Triage Stage changed from Unreviewed to Accepted

comment:4 Changed 5 years ago by thejaswi_puthraya

  • Component changed from Uncategorized to Database layer (models, ORM)

comment:5 Changed 4 years ago by mattmcc

  • Severity set to Normal
  • Type set to Bug

comment:6 Changed 4 years ago by ramiro

  • Easy pickings unset
  • Keywords inspectdb added
  • UI/UX unset

comment:7 Changed 19 months ago by manelclos@…

Hi, I've fixed this by changing django.db.backends.oracle.introspection.py

from:

    def table_name_converter(self, name):
        "Table name comparison is case insensitive under Oracle"
        return name.lower()

to:

from django.db.backends import util
    def table_name_converter(self, name):
        "Table name comparison is case insensitive under Oracle"
        max_length = self.connection.ops.max_name_length()
        truncated = util.truncate_name(name.upper(), max_length)
        return truncated.lower()

Is there any problem in this solution?

comment:8 Changed 15 months ago by aaugustin

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

With the introduction of the new migrations framework in Django 1.7, this issue doesn't exist anymore.

comment:9 Changed 10 months ago by manelclos

Hi, I can still reproduce this using Django 1.7 and 1.7.1.

As an example, let's use django-cas' table "django_cas_session_service_ticket". This also happens with easy_thumbails' table "easy_thumbnails_thumbnaildimensions".

The flow is like this:

  • migrate command is called, eventually migrate.py:model_installed() is called:
            def model_installed(model):
                opts = model._meta
                converter = connection.introspection.table_name_converter
                # Note that if a model is unmanaged we short-circuit and never try to install it
                return not ((converter(opts.db_table) in tables) or
                    (opts.auto_created and converter(opts.auto_created._meta.db_table) in tables))
  • here, the table name converter will just return name.lower(), which is clearly NOT the name that was used to create the table.
  • at creation time, the Oracle DatabaseOperations (oracle/base.py) class will truncate the name:
    def quote_name(self, name):
        # SQL92 requires delimited (quoted) names to be case-sensitive.  When
        # not quoted, Oracle has case-insensitive behavior for identifiers, but
        # always defaults to uppercase.
        # We simplify things by making Oracle identifiers always uppercase.
        if not name.startswith('"') and not name.endswith('"'):
            name = '"%s"' % backend_utils.truncate_name(name.upper(),
                                               self.max_name_length())
        # Oracle puts the query text into a (query % args) construct, so % signs
        # in names need to be escaped. The '%%' will be collapsed back to '%' at
        # that stage so we aren't really making the name longer here.
        name = name.replace('%', '%%')
        return name.upper()
  • the check for table existance "(converter(opts.db_table) in tables)" will fail, as tables array contains "django_cas_session_service1144" and not "django_cas_session_service_ticket"
  • with these changes in oracle/introspection.py everything works as expected. imports included in function for easier testing:
    def table_name_converter(self, name):
        "Table name comparison is case insensitive under Oracle"
        from django.db.backends import utils as backend_utils
        from django.db.backends.oracle.base import DatabaseOperations
        self.ops = DatabaseOperations(self)
        name = backend_utils.truncate_name(name.upper(), self.ops.max_name_length())
        return name.lower()

comment:10 Changed 10 months ago by manelclos

  • Resolution fixed deleted
  • Status changed from closed to new
Note: See TracTickets for help on using tickets.
Back to Top