migrate command throwing an error

karthikr87 commented 4 years ago

Subject: Migrate classic redshift DB to django model

Problem

I have an AWS redshift database that is used by the python flask app. The database has lot of tables and data. Currently, we are migrating the flask app to python Django. I just created a new django project and provided redshift DB details in the settings.py as suggested.

DATABASES = {
    'default': {
        'ENGINE': 'django_redshift_backend',
        'NAME':  <db name>,
        'USER': <user name>,
        'PASSWORD': <db password>,
        'HOST': 'xxx.xxxxx.ap-south-1.redshift.amazonaws.com',
        'PORT': '5439',
    }
}

'python manage.py makemigrations' - succeeded 'python manage.py migrate - error msg: django.db.utils.NotSupportedError: column "content_type__app_label" specified as distkey/sortkey is not in the table "auth_permission"

Procedure to reproduce the problem

1. install Django - 2.1.15, django_redshift_backend, python 3.7
2. update settings.py to use redshift config
3. run makemigrations and migrate

Error logs / results

python manage.py migrate        
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying auth.0001_initial...Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/operations/models.py", line 97, in database_forwards
    schema_editor.create_model(model)
  File "/usr/local/lib/python2.7/site-packages/django_redshift_backend/base.py", line 209, in create_model
    self.execute(sql, params or None)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 136, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
django.db.utils.NotSupportedError: column "content_type__app_label" specified as distkey/sortkey is not in the table "auth_permission"

Expected results

results in success. Create tables for admin user. Create model file from existing db using inspectdb

Environment info

OS: Mac
Python version: 3.7
Django version: 2.1.15
Django-Redshift-Backend version: 1.1.0

hemantkshirsagar8 commented 4 years ago

I came across the same issue. Please let me know in case of any solution available. It Will be a great help, thanks.

kz-atishgore commented 4 years ago

I also facing the same issue.

sebastiaopf commented 3 years ago

I also faced the same problem trying to run migrate on a brand new Django project, on the admin models.

What happens is that redshift-backend tries to add a SORTKEY parameter to the CREATE TABLE statement, and for that it uses the model._meta.ordering list. The problem is that, in Django, you can specify columns from other models in ordering. For this columns, the list will show an entry in the format "model__column", which is invalid for Redshift, since it's not a valid column for that model. This is specially true for Django content_types.

I also noticed that, even if you fix this issue to only add columns from the same model on the SORTKEY, it will throw another exception when some migration tries to delete a column that is part of the SORTKEY (which, also, the default migrations for Django Admin do).

The only way I found to work around this issue was to remove the SORTKEY parameter from the CREATE TABLE statements created by this component. I did this by commenting lines 533:539 in base.py on the django_redshift_backend package.

I this this is also related to issue #37 .

EDIT: Just as a follow up, even after fixing all the problems above (and others that came up after) I found that Django 3.2 will, probably, be incompatible with Redshift. It generates SQL statements with functions not supported by Redshift (such as unnest()). So I'm giving up using Redshift with Django, at least for my current project. This is not related with django-redshift-backend, but with Django ORM itself.

shimizukawa commented 3 years ago

@sebastiaopf Thank you for the detailed report!

I think it is because of the idea of using Model.Meta.ordering for SORTKEY (https://github.com/jazzband/django-redshift-backend/pull/20). It seems to me that having a separate setting value, for example sortkey, would solve this problem.

The other problem you mentioned, the use of functions not supported by Redshift such as unnest(), can be addressed if we know what kind of SQL is being generated by what kind of ORM queries. Since this backend is based on the postgres backend, it is affected by the addition of features to the base. Each time this happens, we need to adjust the SQL syntax and functions that are not available in Redshift. However, since these backend developers can't keep track of everything by themselves, it would be great if you could let us know when you find something.

shimizukawa commented 2 years ago

fixed by https://github.com/jazzband/django-redshift-backend/pull/97.

shimizukawa commented 2 years ago

It has been released https://pypi.org/project/django-redshift-backend/3.0.0/

jazzband / django-redshift-backend