add new blank=True CharField on SQLite results in OperationalError: "Cannot add a NOT NULL column with default value NULL"

[carl@…]

I am trying to run this migration on SQLite3:

db.add_column('portfolio_production', 'short_title', orm['portfolio.production:short_title'])

Where the new field looks like:

short_title = models.CharField(max_length=20, blank=True)

And I am getting this traceback on running the migration:

Running migrations for portfolio:
 - Migrating forwards to 0003_add_short_title.
 > portfolio: 0003_add_short_title
Traceback (most recent call last):
  File "/home/carljm/projects/bruce/bruce_env/bin/django-admin.py", line 5, in <module>
    management.execute_from_command_line()
  File "/home/carljm/projects/bruce/bruce_env/lib/python2.5/site-packages/django/core/management/__init__.py", line 353, in execute_from_command_line
    utility.execute()
  File "/home/carljm/projects/bruce/bruce_env/lib/python2.5/site-packages/django/core/management/__init__.py", line 303, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/carljm/projects/bruce/bruce_env/lib/python2.5/site-packages/django/core/management/base.py", line 195, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/home/carljm/projects/bruce/bruce_env/lib/python2.5/site-packages/django/core/management/base.py", line 222, in execute
    output = self.handle(*args, **options)
  File "/home/carljm/projects/bruce/bruce_env/src/south/south/management/commands/migrate.py", line 92, in handle
    skip = skip,
  File "/home/carljm/projects/bruce/bruce_env/src/south/south/migration.py", line 591, in migrate_app
    result = run_forwards(mapp, [mname], fake=fake, db_dry_run=db_dry_run, verbosity=verbosity)
  File "/home/carljm/projects/bruce/bruce_env/src/south/south/migration.py", line 398, in run_forwards
    verbosity = verbosity,
  File "/home/carljm/projects/bruce/bruce_env/src/south/south/migration.py", line 339, in run_migrations
    runfunc(orm)
  File "/home/carljm/projects/bruce/bruce/project/portfolio/migrations/0003_add_short_title.py", line 11, in forwards
    db.add_column('portfolio_production', 'short_title', orm['portfolio.production:short_title'])
  File "/home/carljm/projects/bruce/bruce_env/src/south/south/db/sqlite3.py", line 132, in add_column
    generic.DatabaseOperations.add_column(self, table_name, name, field, *args, **kwds)
  File "/home/carljm/projects/bruce/bruce_env/src/south/south/db/generic.py", line 258, in add_column
    self.execute(sql)
  File "/home/carljm/projects/bruce/bruce_env/src/south/south/db/generic.py", line 126, in execute
    cursor.execute(sql, params)
  File "/home/carljm/projects/bruce/bruce_env/lib/python2.5/site-packages/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "/home/carljm/projects/bruce/bruce_env/lib/python2.5/site-packages/django/db/backends/sqlite3/base.py", line 193, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: Cannot add a NOT NULL column with default value NULL

I see #79, which is similar, was closed. It seems to me the rationale for closing that ticket does not apply here because my field has blank=True. It seems clear that this migration should run successfully, with a default set to empty string if the database needs that to be happy.

In my testing, it appears that this patch fixes the problem:

diff --git a/south/db/generic.py b/south/db/generic.py
--- a/south/db/generic.py
+++ b/south/db/generic.py
@@ -473,7 +473,7 @@
                     sql += " DEFAULT %s"
                     sqlparams = (default)
             elif (not field.null and field.blank) or ((field.get_default() == '') and (not getattr(field, '_suppress_default', False))):
-                if field.empty_strings_allowed and self._get_connection().features.interprets_empty_strings_as_nulls:
+                if field.empty_strings_allowed:
                     sql += " DEFAULT ''"
                 # Error here would be nice, but doesn't seem to play fair.
                 #else:

Tested in both trunk and 0.6.2. I'm not sure precisely what the DB feature "interprets_empty_strings_as_nulls" means, so I don't know if the real trouble is that SQLite3 ought to have that set to True, or that its being used here where it shouldn't be.

[carl@…]

In case it helps others, here's the workaround that worked for me:

        # workaround for South issue #313
        field = orm['portfolio.production:short_title']
        field.default = ''
        db.add_column('portfolio_production', 'short_title', field)

[andrew]

We have to be careful with these kinda of bugs as MySQL/PostgreSQL sometimes expects contrary behaviour (hence the interprets_empty_strings_as_nulls thing). I'll stick it on the 0.7 list, the sqlite module's getting a refresh then anyway.

[andrew]

I'm deciding to WONTFIX this. My reasoning:

• The field has null set to False, so the column's definitely NOT NULL.
• It has default set to None - that's how Django stores it internally.
• Thus, you need to define a default. 0.7 picks this up and prompts you at migration-creation time, so you'll never get the ugly error you see here.

However, I'm not going to make CharFields with blank=True magically gain a default of '', because it's a special case, and just doesn't fit for me.

[slolankellent]

Drug Youth Group Games ​http://forrealbeachresort.com/ - venlafaxine er The medication was approved by the FDA and introduced to the market by Wyeth in 1993. ​http://forrealbeachresort.com/ - effexor pills