Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#28846 closed Bug (fixed)

SQLite schema editor clears deferred SQL on operation requiring table rebuilds

Reported by: Yevhen Kozlov Owned by: nobody
Component: Migrations Version: 1.11
Severity: Normal Keywords: SQLite, migration, manyToMany
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Yevhen Kozlov)

  1. create a models.py, makemigrations for them
from django.db import models

class Test(models.Model):
	pass

class Test2(models.Model):
	title = models.CharField(max_length=50)
  1. add manyToMany field only, makemigrations:
from django.db import models

class Test(models.Model):
	pass

class Test2(models.Model):
	title = models.CharField(max_length=50)
	tests = models.ManyToManyField(Test)


Check with sqlmigrate there is table ..._test2_test with UNIQUE INDEX:

CREATE TABLE "m2m_test2_tests" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "test2_id" integer NOT NULL REFERENCES "m2m_test2" ("id"), "test_id" integer NOT NULL REFERENCES "m2m_test" ("id"));
CREATE UNIQUE INDEX "m2m_test2_tests_test2_id_test_id_c4708311_uniq" ON "m2m_test2_tests" ("test2_id", "test_id");
CREATE INDEX "m2m_test2_tests_test2_id_de2c1678" ON "m2m_test2_tests" ("test2_id");
CREATE INDEX "m2m_test2_tests_test_id_9723a1f8" ON "m2m_test2_tests" ("test_id");
  1. drop migration created at step 1 and change title field additionally(say, set max_length=100)
from django.db import models

class Test(models.Model):
	pass

class Test2(models.Model):
	title = models.CharField(max_length=100)
	tests = models.ManyToManyField(Test)

Then run makemigrations and check its output:

BEGIN;
--
-- Add field tests to test2
--
CREATE TABLE "m2m_test2_tests" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "test2_id" integer NOT NULL REFERENCES "m2m_test2" ("id"), "test_id" integer NOT NULL REFERENCES "m2m_test" ("id"));
--
-- Alter field title on test2
--
ALTER TABLE "m2m_test2" RENAME TO "m2m_test2__old";
CREATE TABLE "m2m_test2" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "title" varchar(50) NOT NULL);
INSERT INTO "m2m_test2" ("id", "title") SELECT "id", "title" FROM "m2m_test2__old";
DROP TABLE "m2m_test2__old";
COMMIT;

So no UNIQUE constraint is generated for automatically created table if there is any other field changed in the same migration.
At least it happens as for SQLite

Change History (11)

comment:1 by Simon Charette, 7 years ago

In order to facilitate reproduction could you precise what you mean by _drop migration created at step 1_.

Does that mean deleting the migration file and dropping the database? Are you unapplying the migration before deleting it? Can you reproduce from starting from the second model state (step 2.)?

Thanks!

comment:2 by Yevhen Kozlov, 7 years ago

Description: modified (diff)

in reply to:  1 comment:3 by Yevhen Kozlov, 7 years ago

Replying to Simon Charette:

In order to facilitate reproduction could you precise what you mean by _drop migration created at step 1_.

Does that mean deleting the migration file and dropping the database? Are you unapplying the migration before deleting it? Can you reproduce from starting from the second model state (step 2.)?

Thanks!

Sorry for unclear description.

When I've run into that initially I applied and unapplied migrations.
While reproducing that on smaller code I went in different way: makemigrations to generate migration then sqlmigrate to see SQL code generated; this way I did not applied or unapplied migrations.
So I was literally deleting migration file on step No 2.

Actually it is not even required to have step No 1.
I included that just to demonstrate UNIQUE index is created properly until I want to change some other field in the same migration.

in reply to:  description comment:4 by Yevhen Kozlov, 7 years ago

have just realized that happens for SQLite; it could be database-specific

comment:5 by Yevhen Kozlov, 7 years ago

Description: modified (diff)
Keywords: SQLite migration manyToMany added
Summary: autogenerated table for ManyToMany relation does not include UNIQUE constraint if any other field is changedautogenerated table for ManyToMany relation does not include UNIQUE constraint if any other field is changed(SQLite)

in reply to:  description comment:6 by Yevhen Kozlov, 7 years ago

I've not noticed that initially but ordinary indexes are not created also. I believe it's because UNIQUE constraint and INDEX should be created last through .deferred_sql list.

comment:7 by Simon Charette, 7 years ago

I'm afraid you'll have to be more specific here. Could you described the exact set of commands and operations you ran.

I can't reproduce with the following with the following operations.

  1. Define the two models.
  2. Run makemigrations
  3. Alter the second model's title field
  4. Run makemigrations
  5. Inspect sqlmigrate 0001 output
  6. Inspect sqlmigrate 0002 output

Here's the test application I used: https://github.com/charettes/django-ticketing/compare/ticket-28846

Version 0, edited 7 years ago by Simon Charette (next)

comment:8 by Simon Charette, 7 years ago

Triage Stage: UnreviewedAccepted

Actually I managed to reproduce. I somehow missed that the ManyToManyField was added in the second migration.

comment:9 by Simon Charette, 7 years ago

Summary: autogenerated table for ManyToMany relation does not include UNIQUE constraint if any other field is changed(SQLite)SQLite schema editor clears deferred SQL on operation requiring table rebuilds

comment:10 by Simon Charette, 7 years ago

Resolution: fixed
Status: newclosed

The issue is already fixed on master and in the upcoming 2.0 release by 3b429c96736b8328c40e5d77282b0d30de563c3c (#25530).

comment:11 by Yevhen Kozlov, 7 years ago

Thank you @Simon Charette

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