Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Database problem after upgrade to 0.6.11 #230

Closed
sgofferj opened this issue Sep 10, 2022 · 4 comments
Closed

Database problem after upgrade to 0.6.11 #230

sgofferj opened this issue Sep 10, 2022 · 4 comments
Labels
api bug v0.6.x Issues planned in versions 0.6.x
Milestone
v0.6 - User regis...

Comments

@sgofferj
Copy link

After upgrade from 0.6.9 to 0.6.11, login fails.
Log:

[SQL: SELECT users.id AS users_id, users.username AS users_username, users.email AS users_email, users.password AS users_password, users.created_at AS users_created_at, users.admin AS users_admin, users.first_name AS users_first_name, users.last_name AS users_last_name, users.birth_date AS users_birth_date, users.location AS users_location, users.bio AS users_bio, users.picture AS users_picture, users.timezone AS users_timezone, users.weekm AS users_weekm, users.language AS users_language, users.imperial_units AS users_imperial_units, users.is_active AS users_is_active, users.email_to_confirm AS users_email_to_confirm, users.confirmation_token AS users_confirmation_token, users.display_ascent AS users_display_ascent 
FROM users 
WHERE lower(users.email) = lower(%(lower_1)s) AND users.is_active = true 
 LIMIT %(param_1)s]
