psycopg2.InterfaceError: connection already closed

[allouzi]

Describe the bug
psycopg2.InterfaceError: connection already closed.

Exception or Stack Trace
2020-12-03T00:06:41.150453788Z psycopg2.OperationalError: SSL SYSCALL error: EOF detected
2020-12-03T00:06:41.150457488Z
2020-12-03T00:06:41.150460788Z
2020-12-03T00:06:41.150464188Z The above exception was the direct cause of the following exception:
2020-12-03T00:06:41.150467888Z
2020-12-03T00:06:41.150471088Z Traceback (most recent call last):
2020-12-03T00:06:41.150474588Z File "/home/site/wwwroot/backend_api/pre_model_scheduler.py", line 56, in start_pre_model_scheduler
2020-12-03T00:06:41.150478088Z handle_expired_documents()
2020-12-03T00:06:41.150481688Z File "/home/site/wwwroot/backend_api/pre_model_scheduler.py", line 39, in handle_expired_documents
2020-12-03T00:06:41.150485188Z documents_to_resend = check_status_in_db()
2020-12-03T00:06:41.150488588Z File "/home/site/wwwroot/backend_api/pre_model_scheduler.py", line 32, in check_status_in_db
2020-12-03T00:06:41.150492188Z if res:
2020-12-03T00:06:41.150502488Z File "/antenv/lib/python3.7/site-packages/django/db/models/query.py", line 272, in bool
2020-12-03T00:06:41.150516188Z self._fetch_all()
2020-12-03T00:06:41.150519688Z File "/antenv/lib/python3.7/site-packages/django/db/models/query.py", line 1186, in _fetch_all
2020-12-03T00:06:41.150522988Z self._result_cache = list(self._iterable_class(self))
2020-12-03T00:06:41.150526088Z File "/antenv/lib/python3.7/site-packages/django/db/models/query.py", line 54, in iter
2020-12-03T00:06:41.150529288Z results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
2020-12-03T00:06:41.150532388Z File "/antenv/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1065, in execute_sql
2020-12-03T00:06:41.150535588Z cursor.execute(sql, params)
2020-12-03T00:06:41.150538688Z File "/antenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 68, in execute
2020-12-03T00:06:41.150541888Z return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
2020-12-03T00:06:41.150544988Z File "/antenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
2020-12-03T00:06:41.150548188Z return executor(sql, params, many, context)
2020-12-03T00:06:41.150551288Z File "/antenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
2020-12-03T00:06:41.150554388Z return self.cursor.execute(sql, params)
2020-12-03T00:06:41.150557388Z File "/antenv/lib/python3.7/site-packages/django/db/utils.py", line 89, in exit
2020-12-03T00:06:41.150562188Z raise dj_exc_value.with_traceback(traceback) from exc_value
2020-12-03T00:06:41.150565488Z File "/antenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
2020-12-03T00:06:41.150568588Z return self.cursor.execute(sql, params)
2020-12-03T00:06:41.150571588Z django.db.utils.OperationalError: SSL SYSCALL error: EOF detected

To Reproduce
Create a PostgreSQL database in Azure.
Create a Django App (we are using Django == 2.2) on your local machine.
Use the requirements.txt file that is attached to have all of our project dependencies. (if I need to be more specific for you to understand how to use and modify settings please let me know)
In the settings.py file update the DATABASES default settings to your PostgreSQL settings. (USER, HOST, etc.)
You can set one or two endpoints (in the views.py file) in order to test the app after deployment.
Create a python-based Azure Web App.
Deploy the app to Azure Web App. (we are using CI/CD with Local Git)
Add the attached scheduler file to the main folder. (Based on Django layout it is the folder where the apps.py file is in)
In apps.py file in ready() function add the below lines -

•      from < THE_NAME_OF_THE_DJANGO_PROJECT > import pre_model_scheduler
  

•      pre_model_scheduler.start()
  

Create a table in the database and modify line 28 in pre_model_scheduler.py accordingly.
(You can see in the pre_model_scheduler.py file that we are referring to the Documents table by using the Django ORM.)

Code Snippet
Add the code snippet that causes the issue.

def check_status_in_db():
    """checks in database for pre-model data that has not been updated for x hours"""
    # get data older than the time threshold
    documents_to_resend = []
    time_threshold = datetime.datetime.now() - timedelta(hours=MAX_TIME_AS_PRE_MODEL)
    res = Documents.objects.filter(document_status="pre-model", source__in=[Source.WEB, Source.EMAIL],
                                   upload_date__lt=time_threshold)
    if res:
        documents_to_resend = [{"document_id": r.document_id, "company_id": r.company_id} for r in res]
    return documents_to_resend

Screenshots
If applicable, add screenshots to help explain your problem.

Setup (please complete the following information):

• Python Version: Python 3.7
• azure-servicebus==0.50.2
• Django 2.2

[yunhaoling]

hey @allouzi,

based on your description, I'm wondering it's something related to the Azure Web App not Azure Service Bus.
where is this "pre_model_scheduler.py" -- is there any sample code that you're trying out in our repo?

[allouzi]

