Bug 986219 - Unable to connect to postgresql-9.2 database with python-2.7 app.
Unable to connect to postgresql-9.2 database with python-2.7 app.
Status: CLOSED UPSTREAM
Product: OpenShift Online
Classification: Red Hat
Component: Containers (Show other bugs)
1.x
Unspecified Unspecified
high Severity high
: ---
: ---
Assigned To: Michal Fojtik
libra bugs
: Reopened, SupportQuestion
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-07-19 05:17 EDT by Oleg Fayans
Modified: 2016-11-30 19:27 EST (History)
13 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2014-02-11 13:15:58 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
wsgi/application and wsgi/postgresql_factory.py (4.32 KB, application/zip)
2013-07-19 05:17 EDT, Oleg Fayans
no flags Details

  None (edit)
Description Oleg Fayans 2013-07-19 05:17:21 EDT
Created attachment 775696 [details]
wsgi/application and wsgi/postgresql_factory.py

Description of problem:
I created a python-2.7 app, embedded a postgresql-9.2 cartridge and created a simple script that inserts test data into the database.
The script throws the following error:
"libpq.so.postgresql92-5: cannot open shared object file: No such file or directory" The script, "postgresql_factory" as well as wsgi/application are attached



Version-Release number of selected component (if applicable):


How reproducible:
Always

Steps to Reproduce:
1. rhc app create myapp python-2.7
2. rhc cartridge add postgresql-9.2 -a myapp
3. add attached files, git add . ; git commit & git push
4. access /postgresql url of your app

Actual results:

FILE.postgresql factory is not added to this app
libpq.so.postgresql92-5: cannot open shared object file: No such file or directory

Expected results:

version: 1
Additional info:

reproduced on all devenv instances
Comment 1 Abhishek Gupta 2013-07-23 17:55:23 EDT
Spent some time on this and gave up. Not sure if this is just a matter of specifying the LD_LIBRARY_PATH or not. Mrunal seemed to indicate that we may need to check that the correct version of the .so files are being loaded.
Comment 2 Mrunal Patel 2013-07-23 20:33:20 EDT
ofayans:

Thanks for reporting the issue.

The problem is that the LD_LIBRARY_PATH does not include the path to postgresql-9.2 libraries. Could you try adding rhis line:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/rh/postgresql92/root/usr/lib64/

to your pre start python script to get around it for right now?

We will work on a fix!

- Mrunal
Comment 3 Juan P. Daza P. 2013-08-06 18:53:29 EDT
Hi, I've tried putting this command in ./.openshift/action_hooks/deploy with no good result, where is the "pre start python script".
Comment 4 Juan P. Daza P. 2013-08-06 19:24:46 EDT
After a while the libpq.so error disappears and now this error shows up:

ImportError at /
cannot import name utils

 /var/lib/openshift/520161db5004468578000363/python/virtenv/lib/python2.7/site-packages/Django-1.4-py2.7.egg/django/db/backends/postgresql_psycopg2/base.py in <module> 
  8. from django.db import utils
Comment 5 Xiaoli Tian 2013-08-07 01:02:29 EDT
(In reply to Juan P. Daza P. from comment #3)
> Hi, I've tried putting this command in ./.openshift/action_hooks/deploy with
> no good result, where is the "pre start python script".

You could create one hook scripts named pre_start_python in ./.openshift/action_hooks/ and add the following to the hook script:

 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/rh/postgresql92/root/usr/lib64/
Comment 6 Zhe Wang 2013-08-07 01:40:31 EDT
The bug is fixed with the resolution in Comment #5.

Steps
1) create a python-2.7 app with postgresql-9.2 cartridge added
2) add the files in the attachment to the app's local repo, and enable psycopg2 support in its setup.py file.
3) create an executable hook named pre_start_python as Comment #5
4) push the changes
5) access <app_url>/postgresql