[parameters: {'lower_1': '[email protected]', 'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/14/f405)
2022/09/10 10:34:25 - fittrackee - ERROR - Exception on /api/auth/login [POST]
Traceback (most recent call last):
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column users.display_ascent does not exist
LINE 1: ...s.confirmation_token AS users_confirmation_token, users.disp...
                                                             ^


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

Traceback (most recent call last):
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask/app.py", line 2073, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask/app.py", line 1519, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask/app.py", line 1517, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask/app.py", line 1503, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/fittrackee/users/auth.py", line 247, in login_user
    user = User.query.filter(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2819, in first
    return self.limit(1)._iter().first()
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2903, in _iter
    result = self.session.execute(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 332, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column users.display_ascent does not exist
LINE 1: ...s.confirmation_token AS users_confirmation_token, users.disp...
                                                             ^

[SQL: SELECT users.id AS users_id, users.username AS users_username, users.email AS users_email, users.password AS users_password, users.created_at AS users_created_at, users.admin AS users_admin, users.first_name AS users_first_name, users.last_name AS users_last_name, users.birth_date AS users_birth_date, users.location AS users_location, users.bio AS users_bio, users.picture AS users_picture, users.timezone AS users_timezone, users.weekm AS users_weekm, users.language AS users_language, users.imperial_units AS users_imperial_units, users.is_active AS users_is_active, users.email_to_confirm AS users_email_to_confirm, users.confirmation_token AS users_confirmation_token, users.display_ascent AS users_display_ascent 
FROM users 
WHERE lower(users.email) = lower(%(lower_1)s) AND users.is_active = true 
 LIMIT %(param_1)s]
[parameters: {'lower_1': '[email protected]', 'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/14/f405)
2022/09/10 10:36:55 - fittrackee - ERROR - Exception on /api/auth/login [POST]
Traceback (most recent call last):
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column users.display_ascent does not exist
LINE 1: ...s.confirmation_token AS users_confirmation_token, users.disp...
                                                             ^


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

Traceback (most recent call last):
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask/app.py", line 2073, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask/app.py", line 1519, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask/app.py", line 1517, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask/app.py", line 1503, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/fittrackee/users/auth.py", line 247, in login_user
    user = User.query.filter(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2819, in first
    return self.limit(1)._iter().first()
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2903, in _iter
    result = self.session.execute(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 332, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column users.display_ascent does not exist
LINE 1: ...s.confirmation_token AS users_confirmation_token, users.disp...
                                                             ^

[SQL: SELECT users.id AS users_id, users.username AS users_username, users.email AS users_email, users.password AS users_password, users.created_at AS users_created_at, users.admin AS users_admin, users.first_name AS users_first_name, users.last_name AS users_last_name, users.birth_date AS users_birth_date, users.location AS users_location, users.bio AS users_bio, users.picture AS users_picture, users.timezone AS users_timezone, users.weekm AS users_weekm, users.language AS users_language, users.imperial_units AS users_imperial_units, users.is_active AS users_is_active, users.email_to_confirm AS users_email_to_confirm, users.confirmation_token AS users_confirmation_token, users.display_ascent AS users_display_ascent 
FROM users 
WHERE lower(users.email) = lower(%(lower_1)s) AND users.is_active = true 
 LIMIT %(param_1)s]
[parameters: {'lower_1': '[email protected]', 'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/14/f405)

migrate.sh fails also:

┌─[root@nostromo][/storage/docker/docker-fittrackee]
└──▪ docker-compose exec fittrackee scripts/migrate.sh


INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 5e3a3a31c432 -> cd0e6cf83207, add ascent record
Traceback (most recent call last):
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ActiveSqlTransaction: ALTER TYPE ... ADD cannot run inside a transaction block


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

Traceback (most recent call last):
  File "/usr/src/app/.venv/bin/ftcli", line 8, in <module>
    sys.exit(cli())
  File "/usr/src/app/.venv/lib/python3.9/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/src/app/.venv/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/src/app/.venv/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/fittrackee/migrations/commands.py", line 24, in upgrade_db
    upgrade(directory=BASEDIR)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 98, in wrapped
    f(*args, **kwargs)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 185, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/command.py", line 322, in upgrade
    script.run_env()
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 855, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "/usr/src/app/.venv/lib/python3.9/site-packages/fittrackee/migrations/env.py", line 88, in <module>
    run_migrations_online()
  File "/usr/src/app/.venv/lib/python3.9/site-packages/fittrackee/migrations/env.py", line 80, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/runtime/environment.py", line 853, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
    step.migration_fn(**kw)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/fittrackee/migrations/versions/24_cd0e6cf83207_add_ascent_record.py", line 20, in upgrade
    op.execute(
  File "<string>", line 8, in execute
  File "<string>", line 3, in execute
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/operations/ops.py", line 2414, in execute
    return operations.invoke(op)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/operations/base.py", line 399, in invoke
    return fn(self, operation)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/operations/toimpl.py", line 207, in execute_sql
    operations.migration_context.impl.execute(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 202, in execute
    self._exec(sql, execution_options)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 195, in _exec
    return conn.execute(construct, multiparams)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 332, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.errors.ActiveSqlTransaction) ALTER TYPE ... ADD cannot run inside a transaction block

[SQL: 
        ALTER TYPE record_types ADD VALUE 'HA';
        ]
(Background on this error at: https://sqlalche.me/e/14/2j85)

I'm using a central PGSQL which worked so far great.
@SamR1
Copy link
Owner

SamR1 commented Sep 13, 2022

Hi,

Sorry for the delay.

The first error is due to using the application after upgrade without applying migrations.

The second one (migration error) is related to PostgreSQL version (I reproduced it with PostgreSQL 10 and 11, it seems ALTER TYPE needs PostgreSQL 12+ to work in migrations).
I'll release a patch to fix it.

Can you confirm the PostgreSQL version is <12? Thanks in advance.

@sgofferj
Copy link
Author

Jep, PGSQL is 10. Upgrading PGSQL is a bit of an effort so I haven't gotten around to do it yet...

@sgofferj
Copy link
Author

By the way, stop apologizing for delays 😄 !
You're sacrificing your free time to provide a great piece of free and open source software! It's fine to not respond immediately.

@SamR1 SamR1 changed the title Database problem after upgrade from 0.6.9 to 0.6.11 Database problem after upgrade to 0.6.11 Sep 14, 2022
@SamR1 SamR1 closed this as completed in 5e0ebd6 Sep 14, 2022
@SamR1 SamR1 added bug api v0.6.x Issues planned in versions 0.6.x labels Sep 14, 2022
@SamR1
Copy link
Owner

SamR1 commented Sep 14, 2022

The fix has been released in v0.6.12, the migration should now work on PostgreSQL 10 and 11.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
api bug v0.6.x Issues planned in versions 0.6.x
Projects
None yet
Milestone
v0.6 - User registration and account
Development

No branches or pull requests
2 participants
@sgofferj @SamR1