Login not possible, SQL user table misses email column #177

Snamhdaen · 2023-05-31T08:10:24Z

First things first: Thanks for this awesome project, I like it a lot!

Now the issue(s): Yesterday all of a sudden login attempts started to fail. Up until that point I had no issues and neither did the other user of our "household". I run a docker container, which was healthy and reachable. Credentials were also guaranteed to be correct (managed by bitwarden).
I tried to investigate and ran docker exec -it NAME_OF_THE_BACKEND_CONTAINER python manage.py in order to reset the password manually. Curiously, the script failed with a bunch of error messages. Its main complaint seemed to be that there was no email in the user table. So I manually added an email column to the user table in the sql database and ran the script again. And indeed, this time it ran without issue. I managed to reset my password and have regained access.
Unfortunately, I am at a loss why I became locked out in the first place (maybe something to do with the email column as well? I didn't check whether login was again possible after addition of the column and before resetting the pw).
One additional thing I noticed: In my setting the frontend container does not restart after reboot. I have to manually restart using docker-compose, which is manageable if a bit annoying.

The text was updated successfully, but these errors were encountered:

TomBursch · 2023-05-31T08:32:05Z

Really strange. Generally, I would recommend not editing the database schema manually. Migrating it to future versions could lead to errors if the schema is different from what the migration expects. In the database, there is a table called alembic_version, with a version_num row. The value should be ed32086bf606, is it the same in your database?

PinkFloyd1213 · 2023-05-31T11:28:13Z

Hello,

I have exactly the same problem with a clean, new installation. I get these error messages when I try to manage users with the manage.py script. Below is the error code provided.

root@localhost:~/kitchenowl# docker exec -it 17cee98a503b python manage.py

Manage KitchenOwl
---
What do you want to do?
1.  Manage users
2.  Manage households
3.  Import files
4.  Run all jobs
(q) Exit
Your selection (q):1

What next?
    1. List all users
    2. Update user
    3. Delete user
    (q) Go back
Your selection (q):1
Traceback (most recent call last):
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1968, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: user.email

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

Traceback (most recent call last):
  File "/usr/src/kitchenowl/manage.py", line 112, in <module>
    manageUsers()
  File "/usr/src/kitchenowl/manage.py", line 43, in manageUsers
    for u in User.all():
             ^^^^^^^^^^
  File "/usr/src/kitchenowl/app/helpers/db_model_mixin.py", line 67, in all
    return cls.query.order_by(cls.id).all()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2692, in all
    return self._iter().all()  # type: ignore
           ^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
                                                  ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2232, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2127, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1413, in execute
    return meth(
           ^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1987, in _exec_single_context
    self._handle_dbapi_exception(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2344, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1968, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: user.email
[SQL: SELECT user.id AS user_id, user.name AS user_name, user.username AS user_username, user.email AS user_email, user.password AS user_password, user.photo AS user_photo, user.admin AS user_admin, user.created_at AS user_created_at, user.updated_at AS user_updated_at
FROM user ORDER BY user.id]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

TomBursch · 2023-05-31T11:49:38Z

Thanks for the logs. Both my servers had no problem upgrading, so I'm a bit baffled. Can you guys try to run docker exec -it NAME_OF_THE_BACKEND_CONTAINER flask db upgrade and let me know if there are any errors?

PinkFloyd1213 · 2023-05-31T11:53:41Z

Sure, here is the output:
I tried to redo the manage.py after that and same error.

root@localhost:~/kitchenowl# docker exec -it cdd80df155e5 flask db upgrade
/usr/src/kitchenowl/migrations/env.py:21: DeprecationWarning: 'get_engine' is deprecated and will be removed in Flask-SQLAlchemy 3.1. Use 'engine' or 'engines[key]' instead.
  return current_app.extensions['migrate'].db.get_engine()
/usr/src/kitchenowl/migrations/env.py:21: DeprecationWarning: 'get_engine' is deprecated and will be removed in Flask-SQLAlchemy 3.1. Use 'engine' or 'engines[key]' instead.
  return current_app.extensions['migrate'].db.get_engine()
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 6c669d9ec3bd -> c058421705ec, empty message
Traceback (most recent call last):
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1968, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: table file already exists

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

Traceback (most recent call last):
  File "/opt/venv/bin/flask", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/flask/cli.py", line 1063, in main
    cli.main()
  File "/opt/venv/lib/python3.11/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/flask/cli.py", line 357, in decorator
    return __ctx.invoke(f, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/flask_migrate/cli.py", line 150, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
  File "/opt/venv/lib/python3.11/site-packages/flask_migrate/__init__.py", line 111, in wrapped
    f(*args, **kwargs)
  File "/opt/venv/lib/python3.11/site-packages/flask_migrate/__init__.py", line 200, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/opt/venv/lib/python3.11/site-packages/alembic/command.py", line 385, in upgrade
    script.run_env()
  File "/opt/venv/lib/python3.11/site-packages/alembic/script/base.py", line 582, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/opt/venv/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/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 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/usr/src/kitchenowl/migrations/env.py", line 110, in <module>
    run_migrations_online()
  File "/usr/src/kitchenowl/migrations/env.py", line 104, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/opt/venv/lib/python3.11/site-packages/alembic/runtime/environment.py", line 928, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/opt/venv/lib/python3.11/site-packages/alembic/runtime/migration.py", line 628, in run_migrations
    step.migration_fn(**kw)
  File "/usr/src/kitchenowl/migrations/versions/c058421705ec_.py", line 50, in upgrade
    op.create_table('file',
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "/opt/venv/lib/python3.11/site-packages/alembic/operations/ops.py", line 1277, in create_table
    return operations.invoke(op)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/alembic/operations/base.py", line 395, in invoke
    return fn(self, operation)
           ^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/alembic/operations/toimpl.py", line 114, in create_table
    operations.impl.create_table(table)
  File "/opt/venv/lib/python3.11/site-packages/alembic/ddl/impl.py", line 354, in create_table
    self._exec(schema.CreateTable(table))
  File "/opt/venv/lib/python3.11/site-packages/alembic/ddl/impl.py", line 193, in _exec
    return conn.execute(  # type: ignore[call-overload]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1413, in execute
    return meth(
           ^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 181, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1525, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1987, in _exec_single_context
    self._handle_dbapi_exception(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2344, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1968, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table file already exists
[SQL:
CREATE TABLE file (
        filename VARCHAR NOT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL,
        created_by INTEGER,
        CONSTRAINT pk_file PRIMARY KEY (filename),
        CONSTRAINT fk_file_created_by_user FOREIGN KEY(created_by) REFERENCES user (id)
)

]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

TomBursch · 2023-05-31T12:31:28Z

Strange that that error only came up now. Your database didn't do any migrations for a month or so.

@Snamhdaen I hope you have the same issue as @PinkFloyd1213. For the migration to succeed, you might have to delete the email column again. You can run the flask db migrate command and if it does not return an error you should be good to go.

Snamhdaen · 2023-05-31T13:56:16Z

This gave me the opportunity of further investigating my problem. As soon as I had deleted the email column, I was again unable to login. When I added it again, login went smoothly. The flask db command did not work, but after pulling the latest image everything works. Thanks a lot.
PS: Why does the frontend not restart after reboot, though? Maybe I should raise a seperate issue, it might possibly be only a problem on my end.

TomBursch mentioned this issue May 31, 2023

fix: migration of c058421705ec TomBursch/kitchenowl-backend#37

Merged

TomBursch closed this as completed in TomBursch/kitchenowl-backend#37 May 31, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Login not possible, SQL user table misses email column #177

Login not possible, SQL user table misses email column #177

Snamhdaen commented May 31, 2023 •

edited

Loading

TomBursch commented May 31, 2023

PinkFloyd1213 commented May 31, 2023

TomBursch commented May 31, 2023

PinkFloyd1213 commented May 31, 2023

TomBursch commented May 31, 2023 •

edited

Loading

Snamhdaen commented May 31, 2023

Login not possible, SQL user table misses email column #177

Login not possible, SQL user table misses email column #177

Comments

Snamhdaen commented May 31, 2023 • edited Loading

TomBursch commented May 31, 2023

PinkFloyd1213 commented May 31, 2023

TomBursch commented May 31, 2023

PinkFloyd1213 commented May 31, 2023

TomBursch commented May 31, 2023 • edited Loading

Snamhdaen commented May 31, 2023

Snamhdaen commented May 31, 2023 •

edited

Loading

TomBursch commented May 31, 2023 •

edited

Loading