search

palewire / django-calaccess-raw-data

A Django app to download, extract and load campaign finance and lobbying activity data from the California Secretary of State's CAL-ACCESS database
http://django-calaccess.californiacivicdata.org/
MIT License
64 stars 143 forks source link

Can't drop and re-add table constraints/indexes in MySQL #1499

Closed gordonje closed 6 years ago

gordonje commented 7 years ago

Got this error when testing Django 1.11:

Traceback (most recent call last):
  File "calaccess_raw/tests/test_docs.py", line 28, in setUpClass
    call_command("updatecalaccessrawdata", **kwargs)
  File "/Users/gordo/.virtualenvs/django-calaccess-raw-data/lib/python2.7/site-packages/django/core/management/__init__.py", line 130, in call_command
    return command.execute(*args, **defaults)
  File "/Users/gordo/.virtualenvs/django-calaccess-raw-data/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "calaccess_raw/management/commands/updatecalaccessrawdata.py", line 322, in handle
    self.load()
  File "calaccess_raw/management/commands/updatecalaccessrawdata.py", line 482, in load
    app_name=self.app_name,
  File "/Users/gordo/.virtualenvs/django-calaccess-raw-data/lib/python2.7/site-packages/django/core/management/__init__.py", line 130, in call_command
    return command.execute(*args, **defaults)
  File "/Users/gordo/.virtualenvs/django-calaccess-raw-data/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "calaccess_raw/management/commands/loadcalaccessrawfile.py", line 118, in handle
    self.load()
  File "calaccess_raw/management/commands/loadcalaccessrawfile.py", line 151, in load
    self.model.objects.drop_constraints_and_indexes()
  File "calaccess_raw/managers.py", line 107, in drop_constraints_and_indexes
    self.model, field, field_copy
  File "/Users/gordo/.virtualenvs/django-calaccess-raw-data/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 513, in alter_field
    old_db_params, new_db_params, strict)
  File "/Users/gordo/.virtualenvs/django-calaccess-raw-data/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 569, in _alter_field
    self.execute(self._delete_constraint_sql(self.sql_delete_index, model, index_name))
  File "/Users/gordo/.virtualenvs/django-calaccess-raw-data/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 106, in execute
    "Executing DDL statements while in a transaction on databases "
TransactionManagementError: Executing DDL statements while in a transaction on databases that can't perform a rollback is prohibited.

This occurs when we attempt to drop a db table's constraints and indexes before loading. Apparently MySQL doesn't support transactional DDL statements, though PostgreSQL, SQL Server and a few other db managers do.

There might be a way around this. Like instead of using the Django schema_editor, we could build the SQL statement and execute it raw. Feels a little dangerous, though.

For now, I'll skip the dropping and re-adding of constraints and indexes when loading into MySQL.

palewire commented 7 years ago

Wise decision I think.