Issue when updating aftter adding user_accounts : true in config
Closed this issue · 9 comments
FafnerKeyZee commented
Got a weird error when running update script after adding user_accounts in config, with sqlite3
* Migrate database.
Continue? (y/N) y
* Tip: There are .env or .flaskenv files present. Do "pip install python-dotenv" to use them.
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 7e42683b12cd, new bundle table
Traceback (most recent call last):
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 137, in _compiler_dispatch
meth = getter(visitor)
AttributeError: 'SQLiteTypeCompiler' object has no attribute 'visit_JSONB'. Did you mean: 'visit_JSON'?
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 6645, in visit_create_table
processed = self.process(
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 915, in process
return obj._compiler_dispatch(self, **kwargs)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 141, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 6676, in visit_create_column
text = self.get_column_specification(column, first_pk=first_pk)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/dialects/sqlite/base.py", line 1534, in get_column_specification
coltype = self.dialect.type_compiler_instance.process(
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 960, in process
return type_._compiler_dispatch(self, **kw)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 139, in _compiler_dispatch
return visitor.visit_unsupported_compilation(self, err, **kw) # type: ignore # noqa: E501
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 965, in visit_unsupported_compilation
raise exc.UnsupportedCompilationError(self, element) from err
sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.dialects.sqlite.base.SQLiteTypeCompiler object at 0x7f6e1c844e20> can't render element of type JSONB (Background on this error at: https://sqlalche.me/e/20/l7de)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/bin/flask", line 8, in <module>
sys.exit(main())
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask/cli.py", line 1105, in main
cli.main()
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/decorators.py", line 33, in new_func
return f(get_current_context(), *args, **kwargs)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask/cli.py", line 386, in decorator
return ctx.invoke(f, *args, **kwargs)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask_migrate/cli.py", line 154, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask_migrate/__init__.py", line 111, in wrapped
f(*args, **kwargs)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask_migrate/__init__.py", line 200, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/command.py", line 406, in upgrade
script.run_env()
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/script/base.py", line 582, in run_env
util.load_python_file(self.dir, "env.py")
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
module = load_module_py(module_id, path)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
spec.loader.exec_module(module) # type: ignore
File "<frozen importlib._bootstrap_external>", line 883, in exec_module
File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
File "/home/myuser/vulnerability-lookup/website/migrations/env.py", line 95, in <module>
run_migrations_online()
File "/home/myuser/vulnerability-lookup/website/migrations/env.py", line 87, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
self.get_context().run_migrations(**kw)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/runtime/migration.py", line 628, in run_migrations
step.migration_fn(**kw)
File "/home/myuser/vulnerability-lookup/website/migrations/versions/7e42683b12cd_new_bundle_table.py", line 21, in upgrade
op.create_table(
File "<string>", line 8, in create_table
File "<string>", line 3, in create_table
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/operations/ops.py", line 1311, in create_table
return operations.invoke(op)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/operations/base.py", line 442, in invoke
return fn(self, operation)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 131, in create_table
operations.impl.create_table(table)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/ddl/impl.py", line 369, in create_table
self._exec(schema.CreateTable(table))
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/ddl/impl.py", line 210, in _exec
return conn.execute(construct, params)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
return connection._execute_ddl(
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1526, in _execute_ddl
compiled = ddl.compile(
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 308, in compile
return self._compiler(dialect, **kw)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 69, in _compiler
return dialect.ddl_compiler(dialect, self, **kw)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 870, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 915, in process
return obj._compiler_dispatch(self, **kwargs)
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 141, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/home/myuser/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 6655, in visit_create_table
raise exc.CompileError(
sqlalchemy.exc.CompileError: (in table 'bundle', column 'related_vulnerabilities'): Compiler <sqlalchemy.dialects.sqlite.base.SQLiteTypeCompiler object at 0x7f6e1c844e20> can't render element of type JSONB
FafnerKeyZee commented
Hum...
I installed postgresql, instead of sqlite3, change the config file to work with postgresql and it's worrking like a charm... Nope I'm kidding,
Now I got:
* Migrate database.
* Tip: There are .env or .flaskenv files present. Do "pip install python-dotenv" to use them.
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 7e42683b12cd, new bundle table
Traceback (most recent call last):
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.DuplicateTable: relation "bundle" already exists
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/bin/flask", line 8, in <module>
sys.exit(main())
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask/cli.py", line 1105, in main
cli.main()
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/decorators.py", line 33, in new_func
return f(get_current_context(), *args, **kwargs)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask/cli.py", line 386, in decorator
return ctx.invoke(f, *args, **kwargs)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask_migrate/cli.py", line 154, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask_migrate/__init__.py", line 111, in wrapped
f(*args, **kwargs)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/flask_migrate/__init__.py", line 200, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/command.py", line 406, in upgrade
script.run_env()
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/script/base.py", line 582, in run_env
util.load_python_file(self.dir, "env.py")
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
module = load_module_py(module_id, path)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
spec.loader.exec_module(module) # type: ignore
File "<frozen importlib._bootstrap_external>", line 883, in exec_module
File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
File "/home/techteam/vulnerability-lookup/website/migrations/env.py", line 95, in <module>
run_migrations_online()
File "/home/techteam/vulnerability-lookup/website/migrations/env.py", line 87, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
self.get_context().run_migrations(**kw)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/runtime/migration.py", line 628, in run_migrations
step.migration_fn(**kw)
File "/home/techteam/vulnerability-lookup/website/migrations/versions/7e42683b12cd_new_bundle_table.py", line 21, in upgrade
op.create_table(
File "<string>", line 8, in create_table
File "<string>", line 3, in create_table
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/operations/ops.py", line 1311, in create_table
return operations.invoke(op)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/operations/base.py", line 442, in invoke
return fn(self, operation)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 131, in create_table
operations.impl.create_table(table)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/ddl/impl.py", line 369, in create_table
self._exec(schema.CreateTable(table))
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/alembic/ddl/impl.py", line 210, in _exec
return conn.execute(construct, params)
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
return connection._execute_ddl(
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
ret = self._execute_context(
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
return self._exec_single_context(
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
self._handle_dbapi_exception(
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/home/techteam/.cache/pypoetry/virtualenvs/vulnerabilitylookup-mbsOsXE9-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "bundle" already exists
[SQL:
CREATE TABLE bundle (
uuid UUID NOT NULL,
vulnerability_lookup_origin UUID NOT NULL,
name VARCHAR NOT NULL,
description VARCHAR NOT NULL,
description_format VARCHAR,
creation_timestamp TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
timestamp TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
related_vulnerabilities JSONB,
meta JSONB,
author_id INTEGER NOT NULL,
PRIMARY KEY (uuid),
FOREIGN KEY(author_id) REFERENCES "user" (id)
)
]
(Background on this error at: https://sqlalche.me/e/20/f405)
FafnerKeyZee commented
But a poetry run start works after this error.
By the way if we have a password with less than 6 char, we can't login :)
cedricbonhomme commented
I see, in your case the migration script should not be executed or it should detect that you have a new database and already up-to-date. Despite the fact that you see this error when running the update script you have no other issues related to this point. That's why poetry run start works correctly.
cedricbonhomme commented
I pushed a small change in order to disable a couple of useless namespaces of the API when you have set config.user_accounts to false. So you can test more without the new features... But honestly I really think that an important part of the future developments will require to have this boolean set to true.
FafnerKeyZee commented
Hey,
Thanks for the update. But looks like sqlite3 is still broken .
Will test on an other instance to confirm it :(
Regards,
cedricbonhomme commented
oh, no need to try SQLite3 with JSONB type. It cannot work. Initially we were using the simple JSON type from SQLAlchemy, but it's more limited. But it works with SQLite.
So SQLite does not support JSONB.
I'll remove this line from the default configuration file to make clear to not try with SQLite.
adulau commented
Should we just drop sqlite support to make it easier in the future? Just an idea
cedricbonhomme commented
I removed the reference to SQLite from the documentation and the default configuration file.
cedricbonhomme commented