Kotti, uwsgi, gunicorn and PostgreSQL

[david.c...@gmail.com]

Hi all,

I have installed Kotti and I can't make it run properly when using more than 1 uwsgi process and PostgreSQL with SSL enabled.
To test Kotti I launch the server, visit 127.0.0.1 and refresh the web page several times until I hit the error.
a) PostgreSQL (ssl = true)  and  >1 uwsgi process : Fail
sqlalchemy.exc.OperationalError: (OperationalError) SSL SYSCALL error: EOF detected
 'SELECT nodes.id AS nodes_id, nodes.type AS nodes_type, nodes.parent_id AS nodes_parent_id, nodes.position AS nodes_position, nodes._acl AS nodes__acl, nodes.name AS nodes_name, nodes.title AS nodes_title, nodes.annotations AS nodes_annotations, contents.id AS contents_id, contents.default_view AS contents_default_view, contents.description AS contents_description, contents.language AS contents_language, contents.owner AS contents_owner, contents.state AS contents_state, contents.creation_date AS contents_creation_date, contents.modification_date AS contents_modification_date, contents.in_navigation AS contents_in_navigation, files.id AS files_id, files.filename AS files_filename, files.mimetype AS files_mimetype, files.size AS files_size, documents.id AS documents_id, documents.body AS documents_body, documents.mime_type AS documents_mime_type, images.id AS images_id \nFROM nodes LEFT OUTER JOIN contents ON nodes.id = contents.id LEFT OUTER JOIN files ON contents.id = files.id LEFT OUTER JOIN documents ON contents.id = documents.id LEFT OUTER JOIN images ON files.id = images.id \nWHERE nodes.parent_id IS NULL' {}
b) PostgreSQL (ssl = true)  and  1 uwsgi process : Ok
c) PostgreSQL (ssl = false)  and  >1 uwsgi process : Ok
d) PostgreSQL (ssl = false)  and  1 uwsgi process : Ok


As this might not be enough, I have tried with one of our applications (that does some database queries)
and I get the following:

Using uWSGI:

a) PostgreSQL (ssl = true)  and  >1 uwsgi process : Fail
sqlalchemy.exc.OperationalError: (OperationalError) SSL SYSCALL error: EOF detected
or
OperationalError: (OperationalError) SSL error: decryption failed or bad record mac
b) PostrgeSQL (ssl = true)  and  1 uwsgi process : Ok
c) PostgreSQL (ssl = false)  and  >1 uwsgi process : Fail
sqlalchemy.exc.DatabaseError: (DatabaseError) error with no message from the libpq
d) PostgreSQL (ssl = false)  and  1 uwsgi process : Ok

Using gunicorn:

e) PostgreSQL (ssl = true)  and  >1 gunicorn worker : Fail. Well, it doesn't even start. It kills and creates workers
OperationalError: (OperationalError) SSL SYSCALL error: EOF detected
f) PostgreSQL (ssl = true)  and  1 gunicorn worker : Ok
g) PostgreSQL (ssl = false)  and  >1 gunicorn worker : Fail
DatabaseError: (DatabaseError) error with no message from the libpq
h) PostgreSQL (ssl = false)  and  1 gunicorn worker : Ok

Using sqlite, neither kotti nor my application fail.
No SSL is used, I only visit http://127.0.0.1 in both applications.

So, it seems that the application fails when more than 1 uwsgi/gunicorn process is being used and Kotti fails when
more than 1 uwsgi process is being used and PostgreSQL has ssl enabled. But I don't think PostgreSQL is the only
suspect here because if it was the queries would fail always.
I think that either Zope or SQLAlchemy could be the problem, but I don't know how to continue.

Have you had any experiences with several uwsgi workers, zope, sqlalchemy and PostgreSQL?
Is this a known bug?
I haven't found much information, so any ideas might help.

Versions:
Ubuntu: 12.10
nginx: 1.2.1
uwsgi: 1.4.5
gunicorn: 0.17.2
PostgreSQL: 9.1

pyramid==1.4
SQLAlchemy==0.8.0
zope.sqlalchemy==0.7.2

Thank you very much.

[Andreas Kaiser]

Hi David,

On 26.03.2013, at 18:11, david.c...@gmail.com wrote:

> I have installed Kotti and I can't make it run properly when using more than 1 uwsgi process and PostgreSQL with SSL enabled.
> To test Kotti I launch the server, visit 127.0.0.1 and refresh the web page several times until I hit the error.
> a) PostgreSQL (ssl = true) and >1 uwsgi process : Fail
> sqlalchemy.exc.OperationalError: (OperationalError) SSL SYSCALL error: EOF detected
> 'SELECT nodes.id AS nodes_id, nodes.type AS nodes_type, nodes.parent_id AS nodes_parent_id, nodes.position AS nodes_position, nodes._acl AS nodes__acl, nodes.name AS nodes_name, nodes.title AS nodes_title, nodes.annotations AS nodes_annotations, contents.id AS contents_id, contents.default_view AS contents_default_view, contents.description AS contents_description, contents.language AS contents_language, contents.owner AS contents_owner, contents.state AS contents_state, contents.creation_date AS contents_creation_date, contents.modification_date AS contents_modification_date, contents.in_navigation AS contents_in_navigation, files.id AS files_id, files.filename AS files_filename, files.mimetype AS files_mimetype, files.size AS files_size, documents.id AS documents_id, documents.body AS documents_body, documents.mime_type AS documents_mime_type, images.id AS images_id \nFROM nodes LEFT OUTER JOIN contents ON nodes.id = contents.id LEFT OUTER JOIN files ON contents.id = files.id LEFT OUTER JOIN documents ON contents.id = documents.id LEFT OUTER JOIN images ON files.id = images.id \nWHERE nodes.parent_id IS NULL' {}
> b) PostgreSQL (ssl = true) and 1 uwsgi process : Ok
> c) PostgreSQL (ssl = false) and >1 uwsgi process : Ok
> d) PostgreSQL (ssl = false) and 1 uwsgi process : Ok
>
> As this might not be enough, I have tried with one of our applications (that does some database queries)
> and I get the following:
>
> Using uWSGI:
>
> a) PostgreSQL (ssl = true) and >1 uwsgi process : Fail
> sqlalchemy.exc.OperationalError: (OperationalError) SSL SYSCALL error: EOF detected
> or
> OperationalError: (OperationalError) SSL error: decryption failed or bad record mac
> b) PostrgeSQL (ssl = true) and 1 uwsgi process : Ok
> c) PostgreSQL (ssl = false) and >1 uwsgi process : Fail
> sqlalchemy.exc.DatabaseError: (DatabaseError) error with no message from the libpq
> d) PostgreSQL (ssl = false) and 1 uwsgi process : Ok

