1

I have built a Webapp with python flask, that reads and writes to SQL tables.

Typically in the morning, I receive an error regarding SQL query execution that looks like this (see logs snippet below). I'm quite new to web development and everything involved, so this is pretty alien to me.

From what I can understand based on google searches, it's an issue with my SQL connection timing out, and completely stopping. It works again if I restart my app on Azure but I'm hoping someone can help me sort out the root of the issue.

If there are any other things you may need to see to help me out with this issue (such as the structure of my SQL connection script, or more lines from the logs) let me know and I'll add them to the post. Thanks all!

[2021-06-24 08:30:20,133] ERROR in app: Exception on /FUNCTION/ [GET]
2021-06-24T08:30:20.134169815Z Traceback (most recent call last):
2021-06-24T08:30:20.134180016Z   File "/tmp/8d9367259a977d960/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 1725, in execute
2021-06-24T08:30:20.134188617Z     cur.execute(*args, **kwargs)
2021-06-24T08:30:20.134196217Z pyodbc.Error: ('08S02', '[08S02] [Microsoft][ODBC Driver 17 for SQL Server]SMux Provider: Physical connection is not usable [xFFFFFFFF].  (-1) (SQLExecDirectW)')
2021-06-24T08:30:20.134215319Z 
2021-06-24T08:30:20.134222119Z The above exception was the direct cause of the following exception:
2021-06-24T08:30:20.134229220Z 
2021-06-24T08:30:20.134236120Z Traceback (most recent call last):
2021-06-24T08:30:20.134243021Z   File "/tmp/8d9367259a977d960/antenv/lib/python3.7/site-packages/flask/app.py", line 2070, in wsgi_app
2021-06-24T08:30:20.134250221Z     response = self.full_dispatch_request()
2021-06-24T08:30:20.134257122Z   File "/tmp/8d9367259a977d960/antenv/lib/python3.7/site-packages/flask/app.py", line 1515, in full_dispatch_request
2021-06-24T08:30:20.134264322Z     rv = self.handle_user_exception(e)
2021-06-24T08:30:20.134271123Z   File "/tmp/8d9367259a977d960/antenv/lib/python3.7/site-packages/flask/app.py", line 1513, in full_dispatch_request
2021-06-24T08:30:20.134278424Z     rv = self.dispatch_request()
2021-06-24T08:30:20.134285124Z   File "/tmp/8d9367259a977d960/antenv/lib/python3.7/site-packages/flask/app.py", line 1499, in dispatch_request
2021-06-24T08:30:20.134292325Z     return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
2021-06-24T08:30:20.134299325Z   File "/tmp/8d9367259a977d960/app/routes.py", line 1156, in FUNCTION
2021-06-24T08:30:20.134307226Z     RESULTS = FUNCTION()
2021-06-24T08:30:20.134314026Z   File "/tmp/8d9367259a977d960/models.py", line 403, in FUNCTION
2021-06-24T08:30:20.134320927Z     DF = pd.read_sql_query(Query, sql_conn)
2021-06-24T08:30:20.134327427Z   File "/tmp/8d9367259a977d960/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 394, in read_sql_query
2021-06-24T08:30:20.134334828Z     chunksize=chunksize,
2021-06-24T08:30:20.134341228Z   File "/tmp/8d9367259a977d960/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 1771, in read_query
2021-06-24T08:30:20.134348429Z     cursor = self.execute(*args)
2021-06-24T08:30:20.134368130Z   File "/tmp/8d9367259a977d960/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 1737, in execute
2021-06-24T08:30:20.134375531Z     raise ex from exc
2021-06-24T08:30:20.134383232Z pandas.io.sql.DatabaseError: Execution failed on sql 'select * from table': ('08S02', '[08S02] [Microsoft][ODBC Driver 17 for SQL Server]SMux Provider: Physical connection is not usable [xFFFFFFFF].  (-1) (SQLExecDirectW)')
2021-06-24T08:30:20.144119574Z 172.16.2.1 - - [24/Jun/2021:08:30:20 +0000] "GET /FUNCTION/ HTTP/1.1" 500 290 "https://webapp.co.uk/page" "Mozilla/5.0 (iPhone; CPU iPhone OS 14_6 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Mobile/15E148 Safari/604.1"
2021-06-24T08:30:23.114523917Z [2021-06-24 08:30:23,113] ERROR in app: Exception on /FUNCTION/ID [GET]
2021-06-24T08:30:23.114576221Z Traceback (most recent call last):
2021-06-24T08:30:23.114583522Z   File "/tmp/8d9367259a977d960/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 1725, in execute
2021-06-24T08:30:23.114588322Z     cur.execute(*args, **kwargs)
2021-06-24T08:30:23.114592723Z pyodbc.Error: ('08S02', '[08S02] [Microsoft][ODBC Driver 17 for SQL Server]SMux Provider: Physical connection is not usable [xFFFFFFFF].  (-1) (SQLExecDirectW)')

