• 4

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191


File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

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.

  • 13
Reply Report
    • 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.
      • 2
    • Well this might work, but it doesn't sound like a real solution. There should be a way to optimise this somehow
      • 1
    • 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.
      • 1
    • @gies0r This problem probably has more causes than memory issues, even though I wouldn't completely rule it out.

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>);
  • 7
Reply Report

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
  • 4
Reply Report
    • 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.
      • 1
    • 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.

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 ;

  • 2
Reply Report

Trending Tags