babelfish_extensions icon indicating copy to clipboard operation
babelfish_extensions copied to clipboard
verified publisher icon babelfish-for-postgresql

[Bug]: [ODBC Driver 17 for SQL Server][SQL Server]ROLLBACK can only be used in transaction blocks (3903) (SQLEndTran)')

Open bachpro900 opened this issue 1 year ago • 8 comments

What happened?

Explicit transaction level is not working. Engine and connection are working only if the proprety "autocommit = True" is set.

I have already seen a comment saying that it is not directly related to Babelfish, but to SQLAlchemy. However, I did exactly the same test (as you can see below) on two servers, native SQL Server, and Babelfish. The only difference is the connection string. The same test works fine on SQL Server, but an exception is raised when it comes to the Babelfish server. I therefore deduce that the issue comes from Babelfish because it is the only difference in the two tests. Otherwise, am I mistaken elsewhere?

I am using:

  • Babelgish 3.1.0
  • sqlalchemy 1.4.50
  • ODBC 17

Version

BABEL_3_X_DEV (Default)

Extension

None

Which flavor of Linux are you using when you see the bug?

No response

Relevant log output

>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.4.50'
>>> from sqlalchemy import create_engine


>>> Test on native sql server :

>>> engine = create_engine('mssql+pyodbc://sql_user:sql_password@sql_host/db_name?driver=ODBC+Driver+17+for+SQL+Server', echo=True)
>>> engine.connect()
2024-02-20 15:40:03,373 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2024-02-20 15:40:03,373 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-02-20 15:40:03,376 INFO sqlalchemy.engine.Engine SELECT schema_name()
2024-02-20 15:40:03,377 INFO sqlalchemy.engine.Engine [generated in 0.00157s] ()
2024-02-20 15:40:03,395 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2024-02-20 15:40:03,397 INFO sqlalchemy.engine.Engine [generated in 0.00142s] ()
<sqlalchemy.engine.base.Connection object at 0x7f545590cf10>




>>> test on babelfish:


