Cant change ForeginKey from null=False to null=True in MySQL

[jbzdak@…]

It results in MySQl error:

mysql_exceptions.OperationalError: (1005, "Can't create table 'kraken.#sql-38b5_4e' (errno: 121)")

It is raised (it's an educated guess) because South at some point tries to create a foregin key with name that already exists in the table.

 - Migrating forwards to 0002_auto__chg_field_baz_fk.
 > test_app:0001_initial
162 south DEBUG south execute "CREATE TABLE `test_app_foo` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY);" with params "[]"
162 south DEBUG south execute "CREATE TABLE `test_app_foo` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY);" with params "[]"
163 south DEBUG south execute "CREATE TABLE `test_app_baz` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `fk_id` integer NOT NULL);" with params "[]"
163 south DEBUG south execute "CREATE TABLE `test_app_baz` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `fk_id` integer NOT NULL);" with params "[]"
164 south DEBUG south execute "SET FOREIGN_KEY_CHECKS=1;" with params "[]"
164 south DEBUG south execute "SET FOREIGN_KEY_CHECKS=1;" with params "[]"
164 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
164 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
164 south DEBUG south execute "CREATE INDEX `test_app_baz_37789993` ON `test_app_baz` (`fk_id`);" with params "[]"
164 south DEBUG south execute "CREATE INDEX `test_app_baz_37789993` ON `test_app_baz` (`fk_id`);" with params "[]"
165 south DEBUG south execute "CREATE TABLE `test_app_foo` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY);" with params "[]"
165 south DEBUG south execute "CREATE TABLE `test_app_foo` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY);" with params "[]"
331 south DEBUG south execute "CREATE TABLE `test_app_baz` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `fk_id` integer NOT NULL);" with params "[]"
331 south DEBUG south execute "CREATE TABLE `test_app_baz` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `fk_id` integer NOT NULL);" with params "[]"
465 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
465 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
768 south DEBUG south execute "CREATE INDEX `test_app_baz_37789993` ON `test_app_baz` (`fk_id`);" with params "[]"
768 south DEBUG south execute "CREATE INDEX `test_app_baz_37789993` ON `test_app_baz` (`fk_id`);" with params "[]"
 > test_app:0002_auto__chg_field_baz_fk
1050 south DEBUG south execute "ALTER TABLE `test_app_baz` ;" with params "[]"
1050 south DEBUG south execute "ALTER TABLE `test_app_baz` ;" with params "[]"
1051 south DEBUG south execute "ALTER TABLE `test_app_baz` MODIFY `fk_id` integer NULL;;" with params "[]"
1051 south DEBUG south execute "ALTER TABLE `test_app_baz` MODIFY `fk_id` integer NULL;;" with params "[]"
1052 south DEBUG south execute "ALTER TABLE `test_app_baz` ALTER COLUMN `fk_id` DROP DEFAULT;" with params "[]"
1052 south DEBUG south execute "ALTER TABLE `test_app_baz` ALTER COLUMN `fk_id` DROP DEFAULT;" with params "[]"
1053 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
1053 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
1055 south DEBUG south execute "
            SELECT kc.constraint_name, kc.column_name
            FROM information_schema.key_column_usage AS kc
            JOIN information_schema.table_constraints AS c ON
                kc.table_schema = c.table_schema AND
                kc.table_name = c.table_name AND
                kc.constraint_name = c.constraint_name
            WHERE
                kc.table_schema = %s AND
                kc.table_catalog IS NULL AND
                kc.table_name = %s AND
                c.constraint_type = %s
        " with params "['', 'test_app_baz', 'FOREIGN KEY']"
1055 south DEBUG south execute "
            SELECT kc.constraint_name, kc.column_name
            FROM information_schema.key_column_usage AS kc
            JOIN information_schema.table_constraints AS c ON
                kc.table_schema = c.table_schema AND
                kc.table_name = c.table_name AND
                kc.constraint_name = c.constraint_name
            WHERE
                kc.table_schema = %s AND
                kc.table_catalog IS NULL AND
                kc.table_name = %s AND
                c.constraint_type = %s
        " with params "['', 'test_app_baz', 'FOREIGN KEY']"
1063 south DEBUG south execute "ALTER TABLE `test_app_baz` ;" with params "[]"
1063 south DEBUG south execute "ALTER TABLE `test_app_baz` ;" with params "[]"
1064 south DEBUG south execute "ALTER TABLE `test_app_baz` MODIFY `fk_id` integer NULL;;" with params "[]"
1064 south DEBUG south execute "ALTER TABLE `test_app_baz` MODIFY `fk_id` integer NULL;;" with params "[]"
1305 south DEBUG south execute "ALTER TABLE `test_app_baz` ALTER COLUMN `fk_id` DROP DEFAULT;" with params "[]"
1305 south DEBUG south execute "ALTER TABLE `test_app_baz` ALTER COLUMN `fk_id` DROP DEFAULT;" with params "[]"
1356 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
1356 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
1450 south DEBUG south execute "ALTER TABLE `test_app_baz` ;" with params "[]"
1450 south DEBUG south execute "ALTER TABLE `test_app_baz` ;" with params "[]"
1451 south DEBUG south execute "ALTER TABLE `test_app_baz` MODIFY `fk_id` integer NOT NULL;;" with params "[]"
1451 south DEBUG south execute "ALTER TABLE `test_app_baz` MODIFY `fk_id` integer NOT NULL;;" with params "[]"
1452 south DEBUG south execute "ALTER TABLE `test_app_baz` ALTER COLUMN `fk_id` DROP DEFAULT;" with params "[]"
1452 south DEBUG south execute "ALTER TABLE `test_app_baz` ALTER COLUMN `fk_id` DROP DEFAULT;" with params "[]"
1453 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
1453 south DEBUG south execute "ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`);" with params "[]"
 ! 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 `test_app_baz` ; []
   = ALTER TABLE `test_app_baz` MODIFY `fk_id` integer NOT NULL;; []
   = ALTER TABLE `test_app_baz` ALTER COLUMN `fk_id` DROP DEFAULT; []
   = ALTER TABLE `test_app_baz` ADD CONSTRAINT `fk_id_refs_id_67512fa34967d0b6` FOREIGN KEY (`fk_id`) REFERENCES `test_app_foo` (`id`); []

 ! 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 11, in <module>
    execute_manager(settings)
  File "/usr/local/lib/python2.6/dist-packages/django/core/management/__init__.py", line 438, in execute_manager
    utility.execute()
  File "/usr/local/lib/python2.6/dist-packages/django/core/management/__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.6/dist-packages/django/core/management/base.py", line 191, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/local/lib/python2.6/dist-packages/django/core/management/base.py", line 218, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/management/commands/migrate.py", line 109, in handle
    ignore_ghosts = ignore_ghosts,
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/migration/__init__.py", line 202, in migrate_app
    success = migrator.migrate_many(target, workplan, database)
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/migration/migrators.py", line 220, in migrate_many
    result = migrator.__class__.migrate_many(migrator, target, migrations, database)
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/migration/migrators.py", line 291, in migrate_many
    result = self.migrate(migration, database)
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/migration/migrators.py", line 125, in migrate
    result = self.run(migration)
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/migration/migrators.py", line 99, in run
    return self.run_migration(migration)
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/migration/migrators.py", line 81, in run_migration
    migration_function()
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/migration/migrators.py", line 57, in <lambda>
    return (lambda: direction(orm))
  File "/var/wsgi/kraken/test_app/migrations/0002_auto__chg_field_baz_fk.py", line 12, in forwards
    db.alter_column('test_app_baz', 'fk_id', self.gf('django.db.models.fields.related.ForeignKey')(to=orm['test_app.Foo'], null=True))
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/db/generic.py", line 373, in alter_column
    field.rel.to._meta.get_field(field.rel.field_name).column
  File "/usr/local/lib/python2.6/dist-packages/South-0.7.1-py2.6.egg/south/db/generic.py", line 134, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/dist-packages/django/db/backends/util.py", line 15, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/dist-packages/django/db/backends/mysql/base.py", line 86, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1005, "Can't create table 'kraken.#sql-38b5_70' (errno: 121)")

