Postgres SSL SYSCALL error: EOF detected with python and psycopg

psql: ssl syscall error: eof detected azure
ssl syscall error: connection timed out
peewee operationalerror ssl syscall error eof detected
psycopg2.operationalerror: ssl error: decryption failed or bad record mac
postgresql eof
ssl syscall error: connection reset by peer
psycopg2 operationalerror ssl syscall error: software caused connection abort (0x00002745/10053)
psycopg2.interfaceerror: connection already closed

Using psycopg2 package with python 2.7 I keep getting the titled error: psycopg2.DatabaseError: SSL SYSCALL error: EOF detected

It only occurs when I add a WHERE column LIKE ''%X%'' clause to my pgrouting query. An example:

SELECT id1 as node, cost FROM PGR_Driving_Distance(
  'SELECT id, source, target, cost 
     FROM edge_table
     WHERE cost IS NOT NULL and column LIKE ''%x%'' ',
  1, 10, false, false)

Threads on the internet suggest it is an issue with SSL intuitively, but whenever I comment out the pattern matching side of things the query and connection to the database works fine.

This is on a local database running Xubuntu 13.10.

After further investigation: It looks like this may be cause by the pgrouting extension crashing the database because it is a bad query and their are not links which have this pattern.

Will post an answer soon ...

I ran into this problem when running a slow query in a Droplet on a Digital Ocean instance. All other SQL would run fine and it worked on my laptop. After scaling up to a 1 GB RAM instance instead of 512 MB it works fine so it seems that this error could occur if the process is running out of memory.

DatabaseError: SSL SYSCALL error: EOF detected · Issue #533 , Psycopg is just reporting you are being disconnected as far as I can see. I think you should ask around to the postgres mailing lists (e.g. pgsql-  Psycopg is just reporting you are being disconnected as far as I can see. I think you should ask around to the postgres mailing lists (e.g. pgsql-general) to understand the cause of disconnection. I think you should ask around to the postgres mailing lists (e.g. pgsql-general) to understand the cause of disconnection.

This issue occurred for me when I had some rogue queries running causing tables to be locked indefinitely. I was able to see the queries by running:

SELECT * from STV_RECENTS where status='Running' order by starttime desc;

then kill them with:

SELECT pg_terminate_backend(<pid>);

psycopg2.OperationalError SSL SYSCALL error: EOF detected #151, psycopg2.OperationalError SSL SYSCALL error: EOF detected #151 I imagine something wrong with the default SSL config for postgres on  This seems to be an OOM exception, thus leads to db disconnection. OperationalError: (psycopg2.OperationalError) SSL SYSCALL error: EOF detected Retrying with exponential backoff could help.

You may need to express % as %% because % is the placeholder marker. http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

PostgreSQL: SSL SYSCALL error: EOF detected, My guess is that you're running PostgreSQL on AWS. If so, you need to identify this in the question and the tags. The last process mentioned in  "connection already closed" (which doesn't look like a PostgreSQL error, presumably it is from your framework) sounds like you were done with the connection anyway, or tried to close it twice. As long as the commit got through before it was closed, there might not be a problem. – jjanes Oct 31 at 12:38

Very similar answer to what @FoxMulder900 did, except I could not get his first select to work. This works, though:

WITH long_running AS (
    SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
    FROM pg_stat_activity
    WHERE (now() - pg_stat_activity.query_start) > interval '1 minutes'
      and state = 'active'
)
SELECT * from long_running;

If you want to kill the processes from long_running just comment out the last line and insert SELECT pg_cancel_backend(long_running.pid) from long_running ;

OperationalError: SSL SYSCALL error: EOF detected - ner, OperationalError: SSL SYSCALL error: EOF detected prodigy python -m prodigy ner.make-gold test_db en_core_web_lg Could there be a problem in the way you setup the connection with PostgreSQL using psycopg2 ? python - software - ssl syscall error: eof detected redshift Postgres SSL SYSCALL error: EOF detected with python and psycopg (3) Using psycopg2 package with python 2.7 I keep getting the titled error: psycopg2.DatabaseError: SSL SYSCALL error: EOF detected

In my case that was OOM killer (query is too heavy)

Check dmesg:

dmesg | grep -A2 Kill

In my case:

Out of memory: Kill process 28715 (postgres) score 150 or sacrifice child

OperationalError: (psycopg2.OperationalError) SSL SYSCALL error , Sporadic "SSL SYSCALL error: EOF detected" against Postgres RDS error: EOF detected" errors from our Python code (psycopg2 library). OK, so I have the same problem locally with my system postgresql, while with one installed by conda it was working fine. Due to different defaults for ssl (disabled on the conda one), and after commenting out as you mentioned it also works for the system postgres.

Sporadic "SSL SYSCALL error: EOF, <class 'psycopg2.DatabaseError'> SSL SYSCALL error: EOF detected Python, Python 2.7.3: /usr/bin/python (prefix: /usr) https://devcenter.heroku.com/articles/​postgres-logs-errors#pgerror-ssl-syscall-error-eof-detected,  Hi, I have been trying to chase down a bug I am experiencing: sometimes the connection to the database would go away, and neither psycopg2 nor sqlalchemy detect the condition, and thus, the connection is not removed from the connection p

DatabaseError: SSL SYSCALL error: EOF detected (Intermitten , Hi all, i have a problem while trying to delete rows from a table: I received: SSL SYSCALL error: EOF detected. How can i fix this problem? SSL SYSCALL error: EOF detected. How can i fix this problem?-Postgresql version 9.1-O.S Ubuntu Server 11.04. Thanks. Francesco. Responses. Re: SSL SYSCALL error: EOF detected at 2012-07-19 15:01:23 from Tom Lane Browse pgsql-admin by date From Date Subje

SSL SYSCALL error: EOF detected, Tagged with help, postgres. SQLSTATE[HY000]: General error: 7 SSL SYSCALL error: EOF detected (SQL: .com/questions/259025/does-the-hot-​standby-feedback-on-may-cause-ssl-syscall-error-eof-detected-e. (Yeap  SSL SYSCALL error: EOF detected connection to server was lost Regarding the possible memory issue, I don't know how to troubleshoot it. The data structure is the one described in this question and, as you may found this would a very long running query to complete achieve the full alter table over all the inherited tables.

Comments
  • Why the subquery? makes no sense to ne.
  • The subquery is for the PGR_DrivingDistance function.
  • famous last words: Will post an answer soon ...
  • Sometimes SO did, make me laugh :D
  • @PhilDonovan don't leave us hanging!
  • it appears that isn't always the fix- I'm using a machine with 160gb ram and still having this error while using pg_dump on a SSL-only database. only 15gb is in use.
  • Well this might work, but it doesn't sound like a real solution. There should be a way to optimise this somehow
  • Exactly what I've run into! Added 4GB of swap space to a 512MB instance and everything worked as a charm.
  • My db has 16GB RAM dedicated, no swap of system is getting used but still running into this issue.. Does only happen in a small amount of queries... Weird.
  • @gies0r This problem probably has more causes than memory issues, even though I wouldn't completely rule it out.
  • To the uninitiated it's not really clear what you are saying. Please provide some explanation on what dmesg is and why you are running it.
  • this could be useful, dmesg is just where a lot of the linux kernel errors ends, usually these means driver's messages (e.g. I have been in dmesg looking for how to fix my wifi a lot of times). When Linux (and OSs in general) runs out of memory (and swap), then the kernel picks one of the current process and kills it in order to reclaim the memory. Note that at that point the OS has two options: kill one process or freeze forever.