Skip to content

fn_listextendedproperty is not supported in Azure Synapse #9142

New issue
New issue
Closed
Closed
fn_listextendedproperty is not supported in Azure Synapse#9142
Labels
SQL ServerMicrosoft SQL Server, e.g. mssqlblockerissue that must be resolved asap as it is preventing things from workingbugSomething isn't workingreflectionreflection of tables, columns, constraints, defaults, sequences, views, everything elseregressionsomething worked and was broken by a change
Milestone
 
2.0 final
@cdcadman

Description

@cdcadman
cdcadman
opened on Jan 25, 2023

Describe the bug

Table reflection fails in Azure Synapse using sqlalchemy 2.0rc3 due to use of fn_listextendedproperty, which is not supported.

To Reproduce

from urllib.parse import quote_plus
import sqlalchemy as sa

username = input("Username: ")
password = input("Password: ")
server = input("Server: ") #tcp:<server name>.database.windows.net,1433
database = input("Database: ") #Azure Synapse Database on the server
driver = "{ODBC Driver 17 for SQL Server}"
connstr = f"Driver={driver};Server={server};Database={database};Authentication=ActiveDirectoryPassword;UID={username};PWD={password}"
ce_params = {"odbc_connect": quote_plus(connstr), "autocommit": "true"}

engine = sa.create_engine("mssql+pyodbc:///?" + "&".join(f"{key}={value}" for key, value in ce_params.items()))
with engine.connect() as conn:
    metadata = sa.MetaData()
    sa.Table("ereka_model_data", metadata, autoload_with=conn, schema="ereka_data")

Error

