'sqlalchemy pyodbc connect() fails without setting exception
When I run fastapi/pyodbc to connect MSSQL, unicorn server crashes frequently when connect to sqlalchemy fails (sometimes not everytime). I have noticed specifically with nullpool class is used while creating engine.
Here is the sqlalchemy error: SystemError: returned NULL without setting an exception
I use gunicorn to run unicorn, so whenever unicorn crashes with sqlalchemy issue...unicorn server crashes and gunicorn restart the worker thread.
In the code, I connect to remote SQL Server using pyodbc. I use sqlalchemy as ORM.
Seems like db connect method is failing and it is not setting an exception. So try catch is not executing EXCEPT code correctly. So, python crashes. Eventually unicorn is crashing.
Here is the unicorn crash message:
[2771] [WARNING] Worker with pid 2773 was terminated due to signal 11 <br>
[2852] [INFO] Booting worker with pid: 2852
sqlalchemy, python 3.10.2, pyodbc, gunicorn, unicorn - all are latest releases/versions.
Here is the code:
def get_sqlserver_info(self):
host_names: list = []
if self.gg_details.get('groups'):
for g_idx, g_group in enumerate(self.gg_details['groups']):
if g_group.get('dsn_host_name'):
if g_group['dsn_host_name'].upper() not in [i.upper() for i in host_names]:
host_names.append(g_group['dsn_host_name'])
password: str = ''
if self.target_dbinst_connect_mode == 'sql':
password = get_password(account_type='sql', domain='', login_user='', default_svc_acct='Y')
for host_name in host_names:
if self.target_dbinst_connect_mode == 'windows':
connection_url = URL.create(
"mssql+pyodbc",
host=host_name,
port=1433,
database="master",
query={
"driver": "ODBC Driver 17 for SQL Server",
"authentication": "ActiveDirectoryIntegrated",
"TrustServerCertificate": "Yes",
"MARS_Connection": "Yes",
},
)
elif self.target_dbinst_connect_mode == 'sql':
connection_url = URL.create(
"mssql+pyodbc",
username='sql_user',
password=password,
host=host_name,
port=1433,
database="master",
query={
"driver": "ODBC Driver 17 for SQL Server",
"MARS_Connection": "Yes",
},
)
g_mssql_def_isolation_level = "READ COMMITTED"
g_mssql_RU_isolation_level = "READ UNCOMMITTED"
l_connect_args = {
"timeout": 20,
"encoding": "utf8"
}
r_engine = create_engine(connection_url, echo=False, future=True, query_cache_size=1200,
connect_args=l_connect_args, poolclass=NullPool)
# get agent job information
try:
dbconn = r_engine.connect().execution_options(isolation_level=g_mssql_RU_isolation_level)
# p = Path(Path.cwd() / 'mm/DBUtility/sql_scripts/gg/SQLJobStatus.sql').resolve()
p = Path(__file__).parent.parent.absolute() / 'sql_scripts/gg/SQLJobStatus.sql'
sql = Path(p).read_text()
stmt = text(sql)
result = dbconn.execute(stmt)
dbconn.close()
except exc.IntegrityError as e:
print(f'{host_name} - Integrity error raised', '\n', str(e))
except exc.SQLAlchemyError as e:
print(f'Could not connect to {host_name}. Func: get_sqlserver_info - Something went wrong', '\n',
str(e))
# get database version
try:
dbconn = r_engine.connect().execution_options(isolation_level=g_mssql_RU_isolation_level)
sql = """\
SELECT @@version as db_version
"""
stmt = text(sql)
result = dbconn.execute(stmt)
dbconn.close()
except exc.IntegrityError as e:
print(f'{host_name} - Integrity error raised', '\n', str(e))
except exc.SQLAlchemyError as e:
print(f'Could not connect to {host_name}. Func: get_sqlserver_info - Something went wrong', '\n',
str(e))
r_engine.dispose()
Here is error message
ERROR:apscheduler.executors.default:Job "Job Name (trigger: interval[0:00:10], next run at: 2022-01-23 15:01:28 EST)" raised an exception
Traceback (most recent call last): <br>
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/apscheduler/executors/base.py", line 125, in run_job
retval = job.func(*job.args, **job.kwargs)
File "/home/sqlserver/MMProj/mm/Config/services/APSchService.py", line 30, in run_job
apsch.gg_monitor(p_job)
File "/home/sqlserver/MMProj/mm/Config/services/APSchService.py", line 51, in gg_monitor
ps_status_code, error_msg, outlst = gg.run_ggcmds_using_winrm(
File "/home/sqlserver/MMProj/mm/DBUtility/services/GGMonitor19Service.py", line 2172, in run_ggcmds_using_winrm
self.get_sqlserver_info()
File "/home/sqlserver/MMProj/mm/DBUtility/services/GGMonitor19Service.py", line 1603, in get_sqlserver_info
dbconn = r_engine.connect().execution_options(isolation_level=g_mssql_RU_isolation_level)
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 406, in connect
return super(Engine, self).connect()
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3204, in connect
return self._connection_cls(self, close_with_result=close_with_result)
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
else engine.raw_connection()
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3283, in raw_connection
return self._wrap_pool_connect(self.pool.connect, _connection)
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3250, in _wrap_pool_connect
return fn()
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 310, in connect
return _ConnectionFairy._checkout(self)
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
rec = pool._do_get()
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
with util.safe_reraise():
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
return self._create_connection()
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
return _ConnectionRecord(self)
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
self.__connect()
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 665, in __connect
with util.safe_reraise():
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 590, in connect
return dialect.connect(*cargs, **cparams)
File "/home/sqlserver/MMProj/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 597, in connect
return self.dbapi.connect(*cargs, **cparams) <br>
SystemError: <built-in function connect> returned NULL without setting an exception
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|