Ticket #601 (closed defect: fixed)

Opened 4 years ago

Last modified 8 months ago

Unable to remove UNIQUE from field with South 0.72

Reported by: shawn@… Owned by: andrew
Priority: major Milestone: 1.0
Component: migrations Version: 0.7.2
Keywords: UNIQUE Cc:

Description

I've created a schemamigration which removes UNIQUE and 'required' from a field. When I try to run 'migrate,' it blows up, claiming no UNIQUE constraint found. However, the UNIQUE constraint does exist in Postgres.

From schemamigration forwards():

db.delete_unique('core_site', site_number?)
db.alter_column('core_site', 'site_number', self.gf('django.db.models.fields.CharField?')(max_length=256, null=True))

From Postgres:

test=# \d core_site

Table "public.core_site"

Column | Type | Modifiers


id | integer | not null default nextval('core_site_id_seq'::regclass)
name | character varying(256) | not null
notes | text |
created_by_id | integer | not null
created_on | timestamp with time zone | not null
modified_by_id | integer | not null
modified_on | timestamp with time zone | not null
phone | character varying(20) |
site_number | character varying(256) | not null
new_address_id | integer |

Indexes:

"core_site_pkey" PRIMARY KEY, btree (id)
"core_site_site_number" UNIQUE, btree (site_number)
"core_site_created_by_id" btree (created_by_id)
"core_site_modified_by_id" btree (modified_by_id)
"core_site_new_address_id" btree (new_address_id)

Foreign-key constraints:

"created_by_id_refs_id_32c8c168" FOREIGN KEY (created_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
"modified_by_id_refs_id_32c8c168" FOREIGN KEY (modified_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
"new_address_id_refs_id_36b784ff" FOREIGN KEY (new_address_id) REFERENCES core_address(id) DEFERRABLE INITIALLY DEFERRED

Referenced by:

TABLE "clinclient_sitecoordinator" CONSTRAINT "site_id_refs_id_1ceb2ef0" FOREIGN KEY (site_id) REFERENCES core_site(id) DEFERRABLE INITIALLY DEFERRED
TABLE "clinclient_travelrequest" CONSTRAINT "site_id_refs_id_42d6fa30" FOREIGN KEY (site_id) REFERENCES core_site(id) DEFERRABLE INITIALLY DEFERRED
TABLE "core_site_investigators" CONSTRAINT "site_id_refs_id_4c5ae786" FOREIGN KEY (site_id) REFERENCES core_site(id) DEFERRABLE INITIALLY DEFERRED
TABLE "core_card" CONSTRAINT "site_id_refs_id_6fa33ec8" FOREIGN KEY (site_id) REFERENCES core_site(id) DEFERRABLE INITIALLY DEFERRED
TABLE "core_cardholder" CONSTRAINT "site_id_refs_id_7259b0c8" FOREIGN KEY (site_id) REFERENCES core_site(id) DEFERRABLE INITIALLY DEFERRED

Debug info:

The SQL query in _constraints_affecting_columns() returns nothing, so the function returns [].

Change History

comment:1 Changed 4 years ago by andrew

  • Status changed from new to assigned

comment:2 Changed 4 years ago by andrew

  • Status changed from assigned to infoneeded
  • Milestone changed from 0.7.3 to 1.0

Sorry about the late reply; is there some way to get some more debugging data (the actual error traceback, for example)? I can't replicate this locally.

comment:3 Changed 3 years ago by andrew

  • Status changed from infoneeded to closed
  • Resolution set to invalid

Closing - no response.

comment:4 Changed 3 years ago by Peter Kese <peter.kese@…>

  • Status changed from closed to reopened
  • Resolution invalid deleted

I've hit this bug too. It is easy to replicate: just install the dbtemplates app and run its migration...
I am using:

comment:5 Changed 3 years ago by andrew

  • Status changed from reopened to assigned

Could you attach the full traceback and your precise South version please?

comment:6 follow-up: ↓ 7 Changed 3 years ago by andrew

  • Status changed from assigned to infoneeded

comment:7 in reply to: ↑ 6 Changed 3 years ago by peter.kese@…

  • Status changed from infoneeded to assigned

Replying to andrew:

I am running South 0.7.3 (installed with buildout from http://www.aeracode.org/releases/south/south-0.7.3.tar.gz, md5sum 693a2a7a57b29c49bc177fe951e04767).

The stack trace is:

peter@maat:~/work/lb$ bin/django migrate dbtemplates
Running migrations for dbtemplates:
 - Migrating forwards to 0002_auto__del_unique_template_name.
 > dbtemplates:0001_initial
 > dbtemplates:0002_auto__del_unique_template_name
Traceback (most recent call last):
  File "bin/django", line 51, in <module>
    djangorecipe.manage.main('lectures.settings')
  File "/home/peter/work/lb/eggs/djangorecipe-1.0-py2.7.egg/djangorecipe/manage.py", line 17, in main
    management.execute_manager(mod)
  File "/home/peter/work/lb/eggs/Django-1.3.1-py2.7.egg/django/core/management/__init__.py", line 438, in execute_manager
    utility.execute()
  File "/home/peter/work/lb/eggs/Django-1.3.1-py2.7.egg/django/core/management/__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/peter/work/lb/eggs/Django-1.3.1-py2.7.egg/django/core/management/base.py", line 191, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/home/peter/work/lb/eggs/Django-1.3.1-py2.7.egg/django/core/management/base.py", line 220, in execute
    output = self.handle(*args, **options)
  File "/home/peter/work/lb/eggs/South-0.7.3-py2.7.egg/south/management/commands/migrate.py", line 105, in handle
    ignore_ghosts = ignore_ghosts,
  File "/home/peter/work/lb/eggs/South-0.7.3-py2.7.egg/south/migration/__init__.py", line 191, in migrate_app
    success = migrator.migrate_many(target, workplan, database)
  File "/home/peter/work/lb/eggs/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 221, in migrate_many
    result = migrator.__class__.migrate_many(migrator, target, migrations, database)
  File "/home/peter/work/lb/eggs/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 292, in migrate_many
    result = self.migrate(migration, database)
  File "/home/peter/work/lb/eggs/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 125, in migrate
    result = self.run(migration)
  File "/home/peter/work/lb/eggs/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 99, in run
    return self.run_migration(migration)
  File "/home/peter/work/lb/eggs/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 81, in run_migration
    migration_function()
  File "/home/peter/work/lb/eggs/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 57, in <lambda>
    return (lambda: direction(orm))
  File "/home/peter/work/lb/eggs/django_dbtemplates-1.2.1-py2.7.egg/dbtemplates/migrations/0002_auto__del_unique_template_name.py", line 12, in forwards
    db.delete_unique('django_template', ['name'])
  File "/home/peter/work/lb/eggs/South-0.7.3-py2.7.egg/south/db/generic.py", line 479, in delete_unique
    raise ValueError("Cannot find a UNIQUE constraint on table %s, columns %r" % (table_name, columns))
ValueError: Cannot find a UNIQUE constraint on table django_template, columns ['name']

comment:8 Changed 3 years ago by anonymous

I have this problem too with South 0.7.3 and MySQL 5.5.20

Running migrations for joblistings:
 - Migrating forwards to 0009_auto__del_unique_location_name.
 > joblistings:0009_auto__del_unique_location_name
 ! Error found during real run of migration! Aborting.
 
 ! Since you have a database that does not support running
 ! schema-altering statements in transactions, we have had
 ! to leave it in an interim state between migrations.

! You *might* be able to recover with:   = ALTER TABLE `joblistings_location` ADD CONSTRAINT `joblistings_location_name_uniq` UNIQUE (`name`) []
 
 ! The South developers regret this has happened, and would
 ! like to gently persuade you to consider a slightly
 ! easier-to-deal-with DBMS.
 ! NOTE: The error which caused the migration to fail is further up.
Traceback (most recent call last):
  File "./manage.py", line 14, in <module>
    execute_manager(settings)
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/django/core/management/__init__.py", line 438, in execute_manager
    utility.execute()
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/django/core/management/__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/django/core/management/base.py", line 191, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/django/core/management/base.py", line 220, in execute
    output = self.handle(*args, **options)
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/south/management/commands/migrate.py", line 105, in handle
    ignore_ghosts = ignore_ghosts,
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/south/migration/__init__.py", line 191, in migrate_app
    success = migrator.migrate_many(target, workplan, database)
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/south/migration/migrators.py", line 221, in migrate_many
    result = migrator.__class__.migrate_many(migrator, target, migrations, database)
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/south/migration/migrators.py", line 292, in migrate_many
    result = self.migrate(migration, database)
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/south/migration/migrators.py", line 125, in migrate
    result = self.run(migration)
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/south/migration/migrators.py", line 99, in run
    return self.run_migration(migration)
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/south/migration/migrators.py", line 81, in run_migration
    migration_function()
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/south/migration/migrators.py", line 57, in <lambda>
    return (lambda: direction(orm))
  File "/home/yoshi/dev/django_jobs/joblistings/migrations/0009_auto__del_unique_location_name.py", line 12, in forwards
    db.delete_unique('joblistings_location', ['name'])
  File "/home/yoshi/bin/virtualenvs/django_jobs/lib/python2.7/site-packages/south/db/generic.py", line 479, in delete_unique
    raise ValueError("Cannot find a UNIQUE constraint on table %s, columns %r" % (table_name, columns))
ValueError: Cannot find a UNIQUE constraint on table joblistings_location, columns ['name']

This is the table structure when I tried to apply the migration:

CREATE TABLE `joblistings_location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `name_slug` varchar(255) NOT NULL,
  `country` tinyint(1) NOT NULL,
  `parent_id` int(11),
  PRIMARY KEY (`id`),
  UNIQUE KEY `joblistings_location_name_uniq` (`name`),
  UNIQUE KEY `name_slug` (`name_slug`),
  KEY `joblistings_location_name_slug` (`name_slug`),
  KEY `joblistings_location_63f17a16` (`parent_id`),
  CONSTRAINT `parent_id_refs_id_7afbd9f5` FOREIGN KEY (`parent_id`) REFERENCES `joblistings_location` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

comment:9 Changed 3 years ago by matt@…

I've been researching this, and I think I've found some stuff that might help:

In south.db.mysql._constraints_affecting_columns(), there's a query that loads all the constraint to column mappings for the table. It includes a WHERE clause:

kc.table_catalog IS NULL AND

That's proper in MySQL < 5.5.0, where due to a (documented) bug, the default TABLE_CATALOG was NULL. However, in 5.5.0, the default was changed to 'def'

Here's the MySQL bug in question documenting the change in behavior as of 5.5.0: http://bugs.mysql.com/bug.php?id=35427

I'm not familiar enough with MySQL to know if you should (a) drop that WHERE clause, (b) alter it to work for either NULL or 'def' or (c) somehow determine the user's TABLE_CATALOG and select on it. I'm going to patch my local version to drop the where clause, as that seems to work.

Hope this helps.

comment:10 Changed 3 years ago by andrew

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

This apparently got fixed without me closing this bug.

comment:11 Changed 20 months ago by zev@…

I recently ran into a similar problem. In our case, the issue was that the tables are not stored in the 'public' schema, which South looks in by default. Therefore, it was not able to find any constraints. Adding a 'SCHEMA' entry to the database configuration (along side 'USER', 'HOST', etc.) in settings.py fixed the problem.

comment:12 Changed 8 months ago by Anentropic

I have just hit this problem. South 0.8.1 and Postgres 9.3

My tables are in the 'public' schema

pgadmin shows this under the table definition:

CREATE UNIQUE INDEX core_tilebrand_brand_id_tile_id
  ON core_tilebrand
  USING btree
  (brand_id, tile_id);

My migration:

def forwards(self, orm):
     db.delete_unique(u'core_tilebrand', ['brand_id', 'tile_id'])

./manage.py migrate core

ValueError: Cannot find a UNIQUE constraint on table core_tilebrand, columns ['brand_id', 'tile_id']

comment:13 Changed 8 months ago by Anentropic

Hmm, well now it seems like my table in the db doesn't match what Django would have created... looking in the South source there is:

delete_unique_sql = "ALTER TABLE %s DROP CONSTRAINT %s"

...and in Postgres docs the syntax for a unique constraint is

CREATE TABLE core_tilebrand (
    brand_id integer,
    tile_id integer,
    UNIQUE (brand_id, tile_id)
)

I don't have that in my table definition, so no surprise it fails.

Note: See TracTickets for help on using tickets.