Traceback (most recent call last):
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\default.py", line 747, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Catalog view 'fn_listextendedproperty' is not supported in this version. (104385) (SQLExecDirectW)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Temp\test_azure_synapse.py", line 15, in <module>
    sa.Table("ereka_model_data", metadata, autoload_with=conn, schema="ereka_data")
  File "<string>", line 2, in __new__
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\util\deprecations.py", line 277, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\sql\schema.py", line 432, in __new__
    return cls._new(*args, **kw)
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\sql\schema.py", line 486, in _new
    with util.safe_reraise():
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\sql\schema.py", line 482, in _new
    table.__init__(name, metadata, *args, _no_init=False, **kw)
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\sql\schema.py", line 858, in __init__
    self._autoload(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\sql\schema.py", line 890, in _autoload
    conn_insp.reflect_table(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\reflection.py", line 1535, in reflect_table
    _reflect_info = self._get_reflection_info(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\reflection.py", line 2023, in _get_reflection_info
    table_comment=run(self.get_multi_table_comment, optional=True),
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\reflection.py", line 2000, in run
    res = meth(filter_names=_fn, **kw)
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\reflection.py", line 1374, in get_multi_table_comment
    return dict(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\default.py", line 917, in _default_multi_reflect
    single_tbl_method(
  File "<string>", line 2, in get_table_comment
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\reflection.py", line 97, in cache
    ret = fn(self, con, *args, **kw)
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 3445, in get_table_comment
    comment = connection.execute(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\base.py", line 1414, in execute
    return meth(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\sql\elements.py", line 489, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\base.py", line 1842, in _execute_context
    return self._exec_single_context(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\base.py", line 2325, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\s004246\AppData\Local\Temp\python_sa_pd_20\lib\site-packages\sqlalchemy\engine\default.py", line 747, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Catalog view 'fn_listextendedproperty' is not supported in this version. (104385) (SQLExecDirectW)")
[SQL:
            SELECT cast(com.value as nvarchar(max))
            FROM fn_listextendedproperty('MS_Description',
                'schema', CAST(? AS NVARCHAR(max)), 'table', CAST(? AS NVARCHAR(max)), NULL, NULL
            ) as com;
        ]
[parameters: ('ereka_data', 'ereka_model_data')]
(Background on this error at: https://sqlalche.me/e/20/f405)

Versions

  • OS: Windows 11
  • Python: 3.10.2
  • SQLAlchemy: 2.0.0rc3
  • Database: MS Azure Synapse Analytics
  • DBAPI (eg: psycopg, cx_oracle, mysqlclient): pyodbc==4.0.35

Additional context

sys.extended_properties is available in Azure Synapse, and the following two queries seem to give the same output on MS Azure SQL Server:

select ep.value from sys.extended_properties ep
	inner join sys.tables t on ep.major_id = t.object_id
	inner join sys.schemas s on t.schema_id = s.schema_id
where t.name = 'ereka_model'
and s.name = 'ereka_meta'
and ep.name = 'MS_Description'
and ep.minor_id = 0

SELECT cast(com.value as nvarchar(max))
            FROM fn_listextendedproperty('MS_Description',
                'schema', 'ereka_meta', 'table', 'ereka_model', NULL, NULL
            ) as com;

Activity

cdcadman
added
requires triageNew issue that requires categorization
on Jan 25, 2023
cdcadman

cdcadman commented on Jan 25, 2023

@cdcadman
cdcadman
on Jan 25, 2023
Author

This is not an issue in sqlalchemy==1.4.46.

CaselIT

CaselIT commented on Jan 25, 2023

@CaselIT
CaselIT
on Jan 25, 2023 · edited by CaselIT
Member

Thanks for reporting, this is the reflection of comments that was added for v2.

I'll take a look at the current query to see if there was a reason for using that function. Does the version you proposed work also in sql server (thanks for the suggestion btw)?

Can you test the other reflection methods in v2 to check if there are other surprises?

(@zzzeek I've added blocker since I don't think we want to release v2 public with this issue)

CaselIT
added
bugSomething isn't working
blockerissue that must be resolved asap as it is preventing things from working
SQL ServerMicrosoft SQL Server, e.g. mssql
reflectionreflection of tables, columns, constraints, defaults, sequences, views, everything else
regressionsomething worked and was broken by a change
and removed
requires triageNew issue that requires categorization
on Jan 25, 2023
CaselIT
added this to the 2.0 final milestone on Jan 25, 2023
zzzeek

zzzeek commented on Jan 25, 2023

@zzzeek
zzzeek
on Jan 25, 2023
Member

this is not just reflection, any table schema that has comments set upon it will also fail, as I assume execute sp_addextendedproperty and execute sp_dropextendedproperty are also not supported, @cdcadman can you confirm?

assuming comments are not supported at all by this database, approach would be we'd need to detect azure synapse entirely and set supports_comments=False at dialect startup time, as well as make it a configurable option.

OTOH if comments are supported, we likely have to simply re-do ddbd9da entirely and as we need to get the release out it might mean reverting it for now.

zzzeek

zzzeek commented on Jan 25, 2023

@zzzeek
zzzeek
on Jan 25, 2023
Member

well, let's not revert, let's jut get supports_comments set up, and then if another approach to support comments on azure can be devised, that can be released at a later point .

zzzeek

zzzeek commented on Jan 25, 2023

@zzzeek
zzzeek
on Jan 25, 2023
Member

OK I'm going with "disable comments completely if the backend doesnt have fn_listextendedproperty for now, if I can get more detail on what is and is not available on this backend we can adjust

CaselIT

CaselIT commented on Jan 25, 2023

@CaselIT
CaselIT
on Jan 25, 2023 · edited by CaselIT
Member

sounds good to me.
supporting the 1000 and 1 different azure sql thingy that behave like sql-server-but-not-really is really a pain :(

sqla-tester

sqla-tester commented on Jan 25, 2023

@sqla-tester
sqla-tester
on Jan 25, 2023
Collaborator

Mike Bayer has proposed a fix for this issue in the main branch:

Make comment support conditional on fn_listextendedproperty availability https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4385

7 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    SQL ServerMicrosoft SQL Server, e.g. mssqlblockerissue that must be resolved asap as it is preventing things from workingbugSomething isn't workingreflectionreflection of tables, columns, constraints, defaults, sequences, views, everything elseregressionsomething worked and was broken by a change

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @zzzeek@sqla-tester@CaselIT@cdcadman

        Issue actions
          fn_listextendedproperty is not supported in Azure Synapse · Issue #9142 · sqlalchemy/sqlalchemy