You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "cesar-loadsmart (via GitHub)" <gi...@apache.org> on 2023/03/01 17:48:34 UTC
[GitHub] [superset] cesar-loadsmart opened a new issue, #23251: SQL Server DB connection only displaying system tables on SQL Lab
cesar-loadsmart opened a new issue, #23251:
URL: https://github.com/apache/superset/issues/23251
I've followed this [doc](https://superset.apache.org/docs/databases/sql-server/) to connect Superset with a Microsoft SQL Server Database, but using the recommended connection string `mssql+pymssql://<Username>:<Password>@<Host>:<Port-default:1433>/<Database Name>/?Encrypt=yes` did not work:
<img width="846" alt="image" src="https://user-images.githubusercontent.com/89807801/222209478-0cd5302b-669c-45f3-9a33-602605a39de8.png">
Logs are also unclear:
```
SupersetErrorsException
Traceback (most recent call last):
File "/app/superset/databases/commands/test_connection.py", line 120, in run
raise DBAPIError(None, None, None)
sqlalchemy.exc.DBAPIError: (builtins.NoneType) None
(Background on this error at: http://sqlalche.me/e/13/dbapi)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1499, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1485, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 89, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 113, in wraps
raise ex
File "/app/superset/views/base_api.py", line 110, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1507, in time_function
response = func(*args, **kwargs)
File "/app/superset/utils/log.py", line 245, in wrapper
value = f(*args, **kwargs)
File "/app/superset/views/base_api.py", line 83, in wraps
return f(self, *args, **kwargs)
File "/app/superset/databases/api.py", line 709, in test_connection
TestConnectionDatabaseCommand(g.user, item).run()
File "/app/superset/databases/commands/test_connection.py", line 145, in run
raise DatabaseTestConnectionFailedError(errors) from ex
superset.databases.commands.exceptions.DatabaseTestConnectionFailedError: [SupersetError(message='(builtins.NoneType) None\n(Background on this error at: http://sqlalche.me/e/13/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Azure Synapse', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
2023-03-01 13:09:04,232:WARNING:superset.views.base:SupersetErrorsException
Traceback (most recent call last):
File "/app/superset/databases/commands/test_connection.py", line 120, in run
raise DBAPIError(None, None, None)
sqlalchemy.exc.DBAPIError: (builtins.NoneType) None
(Background on this error at: http://sqlalche.me/e/13/dbapi)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1499, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1485, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 89, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 113, in wraps
raise ex
File "/app/superset/views/base_api.py", line 110, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1507, in time_function
response = func(*args, **kwargs)
File "/app/superset/utils/log.py", line 245, in wrapper
value = f(*args, **kwargs)
File "/app/superset/views/base_api.py", line 83, in wraps
return f(self, *args, **kwargs)
File "/app/superset/databases/api.py", line 709, in test_connection
TestConnectionDatabaseCommand(g.user, item).run()
File "/app/superset/databases/commands/test_connection.py", line 145, in run
raise DatabaseTestConnectionFailedError(errors) from ex
superset.databases.commands.exceptions.DatabaseTestConnectionFailedError: [SupersetError(message='(builtins.NoneType) None\n(Background on this error at: http://sqlalche.me/e/13/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Azure Synapse', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
```
So, I tested some different connections string, the following two worked:
`mssql+pymssql://<user>:<password>@<host>/?charset=utf8`
`mssql+pymssql://<user>:<password>@<host>:<port>`
However, when checking the Database schema on SQL Lab, it only shows the system tables:
It's not a permissions problem, because it works on Dbeaver!
Queries also work, by specifying the complete path such as: `select * from db_name.dbo.table_name;`
**The only problem is the fact that it is only displaying system tables and views.**
#### How to reproduce the bug
1. Go to Databases tab
2. Create a new database connection using pymssql
3. By using the default connection string, you should receive an error message.
4. See error
5. Try using my suggested connection string, it may work, but you will only see system tables at the SQL Lab.
### Expected results
Show all tables inside the database
### Actual results
Only shows system tables, such as:
### Environment
- browser type and version: Chrome Version 110.0.5481.100 (Official Build) (x86_64), but the same happens for all browsers
- superset version: `2.0.0`
- lib version: `pymssql==2.2.7`
- python version: `3.8`
- node.js version: `node 16`
- any feature flags active: ALERT_REPORTS, DASHBOARD_NATIVE_FILTERS, ENABLE_TEMPLATE_PROCESSING
- My Superset is running via Kubernetes
### Checklist
Make sure to follow these steps before submitting your issue - thank you!
- [X] I have checked the superset logs for python stacktraces and included it here as text if there are any.
- [ ] I have reproduced the issue with at least the latest released version of superset.
- [X] I have checked the issue tracker for the same issue and I haven't found one similar.
### Additional context
This is not a database we own, so some tests would require getting in contact with the external provider, something We wouldn't like to do at this moment.
--
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] SQL Server DB connection only displaying system tables on SQL Lab [superset]
Posted by "geido (via GitHub)" <gi...@apache.org>.
geido closed issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
URL: https://github.com/apache/superset/issues/23251
--
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
[GitHub] [superset] kikolouro commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "kikolouro (via GitHub)" <gi...@apache.org>.
kikolouro commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1461756686
Any updates on this topic?
--
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
[GitHub] [superset] kikolouro commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "kikolouro (via GitHub)" <gi...@apache.org>.
kikolouro commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1488468175
@RobLucchi
So I have two scenarios:
Scenario A
machine A - has superset and other services, linux machine and superset running on docker
machine B - has SQL Server in a domain.
When connection superset to the SQL Server using a Domain User gives me the error.
Scenario B
machine A - has superset and other services, linux machine and superset running on docker
machine B - has SQL Server and it is NOT in a domain
When connecting superset to the SQL using a local user (since it isn't in a domain) does not give me a error.
Might add that machine A from scenario A and B are in different networks and the Scenario A is from a Client so a I don't have permission to do some things like local users, SQL users etc and they wont provide me.
When selecting a database type I choose "Microsoft SQL Server".
--
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] SQL Server DB connection only displaying system tables on SQL Lab [superset]
Posted by "geido (via GitHub)" <gi...@apache.org>.
geido commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-2610827725
Thank you for reporting this issue. However, the issue was reported on a version of Superset that we no longer support. As of this moment, we only actively support Superset 4.0 and 4.1. To maintain a more actionable Issues backlog, we're going to close this issue for now. If you (or anyone reading this) are still experiencing this on a currently supported version of Superset, please either reopen this Issue, or file a new one with updated context (screenshots, reproduction steps) and we'll do our best to support it. Thank you!
--
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
[GitHub] [superset] cesar-loadsmart commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "cesar-loadsmart (via GitHub)" <gi...@apache.org>.
cesar-loadsmart commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1452713707
Hey folks!
This stackoverflow entry, makes me believe the real problem is not specifying the Database:
https://stackoverflow.com/questions/29151478/how-does-a-table-not-being-in-information-schema-or-systables-happen
I believe that making the connection string work properly will fix my issue.
--
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
[GitHub] [superset] cesar-loadsmart commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "cesar-loadsmart (via GitHub)" <gi...@apache.org>.
cesar-loadsmart commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1456019459
Here I'm using database authentication,
I'm also waiting for my local machine to be whitelisted once again so that I can test a built docker image at the latest version connecting to the Database.
I believe that by using windows authentication, your connection string should look different, not sure though.
--
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
[GitHub] [superset] RobLucchi commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "RobLucchi (via GitHub)" <gi...@apache.org>.
RobLucchi commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1485873878
@cesar-loadsmart
I think the issue is when you are using:
`mssql+pymssql://<user>:<password>@<host>/?charset=utf8
`or
`mssql+pymssql://<user>:<password>@<host>:<port>
`
it connects to the master database, not your actual database
which I tested by making it like this:
`mssql+pymssql://<user>:<password>@<host>:<port>/master
`
it worked perfectly but when changing the DB to the required one I got the same error
--
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
[GitHub] [superset] JohnDietrich-Pepper commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "JohnDietrich-Pepper (via GitHub)" <gi...@apache.org>.
JohnDietrich-Pepper commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1617542677
Yep same issue. Synapse Serverless works great with no issues. The second you try and connect to a dedicated pool well then you can only connect to the master database as described above. Queries don't work for me because the dedicated pool doesn't support full path queries.
--
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] SQL Server DB connection only displaying system tables on SQL Lab [superset]
Posted by "pamelascr (via GitHub)" <gi...@apache.org>.
pamelascr commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1916684414
Hello all,
Did any of you find a solution to this problem? I tried every solution in this thread but I still was not able to connect to it. If I specify the driver I get an error that the URL has an unexpected "driver".
Please let me know if you find any solution for this... Stuck in this problem for some time.
--
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] SQL Server DB connection only displaying system tables on SQL Lab [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-2108771963
This issue seems to have gone stale... is anyone still facing this? We might need someone to recapitulate the issue as it pertains to currently supported versions (3.1/4.0). If it's a superset bug, we can leave it open. If it's a config issue, it should be a GitHub discussion instead (I can migrate it). If nobody's worried about it anymore, we can certainly close it. We also warmly welcome documentation contributions if anyone knows a solution and wants to help others in the future.
--
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
[GitHub] [superset] kikolouro commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "kikolouro (via GitHub)" <gi...@apache.org>.
kikolouro commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1456045820
What I've seen was you only needed to mention the user like "Domain\User" so the string would be something like: "mssql+pymssql://<Domain>\<user>:<password>@<host>:<port>/<DB>". I've also tried with double back slashes because character escaping is a thing.
--
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
[GitHub] [superset] kikolouro commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "kikolouro (via GitHub)" <gi...@apache.org>.
kikolouro commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1486477867
Didn't work. I've tested with another environment and it worked, the only difference is that the user i am connecting. For the environment that is not working the user is from a domain controller and the other is a local windows user.
--
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
[GitHub] [superset] kikolouro commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "kikolouro (via GitHub)" <gi...@apache.org>.
kikolouro commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1455973641
Hey!
I'm having the same problems connecting to the database server, the only difference is that I can't connect even without database on the connection string.
Are you using windows authentication?
--
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
[GitHub] [superset] RobLucchi commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "RobLucchi (via GitHub)" <gi...@apache.org>.
RobLucchi commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1486627083
@kikolouro
My user connects perfectly through DBeaver but not sqlalchemy URL.
can you please explain to me the difference between the two users? and if possible how to create it.
Aslo, are you connecting to the Azure SQL server or Azure Synapse Analytics?
--
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
[GitHub] [superset] RobLucchi commented on issue #23251: SQL Server DB connection only displaying system tables on SQL Lab
Posted by "RobLucchi (via GitHub)" <gi...@apache.org>.
RobLucchi commented on issue #23251:
URL: https://github.com/apache/superset/issues/23251#issuecomment-1488478927
well it worked with me after adding autocommit=true to the pyodbc driver URL
` mssql+pyodbc://USER:PASSWORD@HOST:PORT/Database?driver=ODBC+Driver+17+for+SQL+Server&autocommit=true`
--
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