Changing fields to null=True fails on MySql InnoDB

[klaas@…]

Hi,

I've noticed that changing fields to null=True and then running the automatic south migrations fails on InnoDB.

...
  File "/Users/xxx/Sites/online_virtualenv/lib/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/Users/xxx/Sites/online_virtualenv/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1005, "Can't create table 'demo.#sql-1b9_2e' (errno: 121)")

This inconsistently (unfortunately) happens on various Ubuntu & MacOS setups.
MacOs Ver. 14.14 Distrib 5.5.14 failed for me today.

Funny thing is, that even though this error is raised, the mentioned field is actually made nullable in the DB. So commenting out the offending line in the migration and rerunning it fixes it.
InnoDB error log doesn't show much interesting info.

120106 13:05:56 [Warning] Invalid (old?) table or database name '#sql-1b9_2e'

ciao,
Klaas

[klaas@…]

South is version 0.7.3 by the way

[andrew]

Can you please try with the development version first? There's several unreleased bugfixes in it.

[anonymous]

With south 0.7.3 it works with MySQL 5.1, 5.0 and doesn't work with 5.5.

[andrew]

Right, but it's the development version that needs testing - the release version is too old for any real testing to be of value.

[klaas@…]

Hi,

A similar bug,

_mysql_exceptions.OperationalError: (1005, "Can't create table 'yekyek.#sql-314_5cf1' (errno: 150)")

*is* solved by moving to development. May run into nr. 121 again in a different setup, will keep you posted.

[ulysses.cv@…]

Some more information about this bug:

exmoor:bin core$ ./mysqld --version
./mysqld  Ver 5.5.25 for osx10.6 on i386 (MySQL Community Server (GPL))

Migration (adding null=True to a FKField):

db.alter_column('evaluation_levelchangerequest', 'thread_id', self.gf('django.db.models.fields.related.ForeignKey')(to=orm['discuss.Thread'], null=True))

Error:

Running migrations for evaluation:
 - Migrating forwards to 0005_auto__chg_field_levelchangerequest_thread.
 > evaluation:0002_auto__chg_field_levelchangerequest_created__chg_field_levelchangereque
 > evaluation:0003_level
 - Migration 'evaluation:0003_level' is marked for no-dry-run.
 > evaluation:0004_auto__del_unique_levelchangerequest_target
 > evaluation:0005_auto__chg_field_levelchangerequest_thread
FATAL ERROR - The following SQL query failed: ALTER TABLE `evaluation_levelchangerequest` DROP FOREIGN KEY `thread_id_refs_id_66155f1f`
The error was: (1025, "Error on rename of './uv_ib/#sql-28d2_68' to './uv_ib/evaluation_levelchangerequest' (errno: 150)")
Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/django/core/management/__init__.py", line 443, in execute_from_command_line
    utility.execute()
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/django/core/management/__init__.py", line 382, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/django/core/management/base.py", line 196, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/django/core/management/base.py", line 232, in execute
    output = self.handle(*args, **options)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/management/commands/migrate.py", line 107, in handle
    ignore_ghosts = ignore_ghosts,
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/migration/__init__.py", line 213, in migrate_app
    success = migrator.migrate_many(target, workplan, database)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/migration/migrators.py", line 235, in migrate_many
    result = migrator.__class__.migrate_many(migrator, target, migrations, database)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/migration/migrators.py", line 310, in migrate_many
    result = self.migrate(migration, database)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/migration/migrators.py", line 133, in migrate
    result = self.run(migration)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/migration/migrators.py", line 107, in run
    return self.run_migration(migration)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/migration/migrators.py", line 86, in run_migration
    print self.run_migration_error(migration)
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/migration/migrators.py", line 304, in run_migration_error
    (self.format_backwards(migration), extra_info))
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/migration/migrators.py", line 291, in format_backwards
    self.backwards(migration)()
  File "/Users/core/Inoa/ib/env/lib/python2.7/site-packages/South-0.7.5-py2.7.egg/south/migration/migrators.py", line 57, in <lambda>
    return (lambda: direction(orm))
  File "/Users/core/Inoa/ib/webapp/ib/evaluation/migrations/0005_auto__chg_field_levelchangerequest_thread.py", line 14, in backwards
    raise RuntimeError("Cannot reverse this migration. 'LevelChangeRequest.thread' and its values cannot be restored.")
RuntimeError: Cannot reverse this migration. 'LevelChangeRequest.thread' and its values cannot be restored.

[ulysses.cv@…]

Forgot to say that I'm running django 1.4 and the following South version:

(env)exmoor:south core$ hg log -l 1
changeset:   1099:c29229c048e8
tag:         tip
parent:      1097:94f9e3966514
parent:      1098:373d1700a994
user:        Andrew Godwin <andrew@aeracode.org>
date:        Mon May 28 17:58:46 2012 +0100
summary:     Merged in miracle2k/south/migration-update2 (pull request #66)