Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#29496 closed Bug (fixed)

Altering non-unique field to primary key fails on Oracle

Reported by: Peter Levi Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 2.0
Severity: Normal Keywords: orm, oracle, alterfield, primary_key
Cc: Mariusz Felisiak Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I am trying to migrate a table from an auto-incrementing Integer PK to a UUID PK.
I have custom-written migrations that add the new UUID field, add new foreign key fields in dependent tables, populate them, remove the old FKs, etc.
One of the final steps of the process is to alter the new uuid field in the table to be a primary_key:

        migrations.AlterField(
            model_name='examplelayout',
            name='uuid',
            field=models.UUIDField(default=uuid.uuid4, primary_key=True, editable=False),
        ),

The whole process works great on Postgres, but this specific step ends up in an error on Oracle 12, despite the fact that before this step there are no indexes in the DB on this field.

query = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT "VARIO_EXA_UUID_8498C9C8_P" PRIMARY KEY ("UUID")'
...
django.db.utils.DatabaseError: ORA-02261: such unique or primary key already exists in the table

The issue seems to be that Django tries to first add a UNIQUE constraint for the field, then a PRIMARY KEY on the same field. Here are all the SQL queries executed by this specific migration step:

==================== STEP =========================  4  =====================


[2018-06-15 08:51:11.141 | DEBUG | django.db.backends.schema:124] ALTER TABLE "VARIO_EXAMPLELAYOUT" MODIFY "UUID" DEFAULT '9cf3fe16f11d4fa09b62e337c50ae5f7'; (params [])
[2018-06-15 08:51:11.212 | DEBUG | django.db.backends:111] (0.071) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" MODIFY "UUID" DEFAULT \'9cf3fe16f11d4fa09b62e337c50ae5f7\'' - PARAMS = (); args=[]
[2018-06-15 08:51:11.212 | DEBUG | django.db.backends.schema:124] UPDATE "VARIO_EXAMPLELAYOUT" SET "UUID" = %s WHERE "UUID" IS NULL; (params ['347ac08714bd4c208ffd2f79e7932bb3'])
[2018-06-15 08:51:11.269 | DEBUG | django.db.backends:111] (0.056) QUERY = 'UPDATE "VARIO_EXAMPLELAYOUT" SET "UUID" = :arg0 WHERE "UUID" IS NULL' - PARAMS = ('347ac08714bd4c208ffd2f79e7932bb3',); args=['347ac08714bd4c208ffd2f79e7932bb3']
[2018-06-15 08:51:11.269 | DEBUG | django.db.backends.schema:124] ALTER TABLE "VARIO_EXAMPLELAYOUT" MODIFY "UUID" NOT NULL; (params [])
[2018-06-15 08:51:11.343 | DEBUG | django.db.backends:111] (0.073) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" MODIFY "UUID" NOT NULL' - PARAMS = (); args=[]
[2018-06-15 08:51:11.343 | DEBUG | django.db.backends.schema:124] ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT vario_exa_uuid_8498c9c8_u UNIQUE ("UUID"); (params ())
[2018-06-15 08:51:11.437 | DEBUG | django.db.backends:111] (0.094) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT vario_exa_uuid_8498c9c8_u UNIQUE ("UUID")' - PARAMS = (); args=()
[2018-06-15 08:51:11.792 | DEBUG | django.db.backends:111] (0.354) QUERY = "\n            SELECT\n                user_constraints.constraint_name,\n                LISTAGG(LOWER(cols.column_name), ',') WITHIN GROUP (ORDER BY cols.position),\n                CASE user_constraints.constraint_type\n                    WHEN 'P' THEN 1\n                    ELSE 0\n                END AS is_primary_key,\n                CASE\n                    WHEN user_constraints.constraint_type IN ('P', 'U') THEN 1\n                    ELSE 0\n                END AS is_unique,\n                CASE user_constraints.constraint_type\n                    WHEN 'C' THEN 1\n                    ELSE 0\n                END AS is_check_constraint\n            FROM\n                user_constraints\n            LEFT OUTER JOIN\n                user_cons_columns cols ON user_constraints.constraint_name = cols.constraint_name\n            WHERE\n                user_constraints.constraint_type = ANY('P', 'U', 'C')\n                AND user_constraints.table_name = UPPER(:arg0)\n            GROUP BY user_constraints.constraint_name, user_constraints.constraint_type\n        " - PARAMS = ('vario_examplelayout',); args=['vario_examplelayout']
[2018-06-15 08:51:12.480 | DEBUG | django.db.backends:111] (0.688) QUERY = "\n            SELECT\n                cons.constraint_name,\n                LISTAGG(LOWER(cols.column_name), ',') WITHIN GROUP (ORDER BY cols.position),\n                LOWER(rcols.table_name),\n                LOWER(rcols.column_name)\n            FROM\n                user_constraints cons\n            INNER JOIN\n                user_cons_columns rcols ON rcols.constraint_name = cons.r_constraint_name AND rcols.position = 1\n            LEFT OUTER JOIN\n                user_cons_columns cols ON cons.constraint_name = cols.constraint_name\n            WHERE\n                cons.constraint_type = 'R' AND\n                cons.table_name = UPPER(:arg0)\n            GROUP BY cons.constraint_name, rcols.table_name, rcols.column_name\n        " - PARAMS = ('vario_examplelayout',); args=['vario_examplelayout']
[2018-06-15 08:51:13.695 | DEBUG | django.db.backends:111] (1.214) QUERY = "\n            SELECT\n                ind.index_name,\n                LOWER(ind.index_type),\n                LISTAGG(LOWER(cols.column_name), ',') WITHIN GROUP (ORDER BY cols.column_position),\n                LISTAGG(cols.descend, ',') WITHIN GROUP (ORDER BY cols.column_position)\n            FROM\n                user_ind_columns cols, user_indexes ind\n            WHERE\n                cols.table_name = UPPER(:arg0) AND\n                NOT EXISTS (\n                    SELECT 1\n                    FROM user_constraints cons\n                    WHERE ind.index_name = cons.index_name\n                ) AND cols.index_name = ind.index_name\n            GROUP BY ind.index_name, ind.index_type\n        " - PARAMS = ('vario_examplelayout',); args=['vario_examplelayout']
[2018-06-15 08:51:13.696 | DEBUG | django.db.backends.schema:124] ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT "VARIO_EXA_UUID_8498C9C8_P" PRIMARY KEY ("UUID"); (params ())
[2018-06-15 08:51:13.775 | DEBUG | django.db.backends:111] (0.079) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT "VARIO_EXA_UUID_8498C9C8_P" PRIMARY KEY ("UUID")' - PARAMS = (); args=()
Traceback (most recent call last):
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/oracle/base.py", line 500, in execute
    return self.cursor.execute(query, self._param_generator(params))
