CODEWITHSUNDEEP
pythonamazon-redshiftpsycopg2
Derek Lee
Derek Lee

Reputation: 505

psycopg2.OperationalError: SSL SYSCALL error: EOF detected

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?

Upvotes: 13

Views: 29339

Answers (4)

BatCat
BatCat

Reputation: 671

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.

Upvotes: 17

Flex Chan
Flex Chan

Reputation: 59

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.

Upvotes: 0

Nick
Nick

Reputation: 163

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

Upvotes: 0

Elle
Elle

Reputation: 157

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!')

Upvotes: 0

Related Questions