psycopg2.OperationalError: SSL SYSCALL error: EOF detected

Question

I was using psycopg2 in python script to connect to Redshift database and occasionally I receive the error as below:

psycopg2.OperationalError: SSL SYSCALL error: EOF detected

This error only happened once awhile and 90% of the time the script worked.

I tried to put it into a try and except block to catch the error, but it seems like the catching didn't work. For example, I try to capture the error so that it will automatically send me an email if this happens. However, the email was not sent when error happened. Below are my code for try except:

try:
    conn2 = psycopg2.connect(host="localhost", port = '5439', 
    database="testing", user="admin", password="admin")

except psycopg2.Error as e:
    print ("Unable to connect!")
    print (e.pgerror)
    print (e.diag.message_detail)

    # Call check_row_count function to check today's number of rows and send 
      mail to notify issue
    print("Trigger send mail now")
    import status_mail
    print (status_mail.redshift_failed(YtdDate))

    sys.exit(1)
else:
    print("RedShift Database Connected")
    cur2 = conn2.cursor()
    rowcount = cur2.rowcount

Errors I received in my log:

Traceback (most recent call last): File "/home/ec2-user/dradis/dradisetl-daily.py", line 579, in load_from_redshift_to_s3() File "/home/ec2-user/dradis/dradisetl-daily.py", line 106, in load_from_redshift_to_s3 delimiter as ','; """.format(YtdDate, s3location)) psycopg2.OperationalError: SSL SYSCALL error: EOF detected

So the question is, what causes this error and why isn't my try except block catching it?

Answer 1

From the docs:

exception psycopg2.OperationalError

Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc.

This is an error which can be a result of many different things.

• slow query
• the process is running out of memory
• other queries running causing tables to be locked indefinitely
• running out of disk space
• firewall

(You should definitely provide more information about these factors and more code.)

You were connected successfully but the OperationalError happened later. Try to handle these disconnects in your script: Put the command you want to execute into a try-catch block and try to reconnect if the connection is lost.