Ticket #523 (closed defect: fixed)

Opened 4 years ago

Last modified 2 years ago

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

Reported by: jbzdak@… Owned by: andrew
Priority: major Milestone: 1.0
Component: migrations Version: 0.7.1
Keywords: mysql, ForeginKey Cc:

Description

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)")

Attachments

test_app.zip (5.7 KB) - added by jbzdak@… 4 years ago.
test_app.2.zip (5.7 KB) - added by jbzdak@… 4 years ago.
Test app that fails to migrate on clean database
fix-constraint-selection.patch (525 bytes) - added by dpmcgee@… 4 years ago.
Potential fix, don't exclude via table_catalog column

Change History

Changed 4 years ago by jbzdak@…

Changed 4 years ago by jbzdak@…

Test app that fails to migrate on clean database

comment:1 Changed 4 years ago by andrew

  • Status changed from new to assigned
  • Milestone changed from 0.7.2 to 0.7.3

comment:2 Changed 4 years ago by andrew

  • Milestone changed from 0.7.3 to 1.0

comment:3 Changed 4 years ago by 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.

Changed 4 years ago by dpmcgee@…

Potential fix, don't exclude via table_catalog column

comment:4 Changed 4 years ago by andrew

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

Committed in [2aa2394065ec]. Thanks!

comment:5 Changed 3 years ago by 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.

comment:6 Changed 3 years ago by 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.

comment:7 Changed 3 years ago by 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...

comment:8 Changed 3 years ago by 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.

comment:9 Changed 3 years ago by 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.

comment:10 Changed 3 years ago by anonymous

This is definitely still happening.

comment:11 Changed 3 years ago by HB

+1. Still happening.

comment:12 Changed 3 years ago by Ludovic Gasc <gmludo@…>

comment:13 Changed 2 years ago by anonymous

  • Status changed from closed to reopened
  • Resolution fixed deleted

This issue still exists.

comment:14 Changed 2 years ago by andrew

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

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.

Note: See TracTickets for help on using tickets.