>>> engine = create_engine('mssql+pyodbc://babelfish_user:babelfish_password@babelfish_host/db_name?driver=ODBC+Driver+17+for+SQL+Server', echo=True)
>>> engine.connect()
2024-02-20 15:41:06,690 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2024-02-20 15:41:06,691 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-02-20 15:41:06,910 INFO sqlalchemy.engine.Engine SELECT schema_name()
2024-02-20 15:41:06,910 INFO sqlalchemy.engine.Engine [generated in 0.00024s] ()
2024-02-20 15:41:07,152 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2024-02-20 15:41:07,152 INFO sqlalchemy.engine.Engine [generated in 0.00026s] ()
Traceback (most recent call last):
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3371, in _wrap_pool_connect
    return fn()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
    rec = pool._do_get()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 388, in __init__
    self.__connect()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 702, in __connect
    pool.dispatch.connect.for_modify(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 320, in _exec_w_sync_on_first_run
    self(*args, **kw)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 334, in __call__
    fn(*args, **kw)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 1695, in go
    return once_fn(*arg, **kw)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 678, in first_connect
    dialect.do_rollback(c.connection)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py", line 2845, in do_rollback
    super(MSDialect, self).do_rollback(dbapi_connection)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 683, in do_rollback
    dbapi_connection.rollback()
pyodbc.ProgrammingError: ('25000', '[25000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ROLLBACK can only be used in transaction blocks (3903) (SQLEndTran)')

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

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3325, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3404, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3374, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2208, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3371, in _wrap_pool_connect
    return fn()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
    rec = pool._do_get()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 388, in __init__
    self.__connect()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 702, in __connect
    pool.dispatch.connect.for_modify(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 320, in _exec_w_sync_on_first_run
    self(*args, **kw)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 334, in __call__
    fn(*args, **kw)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 1695, in go
    return once_fn(*arg, **kw)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 678, in first_connect
    dialect.do_rollback(c.connection)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py", line 2845, in do_rollback
    super(MSDialect, self).do_rollback(dbapi_connection)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 683, in do_rollback
    dbapi_connection.rollback()
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('25000', '[25000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ROLLBACK can only be used in transaction blocks (3903) (SQLEndTran)')
(Background on this error at: https://sqlalche.me/e/14/f405)

Code of Conduct

  • [X] I agree to follow this project's Code of Conduct.

bachpro900 avatar Feb 20 '24 14:02 bachpro900

I believe this is either a duplicate of #1354 or it is closely related.

To work around it we were using a patched version of PyODBC for some time. Then #1933 patch was added to local builds and it solved the problem. This PR was not yet merged to mainline Babelfish though.

staticlibs avatar Feb 20 '24 22:02 staticlibs

It seems to work when calling the engine.connect() object, and when an implicit transaction is begun and data is inserted without issue, the commit is done correctly. However, when I simulate an error (insert text value in a column of type integer) during data insertion, the rollback is done correctly (atomicity is guaranteed), but here again, the exception is raised, bringing us back to the initial problem. I tested the same code on a SQL Server, and everything is OK. Therefore, the problem persists only with Babelfish.

python file :

print(pyodbc.version)
engine = sqlalchemy.create_engine('mssql+pyodbc://babelfish_user:babelfish_password@babelfish_host/babelfish_db?driver=ODBC+Driver+17+for+SQL+Server', echo=True)
with engine.connect() as connection:
    with connection.begin() as transaction:
        for i in range(1,4):
            connection.execute(text(f"insert into dbo.airflow_transac values ({i}, {i+1} ,'col_2_{i}')"))
            if i == 3:
                connection.execute(text(f"insert into dbo.airflow_transac values ({i},'test ROLLBACK','col_2_{i}')"))

Logs:

4.0.39+babelfish
2024-02-21 14:47:41,145 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2024-02-21 14:47:41,145 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-02-21 14:47:41,194 INFO sqlalchemy.engine.Engine SELECT schema_name()
2024-02-21 14:47:41,194 INFO sqlalchemy.engine.Engine [generated in 0.00031s] ()
2024-02-21 14:47:41,278 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2024-02-21 14:47:41,279 INFO sqlalchemy.engine.Engine [generated in 0.00050s] ()
2024-02-21 14:47:41,349 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-02-21 14:47:41,350 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (1, 2 ,'col_2_1')
2024-02-21 14:47:41,350 INFO sqlalchemy.engine.Engine [generated in 0.00026s] ()
2024-02-21 14:47:41,363 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (2, 3 ,'col_2_2')
2024-02-21 14:47:41,363 INFO sqlalchemy.engine.Engine [generated in 0.00026s] ()
2024-02-21 14:47:41,365 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (3, 4 ,'col_2_3')
2024-02-21 14:47:41,365 INFO sqlalchemy.engine.Engine [generated in 0.00023s] ()
2024-02-21 14:47:41,367 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (3,'test ROLLBACK','col_2_3')
2024-02-21 14:47:41,367 INFO sqlalchemy.engine.Engine [generated in 0.00025s] ()
2024-02-21 14:47:41,377 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]invalid input syntax for type integer: "test ROLLBACK" (33557097) (SQLExecDirectW)')

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

Traceback (most recent call last):
  File "connect_to_sql.py", line 70, in <module>
    connection.execute(text(f"insert into dbo.airflow_transac values ({i},'test ROLLBACK','col_2_{i}')"))
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1389, in _execute_clauseelement
    ret = self._execute_context(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]invalid input syntax for type integer: "test ROLLBACK" (33557097) (SQLExecDirectW)')
[SQL: insert into dbo.airflow_transac values (3,'test ROLLBACK','col_2_3')]
(Background on this error at: http://sqlalche.me/e/14/f405)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 896, in _rollback_impl
    self.engine.dialect.do_rollback(self.connection)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 666, in do_rollback
    dbapi_connection.rollback()
pyodbc.ProgrammingError: ('25000', '[25000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ROLLBACK can only be used in transaction blocks (3903) (SQLEndTran)')

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

Traceback (most recent call last):
  File "connect_to_sql.py", line 70, in <module>
    connection.execute(text(f"insert into dbo.airflow_transac values ({i},'test ROLLBACK','col_2_{i}')"))
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2283, in __exit__
    self.rollback()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2243, in rollback
    self._do_rollback()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2433, in _do_rollback
    self._close_impl(try_deactivate=True)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2416, in _close_impl
    self._connection_rollback_impl()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2408, in _connection_rollback_impl
    self.connection._rollback_impl()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 898, in _rollback_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 896, in _rollback_impl
    self.engine.dialect.do_rollback(self.connection)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 666, in do_rollback
    dbapi_connection.rollback()
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('25000', '[25000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ROLLBACK can only be used in transaction blocks (3903) (SQLEndTran)')
(Background on this error at: http://sqlalche.me/e/14/f405)

bachpro900 avatar Feb 21 '24 13:02 bachpro900

@bachpro900 Do I understand correctly that the current issue (in addition to #1354) is about what exceptions are returned, and not about the behavior of the transaction itself? That on SQL Server only the INSERT exception is returned, without the ROLLBACK exception, while on Babelfish both the INSERT exception and the ROLLBACK exceptions are returned? If so, could you tell us a bit more about how this difference is affecting your application? We would like to look into it and better understand the use-case.

AlexFriedmanAWS avatar Feb 22 '24 10:02 AlexFriedmanAWS

Difference is not limited to what exception is returned but transactions state as well. For SQL server, transaction is kept open after exception and user can do rollback/commit as desired (commit might not be possible in all cases, depends on error). Babelfish rolls back transaction on its own before returning the exception and user attempt to rollback/commit will fail.

@bachpro900 Could you please provide the details for first exception (before calling rollback) encountered for original issue. Babelfish can support rollback behavior (it already does support for few errors) but it depends on exception encountered.

surendravishnoi avatar Feb 22 '24 12:02 surendravishnoi

@surendravishnoi In this case it's rolled back on both SQL Server and Babelfish, so it doesn't look like there's a difference in transactions state here (unless the framework is doing it behind the scenes and silently rolls back instead of committing? However, when using e.g. SSMS on SQL Server, this error rolls back the transaction.).

AlexFriedmanAWS avatar Feb 22 '24 12:02 AlexFriedmanAWS

It's rolled back on both Babelfish and SQL Server, but the behavior is not the same. In the case of Babelfish, along with the data insertion exception, there is also an exception stating '[ROLLBACK can only be used in transaction blocks (3903) (SQLEndTran)'. Consequently, I need to handle it in my application as a non-programming error, more like a warning. My focus is to understand the reason for the rollback, rather than the reason for the rollback exception.

@surendravishnoi, sorry but I am not sure to understand to which "first exception" details you are refering to. But please find below more information.

Sorry if my comment is too long.

Python code: Exactely the same as in my second comment.

- Data insert in SQL Server ==> only 2 exceptions : pyodbc.DataError: ('22018', "[22018] ...etc. and sqlalchemy.exc.DataError: (pyodbc.DataError) ('22018', "[22018] ... etc.

*** test on SQL SERVER ***
4.0.39+babelfish
2024-02-22 16:41:53,860 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2024-02-22 16:41:53,861 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-02-22 16:41:53,863 INFO sqlalchemy.engine.Engine SELECT schema_name()
2024-02-22 16:41:53,863 INFO sqlalchemy.engine.Engine [generated in 0.00038s] ()
2024-02-22 16:41:53,865 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2024-02-22 16:41:53,865 INFO sqlalchemy.engine.Engine [generated in 0.00034s] ()
2024-02-22 16:41:53,869 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-02-22 16:41:53,869 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (1, 2 ,'col_2_1')
2024-02-22 16:41:53,869 INFO sqlalchemy.engine.Engine [generated in 0.00039s] ()
2024-02-22 16:41:53,871 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (2, 3 ,'col_2_2')
2024-02-22 16:41:53,872 INFO sqlalchemy.engine.Engine [generated in 0.00042s] ()
2024-02-22 16:41:53,873 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (3, 4 ,'col_2_3')
2024-02-22 16:41:53,873 INFO sqlalchemy.engine.Engine [generated in 0.00032s] ()
2024-02-22 16:41:53,875 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (3,'test ROLLBACK','col_2_3')
2024-02-22 16:41:53,875 INFO sqlalchemy.engine.Engine [generated in 0.00033s] ()
2024-02-22 16:41:53,878 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
pyodbc.DataError: ('22018', "[22018] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting the varchar value 'test ROLLBACK' to data type int. (245) (SQLExecDirectW)")

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

Traceback (most recent call last):
  File "connect_to_sql.py", line 70, in <module>
    connection.execute(text(f"insert into dbo.airflow_transac values ({i},'test ROLLBACK','col_2_{i}')"))
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1389, in _execute_clauseelement
    ret = self._execute_context(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (pyodbc.DataError) ('22018', "[22018] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting the varchar value 'test ROLLBACK' to data type int. (245) (SQLExecDirectW)")
[SQL: insert into dbo.airflow_transac values (3,'test ROLLBACK','col_2_3')]
(Background on this error at: http://sqlalche.me/e/14/9h9h)

- Data insert in Babelfish ==> 4 exceptions : 2 as above, plus pyodbc.ProgrammingError: ('25000', '[25000] ...etc. and sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('25000', '[25000] ...etc.

*** test on Babelfish ***
4.0.39+babelfish
2024-02-22 16:46:01,855 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2024-02-22 16:46:01,856 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-02-22 16:46:01,921 INFO sqlalchemy.engine.Engine SELECT schema_name()
2024-02-22 16:46:01,921 INFO sqlalchemy.engine.Engine [generated in 0.00047s] ()
2024-02-22 16:46:02,002 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2024-02-22 16:46:02,002 INFO sqlalchemy.engine.Engine [generated in 0.00021s] ()
2024-02-22 16:46:02,071 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-02-22 16:46:02,072 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (1, 2 ,'col_2_1')
2024-02-22 16:46:02,072 INFO sqlalchemy.engine.Engine [generated in 0.00028s] ()
2024-02-22 16:46:02,086 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (2, 3 ,'col_2_2')
2024-02-22 16:46:02,087 INFO sqlalchemy.engine.Engine [generated in 0.00028s] ()
2024-02-22 16:46:02,088 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (3, 4 ,'col_2_3')
2024-02-22 16:46:02,088 INFO sqlalchemy.engine.Engine [generated in 0.00025s] ()
2024-02-22 16:46:02,089 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (3,'test ROLLBACK','col_2_3')
2024-02-22 16:46:02,089 INFO sqlalchemy.engine.Engine [generated in 0.00027s] ()
2024-02-22 16:46:02,091 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]invalid input syntax for type integer: "test ROLLBACK" (33557097) (SQLExecDirectW)')

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

Traceback (most recent call last):
  File "connect_to_sql.py", line 70, in <module>
    connection.execute(text(f"insert into dbo.airflow_transac values ({i},'test ROLLBACK','col_2_{i}')"))
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/future/engine.py", line 295, in execute
    return self._execute_20(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1520, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1389, in _execute_clauseelement
    ret = self._execute_context(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]invalid input syntax for type integer: "test ROLLBACK" (33557097) (SQLExecDirectW)')
[SQL: insert into dbo.airflow_transac values (3,'test ROLLBACK','col_2_3')]
(Background on this error at: http://sqlalche.me/e/14/f405)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 896, in _rollback_impl
    self.engine.dialect.do_rollback(self.connection)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 666, in do_rollback
    dbapi_connection.rollback()
pyodbc.ProgrammingError: ('25000', '[25000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ROLLBACK can only be used in transaction blocks (3903) (SQLEndTran)')

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

Traceback (most recent call last):
  File "connect_to_sql.py", line 70, in <module>
    connection.execute(text(f"insert into dbo.airflow_transac values ({i},'test ROLLBACK','col_2_{i}')"))
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2283, in __exit__
    self.rollback()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2243, in rollback
    self._do_rollback()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2433, in _do_rollback
    self._close_impl(try_deactivate=True)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2416, in _close_impl
    self._connection_rollback_impl()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2408, in _connection_rollback_impl
    self.connection._rollback_impl()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 898, in _rollback_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 896, in _rollback_impl
    self.engine.dialect.do_rollback(self.connection)
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 666, in do_rollback
    dbapi_connection.rollback()
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('25000', '[25000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ROLLBACK can only be used in transaction blocks (3903) (SQLEndTran)')
(Background on this error at: http://sqlalche.me/e/14/f405)

I also tested whether the user can commit/rollback as desired in both SQL Server and Babelfish. The results are not the same.

I just added a commit for each row insertion :

with engine.connect() as connection:
    with connection.begin() as transaction:
        for i in range(1,4):
            connection.execute(text(f"insert into dbo.airflow_transac values ({i}, {i+1} ,'col_2_{i}')"))
            if i == 3:
                connection.execute(text(f"insert into dbo.airflow_transac values ({i},'test ROLLBACK','col_2_{i}')"))
            transaction.commit()

In SQL Server : when i is in (1,2), the row is inserted ==> two commits (two rows inserted). Then when i =3 the transaction is closed.

*** test on SQL SERVER ***
4.0.39+babelfish
2024-02-22 16:58:43,338 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2024-02-22 16:58:43,338 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-02-22 16:58:43,340 INFO sqlalchemy.engine.Engine SELECT schema_name()
2024-02-22 16:58:43,340 INFO sqlalchemy.engine.Engine [generated in 0.00028s] ()
2024-02-22 16:58:43,342 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2024-02-22 16:58:43,342 INFO sqlalchemy.engine.Engine [generated in 0.00026s] ()
2024-02-22 16:58:43,345 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-02-22 16:58:43,346 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (1, 2 ,'col_2_1')
2024-02-22 16:58:43,346 INFO sqlalchemy.engine.Engine [generated in 0.00025s] ()
2024-02-22 16:58:43,348 INFO sqlalchemy.engine.Engine COMMIT
2024-02-22 16:58:43,353 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (2, 3 ,'col_2_2')
2024-02-22 16:58:43,354 INFO sqlalchemy.engine.Engine [generated in 0.00073s] ()
2024-02-22 16:58:43,356 INFO sqlalchemy.engine.Engine COMMIT
Traceback (most recent call last):
  File "connect_to_sql.py", line 71, in <module>
    transaction.commit()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2265, in commit
    self._do_commit()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2458, in _do_commit
    raise exc.InvalidRequestError("This transaction is inactive")
sqlalchemy.exc.InvalidRequestError: This transaction is inactive

In Babelfish : the row is inserted only when i=1 ==> only one commit here (only one row is inserted). Seems that the transaction is closed when i = 2, even before encountering an insertion error !.

*** test on Babelfish ***
4.0.39+babelfish
2024-02-22 16:59:57,456 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2024-02-22 16:59:57,457 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-02-22 16:59:57,511 INFO sqlalchemy.engine.Engine SELECT schema_name()
2024-02-22 16:59:57,511 INFO sqlalchemy.engine.Engine [generated in 0.00037s] ()
2024-02-22 16:59:57,595 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2024-02-22 16:59:57,595 INFO sqlalchemy.engine.Engine [generated in 0.00023s] ()
2024-02-22 16:59:57,674 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-02-22 16:59:57,675 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (1, 2 ,'col_2_1')
2024-02-22 16:59:57,675 INFO sqlalchemy.engine.Engine [generated in 0.00016s] ()
2024-02-22 16:59:57,689 INFO sqlalchemy.engine.Engine COMMIT
2024-02-22 16:59:57,728 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-02-22 16:59:57,728 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (2, 3 ,'col_2_2')
2024-02-22 16:59:57,728 INFO sqlalchemy.engine.Engine [generated in 0.00035s] ()
2024-02-22 16:59:57,729 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File "connect_to_sql.py", line 71, in <module>
    transaction.commit()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2265, in commit
    self._do_commit()
  File "/opt/pns-refontedata/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2458, in _do_commit
    raise exc.InvalidRequestError("This transaction is inactive")
sqlalchemy.exc.InvalidRequestError: This transaction is inactive

bachpro900 avatar Feb 22 '24 16:02 bachpro900

@bachpro900 understood, thanks. I have reproduced the issue internally, and we are now tracking it. It has to do with mapping unmapped error codes (see this comment for an explanation).

For reference, the error codes in this case are PG error 33685634 "invalid input syntax for type" (T-SQL error 245 "conversion failed").

AlexFriedmanAWS avatar Feb 22 '24 16:02 AlexFriedmanAWS

Hi @bachpro900, I'm trying to reproduce the latest example mentioned in the thread but getting a different error compared to what you mentioned when running it against SQL server. Please confirm if there are any other configuration parameters we need to set here. I am using the native pyodbc library.

Python file

import sqlalchemy as sa
from sqlalchemy import text
import logging
import pyodbc

print(sa.__version__)
print(pyodbc.version)

engine = sa.create_engine('mssql+pyodbc://username:password@host_url/datbase_name?driver=ODBC+Driver+17+for+SQL+Server', echo=True)
with engine.connect() as connection:
    with connection.begin() as transaction:
        for i in range(1,4):
            connection.execute(text(f"insert into dbo.airflow_transac values ({i}, {i+1} ,'col_2_{i}')"))
            if i == 3:
                connection.execute(text(f"insert into dbo.airflow_transac values ({i},'test ROLLBACK','col_2_{i}')"))
            transaction.commit()

Logs:

-- SQL Server output
1.4.50
4.0.39
2024-03-05 16:09:57,700 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2024-03-05 16:09:57,700 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-03-05 16:09:57,771 INFO sqlalchemy.engine.Engine SELECT schema_name()
2024-03-05 16:09:57,771 INFO sqlalchemy.engine.Engine [generated in 0.00016s] ()
2024-03-05 16:09:57,987 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2024-03-05 16:09:57,987 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ()
2024-03-05 16:09:58,127 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-03-05 16:09:58,127 INFO sqlalchemy.engine.Engine insert into dbo.airflow_transac values (1, 2 ,'col_2_1')
2024-03-05 16:09:58,127 INFO sqlalchemy.engine.Engine [generated in 0.00017s] ()
2024-03-05 16:09:58,198 INFO sqlalchemy.engine.Engine COMMIT
Traceback (most recent call last):
  File "11", line 13, in <module>
    connection.execute(text(f"insert into dbo.airflow_transac values ({i}, {i+1} ,'col_2_{i}')"))
  File "/home/tzlkhan/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1385, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/tzlkhan/.local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 335, in _execute_on_connection
    self, multiparams, params, execution_options
  File "/home/tzlkhan/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1587, in _execute_clauseelement
    cache_hit=cache_hit,
  File "/home/tzlkhan/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1831, in _execute_context
    TransactionalContext._trans_ctx_check(self)
  File "/home/tzlkhan/.local/lib/python3.7/site-packages/sqlalchemy/engine/util.py", line 200, in _trans_ctx_check
    "Can't operate on closed transaction inside context "
sqlalchemy.exc.InvalidRequestError: Can't operate on closed transaction inside context manager.  Please complete the context manager before emitting further commands.

tanscorpio7 avatar Mar 05 '24 16:03 tanscorpio7