Unable to remove UNIQUE from field with South 0.72

[shawn@…]

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 [].

[andrew]

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.

[andrew]

Closing - no response.

[Peter Kese <peter.kese@…>]

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

• django 1.3.1
• postgres 8.4
• ​http://pypi.python.org/pypi/django-dbtemplates

[andrew]

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

[peter.kese@…]

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']

[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;

[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.

[andrew]

This apparently got fixed without me closing this bug.

[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.