You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "colpari (via GitHub)" <gi...@apache.org> on 2023/10/20 13:37:44 UTC
[I] Querying trough the Meta Database fails for some tables [superset]
colpari opened a new issue, #25725:
URL: https://github.com/apache/superset/issues/25725
#### How to reproduce the bug
1. Set up Meta Database like described [here](https://superset.apache.org/docs/databases/meta-database/#enabling-the-meta-database) - in our case it's called 'meta'
2. Select the Meta Database in SQLLab
3. Try to run queries in /sqllab on other databases trough the Meta Database (`SELECT * from "meta.3cxCallLog%2Ecall_log"; `)
### Expected results
Data is returned.
### Actual results
```
fptbr 10.200.14.252 - - [20/Oct/2023:13:02:11 +0000] "PUT /tabstateview/49 HTTP/1.1" 200 2 "https://XXXX/sqllab/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
fptbr Triggering query_id: 801
fptbr 2023-10-20 13:02:11,182:INFO:superset.sqllab.commands.execute:Triggering query_id: 801
fptbr Query 801: Executing 1 statement(s)
fptbr 2023-10-20 13:02:11,249:INFO:superset.sql_lab:Query 801: Executing 1 statement(s)
fptbr Query 801: Set query to 'running'
fptbr 2023-10-20 13:02:11,249:INFO:superset.sql_lab:Query 801: Set query to 'running'
fptbr Query 801: Running statement 1 out of 1
fptbr 2023-10-20 13:02:11,286:INFO:superset.sql_lab:Query 801: Running statement 1 out of 1
fptbr 2023-10-20 13:02:11,333:DEBUG:shillelagh.backends.apsw.vt:Instantiating adapter with deserialized arguments: ['meta.3cxCallLog%2Ecall_log', None]
fptbr 10.200.14.252 - - [20/Oct/2023:13:02:11 +0000] "PUT /tabstateview/49 HTTP/1.1" 200 2 "https://XXXX/sqllab/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
fptbr /app/superset/extensions/metadb.py:383: SAWarning: Dialect superset:apsw will not make use of SQL compilation caching as it does not set the 'supports_statement_cache' attribute to ``True``. This can have significant performance implications including some performance degradations in comparison to prior SQLAlchemy versions. Dialect maintainers should seek to set this attribute to True after appropriate development and testing for SQLAlchemy 1.4 caching support. Alternatively, this attribute may be set to False which will disable this warning. (Background on this error at: https://sqlalche.me/e/14/cprf)
fptbr rows = connection.execute(query)
fptbr 2023-10-20 13:02:12,127:DEBUG:shillelagh.backends.apsw.vt:Instantiating adapter with deserialized arguments: ['3cxCallLog.call_log', None]
fptbr SupersetErrorsException
fptbr Traceback (most recent call last):
fptbr File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
fptbr rv = self.dispatch_request()
fptbr File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
fptbr return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
fptbr File "/usr/local/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
fptbr return f(self, *args, **kwargs)
fptbr File "/app/superset/views/base_api.py", line 127, in wraps
fptbr raise ex
fptbr File "/app/superset/views/base_api.py", line 121, in wraps
fptbr duration, response = time_function(f, self, *args, **kwargs)
fptbr File "/app/superset/utils/core.py", line 1518, in time_function
fptbr response = func(*args, **kwargs)
fptbr File "/app/superset/views/base_api.py", line 93, in wraps
fptbr return f(self, *args, **kwargs)
fptbr File "/app/superset/utils/log.py", line 255, in wrapper
fptbr value = f(*args, **kwargs)
fptbr File "/app/superset/sqllab/api.py", line 357, in execute_sql_query
fptbr command_result: CommandResult = command.run()
fptbr File "/app/superset/sqllab/commands/execute.py", line 121, in run
fptbr raise ex
fptbr File "/app/superset/sqllab/commands/execute.py", line 103, in run
fptbr status = self._run_sql_json_exec_from_scratch()
fptbr File "/app/superset/sqllab/commands/execute.py", line 161, in _run_sql_json_exec_from_scratch
fptbr raise ex
fptbr File "/app/superset/sqllab/commands/execute.py", line 156, in _run_sql_json_exec_from_scratch
fptbr return self._sql_json_executor.execute(
fptbr File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
fptbr raise SupersetErrorsException(
fptbr superset.exceptions.SupersetErrorsException: [SupersetError(message='superset error: Bad return type from function callback', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Superset meta database', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
fptbr 2023-10-20 13:02:12,354:WARNING:superset.views.base:SupersetErrorsException
fptbr Traceback (most recent call last):
fptbr File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
fptbr rv = self.dispatch_request()
fptbr File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
fptbr return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
fptbr File "/usr/local/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
fptbr return f(self, *args, **kwargs)
fptbr File "/app/superset/views/base_api.py", line 127, in wraps
fptbr raise ex
fptbr File "/app/superset/views/base_api.py", line 121, in wraps
fptbr duration, response = time_function(f, self, *args, **kwargs)
fptbr File "/app/superset/utils/core.py", line 1518, in time_function
fptbr response = func(*args, **kwargs)
fptbr File "/app/superset/views/base_api.py", line 93, in wraps
fptbr return f(self, *args, **kwargs)
fptbr File "/app/superset/utils/log.py", line 255, in wrapper
fptbr value = f(*args, **kwargs)
fptbr File "/app/superset/sqllab/api.py", line 357, in execute_sql_query
fptbr command_result: CommandResult = command.run()
fptbr File "/app/superset/sqllab/commands/execute.py", line 121, in run
fptbr raise ex
fptbr File "/app/superset/sqllab/commands/execute.py", line 103, in run
fptbr status = self._run_sql_json_exec_from_scratch()
fptbr File "/app/superset/sqllab/commands/execute.py", line 161, in _run_sql_json_exec_from_scratch
fptbr raise ex
fptbr File "/app/superset/sqllab/commands/execute.py", line 156, in _run_sql_json_exec_from_scratch
fptbr return self._sql_json_executor.execute(
fptbr File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
fptbr raise SupersetErrorsException(
fptbr superset.exceptions.SupersetErrorsException: [SupersetError(message='superset error: Bad return type from function callback', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Superset meta database', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
```
#### Screenshots
### Environment
(please complete the following information):
- browser type and version: Chromium Version 118.0.5993.70 (Official Build) snap (64-bit)
- superset version: The Superset UI shows 0.0.0-dev. We're using [this version](https://hub.docker.com/layers/apache/superset/latest/images/sha256-c8df09d7013b9e00449280303bec766cec904c42fcf2057366f9a5f30095c80b) of the 'latest' image from docker hub with the additional layers
```
USER root
RUN pip3 install AuthLib cloud-sql-python-connector google google-cloud-bigquery
USER superset
```
added by us
- python version: as in the docker hub image
- node.js version: as in the docker hub image
- any feature flags active: `FEATURE_FLAGS = {"ALERT_REPORTS": True, "ENABLE_SUPERSET_META_DB": True}`
- database 3cxCallLog is on a Postgres16 Server but it's maybe worthwhile noting that it was imported from a PG13 Server with `pg_dump -F c | pg_restore -xO` while keeping the original database name
### Additional context
* If we directly select the 3cxCallLog database in SQL Lab and run `SELECT * from "call_log";` it works as expected
* Everything about the 3cxCallLog database also works as expected when used in the `psql` client
* _**If we add a simple table containing 2 varchar-fields into the 3cxCallLog DB we are able to successfully query it via the Meta Database and get results**_
### schema of the failing table (holds ~410K records)
```
c3cxlog=# \d+ call_log
Table "public.call_log"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
historyid | integer | | not null | | plain | | |
callid | character varying(128) | | | | extended | | |
duration | interval | | | | plain | | |
time_start | timestamp with time zone | | | | plain | | |
time_answered | timestamp with time zone | | | | plain | | |
time_end | timestamp with time zone | | | | plain | | |
reason_terminated | character varying(128) | | | | extended | | |
from_no | character varying(128) | | | | extended | | |
to_no | character varying(128) | | | | extended | | |
from_dn | character varying(128) | | | | extended | | |
to_dn | character varying(128) | | | | extended | | |
dial_no | character varying(128) | | | | extended | | |
reason_changed | character varying(128) | | | | extended | | |
final_number | character varying(128) | | | | extended | | |
final_dn | character varying(128) | | | | extended | | |
chain | text | | | | extended | | |
from_type | character varying(128) | | | | extended | | |
to_type | character varying(128) | | | | extended | | |
final_type | character varying(128) | | | | extended | | |
from_dispname | character varying(256) | | | | extended | | |
to_dispname | character varying(128) | | | | extended | | |
final_dispname | character varying(128) | | | | extended | | |
missed_queue_calls | text | | | | extended | | |
c3cx_source | character varying(128) | | | | extended | | |
c3cx_time_received | timestamp with time zone | | | | plain | | |
start_time | timestamp with time zone | | | | plain | | |
end_time | timestamp with time zone | | | | plain | | |
ringing_dur | interval | | | | plain | | |
talking_dur | interval | | | | plain | | |
q_wait_dur | interval | | | | plain | | |
Indexes:
"call_log_pkey" PRIMARY KEY, btree (historyid)
Access method: heap
```
### schema of the working table in the same database
```
c3cxlog=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
code | character(5) | | | | extended | | |
title | character varying(40) | | | | extended | | |
Access method: heap
```
### schema of a more complex table (odoo users table) from another DB (also imported from the same PG13 to the same PG16 with the same command) which works too:
```
o_sv=# \d+ res_users
Table "public.res_users"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+-----------------------------+-----------+----------+---------------------------------------+----------+-------------+--------------+--------------------
id | integer | | not null | nextval('res_users_id_seq'::regclass) | plain | | |
active | boolean | | | true | plain | | |
login | character varying | | not null | | extended | | |
password | character varying | | | | extended | | |
company_id | integer | | not null | | plain | | |
partner_id | integer | | not null | | plain | | |
create_date | timestamp without time zone | | | | plain | | |
signature | text | | | | extended | | | Email Signature
action_id | integer | | | | plain | | | Home Action
share | boolean | | | | plain | | | Share User
create_uid | integer | | | | plain | | | Created by
write_uid | integer | | | | plain | | | Last Updated by
write_date | timestamp without time zone | | | | plain | | | Last Updated on
totp_secret | character varying | | | | extended | | | Totp Secret
notification_type | character varying | | not null | | extended | | | Notification
odoobot_state | character varying | | | | extended | | | OdooBot Status
odoobot_failed | boolean | | | | plain | | | Odoobot Failed
sale_team_id | integer | | | | plain | | | User Sales Team
oauth_provider_id | integer | | | | plain | | | OAuth Provider
oauth_uid | character varying | | | | extended | | | OAuth User ID
oauth_access_token | character varying | | | | extended | | | OAuth Access Token
website_id | integer | | | | plain | | | Website
karma | integer | | | | plain | | | Karma
rank_id | integer | | | | plain | | | Rank
next_rank_id | integer | | | | plain | | | Next Rank
Indexes:
"res_users_pkey" PRIMARY KEY, btree (id)
"res_users_login_key" UNIQUE CONSTRAINT, btree (login, website_id)
"res_users_partner_id_index" btree (partner_id)
"res_users_uniq_users_oauth_provider_oauth_uid" UNIQUE CONSTRAINT, btree (oauth_provider_id, oauth_uid)
Foreign-key constraints:
"res_users_company_id_fkey" FOREIGN KEY (company_id) REFERENCES res_company(id) ON DELETE RESTRICT
"res_users_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET NULL
"res_users_next_rank_id_fkey" FOREIGN KEY (next_rank_id) REFERENCES gamification_karma_rank(id) ON DELETE SET NULL
"res_users_oauth_provider_id_fkey" FOREIGN KEY (oauth_provider_id) REFERENCES auth_oauth_provider(id) ON DELETE SET NULL
"res_users_partner_id_fkey" FOREIGN KEY (partner_id) REFERENCES res_partner(id) ON DELETE RESTRICT
"res_users_rank_id_fkey" FOREIGN KEY (rank_id) REFERENCES gamification_karma_rank(id) ON DELETE SET NULL
"res_users_sale_team_id_fkey" FOREIGN KEY (sale_team_id) REFERENCES crm_team(id) ON DELETE SET NULL
"res_users_website_id_fkey" FOREIGN KEY (website_id) REFERENCES website(id) ON DELETE SET NULL
"res_users_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET NULL
Access method: heap
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Querying trough the Meta Database fails for some tables [superset]
Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #25725:
URL: https://github.com/apache/superset/issues/25725#issuecomment-1773083487
This is definitely a bug in the SQLite library, surfaced by apsw. I suspect it's the `interval` column. I'll create a similar table in Postgres and test.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Querying trough the Meta Database fails for some tables [superset]
Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #25725:
URL: https://github.com/apache/superset/issues/25725#issuecomment-1773118621
OK, I was able to create a similar table ([ChatGPT can be awesome](https://chat.openai.com/share/e3fd59a2-9f05-4c31-ac22-190cfb21caa4)) and repro the bug. Working on a fix.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Querying trough the Meta Database fails for some tables [superset]
Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida closed issue #25725: Querying trough the Meta Database fails for some tables
URL: https://github.com/apache/superset/issues/25725
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Querying trough the Meta Database fails for some tables [superset]
Posted by "colpari (via GitHub)" <gi...@apache.org>.
colpari commented on issue #25725:
URL: https://github.com/apache/superset/issues/25725#issuecomment-1773721215
Works in [latest-dev](https://hub.docker.com/layers/apache/superset/latest-dev/images/sha256-7aa901e20ff9c24df433984d7c67e2ebebdb8c7e20b4fb9c863a11c34b144e3b?context=explore) - thank you folks!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Querying trough the Meta Database fails for some tables [superset]
Posted by "mdeshmu (via GitHub)" <gi...@apache.org>.
mdeshmu commented on issue #25725:
URL: https://github.com/apache/superset/issues/25725#issuecomment-1773027578
cc: @betodealmeida
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Querying trough the Meta Database fails for some tables [superset]
Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #25725:
URL: https://github.com/apache/superset/issues/25725#issuecomment-1773045973
Taking a look, thanks for the detailed info!
In the meantime, a small comment: you don't have to use the meta database name (though you can). So instead of this:
```sql
SELECT * from "meta.3cxCallLog%2Ecall_log";
```
It's better to write:
```sql
SELECT * from "3cxCallLog.call_log";
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org