Re: "connection already closed", how to re-establish connection?

[Charles Leifer]

I don't know about the Bottle-Peewee plugin, but the problem is most likely due to your application not properly managing connections.

Here are instructions for setting up connection hooks with bottle:

http://docs.peewee-orm.com/en/latest/peewee/database.html#bottle

On Tue, May 19, 2015 at 6:20 PM, Alec Taylor <alec.t...@gmail.com> wrote:

I am using Bottle with the bottle-peewee plugin. In my root
__init__.py I initialise my `db` variable to:
`PeeweePlugin(environ['RDBMS_URI'])`.

Peewee-SQL is giving me a "connection already closed" message when:

0) bottle is started
1) database server is stopped
2) model endpoint hit
3) error thrown
4) database server started
5) model endpoint hit
6) "connection already closed" error thrown

Currently I can catch the `peewee.InterfaceError` exception, and once
I'm there I try running:

    global db
    db = PeeweePlugin(environ['RDBMS_URI'])

But that doesn't remove the error.

Is there some trick to re-establishing the connection to Postgres, or
otherwise reinitialising the application?

Thanks for all suggestions

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alec Taylor]

[Alec Taylor]

Thanks for the suggestions.

No longer using that plugin, now just using peewee directly (as recommended).

However the same problem is still occurring.

When I kill the database I get a:
peewee.OperationalError: could not connect to server: Connection refused

Bringing the database back up gives me:
peewee.OperationalError: terminating connection due to administrator
command\nSSL connection has been closed unexpectedly\n

And hitting that endpoint again gives me:
peewee.InterfaceError: connection already closed

How do I fix this issue?

[Alec Taylor]

Full test case: http://stackoverflow.com/q/30338444

[Charles Leifer]

I answered your SO question: http://stackoverflow.com/a/30338700/254346

But tl/dr is that I was not able to replicate that error.

Postgresql 9.4, psycopg2 2.6, python 2.7.9, peewee 2.6.0

[Alec Taylor]

It's very strange, but I can't replicate it either on the test-case,
even when I've made it in a similar package structure:
.
└── test_case
├── apis
│ ├── foo.py
│ └── __init__.py
├── __init__.py
└── models
├── foo.py
└── __init__.py

Will continue investigating.

[Alec Taylor]

Okay, kept extending the test-case until I found the error.
StackOverflow post updated with the new test-case.

The trick is to have a file called `__main__.py` from where you call
the `.run` function to start the bottle API.

Thanks for your continued help.

[Charles Leifer]

I was still unable to replicate the errors. Your code didn't quite work when I tried it, specifically in `__main__.py` I replace "from __init__ import bottle_api" with "from test_case import bottle_api".

[Alec Taylor]

Works for me. Here's how I run it when I'm not using an IDE:


test_case$ tree
.
└── test_case
    ├── foo
    │   ├── __init__.py
    │   ├── models.py
    │   └── routes.py
    ├── __init__.py
    └── __main__.py

2 directories, 5 files


$ python -m test_case
Bottle v0.12.8 server starting up (using WSGIRefServer())...
Listening on http://127.0.0.1:5555/
Hit Ctrl-C to quit.


Errors (I am testing with http from httpie, install with pip install httpie)

Tests

$ http :5555/api/foo
HTTP/1.0 200 OK
Content-Length: 12
Content-Type: application/json
Date: Wed, 20 May 2015 02:40:36 GMT
Server: WSGIServer/0.1 Python/2.7.8

{
    "foos": []
}

$ sudo /etc/init.d/postgresql stop
[sudo] password for <username>:
 * Stopping PostgreSQL 9.4 database server                               [ OK ]
$ http :5555/api/foo
HTTP/1.0 500 Internal Server Error
Content-Length: 59
Content-Type: text/plain
Date: Wed, 20 May 2015 02:40:47 GMT
Server: WSGIServer/0.1 Python/2.7.8

A server error occurred.  Please contact the administrator.

$ sudo /etc/init.d/postgresql start
 * Starting PostgreSQL 9.4 database server                               [ OK ]
$ http :5555/api/foo
HTTP/1.0 500 Internal Server Error
Content-Length: 59
Content-Type: text/plain
Date: Wed, 20 May 2015 02:40:55 GMT
Server: WSGIServer/0.1 Python/2.7.8

