#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)
Change History (15)
by , 6 years ago
Attachment: | 0025_custom_layouts_changes.py added |
---|
by , 6 years ago
Attachment: | 0026_populate_and_match_layout_uuids.py added |
---|
migration 2 - data changes to populate the new uuid fields and references
by , 6 years ago
Attachment: | 0027_example_layout_layout_field_uuids.py added |
---|
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 , 6 years ago
Cc: | added |
---|
comment:2 by , 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 , 6 years ago
Attachment: | bug29496_minimal_project.zip added |
---|
Minimal project demoing the bug, including the migrate failure log that I observe
comment:3 by , 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 , 6 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
Triage Stage: | Unreviewed → Accepted |
comment:5 by , 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 , 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 , 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.
comment:8 by , 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: | Accepted → Ready for checkin |
migration #1 - schema changes to add new uuid fields