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

[Tim Graham]

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

django.contrib.gis.tests.gis_migrations.test_operations.OperationTests.test_add_gis_field:

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/test_operations.py", 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/fields.py", line 37, in database_forwards
    field,
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/contrib/gis/db/backends/mysql/schema.py", 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/schema.py", 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/schema.py", line 389, in add_field
    self.execute(sql)
  File "/var/lib/jenkins/jobs/django-master/workspace/database/mysql_gis/python/python2.7/django/db/backends/schema.py", 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/utils.py", 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/utils.py", 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/utils.py", 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/base.py", 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/cursors.py", 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/connections.py", line 38, in defaulterrorhandler
    raise errorvalue
OperationalError: (1101, "BLOB/TEXT column 'path' can't have a default value")

[Claude Paroz]

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/schema.py

@@ -3 +3 @@
 
 from django.db.backends.creation import BaseDatabaseCreation
 from django.db.backends.utils import truncate_name
+from django.db.models.fields import NOT_PROVIDED
 from django.db.models.fields.related import ManyToManyField
 from django.db.transaction import atomic
 from django.utils.encoding import force_bytes
@@ -379 +380 @@
         self.execute(sql, params)
         # Drop the default if we need to
         # (Django usually does not use in-database defaults)
-        if not self.skip_default(field) and field.default is not None:
+        if not self.skip_default(field) and field.default is not in (None, NOT_PROVIDED):
             sql = self.sql_alter_column % {
                 "table": self.quote_name(model._meta.db_table),
                 "changes": self.sql_alter_column_no_default % {

#23581 might be related.

[Tim Graham]

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

[Claude Paroz]

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.

[Tim Graham]

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.

[Claude Paroz]

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.

[Tim Graham]

​PR

[Tim Graham]

Loic gave this a LGTM on IRC.

[Tim Graham <timograham@…>]

In 03bd79ed2124d149114fa3b702878d75159ba2b8:

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

[Tim Graham <timograham@…>]

In bb42bab6d32aab3344b809e49c696a83d45a2954:

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

Backport of 03bd79ed2 from master