Getting error can't resolve lable

[GMishx]

Hello team,

I just installed POWA on PostgreSQL 11 running on my Debian Buster machine following the instructions given at https://powa.readthedocs.io/en/latest.

While loading any database, I am getting following error:

/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py:4533: SAWarning: Textual column expression 'dbid' should be explicitly declared with text('dbid'), or use column('dbid') for more specificity (this warning may be suppressed after10 occurrences)
else "column",
[E 190805 10:16:10 web:1788] Uncaught exception GET /metrics/database_overview/fossology/?from=2019-08-05+09%3A16%3A10%2B0530&to=2019-08-05+10%3A16%3A10%2B0530 (127.0.0.1)
HTTPServerRequest(protocol='http', host='localhost:8888', method='GET', uri='/metrics/database_overview/fossology/?from=2019-08-05+09%3A16%3A10%2B0530&to=2019-08-05+10%3A16%3A10%2B0530', version='HTTP/1.1', remote_ip='127.0.0.1')
Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 756, in visit_textual_label_reference
col = only_froms[element.element]
KeyError: 'dbid'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.7/dist-packages/tornado/web.py", line 1697, in _execute
result = method(*self.path_args, **self.path_kwargs)
File "/usr/local/lib/python3.7/dist-packages/powa/dashboards.py", line 77, in get
values = self.execute(query, params=url_params)
File "/usr/local/lib/python3.7/dist-packages/powa/framework.py", line 184, in execute
return engine.execute(query, **params)
File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 2144, in execute
return connection.execute(statement, *multiparams, **params)
File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 980, in execute
return meth(self, multiparams, params)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py", line 273, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1090, in _execute_clauseelement
else None,
File "<string>", line 1, in <lambda>
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py", line 448, in compile
return self._compiler(dialect, bind=bind, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py", line 454, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 553, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 310, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 341, in process
return obj._compiler_dispatch(self, **kwargs)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2068, in visit_select
text, select, inner_columns, froms, byfrom, kwargs
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2160, in _compose_select_body
for f in froms
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2160, in <listcomp>
for f in froms
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 1678, in visit_alias
self, asfrom=True, **kwargs
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2043, in visit_select
for name, column in select._columns_plus_names
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2043, in <listcomp>
for name, column in select._columns_plus_names
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 1796, in _label_select_column
return result_expr._compiler_dispatch(self, **column_clause_args)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 811, in visit_label
+ self.preparer.format_label(label, labelname)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 1074, in visit_function
"expr": self.function_argspec(func, **kwargs)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 1087, in function_argspec
return func.clause_expr._compiler_dispatch(self, **kwargs)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 709, in visit_grouping
return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 961, in visit_clauselist
c._compiler_dispatch(self, **kw) for c in clauselist.clauses
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 959, in <genexpr>
s
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 961, in <genexpr>
c._compiler_dispatch(self, **kw) for c in clauselist.clauses
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 1249, in visit_binary
return self._generate_generic_binary(binary, opstring, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 1293, in _generate_generic_binary
+ binary.right._compiler_dispatch(
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 1037, in visit_over
("ORDER", over.order_by),
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 1039, in <listcomp>
if clause is not None and len(clause)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 961, in visit_clauselist
c._compiler_dispatch(self, **kw) for c in clauselist.clauses
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 959, in <genexpr>
s
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 961, in <genexpr>
c._compiler_dispatch(self, **kw) for c in clauselist.clauses
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
return meth(self, **kw)
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 763, in visit_textual_label_reference
"Can't resolve label reference for ORDER BY / GROUP BY.",
File "/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py", line 4441, in _no_text_coercion
"extra": "%s " % extra if extra else "",
sqlalchemy.exc.CompileError: Can't resolve label reference for ORDER BY / GROUP BY. Textual SQL expression 'dbid' should be explicitly declared as text('dbid')
[E 190805 10:16:10 web:2246] 500 GET /metrics/database_overview/fossology/?from=2019-08-05+09%3A16%3A10%2B0530&to=2019-08-05+10%3A16%3A10%2B0530 (127.0.0.1) 42.00ms

[GMishx]

Further, I am getting following warnings as well:

[I 190805 10:24:08 web:2246] 200 GET /metrics/database_all_queries_waits/fossology/?from=2019-08-05+09%3A24%3A07%2B0530&to=2019-08-05+10%3A24%3A07%2B0530 (127.0.0.1) 81.73ms
/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py:4533: SAWarning: Textual column expression 'qualid' should be explicitly declared with text('qualid'), or use column('qualid') for more specificity (this warning may be suppressedafter 10 occurrences)
else "column",
/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py:4533: SAWarning: Textual column expression 'occurences' should be explicitly declared with text('occurences'), or use column('occurences') for more specificity (this warning may be suppressed after 10 occurrences)
else "column",
/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py:4533: SAWarning: Textual column expression 'filter_ratio' should be explicitly declared with text('filter_ratio'), or use column('filter_ratio') for more specificity (this warningmay be suppressed after 10 occurrences)
else "column",
[I 190805 10:24:16 web:2246] 200 GET /metrics/database/fossology/wizard/?from=2019-08-05+09%3A24%3A07%2B0530&to=2019-08-05+10%3A24%3A07%2B0530 (127.0.0.1) 41.35ms

[rjuju]

Hello,

Thanks for the report. I believe this was fixed in https://github.com/powa-team/powa-web/commit/6fedf9d606349ecf42b7f020ea6abc70e535902f and https://github.com/powa-team/powa-web/commit/e1f49990ec14e3efc5bf9c47588a2dc2c4bf5e50.

This is unfortunately only available for powa-web v4, which is still in beta. If you have to stay with v3, the only alternative would be to downgrade sqlalchemy for a version prior to 1.3.

[GMishx]

Thanks for the update. I've downgraded sqlalchemy to 1.2.0 and it is working now.