[jbzdak@…]

Test app that fails to migrate on clean database

[dpmcgee@…]

We're seeing this exact same issue on one of our old migrations now when trying to start from scratch, I believe it matches the test case issue: ​http://projects.archlinux.org/archweb.git/tree/main/migrations/0002_make_maintainer_nullable.py

The root of the problem comes down to the query used to grab the constraints from the database. This is especially apparent with MySQL 5.5 which makes the current query always return zero results with an "impossible WHERE clause" explanation.

I'm not sure where the original query stemmed from, but I will attach a patch that fixes it for me. It applies against the current code, but can be also backpatched. Basically, the kc.table_catalog IS NULL bit seems to be not always what one wants (in MySQL 5.5 at least this is always the value "def", never NULL), and killing that from the WHERE clause prevents South from trying to recreate an already existing constraint.

[dpmcgee@…]

Potential fix, don't exclude via table_catalog column

[andrew]

Committed in [2aa2394065ec]. Thanks!

[msinia@…]

Was this actually fixed? I'm getting the same error in MySQL 5.5 with South 0.7.3.

South's code seems to have the same (problematic) behavior as described above (kc.table_catalog IS NULL bit in the query) on the south/db/mysql.py module.

[andrew]

The 5.5 issue is because they changed the syntax in a non-backwards-compatable fashion in MySQL 5.5. I'm aware of the issue, but feel free to open a ticket for it and attach some helpful debug info; I haven't started on a fix yet.

[dpmcgee@…]

The fix I attached was committed but is not in 0.7.3; it was done after that release, if I remember right. This would be why it doesn't appear to be fixed...

[msinia@…]

The part of the query causing the problem (same one as before) is now on a different line, so I'm not sure what really happened. You can now find it on line 137 of the same file.

[msinia@…]

I was just checking the latest version found on Mercurial, and the mysql.py module is pretty reworked, so maybe this problem is already fixed. Should try with that and see before submitting a new patch.

[anonymous]

This is definitely still happening.

[HB]

+1. Still happening.

[Ludovic Gasc <gmludo@…>]

+1, this bug is still here: ​https://github.com/e-hulp/HelpIM/issues/141

[anonymous]

This issue still exists.

[andrew]

Please don't re-open tickets - this particular bug _was_ fixed a while ago, in commit [2aa2394065ec]. If there is a new occurence of a very similar bug, please open a new ticket, including a full stack trace, the exact versions of South and Django, and preferably some example models code.