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?

Deathbed

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.

Decoy

In my case, I was using a direct PostgreSQL connection to get some data from an Odoo controller. The solution was to use the framework-provided path to get the data. For example: http.request.env['res.partner'].search([...])) instead of querys. – Roadability

Answer 2

If you attach the actual code that you are trying to except it would be helpful. In your attached stack trace its : " File "/home/ec2-user/dradis/dradisetl-daily.py", line 106" Similar except code works fine for me. mind you, e.pgerror will be empty if the error occurred on the client side, such as the error in my example. the e.diag object will also be useless in this case.

try:
    conn = psycopg2.connect('')
except psycopg2.Error  as e:
    print('Unable to connect!\n{0}'.format(e))
else:
    print('Connected!')

Tin

Hi, can i know how do you stimulate this error? psycopg2.OperationalError: SSL SYSCALL error: EOF detected I tried stimulating it but it wont return the same error – Deathbed

Like stated in my answer - if you attach the code that's causing this error it would be helpful. Also, please provide the versions of Python and psycopg2 that are used as results may differ between versions – Tin

I don't know why Elle got a -1. But I do know how to cause this. Have a user log in with postgres password utility, and then user abandons web page. After a while, connection is lost to DB and this error message is just a symptom of the disconnection. I don't think there is a particular command that causes it for us. It is inattentive user. – Hatchment

Answer 3

Recently encountered this error. The cause in my case was the network instability while working with database. If network will became down for enough time that the socket detect the timeout you will see this error. If down time is not so long you wont see any errors.

You may control timeouts of Keepalive and RTO features using this code sample

s = socket.fromfd(conn.fileno(), socket.AF_INET, socket.SOCK_STREAM)
s.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPIDLE, 6)
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPINTVL, 2)
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPCNT, 2)
s.setsockopt(socket.IPPROTO_TCP, socket.TCP_USER_TIMEOUT, 10000)

More information you can find in this post

Sterol

Answer 4

Maybe it will be helpful for someone but I had such an error when I've tried to restore backup to the database which has not sufficient space for it.

Tristis