cx_Oracle.DatabaseError: ORA-02261: such unique or primary key already exists in the table

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 35, in <module>
    execute_from_command_line(sys.argv)
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/__init__.py", line 371, in execute_from_command_line
    utility.execute()
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/__init__.py", line 365, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/base.py", line 335, in execute
    output = self.handle(*args, **options)
  File "/var/www/forms/forms/common/management/commands/migrate.py", line 54, in handle
    super().handle(*args, **options)
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/commands/migrate.py", line 200, in handle
    fake_initial=fake_initial,
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/migration.py", line 122, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/operations/fields.py", line 216, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/oracle/schema.py", line 59, in alter_field
    super().alter_field(model, old_field, new_field, strict)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 525, in alter_field
    old_db_params, new_db_params, strict)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 725, in _alter_field
    "columns": self.quote_name(new_field.column),
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 133, in execute
    cursor.execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/raven/contrib/django/client.py", line 127, in execute
    return real_execute(self, sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/var/www/forms/forms/forms/db/backends/utils.py", line 16, in execute
    return super().execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/raven/contrib/django/client.py", line 127, in execute
    return real_execute(self, sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/oracle/base.py", line 500, in execute
    return self.cursor.execute(query, self._param_generator(params))
django.db.utils.DatabaseError: ORA-02261: such unique or primary key already exists in the table

Notice the two calls:

[2018-06-15 08:51:11.437 | DEBUG | django.db.backends:111] (0.094) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT vario_exa_uuid_8498c9c8_u UNIQUE ("UUID")' - PARAMS = (); args=()

and a bit below:

[2018-06-15 08:51:13.775 | DEBUG | django.db.backends:111] (0.079) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT "VARIO_EXA_UUID_8498C9C8_P" PRIMARY KEY ("UUID")' - PARAMS = (); args=()
...which raises the error

The only other operations before this on this field that I have in one of the previous migrations is to actually add this UUID field as a nullable, non-unique field, and then in a data migration to populate it with a unique uuids for all ExampleLayouts:

        # prepare to migrate to UUID PKs for ExampleLayout and LayoutField
        migrations.AddField(
            model_name='examplelayout',
            name='uuid',
            field=models.UUIDField(null=True, default=uuid.uuid4),
        ),

For completeness, the initial models look like this:

class ExampleLayout(models.Model):
    image = models.OneToOneField(
        Image, on_delete=models.CASCADE, related_name='example_layout')
    doc_type = models.ForeignKey(
        DocumentType, on_delete=models.CASCADE, related_name='layouts')


class LayoutField(models.Model):
    core_field_type = models.ForeignKey(
        CoreFieldType, on_delete=models.CASCADE, related_name='layout_fields')
    layout = models.ForeignKey(
        ExampleLayout, on_delete=models.CASCADE, related_name='fields')
    bounding_box = BoundingBoxCharField()
    transcription = models.CharField(max_length=128)

while their final "target" version that I am trying to migrate to is this:

class ExampleLayout(models.Model):
    uuid = models.UUIDField(
        default=uuid.uuid4, primary_key=True, editable=False)
    image = models.OneToOneField(
        Image, on_delete=models.CASCADE, related_name='example_layout')
    doc_type = models.ForeignKey(
        DocumentType, on_delete=models.CASCADE, related_name='layouts')


class LayoutField(models.Model):
    uuid = models.UUIDField(
        default=uuid.uuid4, primary_key=True, editable=False)
    core_field_type = models.ForeignKey(
        CoreFieldType, on_delete=models.CASCADE, related_name='layout_fields')
    layout = models.ForeignKey(
        ExampleLayout, on_delete=models.CASCADE, related_name='fields')
    bounding_box = BoundingBoxCharField()
    transcription = models.CharField(max_length=128)

Attachments (5)

0025_custom_layouts_changes.py (893 bytes ) - added by Peter Levi 6 years ago.
migration #1 - schema changes to add new uuid fields
0026_populate_and_match_layout_uuids.py (851 bytes ) - added by Peter Levi 6 years ago.
migration 2 - data changes to populate the new uuid fields and references
0027_example_layout_layout_field_uuids.py (2.2 KB ) - added by Peter Levi 6 years ago.
migration 3 - schema changes to remove old ID fields, rename temporary FKs back to the original names, and introduce the primary_keys. Fails on step 4.
bug29496_minimal_project.zip (7.6 KB ) - added by Peter Levi 6 years ago.
Minimal project demoing the bug, including the migrate failure log that I observe
29496.diff (2.0 KB ) - added by Mariusz Felisiak 6 years ago.
Test.

Download all attachments as: .zip

Change History (15)

by Peter Levi, 6 years ago

migration #1 - schema changes to add new uuid fields

by Peter Levi, 6 years ago

migration 2 - data changes to populate the new uuid fields and references

by Peter Levi, 6 years ago

migration 3 - schema changes to remove old ID fields, rename temporary FKs back to the original names, and introduce the primary_keys. Fails on step 4.

comment:1 by Mariusz Felisiak, 6 years ago

Cc: Mariusz Felisiak added

comment:2 by Tim Graham, 6 years ago

Can you provide a minimal project that reproduces the issue (or ideally, a test for Django's test suite, which would probably go in tests/schema)?

by Peter Levi, 6 years ago

Minimal project demoing the bug, including the migrate failure log that I observe

comment:3 by Peter Levi, 6 years ago

Added a minimal project which exhibits the issue. Single model, single field in it. We start with an auto-created PK, then we try to make the single UUID field to be the PK.
The migration was auto-generated with makemigrations, then edited to show individual steps in the log. log from the failing migrate call included as well.

Thanks and regards,
Peter

comment:4 by Mariusz Felisiak, 6 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned
Triage Stage: UnreviewedAccepted

by Mariusz Felisiak, 6 years ago

Attachment: 29496.diff added

Test.

comment:5 by Mariusz Felisiak, 6 years ago

Summary: AlterField with primary_key=True fails on Oracle 12 with "such unique or primary key already exists in the table"Alter nullable UUID field to primary key fails on Oracle.

I reproduced this issue and prepared test in our test suite.

comment:6 by Mariusz Felisiak, 6 years ago

Summary: Alter nullable UUID field to primary key fails on Oracle.Alter not unique field to primary key fails on Oracle.

comment:7 by Mariusz Felisiak, 6 years ago

Has patch: set

Oracle creates UNIQUE constraints implicite when we add PRIMARY KEY constraints, moreover it is not so clever to match them if they already exist, that's why it throws: ORA-02261: such unique or primary key already exists in the table.

PR

comment:8 by Tim Graham, 6 years ago

Summary: Alter not unique field to primary key fails on Oracle.Altering non-unique field to primary key fails on Oracle
Triage Stage: AcceptedReady for checkin

comment:9 by GitHub <noreply@…>, 6 years ago

Resolution: fixed
Status: assignedclosed

In 6dd4edb1:

Fixed #29496 -- Fixed crash on Oracle when converting a non-unique field to primary key.

Thanks Tim Graham for the review.

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 6 years ago

In d2ca28db:

[2.1.x] Fixed #29496 -- Fixed crash on Oracle when converting a non-unique field to primary key.

Thanks Tim Graham for the review.
Backport of 6dd4edb1b4f5441c5f543e29395039839c50d10b from master

Note: See TracTickets for help on using tickets.
Back to Top