search

doableware / djongo

Django and MongoDB database connector
https://www.djongomapper.com
GNU Affero General Public License v3.0
1.88k stars 354 forks source link

FAILED SQL during migration: not implemented alter command for SQL ALTER TABLE #481

Open FedeTorta opened 3 years ago

FedeTorta commented 3 years ago

I am using djongo 1.3.3 with Django 2.2.12 and with a docker container running MongoDB 4.4.1 on Windows 10. The project has the following INSTALLED_APPS section in settings.py:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'django_bootstrap_base_template',
    'core',
    'rest_framework',
    'rest_framework.authtoken',
    'explorer',
    'leaflet',
    'django_showurls',
    'django_extensions',
]

I am facing a migration problem when cloning the project. Since the project has already a migration file, after cloning, I run the command:

python manage.py migrate

Migrations seems to go well until migrations related to explorer app. Migrate command fails on explorer.0004_querylog_duration, due to the error 

Not implemented alter command for SQL ALTER TABLE "explorer_querylog" ADD COLUMN "duration" double NULL Applying explorer.0004_querylog_duration...Traceback (most recent call last):... djongo/cursor.py", line 59, in execute raise db_exe from e django.db.utils.DatabaseError

Traceback:

Operations to perform:
  Apply all migrations: admin, auth, authtoken, contenttypes, core, explorer, sessions
Running migrations:
This version of djongo does not support "NULL, NOT NULL column validation check" fully. Visit https://www.patreon.com/nesdis
  Applying contenttypes.0001_initial...This version of djongo does not support "schema validation using CONSTRAINT" fully. Visit https://www.patreon.com/nesdis
 OK
  Applying contenttypes.0002_remove_content_type_name...This version of djongo does not support "COLUMN DROP NOT NULL " fully. Visit https://www.patreon.com/nesdis
This version of djongo does not support "DROP CASCADE" fully. Visit https://www.patreon.com/nesdis
 OK
  Applying auth.0001_initial...This version of djongo does not support "schema validation using KEY" fully. Visit https://www.patreon.com/nesdis
This version of djongo does not support "schema validation using REFERENCES" fully. Visit https://www.patreon.com/nesdis
 OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying core.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying authtoken.0001_initial... OK
  Applying authtoken.0002_auto_20160226_1747... OK
  Applying explorer.0001_initial... OK
  Applying explorer.0002_auto_20150501_1515... OK
  Applying explorer.0003_query_snapshot...This version of djongo does not support "schema validation using NOT NULL" fully. Visit https://www.patreon.com/nesdis
This version of djongo does not support "COLUMN DROP DEFAULT " fully. Visit https://www.patreon.com/nesdis
 OK