hey @yunhaoling thanks for your reply regarding to the "pre_model_scheduler.py" below is the structure of project
[project-name]/
├── [project-name]/
│ ├── init.py
│ ├── settings.py
│ ├── urls.py
│ └── wsgi.py
├── [app-name]/
| ├── [migrations]/...
│ ├── init.py
│ ├── admin.py
│ ├── pre_model_scheduler.py
│ ├── urls.py
│ ├── views.py
│ ├── models.py
│ ├── ...
├── init.py
└── manage.py

and for the sample code
`""""
Scheduler to run as a standalone file
Checks if pre-model status for data has been updated over specified time period - if not resend to queue
"""
import logging
import datetime
from datetime import timedelta
from django.utils import timezone
from apscheduler.schedulers.background import BackgroundScheduler
from backend_api.upload_docs import get_container_name, send_uploaded_document_id_list_to_queue
from backend_api.models import Documents
from backend_api.utils import Source
from backend.settings import DEBUG_FLAG

if DEBUG_FLAG:
from Configuration.environments.dev import MAX_TIME_AS_PRE_MODEL, PRE_MODEL_CONFIG, PRE_QUEUE_CONFIG
else:
from Configuration.environments.test import MAX_TIME_AS_PRE_MODEL, PRE_MODEL_CONFIG, PRE_QUEUE_CONFIG

logger = logging.getLogger(name)
FUNC_IS_RUNNING = False

def check_status_in_db():
"""checks in database for pre-model data that has not been updated for x hours"""
# get data older than the time threshold
documents_to_resend = []
# time_threshold = datetime.datetime.now() - timedelta(hours=MAX_TIME_AS_PRE_MODEL)
time_threshold = timezone.now() - timezone.timedelta(hours=MAX_TIME_AS_PRE_MODEL)
res = Documents.objects.filter(document_status="pre-model", source__in=[Source.WEB, Source.EMAIL],
upload_date__lt=time_threshold)
if res:
documents_to_resend = [{"document_id": r.document_id, "company_id": r.company_id} for r in res]
return documents_to_resend

def handle_expired_documents():
"""handles process of identifying documents that have not been updated in the DB - and resending those to queue"""
documents_to_resend = check_status_in_db()
logger.info(f"SYSTEM \ Documents stuck in pre-model to resend to queue: {documents_to_resend}")
if documents_to_resend: # if there are documents that are outdated
# resending document_ids to the queue that have expired
for document in documents_to_resend:
container_name, container_errors = get_container_name(document["company_id"])
send_uploaded_document_id_list_to_queue(document_id_list=[document["document_id"]],
queue_name=PRE_QUEUE_CONFIG["queue_name"],
container_name=container_name)

def start_pre_model_scheduler():
"""main listener function - calls function to check for any expired documents"""
global FUNC_IS_RUNNING
try:
FUNC_IS_RUNNING = True
logger.debug(f'SYSTEM \ In start_pre_model_scheduler function.....')
handle_expired_documents()
FUNC_IS_RUNNING = False
except Exception as e:
logger.error(f'SYSTEM \ Error running the pre-model scheduler: {e}', exc_info=True)

def start():
"""start function to run when django server starts"""
logger.debug(f'SYSTEM \ In start function for pre-model listener....')
if not FUNC_IS_RUNNING:
try:
scheduler = BackgroundScheduler()
scheduler.add_job(start_pre_model_scheduler, 'interval', id='pre-model-scheduler',
minutes=PRE_MODEL_CONFIG["time_scheduler"])
scheduler.start()
except Exception as e:
logger.error(f'SYSTEM \ Unable to start the pre-model scheduler: {e}', exc_info=True)
`

[allouzi]

Unfortunately , we can't reproduce the issue from our end.

[yunhaoling]

hey @allouzi , I'm sorry I'm not familiar with the django and sql stuff (I work on the Python ServiceBus Sdk), but I could look for someone to help on that.

Is it you're trying to create a django app which connects to a PostgreSQL database in Azure, and you deployed the django app to a Azure Web App

and in your app, you call check_status_in_db periodically which causes you the trouble.

Several questions in my head:

• Which PostgreSQL version are you using?
• How large is the interval? would it work if setting to a smaller value?
• How often does the issue happen, occasionally or always?
• would manually try/catch and reconnect to the database be a workaround?

[allouzi]

hey @yunhaoling
below answers to your queries :
• Which PostgreSQL version are you using?
PostgreSQL version = 11
• How large is the interval? would it work if setting to a smaller value?
Interval -> MAX_TIME_AS_PRE_MODEL = 4, it will work with smaller values.
• would manually try/catch and reconnect to the database be a workaround?
I don’t think so as it is a ‘SSL SYSCALL error: EOF detected’ DB connection failure so trying to establish a new connection will end with the same result.
How often does the issue happen, occasionally or always? Occasionally

[lmazuel]

Hi @allouzi

I don't see how your problem is related to the Service Bus SDK? It seems the exception is coming from Django, and I don't even see import in your code that mention SDKs at all.

If you have occasional Django connection issue in Azure WebApp, this repo won't be able to help, you might instead be prefered to open a support ticket to the WebApp team if you feel this has to do with some Azure infra.

If your issue is about SDK, please mention an error that shows a SDK exception so we could make progress and investigation around it. Right now there is not enough for us to help on the SDK side :(.

Thanks,

[aman-quartic]

Hey @allouzi , how did you manage to fix this?