Result:
It showed "version 1" as expected.
Comment 7 Juan P. Daza P. 2013-08-07 04:12:13 EDT
(In reply to Zhe Wang from comment #6)

Can confirm this solutions works.

Thank you.
Comment 8 Steven Citron-Pousty 2014-02-07 14:04:25 EST
Reopening since I am not getting this with Python 2.7 and postgresl 9.2.

This is for the application that is supposed to work with the new book - please consider this an emergency patch.
-------------------

from python/logs/error_log
[Fri Feb 07 13:48:12 2014] [error] [client 127.9.124.1] ImportError: libpq.so.postgresql92-5: cannot open shared object file: No such file or directory
------------------

You have permission to go into the gears and diagnose the error.
account spousty+prod@redhat.com

-------------
	

    insultapp @ http://insultapp-osbeginnerbook.rhcloud.com/ (uuid: 52df5ed54382x)
    -----------------------------------------------------------------------------------------
      Domain:     osbeginnerbook
      Created:    Jan 21 10:01 PM
      Gears:      1 (defaults to small)
      Git URL:    ssh://52df5ed54382x@insultapp-osbeginnerbook.rhcloud.com/~/git/insultapp.git/
      SSH:        52df5ed54382x@insultapp-osbeginnerbook.rhcloud.com
      Deployment: auto (on git push)
     
      python-2.7 (Python 2.7)
      -----------------------
        Gears: Located with postgresql-9.2, cron-1.4
     
      postgresql-9.2 (PostgreSQL 9.2)
      -------------------------------
        Gears:          Located with python-2.7, cron-1.4
        Connection URL: postgresql://$OPENSHIFT_POSTGRESQL_DB_HOST:$OPENSHIFT_POSTGRESQL_DB_PORT
        Database Name:  insultapp
        Password:       x
        Username:       x
     
      cron-1.4 (Cron 1.4)
      -------------------
        Gears: Located with python-2.7, postgresql-9.2
Comment 9 Andy Grimm 2014-02-07 15:14:58 EST
I don't think it's appropriate to close a bug as "currentrelease" (implying that we fixed something) when the solution is really a workaround. Why are we not handling LD_LIBRARY_PATH elements like PATH elements?
Comment 10 Steven Citron-Pousty 2014-02-09 21:05:09 EST
Confirmed on Python 3.3 as well.
Comment 11 Mike McGrath 2014-02-09 21:07:12 EST
This is a production level issue, lets try to get a proper fix and put a hotfix out.  I've dealt with LD_LIBRARY_PATH issues like this in community cartridges:

https://github.com/mmcgrath-openshift/openshift-cartridge-varnish/blob/master/env/LD_LIBRARY_PATH.erb

I know the shifters are running into issues with this now so it would be good to get a proper fix out.
Comment 12 Michal Fojtik 2014-02-10 07:49:15 EST
I'm no longer able to reproduce this on devenv, the app in Oleg's attachment works fine and I got 'version 1'. Also no compilation issues.

Mike: There is a Trello card to multiple cartridges with isolated LD_LIBRARY_PATH (since we went throught this problem for Zend, Jenkins, Mysql...): https://trello.com/c/BB2errnd/31-handle-multiple-catridges-having-their-own-ld-library-path
Comment 14 chunchen 2014-02-11 03:53:14 EST
It's fixed, verified on devenv_4357, please refer to the following results:

1. create a python-2.7 app with postgresql-9.2 cartridge added
2. add the files in the attachment to the app's local repo, and enable psycopg2 support in its setup.py file.
3. push the changes
4. access <app_url>/postgresql

Results:
at step 4: Met "version 1" on the webpage
Comment 15 Dan McPherson 2014-02-11 13:15:58 EST
The actual fix will be a part of:

https://trello.com/c/BB2errnd
Comment 16 Steven Citron-Pousty 2014-02-20 18:17:21 EST
chunchen you actually need to have an application that loads the driver otherwise it won't really test it. 


Here is some example code that throws the error:


from random import choice
import psycopg2

#get a connection to use in the DB calls. Only need a cursor because these connections are read only
def get_cursor():
	conn = psycopg2.connect(database=os.environ['OPENSHIFT_APP_NAME'], user=os.environ['OPENSHIFT_POSTGRESQL_DB_USERNAME'], 
				password=os.environ['OPENSHIFT_POSTGRESQL_DB_PASSWORD'], host=os.environ['OPENSHIFT_POSTGRESQL_DB_HOST'], 
				port=os.environ['OPENSHIFT_POSTGRESQL_DB_PORT'] )
	cursor = conn.cursor()	
	return conn	

#Clean up when done with the cursor and connection	
def close_cursor(cursor):
	conn = cursor.connection
	cursor.close()
	conn.close()

def insult():
    return "Thou " + generate_insult() + "!"

def named_insult(name):
    return name + ", thou " + generate_insult() + "!"

def generate_insult():
    first_adjs = ["artless", "bawdy", "beslubbering", "bootless", "churlish"]
    second_adjs = ["base-court", "bat-fowling", "beef-witted", "beetle-headed", "boil-brained"]
    nouns = ["apple-john", "baggage", "barnacle", "bladder", "boar-pig"]
    return choice(first_adjs) + " " + choice(second_adjs) + " " + choice(nouns)
    
#Expects to be passed a psycopg2 cursor
#Using the solution found on this Stack Overflow page 
#http://stackoverflow.com/questions/5297396/quick-random-row-selection-in-postgres
def get_first_adj(cursor):
	cur.execute("select string from short_adjective offset random() * (select count(*) from short_adjective) limit 1;")
	print "HERE IS THE RESULT of count :: " + str(cur.fetchone()[0])
Comment 17 Steven Citron-Pousty 2014-02-20 18:40:31 EST
One other note for the workaround. If you are using python then the action_hook needs to be name pre_start_python
Comment 18 Michal Fojtik 2014-02-21 05:22:18 EST
Steven, Oleg: The Trello card I'm currently working will allow this without any workarounds or hacks for non-scalable but also for scalable apps. I tested both Oleg's and your code and they both works. Stay tuned! :-)
Comment 19 anish 2014-06-26 05:38:33 EDT
Confirmed bug still exists for Python 2.7  and  PostgreSQL 9.2, however workaround suggested in comment #5 works
Comment 20 Wenjing Zheng 2014-06-27 02:55:16 EDT
Have verified on devenv_4916 with Oleg's and Steven's scripts, both works well, below are the steps using Steven's scripts(please correct if my steps are wrong):