I just checked a bit further back in the logs and I seem to get this similarly themed error as well:

2021-06-23T07:41:28.233313212Z [2021-06-23 07:41:28,228] ERROR in app: Exception on /function/ [GET]
2021-06-23T07:41:28.233318713Z Traceback (most recent call last):
2021-06-23T07:41:28.233323813Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 1725, in execute
2021-06-23T07:41:28.233329613Z     cur.execute(*args, **kwargs)
2021-06-23T07:41:28.233334814Z pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)')
2021-06-23T07:41:28.233340214Z 
2021-06-23T07:41:28.233345215Z During handling of the above exception, another exception occurred:
2021-06-23T07:41:28.233350115Z 
2021-06-23T07:41:28.233374117Z Traceback (most recent call last):
2021-06-23T07:41:28.233379417Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 1729, in execute
2021-06-23T07:41:28.233384918Z     self.con.rollback()
2021-06-23T07:41:28.233390018Z pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure (-2147467259) (SQLEndTran)')
2021-06-23T07:41:28.233395418Z 
2021-06-23T07:41:28.233400319Z The above exception was the direct cause of the following exception:
2021-06-23T07:41:28.233405119Z 
2021-06-23T07:41:28.233409720Z Traceback (most recent call last):
2021-06-23T07:41:28.233414720Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/flask/app.py", line 2070, in wsgi_app
2021-06-23T07:41:28.233420120Z     response = self.full_dispatch_request()
2021-06-23T07:41:28.233425121Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/flask/app.py", line 1515, in full_dispatch_request
2021-06-23T07:41:28.233430721Z     rv = self.handle_user_exception(e)
2021-06-23T07:41:28.233435722Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/flask/app.py", line 1513, in full_dispatch_request
2021-06-23T07:41:28.233441122Z     rv = self.dispatch_request()
2021-06-23T07:41:28.233446022Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/flask/app.py", line 1499, in dispatch_request
2021-06-23T07:41:28.233451123Z     return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
2021-06-23T07:41:28.233455823Z   File "/tmp/8d935b633760569e3/app/routes.py", line 1139, in function
2021-06-23T07:41:28.233460623Z     results= function()
2021-06-23T07:41:28.233466124Z   File "/tmp/8d935b633760569e3/models.py", line 403, in function
2021-06-23T07:41:28.233471124Z     df = pd.read_sql_query(query, sql_conn)
2021-06-23T07:41:28.233475825Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 394, in read_sql_query
2021-06-23T07:41:28.233480925Z     chunksize=chunksize,
2021-06-23T07:41:28.233485925Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 1771, in read_query
2021-06-23T07:41:28.233490926Z     cursor = self.execute(*args)
2021-06-23T07:41:28.233495426Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 1734, in execute
2021-06-23T07:41:28.233500327Z     raise ex from inner_exc
2021-06-23T07:41:28.233504927Z pandas.io.sql.DatabaseError: Execution failed on sql: select * from [table]
2021-06-23T07:41:28.233509927Z ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)')
2021-06-23T07:41:28.233514928Z unable to rollback
2021-06-23T07:41:28.236974194Z 172.16.2.1 - - [23/Jun/2021:07:41:28 +0000] "GET /function/ HTTP/1.1" 500 290 "https://webapp.co.uk/page" "Mozilla/5.0 (iPhone; CPU iPhone OS 14_6 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Mobile/15E148 Safari/604.1"
2021-06-23T07:41:30.460419463Z [2021-06-23 07:41:30,459] ERROR in app: Exception on /form-choice/id[GET]
2021-06-23T07:41:30.460444664Z Traceback (most recent call last):
2021-06-23T07:41:30.460450165Z   File "/tmp/8d935b633760569e3/antenv/lib/python3.7/site-packages/pandas/io/sql.py", line 1725, in execute
2021-06-23T07:41:30.460460166Z     cur.execute(*args, **kwargs)
2021-06-23T07:41:30.460464066Z pyodbc.Error: ('08S02', '[08S02] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x20 (32) (SQLExecDirectW)')

I've already asked this question over here on stack overflow but no one has replied, so hoping this site would be more useful, as it's more of a db related question.

Improve this question
3
  • Do you have a long-lived connection to SQL Server? You shouldn’t.
    – David Browne - Microsoft
    Commented Jun 25, 2021 at 11:20
  • @DavidBrowne-Microsoft could you elaborate a bit more? I'm quite new to this, thanks
    – Liam
    Commented Jun 25, 2021 at 13:59
  • Looks like you may be trying to keep a connection open overnight, and it’s failing.
    – David Browne - Microsoft
    Commented Jun 25, 2021 at 17:00

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.