def test_execute_sql_statements_no_results_backend(
self, mock_execute_sql_statement, mock_get_query
):
sql = """
-- comment
SET @value = 42;
SELECT @value AS foo;
-- comment
"""
mock_session = mock.MagicMock()
mock_query = mock.MagicMock()
mock_query.database.allow_run_async = True
mock_cursor = mock.MagicMock()
mock_query.database.get_sqla_engine.return_value.raw_connection.return_value.cursor.return_value = (
mock_cursor
)
mock_query.database.db_engine_spec.run_multiple_statements_as_one = False
mock_get_query.return_value = mock_query
with pytest.raises(SupersetErrorException) as excinfo:
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
assert excinfo.value.error == SupersetError(
message="Results backend is not configured.",
error_type=SupersetErrorType.RESULTS_BACKEND_NOT_CONFIGURED_ERROR,
level=ErrorLevel.ERROR,
extra={
"issue_codes": [
{
"code": 1021,
"message": (
"Issue 1021 - Results backend needed for asynchronous "
"queries is not configured."
),
}
]
},
)
def check_ownership(obj: Any, raise_if_false: bool = True) -> bool:
"""Meant to be used in `pre_update` hooks on models to enforce ownership
Admin have all access, and other users need to be referenced on either
the created_by field that comes with the ``AuditMixin``, or in a field
named ``owners`` which is expected to be a one-to-many with the User
model. It is meant to be used in the ModelView's pre_update hook in
which raising will abort the update.
"""
if not obj:
return False
security_exception = SupersetSecurityException(
SupersetError(
error_type=SupersetErrorType.MISSING_OWNERSHIP_ERROR,
message="You don't have the rights to alter [{}]".format(obj),
level=ErrorLevel.ERROR,
))
if g.user.is_anonymous:
if raise_if_false:
raise security_exception
return False
roles = [r.name for r in get_user_roles()]
if "Admin" in roles:
return True
scoped_session = db.create_scoped_session()
orig_obj = scoped_session.query(obj.__class__).filter_by(id=obj.id).first()
# Making a list of owners that works across ORM models
owners: List[User] = []
if hasattr(orig_obj, "owners"):
owners += orig_obj.owners
if hasattr(orig_obj, "owner"):
owners += [orig_obj.owner]
if hasattr(orig_obj, "created_by"):
owners += [orig_obj.created_by]
owner_names = [o.username for o in owners if o]
if g.user and hasattr(g.user,
"username") and g.user.username in owner_names:
return True
if raise_if_false:
raise security_exception
else:
return False
def validate_parameters(self) -> FlaskResponse:
"""validates database connection parameters
---
post:
description: >-
Validates parameters used to connect to a database
requestBody:
description: DB-specific parameters
required: true
content:
application/json:
schema:
$ref: "#/components/schemas/DatabaseValidateParametersSchema"
responses:
200:
description: Database Test Connection
content:
application/json:
schema:
type: object
properties:
message:
type: string
400:
$ref: '#/components/responses/400'
422:
$ref: '#/components/responses/422'
500:
$ref: '#/components/responses/500'
"""
try:
payload = DatabaseValidateParametersSchema().load(request.json)
except ValidationError as ex:
errors = [
SupersetError(
message="\n".join(messages),
error_type=SupersetErrorType.INVALID_PAYLOAD_SCHEMA_ERROR,
level=ErrorLevel.ERROR,
extra={"invalid": [attribute]},
)
for attribute, messages in ex.messages.items()
]
raise InvalidParametersError(errors) from ex
command = ValidateDatabaseParametersCommand(g.user, payload)
command.run()
return self.response(200, message="OK")
def get_datasource_access_error_object( # pylint: disable=invalid-name
self, datasource: "BaseDatasource") -> SupersetError:
"""
Return the error object for the denied Superset datasource.
:param datasource: The denied Superset datasource
:returns: The error object
"""
return SupersetError(
error_type=SupersetErrorType.DATASOURCE_SECURITY_ACCESS_ERROR,
message=self.get_datasource_access_error_msg(datasource),
level=ErrorLevel.ERROR,
extra={
"link": self.get_datasource_access_link(datasource),
"datasource": datasource.name,
},
)
def test_test_connection_failed_invalid_hostname(
self, mock_event_logger, mock_build_db
):
"""
Database API: Test test connection failed due to invalid hostname
"""
msg = 'psql: error: could not translate host name "locahost" to address: nodename nor servname provided, or not known'
mock_build_db.return_value.set_sqlalchemy_uri.side_effect = DBAPIError(
msg, None, None
)
mock_build_db.return_value.db_engine_spec.__name__ = "Some name"
superset_error = SupersetError(
message='Unable to resolve hostname "locahost".',
error_type="TEST_CONNECTION_INVALID_HOSTNAME_ERROR",
level="error",
extra={
"hostname": "locahost",
"issue_codes": [
{
"code": 1007,
"message": (
"Issue 1007 - The hostname provided can't be resolved."
),
}
],
},
)
mock_build_db.return_value.db_engine_spec.extract_errors.return_value = [
superset_error
]
self.login("admin")
data = {
"sqlalchemy_uri": "postgres://*****:*****@locahost:12345/db",
"database_name": "examples",
"impersonate_user": False,
"server_cert": None,
}
url = "api/v1/database/test_connection"
rv = self.post_assert_metric(url, data, "test_connection")
assert rv.status_code == 422
assert rv.headers["Content-Type"] == "application/json; charset=utf-8"
response = json.loads(rv.data.decode("utf-8"))
expected_response = {"errors": [dataclasses.asdict(superset_error)]}
assert response == expected_response
def show_http_exception(ex: HTTPException) -> FlaskResponse:
logger.warning("HTTPException", exc_info=True)
if ("text/html" in request.accept_mimetypes and not config["DEBUG"]
and ex.code in {404, 500}):
path = resource_filename("superset", f"static/assets/{ex.code}.html")
return send_file(path, cache_timeout=0), ex.code
return json_errors_response(
errors=[
SupersetError(
message=utils.error_msg_from_exception(ex),
error_type=SupersetErrorType.GENERIC_BACKEND_ERROR,
level=ErrorLevel.ERROR,
),
],
status=ex.code or 500,
)
def get_table_access_error_object(self,
tables: Set["Table"]) -> SupersetError:
"""
Return the error object for the denied SQL tables.
:param tables: The set of denied SQL tables
:returns: The error object
"""
return SupersetError(
error_type=SupersetErrorType.TABLE_SECURITY_ACCESS_ERROR,
message=self.get_table_access_error_msg(tables),
level=ErrorLevel.ERROR,
extra={
"link": self.get_table_access_link(tables),
"tables": [str(table) for table in tables],
},
)
def show_command_errors(ex: CommandException) -> FlaskResponse:
logger.warning(ex)
if "text/html" in request.accept_mimetypes and not config["DEBUG"]:
path = resource_filename("superset", "static/assets/500.html")
return send_file(path, cache_timeout=0), 500
extra = ex.normalized_messages() if isinstance(ex, CommandInvalidError) else {}
return json_errors_response(
errors=[
SupersetError(
message=ex.message,
error_type=SupersetErrorType.GENERIC_COMMAND_ERROR,
level=get_error_level_from_status_code(ex.status),
extra=extra,
),
],
status=ex.status,
)
def test_connection_superset_security_connection(self, mock_event_logger,
mock_get_sqla_engine):
"""Test to make sure event_logger is called when security
connection exc is raised"""
database = get_example_database()
mock_get_sqla_engine.side_effect = SupersetSecurityException(
SupersetError(error_type=500, message="test", level="info"))
db_uri = database.sqlalchemy_uri_decrypted
json_payload = {"sqlalchemy_uri": db_uri}
command_without_db_name = TestConnectionDatabaseCommand(
security_manager.find_user("admin"), json_payload)
with pytest.raises(DatabaseSecurityUnsafeError) as excinfo:
command_without_db_name.run()
assert str(
excinfo.value) == ("Stopped an unsafe database connection")
mock_event_logger.assert_called()
def execute(
self,
execution_context: SqlJsonExecutionContext,
rendered_query: str,
log_params: Optional[Dict[str, Any]],
) -> SqlJsonExecutionStatus:
query_id = execution_context.query.id
logger.info("Query %i: Running query on a Celery worker", query_id)
try:
task = self._get_sql_results_task.delay( # type: ignore
query_id,
rendered_query,
return_results=False,
store_results=not execution_context.select_as_cta,
username=get_username(),
start_time=now_as_float(),
expand_data=execution_context.expand_data,
log_params=log_params,
)
try:
task.forget()
except NotImplementedError:
logger.warning(
"Unable to forget Celery task as backend"
"does not support this operation"
)
except Exception as ex:
logger.exception("Query %i: %s", query_id, str(ex))
message = __("Failed to start remote query on a worker.")
error = SupersetError(
message=message,
error_type=SupersetErrorType.ASYNC_WORKERS_ERROR,
level=ErrorLevel.ERROR,
)
error_payload = dataclasses.asdict(error)
query = execution_context.query
query.set_extra_json_key("errors", [error_payload])
query.status = QueryStatus.FAILED
query.error_message = message
raise SupersetErrorException(error) from ex
self._query_dao.update_saved_query_exec_info(query_id)
return SqlJsonExecutionStatus.QUERY_IS_RUNNING
def test_extract_errors(self):
msg = "403 POST https://bigquery.googleapis.com/bigquery/v2/projects/test-keel-310804/jobs?prettyPrint=false: Access Denied: Project User does not have bigquery.jobs.create permission in project profound-keel-310804"
result = BigQueryEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message=
"We were unable to connect to your database. Please confirm that your service account has the Viewer and Job User roles on the project.",
error_type=SupersetErrorType.
CONNECTION_DATABASE_PERMISSIONS_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name": "Google BigQuery",
"issue_codes": [{
"code": 1017,
"message": "",
}],
},
)
]
def test_validate_parameters_missing():
parameters = {
"host": "",
"port": None,
"username": "",
"password": "",
"database": "",
"query": {},
}
errors = BasicParametersMixin.validate_parameters(parameters)
assert errors == [
SupersetError(
message=("One or more parameters are missing: "
"database, host, port, username"),
error_type=SupersetErrorType.CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={"missing": ["database", "host", "port", "username"]},
),
]
def run( # pylint: disable=too-many-statements,useless-suppression
self, ) -> CommandResult:
"""Runs arbitrary sql and returns data as json"""
try:
query = self._try_get_existing_query()
if self.is_query_handled(query):
self._execution_context.set_query(query) # type: ignore
status = SqlJsonExecutionStatus.QUERY_ALREADY_CREATED
else:
status = self._run_sql_json_exec_from_scratch()
self._execution_context_convertor.set_payload(
self._execution_context, status)
# save columns into metadata_json
self._query_dao.save_metadata(
self._execution_context.query,
self._execution_context_convertor.payload)
return {
"status": status,
"payload":
self._execution_context_convertor.serialize_payload(),
}
except SupersetErrorsException as ex:
if all(ex.error_type == SupersetErrorType.SYNTAX_ERROR
for ex in ex.errors):
raise SupersetSyntaxErrorException(ex.errors) from ex
raise ex
except SupersetException as ex:
if ex.error_type == SupersetErrorType.SYNTAX_ERROR:
raise SupersetSyntaxErrorException([
SupersetError(
message=ex.message,
error_type=ex.error_type,
level=ErrorLevel.ERROR,
)
]) from ex
raise ex
except Exception as ex:
raise SqlLabException(self._execution_context,
exception=ex) from ex
def show_http_exception(ex: HTTPException) -> FlaskResponse:
logger.warning(ex)
if (
"text/html" in request.accept_mimetypes
and not config["DEBUG"]
and ex.code in {404, 500}
):
return redirect(f"/static/assets/{ex.code}.html")
return json_errors_response(
errors=[
SupersetError(
message=utils.error_msg_from_exception(ex),
error_type=SupersetErrorType.GENERIC_BACKEND_ERROR,
level=ErrorLevel.ERROR,
extra={},
),
],
status=ex.code or 500,
)
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = ": mismatched input 'fromm'. Expecting: "
result = AthenaEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message='Please check your query for syntax errors at or near "fromm". Then, try running your query again.',
error_type=SupersetErrorType.SYNTAX_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name": "Amazon Athena",
"issue_codes": [
{
"code": 1030,
"message": "Issue 1030 - The query has a syntax error.",
}
],
},
)
]
def test_validate_parameters_simple(mocker: MockFixture, ) -> None:
from superset.db_engine_specs.gsheets import (
GSheetsEngineSpec,
GSheetsParametersType,
)
parameters: GSheetsParametersType = {
"service_account_info": "",
"catalog": {},
}
errors = GSheetsEngineSpec.validate_parameters(parameters)
assert errors == [
SupersetError(
message="Sheet name is required",
error_type=SupersetErrorType.CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {
"idx": 0,
"name": True
}},
),
]
def run(self) -> List[Dict[str, Any]]:
"""
Validates a SQL statement
:return: A List of SQLValidationAnnotation
:raises: DatabaseNotFoundError, NoValidatorConfigFoundError
NoValidatorFoundError, ValidatorSQLUnexpectedError, ValidatorSQLError
ValidatorSQL400Error
"""
self.validate()
if not self._validator or not self._model:
raise ValidatorSQLUnexpectedError()
sql = self._properties["sql"]
schema = self._properties.get("schema")
try:
timeout = current_app.config["SQLLAB_VALIDATION_TIMEOUT"]
timeout_msg = f"The query exceeded the {timeout} seconds timeout."
with utils.timeout(seconds=timeout, error_message=timeout_msg):
errors = self._validator.validate(sql, schema, self._model)
return [err.to_dict() for err in errors]
except Exception as ex:
logger.exception(ex)
superset_error = SupersetError(
message=__(
"%(validator)s was unable to check your query.\n"
"Please recheck your query.\n"
"Exception: %(ex)s",
validator=self._validator.name,
ex=ex,
),
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
)
# Return as a 400 if the database error message says we got a 4xx error
if re.search(r"([\W]|^)4\d{2}([\W]|$)", str(ex)):
raise ValidatorSQL400Error(superset_error) from ex
raise ValidatorSQLError(superset_error) from ex
def validate_adhoc_subquery(raw_sql: str) -> None:
"""
Check if adhoc SQL contains sub-queries or nested sub-queries with table
:param raw_sql: adhoc sql expression
:raise SupersetSecurityException if sql contains sub-queries or
nested sub-queries with table
"""
# pylint: disable=import-outside-toplevel
from superset import is_feature_enabled
if is_feature_enabled("ALLOW_ADHOC_SUBQUERY"):
return
for statement in sqlparse.parse(raw_sql):
if has_table_query(statement):
raise SupersetSecurityException(
SupersetError(
error_type=SupersetErrorType.
ADHOC_SUBQUERY_NOT_ALLOWED_ERROR,
message=_("Custom SQL fields cannot contain sub-queries."),
level=ErrorLevel.ERROR,
))
return
def test_validate_partial(is_port_open, is_hostname_valid, app_context):
"""
Test parameter validation when only some parameters are present.
"""
is_hostname_valid.return_value = True
is_port_open.return_value = True
payload = {
"engine": "postgresql",
"parameters": {
"host": "localhost",
"port": 5432,
"username": "",
"password": "******",
"database": "test",
"query": {},
},
}
command = ValidateDatabaseParametersCommand(None, payload)
with pytest.raises(SupersetErrorsException) as excinfo:
command.run()
assert excinfo.value.errors == [
SupersetError(
message="One or more parameters are missing: username",
error_type=SupersetErrorType.CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={
"missing": ["username"],
"issue_codes": [{
"code":
1018,
"message":
"Issue 1018 - One or more parameters needed to configure a database are missing.",
}],
},
)
]
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = 'SQLError: near "fromm": syntax error'
result = GSheetsEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message=
'Please check your query for syntax errors near "fromm". Then, try running your query again.',
error_type=SupersetErrorType.SYNTAX_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Google Sheets",
"issue_codes": [{
"code":
1030,
"message":
"Issue 1030 - The query has a syntax error.",
}],
},
)
]
def test_validate_parameters_catalog(
mocker: MockFixture,
app_context: AppContext,
) -> None:
from superset.db_engine_specs.gsheets import (
GSheetsEngineSpec,
GSheetsParametersType,
)
g = mocker.patch("superset.db_engine_specs.gsheets.g")
g.user.email = "*****@*****.**"
create_engine = mocker.patch(
"superset.db_engine_specs.gsheets.create_engine")
conn = create_engine.return_value.connect.return_value
results = conn.execute.return_value
results.fetchall.side_effect = [
ProgrammingError("The caller does not have permission"),
[(1, )],
ProgrammingError("Unsupported table: https://www.google.com/"),
]
parameters: GSheetsParametersType = {
"credentials_info": {},
"catalog": {
"private_sheet": "https://docs.google.com/spreadsheets/d/1/edit",
"public_sheet":
"https://docs.google.com/spreadsheets/d/1/edit#gid=1",
"not_a_sheet": "https://www.google.com/",
},
}
errors = GSheetsEngineSpec.validate_parameters(parameters) # ignore: type
assert errors == [
SupersetError(
message="URL could not be identified",
error_type=SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.WARNING,
extra={
"invalid": ["catalog"],
"name":
"private_sheet",
"url":
"https://docs.google.com/spreadsheets/d/1/edit",
"issue_codes": [
{
"code":
1003,
"message":
"Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code":
1005,
"message":
"Issue 1005 - The table was deleted or renamed in the database.",
},
],
},
),
SupersetError(
message="URL could not be identified",
error_type=SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.WARNING,
extra={
"invalid": ["catalog"],
"name":
"not_a_sheet",
"url":
"https://www.google.com/",
"issue_codes": [
{
"code":
1003,
"message":
"Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code":
1005,
"message":
"Issue 1005 - The table was deleted or renamed in the database.",
},
],
},
),
]
create_engine.assert_called_with(
"gsheets://",
service_account_info={},
subject="*****@*****.**",
)
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = 'psql: error: FATAL: role "testuser" does not exist'
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_INVALID_USERNAME_ERROR,
message='The username "testuser" does not exist.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"PostgreSQL",
"issue_codes": [
{
"code":
1012,
"message":
("Issue 1012 - The username provided when "
"connecting to a database is not valid."),
},
],
},
)
]
msg = (
'psql: error: could not translate host name "locahost" to address: '
"nodename nor servname provided, or not known")
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
message='The hostname "locahost" cannot be resolved.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"PostgreSQL",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname provided "
"can't be resolved.",
}],
},
)
]
msg = dedent("""
psql: error: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 12345?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 12345?
""")
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_PORT_CLOSED_ERROR,
message=
'Port 12345 on hostname "localhost" refused the connection.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"PostgreSQL",
"issue_codes": [{
"code":
1008,
"message":
"Issue 1008 - The port is closed."
}],
},
)
]
msg = dedent("""
psql: error: could not connect to server: Operation timed out
Is the server running on host "example.com" (93.184.216.34) and accepting
TCP/IP connections on port 12345?
""")
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=('The host "example.com" might be down, '
"and can't be reached on port 12345."),
level=ErrorLevel.ERROR,
extra={
"engine_name":
"PostgreSQL",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, "
"and can't be reached on the provided port.",
}],
},
)
]
# response with IP only
msg = dedent("""
psql: error: could not connect to server: Operation timed out
Is the server running on host "93.184.216.34" and accepting
TCP/IP connections on port 12345?
""")
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=('The host "93.184.216.34" might be down, '
"and can't be reached on port 12345."),
level=ErrorLevel.ERROR,
extra={
"engine_name":
"PostgreSQL",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, "
"and can't be reached on the provided port.",
}],
},
)
]
msg = 'FATAL: password authentication failed for user "postgres"'
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_INVALID_PASSWORD_ERROR,
message=
('The password provided for username "postgres" is incorrect.'
),
level=ErrorLevel.ERROR,
extra={
"engine_name":
"PostgreSQL",
"issue_codes": [
{
"code":
1013,
"message":
("Issue 1013 - The password provided when "
"connecting to a database is not valid."),
},
],
},
)
]
msg = 'database "badDB" does not exist'
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message='Unable to connect to database "badDB".',
error_type=SupersetErrorType.CONNECTION_UNKNOWN_DATABASE_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"PostgreSQL",
"issue_codes": [{
"code":
1015,
"message":
("Issue 1015 - Either the database is spelled "
"incorrectly or does not exist.", ),
}],
},
)
]
def execute_sql_statements( # pylint: disable=too-many-arguments, too-many-locals, too-many-statements, too-many-branches
query_id: int,
rendered_query: str,
return_results: bool,
store_results: bool,
user_name: Optional[str],
session: Session,
start_time: Optional[float],
expand_data: bool,
log_params: Optional[Dict[str, Any]],
) -> Optional[Dict[str, Any]]:
"""Executes the sql query returns the results."""
if store_results and start_time:
# only asynchronous queries
stats_logger.timing("sqllab.query.time_pending", now_as_float() - start_time)
query = get_query(query_id, session)
payload: Dict[str, Any] = dict(query_id=query_id)
database = query.database
db_engine_spec = database.db_engine_spec
db_engine_spec.patch()
if database.allow_run_async and not results_backend:
raise SupersetErrorException(
SupersetError(
message=__("Results backend is not configured."),
error_type=SupersetErrorType.RESULTS_BACKEND_NOT_CONFIGURED_ERROR,
level=ErrorLevel.ERROR,
)
)
# Breaking down into multiple statements
parsed_query = ParsedQuery(rendered_query, strip_comments=True)
if not db_engine_spec.run_multiple_statements_as_one:
statements = parsed_query.get_statements()
logger.info(
"Query %s: Executing %i statement(s)", str(query_id), len(statements)
)
else:
statements = [rendered_query]
logger.info("Query %s: Executing query as a single statement", str(query_id))
logger.info("Query %s: Set query to 'running'", str(query_id))
query.status = QueryStatus.RUNNING
query.start_running_time = now_as_float()
session.commit()
# Should we create a table or view from the select?
if (
query.select_as_cta
and query.ctas_method == CtasMethod.TABLE
and not parsed_query.is_valid_ctas()
):
raise SupersetErrorException(
SupersetError(
message=__(
"CTAS (create table as select) can only be run with a query where "
"the last statement is a SELECT. Please make sure your query has "
"a SELECT as its last statement. Then, try running your query "
"again."
),
error_type=SupersetErrorType.INVALID_CTAS_QUERY_ERROR,
level=ErrorLevel.ERROR,
)
)
if (
query.select_as_cta
and query.ctas_method == CtasMethod.VIEW
and not parsed_query.is_valid_cvas()
):
raise SupersetErrorException(
SupersetError(
message=__(
"CVAS (create view as select) can only be run with a query with "
"a single SELECT statement. Please make sure your query has only "
"a SELECT statement. Then, try running your query again."
),
error_type=SupersetErrorType.INVALID_CVAS_QUERY_ERROR,
level=ErrorLevel.ERROR,
)
)
engine = database.get_sqla_engine(
schema=query.schema,
nullpool=True,
user_name=user_name,
source=QuerySource.SQL_LAB,
)
# Sharing a single connection and cursor across the
# execution of all statements (if many)
with closing(engine.raw_connection()) as conn:
# closing the connection closes the cursor as well
cursor = conn.cursor()
statement_count = len(statements)
for i, statement in enumerate(statements):
# Check if stopped
query = get_query(query_id, session)
if query.status == QueryStatus.STOPPED:
return None
# For CTAS we create the table only on the last statement
apply_ctas = query.select_as_cta and (
query.ctas_method == CtasMethod.VIEW
or (query.ctas_method == CtasMethod.TABLE and i == len(statements) - 1)
)
# Run statement
msg = f"Running statement {i+1} out of {statement_count}"
logger.info("Query %s: %s", str(query_id), msg)
query.set_extra_json_key("progress", msg)
session.commit()
try:
result_set = execute_sql_statement(
statement,
query,
user_name,
session,
cursor,
log_params,
apply_ctas,
)
except Exception as ex: # pylint: disable=broad-except
msg = str(ex)
prefix_message = (
f"[Statement {i+1} out of {statement_count}]"
if statement_count > 1
else ""
)
payload = handle_query_error(
ex, query, session, payload, prefix_message
)
return payload
# Commit the connection so CTA queries will create the table.
conn.commit()
# Success, updating the query entry in database
query.rows = result_set.size
query.progress = 100
query.set_extra_json_key("progress", None)
if query.select_as_cta:
query.select_sql = database.select_star(
query.tmp_table_name,
schema=query.tmp_schema_name,
limit=query.limit,
show_cols=False,
latest_partition=False,
)
query.end_time = now_as_float()
use_arrow_data = store_results and cast(bool, results_backend_use_msgpack)
data, selected_columns, all_columns, expanded_columns = _serialize_and_expand_data(
result_set, db_engine_spec, use_arrow_data, expand_data
)
# TODO: data should be saved separately from metadata (likely in Parquet)
payload.update(
{
"status": QueryStatus.SUCCESS,
"data": data,
"columns": all_columns,
"selected_columns": selected_columns,
"expanded_columns": expanded_columns,
"query": query.to_dict(),
}
)
payload["query"]["state"] = QueryStatus.SUCCESS
if store_results and results_backend:
key = str(uuid.uuid4())
logger.info(
"Query %s: Storing results in results backend, key: %s", str(query_id), key
)
with stats_timing("sqllab.query.results_backend_write", stats_logger):
with stats_timing(
"sqllab.query.results_backend_write_serialization", stats_logger
):
serialized_payload = _serialize_payload(
payload, cast(bool, results_backend_use_msgpack)
)
cache_timeout = database.cache_timeout
if cache_timeout is None:
cache_timeout = config["CACHE_DEFAULT_TIMEOUT"]
compressed = zlib_compress(serialized_payload)
logger.debug(
"*** serialized payload size: %i", getsizeof(serialized_payload)
)
logger.debug("*** compressed payload size: %i", getsizeof(compressed))
results_backend.set(key, compressed, cache_timeout)
query.results_key = key
query.status = QueryStatus.SUCCESS
session.commit()
if return_results:
# since we're returning results we need to create non-arrow data
if use_arrow_data:
(
data,
selected_columns,
all_columns,
expanded_columns,
) = _serialize_and_expand_data(
result_set, db_engine_spec, False, expand_data
)
payload.update(
{
"data": data,
"columns": all_columns,
"selected_columns": selected_columns,
"expanded_columns": expanded_columns,
}
)
return payload
return None
def execute_sql_statement(
sql_statement: str,
query: Query,
user_name: Optional[str],
session: Session,
cursor: Any,
log_params: Optional[Dict[str, Any]],
apply_ctas: bool = False,
) -> SupersetResultSet:
"""Executes a single SQL statement"""
database = query.database
db_engine_spec = database.db_engine_spec
parsed_query = ParsedQuery(sql_statement)
sql = parsed_query.stripped()
# This is a test to see if the query is being
# limited by either the dropdown or the sql.
# We are testing to see if more rows exist than the limit.
increased_limit = None if query.limit is None else query.limit + 1
if not db_engine_spec.is_readonly_query(parsed_query) and not database.allow_dml:
raise SupersetErrorException(
SupersetError(
message=__("Only SELECT statements are allowed against this database."),
error_type=SupersetErrorType.DML_NOT_ALLOWED_ERROR,
level=ErrorLevel.ERROR,
)
)
if apply_ctas:
if not query.tmp_table_name:
start_dttm = datetime.fromtimestamp(query.start_time)
query.tmp_table_name = "tmp_{}_table_{}".format(
query.user_id, start_dttm.strftime("%Y_%m_%d_%H_%M_%S")
)
sql = parsed_query.as_create_table(
query.tmp_table_name,
schema_name=query.tmp_schema_name,
method=query.ctas_method,
)
query.select_as_cta_used = True
# Do not apply limit to the CTA queries when SQLLAB_CTAS_NO_LIMIT is set to true
if db_engine_spec.is_select_query(parsed_query) and not (
query.select_as_cta_used and SQLLAB_CTAS_NO_LIMIT
):
if SQL_MAX_ROW and (not query.limit or query.limit > SQL_MAX_ROW):
query.limit = SQL_MAX_ROW
if query.limit:
# We are fetching one more than the requested limit in order
# to test whether there are more rows than the limit.
# Later, the extra row will be dropped before sending
# the results back to the user.
sql = database.apply_limit_to_sql(sql, increased_limit, force=True)
# Hook to allow environment-specific mutation (usually comments) to the SQL
sql = SQL_QUERY_MUTATOR(sql, user_name, security_manager, database)
try:
query.executed_sql = sql
if log_query:
log_query(
query.database.sqlalchemy_uri,
query.executed_sql,
query.schema,
user_name,
__name__,
security_manager,
log_params,
)
session.commit()
with stats_timing("sqllab.query.time_executing_query", stats_logger):
logger.debug("Query %d: Running query: %s", query.id, sql)
db_engine_spec.execute(cursor, sql, async_=True)
logger.debug("Query %d: Handling cursor", query.id)
db_engine_spec.handle_cursor(cursor, query, session)
with stats_timing("sqllab.query.time_fetching_results", stats_logger):
logger.debug(
"Query %d: Fetching data for query object: %s",
query.id,
str(query.to_dict()),
)
data = db_engine_spec.fetch_data(cursor, increased_limit)
if query.limit is None or len(data) <= query.limit:
query.limiting_factor = LimitingFactor.NOT_LIMITED
else:
# return 1 row less than increased_query
data = data[:-1]
except SoftTimeLimitExceeded as ex:
logger.warning("Query %d: Time limit exceeded", query.id)
logger.debug("Query %d: %s", query.id, ex)
raise SupersetErrorException(
SupersetError(
message=__(
f"The query was killed after {SQLLAB_TIMEOUT} seconds. It might "
"be too complex, or the database might be under heavy load."
),
error_type=SupersetErrorType.SQLLAB_TIMEOUT_ERROR,
level=ErrorLevel.ERROR,
)
)
except Exception as ex:
logger.error("Query %d: %s", query.id, type(ex), exc_info=True)
logger.debug("Query %d: %s", query.id, ex)
raise SqlLabException(db_engine_spec.extract_error_message(ex))
logger.debug("Query %d: Fetching cursor description", query.id)
cursor_description = cursor.description
return SupersetResultSet(data, cursor_description, db_engine_spec)
def validate_parameters(
cls,
parameters: GSheetsParametersType,
) -> List[SupersetError]:
errors: List[SupersetError] = []
encrypted_credentials = parameters.get("service_account_info") or "{}"
# On create the encrypted credentials are a string,
# at all other times they are a dict
if isinstance(encrypted_credentials, str):
encrypted_credentials = json.loads(encrypted_credentials)
table_catalog = parameters.get("catalog", {})
if not table_catalog:
# Allowing users to submit empty catalogs
errors.append(
SupersetError(
message="Sheet name is required",
error_type=SupersetErrorType.
CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {
"idx": 0,
"name": True
}},
), )
return errors
# We need a subject in case domain wide delegation is set, otherwise the
# check will fail. This means that the admin will be able to add sheets
# that only they have access, even if later users are not able to access
# them.
subject = g.user.email if g.user else None
engine = create_engine(
"gsheets://",
service_account_info=encrypted_credentials,
subject=subject,
)
conn = engine.connect()
idx = 0
for name, url in table_catalog.items():
if not name:
errors.append(
SupersetError(
message="Sheet name is required",
error_type=SupersetErrorType.
CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {
"idx": idx,
"name": True
}},
), )
return errors
if not url:
errors.append(
SupersetError(
message="URL is required",
error_type=SupersetErrorType.
CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {
"idx": idx,
"url": True
}},
), )
return errors
try:
results = conn.execute(f'SELECT * FROM "{url}" LIMIT 1')
results.fetchall()
except Exception: # pylint: disable=broad-except
errors.append(
SupersetError(
message=
("The URL could not be identified. Please check for typos "
"and make sure that ‘Type of Google Sheets allowed’ "
"selection matches the input."),
error_type=SupersetErrorType.
TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {
"idx": idx,
"url": True
}},
), )
idx += 1
return errors
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = "mysql: Access denied for user 'test'@'testuser.com'"
result = MySQLEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
message=
'Either the username "test" or the password is incorrect.',
level=ErrorLevel.ERROR,
extra={
"invalid": ["username", "password"],
"engine_name":
"MySQL",
"issue_codes": [
{
"code":
1014,
"message":
"Issue 1014 - Either the"
" username or the password is wrong.",
},
{
"code":
1015,
"message":
"Issue 1015 - Either the database is "
"spelled incorrectly or does not exist.",
},
],
},
)
]
msg = "mysql: Unknown MySQL server host 'badhostname.com'"
result = MySQLEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
message='Unknown MySQL server host "badhostname.com".',
level=ErrorLevel.ERROR,
extra={
"invalid": ["host"],
"engine_name":
"MySQL",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname"
" provided can't be resolved.",
}],
},
)
]
msg = "mysql: Can't connect to MySQL server on 'badconnection.com'"
result = MySQLEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message='The host "badconnection.com" might be '
"down and can't be reached.",
level=ErrorLevel.ERROR,
extra={
"invalid": ["host", "port"],
"engine_name":
"MySQL",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname provided"
" can't be resolved.",
}],
},
)
]
msg = "mysql: Can't connect to MySQL server on '93.184.216.34'"
result = MySQLEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=
'The host "93.184.216.34" might be down and can\'t be reached.',
level=ErrorLevel.ERROR,
extra={
"invalid": ["host", "port"],
"engine_name":
"MySQL",
"issue_codes": [{
"code":
10007,
"message":
"Issue 1007 - The hostname provided "
"can't be resolved.",
}],
},
)
]
msg = "mysql: Unknown database 'badDB'"
result = MySQLEngineSpec.extract_errors(Exception(msg))
print(result)
assert result == [
SupersetError(
message='Unable to connect to database "badDB".',
error_type=SupersetErrorType.CONNECTION_UNKNOWN_DATABASE_ERROR,
level=ErrorLevel.ERROR,
extra={
"invalid": ["database"],
"engine_name":
"MySQL",
"issue_codes": [{
"code":
1015,
"message":
"Issue 1015 - Either the database is spelled incorrectly or does not exist.",
}],
},
)
]
def test_execute_sql_statements_ctas(self, mock_execute_sql_statement,
mock_get_query):
sql = """
-- comment
SET @value = 42;
SELECT @value AS foo;
-- comment
"""
mock_session = mock.MagicMock()
mock_query = mock.MagicMock()
mock_query.database.allow_run_async = False
mock_cursor = mock.MagicMock()
mock_query.database.get_sqla_engine.return_value.raw_connection.return_value.cursor.return_value = (
mock_cursor)
mock_query.database.db_engine_spec.run_multiple_statements_as_one = False
mock_get_query.return_value = mock_query
# set the query to CTAS
mock_query.select_as_cta = True
mock_query.ctas_method = CtasMethod.TABLE
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
mock_execute_sql_statement.assert_has_calls([
mock.call(
"SET @value = 42",
mock_query,
mock_session,
mock_cursor,
None,
False,
),
mock.call(
"SELECT @value AS foo",
mock_query,
mock_session,
mock_cursor,
None,
True, # apply_ctas
),
])
# try invalid CTAS
sql = "DROP TABLE my_table"
with pytest.raises(SupersetErrorException) as excinfo:
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
assert excinfo.value.error == SupersetError(
message=
"CTAS (create table as select) can only be run with a query where the last statement is a SELECT. Please make sure your query has a SELECT as its last statement. Then, try running your query again.",
error_type=SupersetErrorType.INVALID_CTAS_QUERY_ERROR,
level=ErrorLevel.ERROR,
extra={
"issue_codes": [{
"code":
1023,
"message":
"Issue 1023 - The CTAS (create table as select) doesn't have a SELECT statement at the end. Please make sure your query has a SELECT as its last statement. Then, try running your query again.",
}]
},
)
# try invalid CVAS
mock_query.ctas_method = CtasMethod.VIEW
sql = """
-- comment
SET @value = 42;
SELECT @value AS foo;
-- comment
"""
with pytest.raises(SupersetErrorException) as excinfo:
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
assert excinfo.value.error == SupersetError(
message=
"CVAS (create view as select) can only be run with a query with a single SELECT statement. Please make sure your query has only a SELECT statement. Then, try running your query again.",
error_type=SupersetErrorType.INVALID_CVAS_QUERY_ERROR,
level=ErrorLevel.ERROR,
extra={
"issue_codes": [
{
"code":
1024,
"message":
"Issue 1024 - CVAS (create view as select) query has more than one statement.",
},
{
"code":
1025,
"message":
"Issue 1025 - CVAS (create view as select) query is not a SELECT statement.",
},
]
},
)
def test_extract_errors(self):
msg = "Generic Error"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message="Generic Error",
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1002,
"message":
"Issue 1002 - The database returned an unexpected error.",
}],
},
)
]
msg = "line 1:8: Column 'bogus' cannot be resolved"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message=
'We can\'t seem to resolve the column "bogus" at line 1:8.',
error_type=SupersetErrorType.COLUMN_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [
{
"code":
1003,
"message":
"Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code":
1004,
"message":
"Issue 1004 - The column was deleted or renamed in the database.",
},
],
},
)
]
msg = "line 1:15: Table 'tpch.tiny.region2' does not exist"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message=
"The table \"'tpch.tiny.region2'\" does not exist. A valid table must be used to run this query.",
error_type=SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [
{
"code":
1003,
"message":
"Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code":
1005,
"message":
"Issue 1005 - The table was deleted or renamed in the database.",
},
],
},
)
]
msg = "line 1:15: Schema 'tin' does not exist"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message=
'The schema "tin" does not exist. A valid schema must be used to run this query.',
error_type=SupersetErrorType.SCHEMA_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [
{
"code":
1003,
"message":
"Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code":
1016,
"message":
"Issue 1005 - The schema was deleted or renamed in the database.",
},
],
},
)
]
msg = b"Access Denied: Invalid credentials"
result = PrestoEngineSpec.extract_errors(Exception(msg),
{"username": "******"})
assert result == [
SupersetError(
message=
'Either the username "alice" or the password is incorrect.',
error_type=SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1014,
"message":
"Issue 1014 - Either the username or the password is wrong.",
}],
},
)
]
msg = "Failed to establish a new connection: [Errno 8] nodename nor servname provided, or not known"
result = PrestoEngineSpec.extract_errors(Exception(msg),
{"hostname": "badhost"})
assert result == [
SupersetError(
message='The hostname "badhost" cannot be resolved.',
error_type=SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname provided can't be resolved.",
}],
},
)
]
msg = "Failed to establish a new connection: [Errno 60] Operation timed out"
result = PrestoEngineSpec.extract_errors(Exception(msg), {
"hostname": "badhost",
"port": 12345
})
assert result == [
SupersetError(
message=
'The host "badhost" might be down, and can\'t be reached on port 12345.',
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, and can't be reached on the provided port.",
}],
},
)
]
msg = "Failed to establish a new connection: [Errno 61] Connection refused"
result = PrestoEngineSpec.extract_errors(Exception(msg), {
"hostname": "badhost",
"port": 12345
})
assert result == [
SupersetError(
message=
'Port 12345 on hostname "badhost" refused the connection.',
error_type=SupersetErrorType.CONNECTION_PORT_CLOSED_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1008,
"message":
"Issue 1008 - The port is closed."
}],
},
)
]
msg = "line 1:15: Catalog 'wrong' does not exist"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message='Unable to connect to catalog named "wrong".',
error_type=SupersetErrorType.CONNECTION_UNKNOWN_DATABASE_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1015,
"message":
"Issue 1015 - Either the database is spelled incorrectly or does not exist.",
}],
},
)
]
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = dedent("""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (locahost)
""")
result = MssqlEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
message='The hostname "locahost" cannot be resolved.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname provided can't be resolved.",
}],
},
)
]
msg = dedent("""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (localhost)
Net-Lib error during Connection refused (61)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (localhost)
Net-Lib error during Connection refused (61)
""")
result = MssqlEngineSpec.extract_errors(Exception(msg),
context={
"port": 12345,
"hostname": "localhost"
})
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_PORT_CLOSED_ERROR,
message=
'Port 12345 on hostname "localhost" refused the connection.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1008,
"message":
"Issue 1008 - The port is closed."
}],
},
)
]
msg = dedent("""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (example.com)
Net-Lib error during Operation timed out (60)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (example.com)
Net-Lib error during Operation timed out (60)
""")
result = MssqlEngineSpec.extract_errors(Exception(msg),
context={
"port": 12345,
"hostname": "example.com"
})
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=('The host "example.com" might be down, '
"and can't be reached on port 12345."),
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, and can't be reached on the provided port.",
}],
},
)
]
msg = dedent("""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (93.184.216.34)
Net-Lib error during Operation timed out (60)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (93.184.216.34)
Net-Lib error during Operation timed out (60)
""")
result = MssqlEngineSpec.extract_errors(Exception(msg),
context={
"port": 12345,
"hostname": "93.184.216.34"
})
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=('The host "93.184.216.34" might be down, '
"and can't be reached on port 12345."),
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, and can't be reached on the provided port.",
}],
},
)
]
msg = dedent("""
DB-Lib error message 20018, severity 14:
General SQL Server error: Check messages from the SQL Server
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed (mssqldb.cxiotftzsypc.us-west-2.rds.amazonaws.com)
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed (mssqldb.cxiotftzsypc.us-west-2.rds.amazonaws.com)
""")
result = MssqlEngineSpec.extract_errors(
Exception(msg), context={"username": "******"})
assert result == [
SupersetError(
message=
'Either the username "testuser" or the password is incorrect.',
error_type=SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1014,
"message":
"Issue 1014 - Either the username or the password is wrong.",
}],
},
)
]
def run(self) -> None:
engine = self._properties["engine"]
engine_specs = get_engine_specs()
if engine in BYPASS_VALIDATION_ENGINES:
# Skip engines that are only validated onCreate
return
if engine not in engine_specs:
raise InvalidEngineError(
SupersetError(
message=__(
'Engine "%(engine)s" is not a valid engine.',
engine=engine,
),
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
extra={
"allowed": list(engine_specs),
"provided": engine
},
), )
engine_spec = engine_specs[engine]
if not issubclass(engine_spec, BasicParametersMixin):
raise InvalidEngineError(
SupersetError(
message=__(
'Engine "%(engine)s" cannot be configured through parameters.',
engine=engine,
),
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
extra={
"allowed": [
name for name, engine_spec in engine_specs.items()
if issubclass(engine_spec, BasicParametersMixin)
],
"provided":
engine,
},
), )
# perform initial validation
errors = engine_spec.validate_parameters(
self._properties.get("parameters", {}))
if errors:
raise InvalidParametersError(errors)
serialized_encrypted_extra = self._properties.get(
"encrypted_extra", "{}")
try:
encrypted_extra = json.loads(serialized_encrypted_extra)
except json.decoder.JSONDecodeError:
encrypted_extra = {}
# try to connect
sqlalchemy_uri = engine_spec.build_sqlalchemy_uri(
self._properties.get("parameters", None), # type: ignore
encrypted_extra,
)
if self._model and sqlalchemy_uri == self._model.safe_sqlalchemy_uri():
sqlalchemy_uri = self._model.sqlalchemy_uri_decrypted
database = DatabaseDAO.build_db_for_connection_test(
server_cert=self._properties.get("server_cert", ""),
extra=self._properties.get("extra", "{}"),
impersonate_user=self._properties.get("impersonate_user", False),
encrypted_extra=serialized_encrypted_extra,
)
database.set_sqlalchemy_uri(sqlalchemy_uri)
database.db_engine_spec.mutate_db_for_connection_test(database)
username = self._actor.username if self._actor is not None else None
engine = database.get_sqla_engine(user_name=username)
try:
with closing(engine.raw_connection()) as conn:
alive = engine.dialect.do_ping(conn)
except Exception as ex: # pylint: disable=broad-except
url = make_url(sqlalchemy_uri)
context = {
"hostname": url.host,
"password": url.password,
"port": url.port,
"username": url.username,
"database": url.database,
}
errors = database.db_engine_spec.extract_errors(ex, context)
raise DatabaseTestConnectionFailedError(errors)
if not alive:
raise DatabaseOfflineError(
SupersetError(
message=__("Database is offline."),
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
), )