A server error occurred.  Please contact the administrator.

Server output

$ python -m test_case
Bottle v0.12.8 server starting up (using WSGIRefServer())...
Listening on http://127.0.0.1:5555/
Hit Ctrl-C to quit.

Connecting to db
Closing db
127.0.0.1 - - [20/May/2015 12:40:36] "GET /api/foo HTTP/1.1" 200 12
Connecting to db
Closing db
Traceback (most recent call last):
  File "/usr/lib/python2.7/wsgiref/handlers.py", line 85, in run
    self.result = application(self.environ, self.start_response)
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 979, in __call__
    return self.wsgi(environ, start_response)
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 954, in wsgi
    out = self._cast(self._handle(environ))
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 857, in _handle
    self.trigger_hook('before_request')
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 640, in trigger_hook
    return [hook(*args, **kwargs) for hook in self._hooks[__name][:]]
  File "test_case/__init__.py", line 26, in _connect_db
    db.connect()
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2946, in connect
    self.initialize_connection(self.__local.conn)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2864, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2944, in connect
    **self.connect_kwargs)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 3256, in _connect
    conn = psycopg2.connect(database=database, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/psycopg2/__init__.py", line 168, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)

OperationalError: could not connect to server: Connection refused

    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

127.0.0.1 - - [20/May/2015 12:40:47] "GET /api/foo HTTP/1.1" 500 59
Connecting to db
Closing db
Traceback (most recent call last):
  File "/usr/lib/python2.7/wsgiref/handlers.py", line 85, in run
    self.result = application(self.environ, self.start_response)
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 979, in __call__
    return self.wsgi(environ, start_response)
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 954, in wsgi
    out = self._cast(self._handle(environ))
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 862, in _handle
    return route.call(**args)
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 1732, in wrapper
    rv = callback(*a, **ka)
  File "test_case/foo/routes.py", line 11, in retrieve_foos
    return {'foos': tuple(model_to_dict(foo) for foo in Foo.select())}
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2643, in __iter__
    return iter(self.execute())
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2636, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2325, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 3015, in execute_sql
    self.commit()
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2864, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 3007, in execute_sql
    cursor.execute(sql, params or ())

OperationalError: terminating connection due to administrator command

SSL connection has been closed unexpectedly

127.0.0.1 - - [20/May/2015 12:40:55] "GET /api/foo HTTP/1.1" 500 59
Connecting to db
Closing db
Traceback (most recent call last):
  File "/usr/lib/python2.7/wsgiref/handlers.py", line 85, in run
    self.result = application(self.environ, self.start_response)
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 979, in __call__
    return self.wsgi(environ, start_response)
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 954, in wsgi
    out = self._cast(self._handle(environ))
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 862, in _handle
    return route.call(**args)
  File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 1732, in wrapper
    rv = callback(*a, **ka)
  File "test_case/foo/routes.py", line 11, in retrieve_foos
    return {'foos': tuple(model_to_dict(foo) for foo in Foo.select())}
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2643, in __iter__
    return iter(self.execute())
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2636, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2325, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 3015, in execute_sql
    self.commit()
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2864, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 3005, in execute_sql
    cursor = self.get_cursor()
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2971, in get_cursor
    return self.get_conn().cursor()
InterfaceError: connection already closed
127.0.0.1 - - [20/May/2015 12:41:06] "GET /api/foo HTTP/1.1" 500 59

[Charles Leifer]

I believe the problem lies in how you've structured your imports and the way python loads and caches modules in sys.path.

I think that one of your modules is being imported and loaded twice and different parts of the codebase use different instances of the module.

Thus, the views in foo.routes, are using one instance of the database object, while the connection hooks are using another.

[Alec Taylor]

Okay, so who's problem is it, Peewee's or Bottle's?

Yes my structure is quite different, but I find it helps to modularise. Is there a different way of importing things around which would still allow me to keep my directory structure?

Thanks for all suggestions

[Charles Leifer]

Dude, it's your code that's wrong. Did you try changing the import statement in __main__.py like I suggested?

It should read:

#!/usr/bin/env python

from test_case import bottle_api  # Not "from __init__"

bottle_api.run(port=5555)

[Alec Taylor]

Sweet that worked