Not implemented alter command for SQL ALTER TABLE "explorer_querylog" ADD COLUMN "duration" double NULL
  Applying explorer.0004_querylog_duration...Traceback (most recent call last):
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\cursor.py", line 51, in execute
    self.result = Query(
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\sql2mongo\query.py", line 783, in __init__
    self._query = self.parse()
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\sql2mongo\query.py", line 875, in parse
    raise e
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\sql2mongo\query.py", line 856, in parse
    return handler(self, statement)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\sql2mongo\query.py", line 888, in _alter
    query = AlterQuery(self.db, self.connection_properties, sm, self._params)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\sql2mongo\query.py", line 425, in __init__
    super().__init__(*args)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\sql2mongo\query.py", line 84, in __init__
    super().__init__(*args)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\sql2mongo\query.py", line 62, in __init__
    self.parse()
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\sql2mongo\query.py", line 435, in parse
    self._add(statement)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\sql2mongo\query.py", line 598, in _add
    raise SQLDecodeError(err_key=tok.value,
djongo.exceptions.SQLDecodeError:

        Keyword: double
        Sub SQL: ALTER TABLE "explorer_querylog" ADD COLUMN "duration" double NULL
        FAILED SQL: ('ALTER TABLE "explorer_querylog" ADD COLUMN "duration" double NULL',)
        Params: ([],)
        Version: 1.3.3

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\cursor.py", line 59, in execute
    raise db_exe from e
djongo.database.DatabaseError

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\core\management\__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\core\management\base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\core\management\base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\core\management\commands\migrate.py", line 232, in handle
    post_migrate_state = executor.migrate(
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\migrations\executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\migrations\executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\migrations\executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\migrations\migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\migrations\operations\fields.py", line 110, in database_forwards
    schema_editor.add_field(
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\backends\base\schema.py", line 447, in add_field
    self.execute(sql, params)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\backends\base\schema.py", line 137, in execute
    cursor.execute(sql, params)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\backends\utils.py", line 99, in execute
    return super().execute(sql, params)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\backends\utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\feder\Desktop\demo\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\feder\Desktop\demo\lib\site-packages\djongo\cursor.py", line 59, in execute
    raise db_exe from e
django.db.utils.DatabaseError

For now, the solution I found is to fake the migration on that specific migration file. Everything seem to work but I would like to understand what's wrong, because this solution is just a workaround.

mahsa-kh-21 commented 3 years ago

I'm stuck on the same error Would you please explain more about your workaround? How did you fake the migration exactly?

FedeTorta commented 3 years ago

I executed the command: python manage.py migrate --fake explorer 0004_querylog_duration

lsaavedr commented 3 years ago

fixed with #474

dariusmilea commented 3 years ago

It is not fixed I am dealing with the same error: `Running migrations: Applying hr.0002_auto_20210525_1447...This version of djongo does not support "schema validation using NOT NULL" fully. Visit https://nesdis.github.io/djongo/support/ This version of djongo does not support "COLUMN DROP DEFAULT " fully. Visit https://nesdis.github.io/djongo/support/ Not implemented alter command for SQL ALTER TABLE "hr_employee" ADD COLUMN "years_of_experience_in_domain" double DEFAULT %(0)s NOT NULL Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/djongo/cursor.py", line 51, in execute self.result = Query( File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 783, in init self._query = self.parse() File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 875, in parse raise e File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 856, in parse return handler(self, statement) File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 888, in _alter query = AlterQuery(self.db, self.connection_properties, sm, self._params) File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 425, in init super().init(args) File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 84, in init super().init(args) File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 62, in init self.parse() File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 435, in parse self._add(statement) File "/usr/local/lib/python3.9/site-packages/djongo/sql2mongo/query.py", line 598, in _add raise SQLDecodeError(err_key=tok.value, djongo.exceptions.SQLDecodeError:

    Keyword: double
    Sub SQL: ALTER TABLE "hr_employee" ADD COLUMN "years_of_experience_in_domain" double DEFAULT %(0)s NOT NULL
    FAILED SQL: ('ALTER TABLE "hr_employee" ADD COLUMN "years_of_experience_in_domain" double DEFAULT %(0)s NOT NULL',)
    Params: ([0.0],)
    Version: 1.3.4

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 86, in _execute return self.cursor.execute(sql, params) File "/usr/local/lib/python3.9/site-packages/djongo/cursor.py", line 59, in execute raise db_exe from e djongo.database.DatabaseError

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/usr/src/app/manage.py", line 22, in main() File "/usr/src/app/manage.py", line 18, in main execute_from_command_line(sys.argv) File "/usr/local/lib/python3.9/site-packages/django/core/management/init.py", line 401, in execute_from_command_line utility.execute() File "/usr/local/lib/python3.9/site-packages/django/core/management/init.py", line 395, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 328, in run_from_argv self.execute(*args, cmd_options) File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 369, in execute output = self.handle(*args, *options) File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 83, in wrapped res = handle_func(args, kwargs) File "/usr/local/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 231, in handle post_migrate_state = executor.migrate( File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 117, in migrate state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial) File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial) File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 245, in apply_migration state = migration.apply(state, schema_editor) File "/usr/local/lib/python3.9/site-packages/django/db/migrations/migration.py", line 124, in apply operation.database_forwards(self.app_label, schema_editor, old_state, project_state) File "/usr/local/lib/python3.9/site-packages/django/db/migrations/operations/fields.py", line 110, in database_forwards schema_editor.add_field( File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 480, in add_field self.execute(sql, params) File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 142, in execute cursor.execute(sql, params) File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 100, in execute return super().execute(sql, params) File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 68, in execute return self._execute_with_wrappers(sql, params, many=False, executor=self._execute) File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers return executor(sql, params, many, context) File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 86, in _execute return self.cursor.execute(sql, params) File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 90, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 86, in _execute return self.cursor.execute(sql, params) File "/usr/local/lib/python3.9/site-packages/djongo/cursor.py", line 59, in execute raise db_exe from e django.db.utils.DatabaseError`

SebastianRemander commented 3 years ago

Relates to #543 . The root cause seems to be that djongo/sql2mongo/operators.py fails to convert some sql queries to work with MongoDB.

RonitDey020801 commented 9 months ago

I had the same issue. I guess djongo does not allow to alter a few things in the database. I simply deleted the collection on mongodb i wanted to alter and also deleted the specific migration file in on django. Then ran the same thing again and the collection was created as a new one.