Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#23719 closed Bug (fixed)

GIS test failure with MySQL 5.6: BLOB/TEXT column can't have a default value

Reported by: Tim Graham Owned by: Tim Graham
Component: GIS Version: 1.7
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Possibly it's related to the first "Incompatible change" listed in the MySQL upgrading notes.


Traceback (most recent call last):
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/contrib/gis/tests/gis_migrations/", line 78, in test_add_gis_field
    operation.database_forwards("gis", editor, project_state, new_state)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/db/migrations/operations/", line 37, in database_forwards
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/contrib/gis/db/backends/mysql/", line 38, in add_field
    super(MySQLGISSchemaEditor, self).add_field(model, field)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/db/backends/mysql/", line 45, in add_field
    super(DatabaseSchemaEditor, self).add_field(model, field)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/db/backends/", line 389, in add_field
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/db/backends/", line 102, in execute
    cursor.execute(sql, params)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/db/backends/", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/db/", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/db/backends/", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/db/backends/mysql/", line 130, in execute
    return self.cursor.execute(query, args)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/tests/.env/local/lib/python2.7/site-packages/MySQLdb/", line 219, in execute
    self.errorhandler(self, exc, value)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/tests/.env/local/lib/python2.7/site-packages/MySQLdb/", line 38, in defaulterrorhandler
    raise errorvalue
OperationalError: (1101, "BLOB/TEXT column 'path' can't have a default value")

Change History (10)

comment:1 by Tim Graham, 10 years ago

Owner: changed from nobody to Tim Graham
Status: newassigned

comment:2 by Claude Paroz, 10 years ago

As I can see, the problem is not due to the add field instruction, but with the following ALTER TABLE `gis_neighborhood` ALTER COLUMN `path` DROP DEFAULT', while the add field didn't set any default.
Applying this might help:

  • django/db/backends/

    diff --git a/django/db/backends/ b/django/db/backends/
    index 282dfe6..9d5ebef 100644
    a b import operator  
    44from django.db.backends.creation import BaseDatabaseCreation
    55from django.db.backends.utils import truncate_name
     6from django.db.models.fields import NOT_PROVIDED
    67from django.db.models.fields.related import ManyToManyField
    78from django.db.transaction import atomic
    89from django.utils.encoding import force_bytes
    class BaseDatabaseSchemaEditor(object):  
    379380        self.execute(sql, params)
    380381        # Drop the default if we need to
    381382        # (Django usually does not use in-database defaults)
    382         if not self.skip_default(field) and field.default is not None:
     383        if not self.skip_default(field) and field.default not in (None, NOT_PROVIDED):
    383384            sql = self.sql_alter_column % {
    384385                "table": self.quote_name(model._meta.db_table),
    385386                "changes": self.sql_alter_column_no_default % {

#23581 might be related.

Last edited 10 years ago by Claude Paroz (previous) (diff)

comment:3 by Tim Graham, 10 years ago

Yes, that patch works. So unlike CharField/TextField in [1d3d01b4], the default model field option can't be used with all GeometryFields, correct?

comment:4 by Claude Paroz, 10 years ago

I'm not sure to understand your question. However, it seems that MySQL 5.6 is less permissive about DROPping default for columns which don't accept defaults. I'm not sure there is a difference between CharField/TextField and GeometryFields. Still, I'd suggest to ask Loic about the best resolution to this issue.

comment:5 by Tim Graham, 10 years ago

You can have something like CharField(default='foo') since model field defaults aren't applied at the database level. [1d3d01b4] prevents temporarily setting a default on MySQL to prevent the same error as this ticket for Char/TextField.

Is PointField(default=???) a possibility? If so, it seems your proposal wouldn't solve the issue entirely. We'd need something like my initial proposal (possibly using isinstance(field, GeometryField) instead). I think your proposal may address #23581 though. I will look at that later today.

comment:6 by Claude Paroz, 10 years ago

I've never seen default values on a geometry column. Even I can't find anything (after a short research) saying that it can never happen, I think it's fine to consider this as unsupported, so your patch goes indeed in the right direction.

comment:7 by Tim Graham, 10 years ago

Has patch: set

comment:8 by Tim Graham, 10 years ago

Triage Stage: AcceptedReady for checkin

Loic gave this a LGTM on IRC.

comment:9 by Tim Graham <timograham@…>, 10 years ago

Resolution: fixed
Status: assignedclosed

In 03bd79ed2124d149114fa3b702878d75159ba2b8:

Fixed #23719 -- Fixed MySQL 5.6 crash with GeometryFields in migrations.

comment:10 by Tim Graham <timograham@…>, 10 years ago

In bb42bab6d32aab3344b809e49c696a83d45a2954:

[1.7.x] Fixed #23719 -- Fixed MySQL 5.6 crash with GeometryFields in migrations.

Backport of 03bd79ed2 from master

Note: See TracTickets for help on using tickets.
Back to Top