I've been seeing a similar error recently. What I got was a SQLAlchemy
Exception, something like "Query didn't return a result" (don't have the
exact error message right now), which could be equivalent to what you
describe under c).

Interestingly there doesn't seem to be any pattern when the error
occurs. Any number of requests can be successful before that error
shows up. This doesn't seem to relate to high load either, as I was
able to reproduce the error by just reloading a single page a few times
with no other requests in parallel.

> Using gunicorn:
>
> e) PostgreSQL (ssl = true) and >1 gunicorn worker : Fail. Well, it doesn't even start. It kills and creates workers
> OperationalError: (OperationalError) SSL SYSCALL error: EOF detected
> f) PostgreSQL (ssl = true) and 1 gunicorn worker : Ok
> g) PostgreSQL (ssl = false) and >1 gunicorn worker : Fail
> DatabaseError: (DatabaseError) error with no message from the libpq
> h) PostgreSQL (ssl = false) and 1 gunicorn worker : Ok

I can't say anything about gunicorn, as I haven't tested it.

> Using sqlite, neither kotti nor my application fail.

I can confirm that too, as I had been running the exact same application
with SQLite before and also had no errors with that.

> No SSL is used, I only visit http://127.0.0.1 in both applications.

I guess with "No SSL" you mean plain HTTP, which shouldn't make any
difference as HTTPS decryption is handled by the webserver (in my case
Nginx, too).

> So, it seems that the application fails when more than 1 uwsgi/gunicorn process is being used and Kotti fails when
> more than 1 uwsgi process is being used and PostgreSQL has ssl enabled. But I don't think PostgreSQL is the only
> suspect here because if it was the queries would fail always.
> I think that either Zope or SQLAlchemy could be the problem, but I don't know how to continue.
>
> Have you had any experiences with several uwsgi workers, zope, sqlalchemy and PostgreSQL?
> Is this a known bug?

I suppose now it is… ;(

> I haven't found much information, so any ideas might help.

First I thought the errors were caused by SSL connections to PostgreSQL,
as the error messages looked so. After having (different) errors
without SSL too, I think it's not the SSL connections themselves but
something else (which just manifests in different errors with / without
SSL). As you get very similar results with both uWSGI and gunicorn, I
don't think it's the WSGI server either.

OTOH I don't see where SQLAlchemy or zope.sqlalchemy would be
the culprit here.

> Versions:
> Ubuntu: 12.10
> nginx: 1.2.1
> uwsgi: 1.4.5
> gunicorn: 0.17.2
> PostgreSQL: 9.1

Same on the versions shipped with Ubuntu 12.04.

> pyramid==1.4
> SQLAlchemy==0.8.0
> zope.sqlalchemy==0.7.2

Same versions here, except SQLAlchemy which hadn't released 0.8
when I encountered this error.

I'm afraid, I cannot help with the issue itself ATM, but another person
having the same problem is at least motivation enough for me to dig
deeper into it. First of all I'll try to setup the same components on a
different OS to see if the problem exists there too, or if it is a bug
specific to Ubuntu or the libraries it ships.


I'll keep you updated in a followup to this thread.


Andreas

[david.c...@gmail.com]

It needs a little bit more of testing but I think the uwsgi's --lazy option fixes the issue.

###
# wsgi server configuration
###

[uwsgi]
master = true
processes = 4
socket = /blah/blah/uwsgi.sock
virtualenv = /blah/blah/virtualenv/

_$: uwsgi --uid your-user --gid your-group --ini-paste app-uwsgi.ini --lazy

[Andreas Kaiser]

A quick test indicates that this does solve the problem in my
environment, too!

On 10.04.2013, at 09:44, david.c...@gmail.com wrote:

> It needs a little bit more of testing but I think the uwsgi's --lazy option fixes the issue.

Note, that you can also have the lazy switch in your .ini file:

> [uwsgi]
> master = true
> processes = 4
> socket = /blah/blah/uwsgi.sock
> virtualenv = /blah/blah/virtualenv/

lazy = true

> _$: uwsgi --uid your-user --gid your-group --ini-paste app-uwsgi.ini --lazy

That's of course just a matter of taste, but I like having as much as
possible in the ini file(s) which makes the command line call simpler.

Thanks for your investigation!


Andreas

[david.c...@gmail.com]

Yes, this is definitely not the final version of the ini files, but while testing it was easier to change
the command line than the file. Anyways, thanks for pointing it.

Glad it worked for you too!