1. Create a python-2.7 with postgresql-9.2
2. In app local repo, edit setup.py: add "psycopg2" to "install_requires"
3. Create a table short_adjective with column "username" and values "openshift" in psql
4. Add below codes to wsgi.py:
from random import choice
import psycopg2
def application(environ, start_response):
....
   elif environ['PATH_INFO'] == '/psql':
            conn_str = "dbname=%s user=%s password=%s host=%s port=%s" % (
                           os.environ['OPENSHIFT_APP_NAME'],
                           os.environ['OPENSHIFT_POSTGRESQL_DB_USERNAME'],
                           os.environ['OPENSHIFT_POSTGRESQL_DB_PASSWORD'],
                           os.environ['OPENSHIFT_POSTGRESQL_DB_HOST'],
                           os.environ['OPENSHIFT_POSTGRESQL_DB_PORT'])
            conn = psycopg2.connect(conn_str)
            cur = conn.cursor()
            cur.execute("select username from short_adjective offset random()")
            response_body =  str(cur.fetchone())
            cur.close()
            conn.close()
5. Git push the changes;
6. Access https://$appurl/psql, and can get the content I input in the table short_adjective successfully.
Comment 21 Wenjing Zheng 2014-06-27 03:03:57 EDT
Below are the LD_LIBRARY_PATH returns in gear(which I didn't add like comment #5):
[myapp-d.dev.rhcloud.com 53ad361405636e0e38000025]\> get_gear_ld_library_path
/opt/rh/postgresql92/root/usr/lib64:/opt/rh/python27/root/usr/lib64

[myapp-d.dev.rhcloud.com 53ad361405636e0e38000025]\> env | grep LD_LIBRARY_PATH
OPENSHIFT_PYTHON_LD_LIBRARY_PATH_ELEMENT=/opt/rh/python27/root/usr/lib64
LD_LIBRARY_PATH=/opt/rh/postgresql92/root/usr/lib64:/opt/rh/python27/root/usr/lib64
OPENSHIFT_POSTGRESQL_LD_LIBRARY_PATH_ELEMENT=/opt/rh/postgresql92/root/usr/lib64

Note You need to log in before you can comment on or make changes to this bug.