Scheduler error using python3.6 and psycopg2

[Bart]

For a new application, i have installed web2py 2.16.1 under python 3.6 and using psycopg2 2.7.4 for postgresql access.

Everything is working okay, except the web2py scheduler.

After successfull execution of the first task, the scheduler gives an error in the send_heartbeat function.

The task output is correctly stored in the database, with status COMPLETED. The transaction is committed.

But then in the first heartbeat after completion, the  error below is thrown:

Looks like the database connection is lost unexpected.

The strange thing is that this is only happening with psycopg2. When the PG8000 connector is used, everything is working fine.

I have tested this on my development system (MacBook)

On my production system under Ubuntu 16.04, the same thing is happening. 

Does anyone have a clue?

The error:

ERROR:web2py.scheduler.MyMac.local#21060:Error retrieving status

Exception in thread Thread-30:

Traceback (most recent call last):

  File "/Users/me/PycharmProjects/myapp/web2py/gluon/scheduler.py", line 1168, in send_heartbeat

    mybackedstatus = db(sw.worker_name == self.worker_name).select().first()

  File "/Users/me/PycharmProjects/myapp/venv/lib/python3.6/site-packages/pydal/objects.py", line 2250, in select

    return adapter.select(self.query, fields, attributes)

  File "/Users/me/PycharmProjects/myapp/venv/lib/python3.6/site-packages/pydal/adapters/base.py", line 762, in select

    return self._select_aux(sql, fields, attributes, colnames)

  File "/Users/me/PycharmProjects/myapp/venv/lib/python3.6/site-packages/pydal/adapters/base.py", line 718, in _select_aux

    rows = self._select_aux_execute(sql)

  File "/Users/me/PycharmProjects/myapp/venv/lib/python3.6/site-packages/pydal/adapters/base.py", line 712, in _select_aux_execute

    self.execute(sql)

  File "/Users/me/PycharmProjects/myapp/venv/lib/python3.6/site-packages/pydal/adapters/__init__.py", line 67, in wrap

    return f(*args, **kwargs)

  File "/Users/me/PycharmProjects/myapp/venv/lib/python3.6/site-packages/pydal/adapters/base.py", line 412, in execute

    rv = self.cursor.execute(command, *args[1:], **kwargs)

psycopg2.OperationalError: server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/usr/local/Cellar/python3/3.6.4_2/Frameworks/Python.framework/Versions/3.6/lib/python3.6/threading.py", line 916, in _bootstrap_inner

    self.run()

  File "/Users/me/PycharmProjects/myapp/web2py/gluon/scheduler.py", line 637, in run

    self.send_heartbeat(counter)

  File "/Users/me/PycharmProjects/myapp/web2py/gluon/scheduler.py", line 1239, in send_heartbeat

    db.rollback()

  File "/Users/me/PycharmProjects/myapp/venv/lib/python3.6/site-packages/pydal/base.py", line 704, in rollback

    self._adapter.rollback()

  File "/Users/me/PycharmProjects/myapp/venv/lib/python3.6/site-packages/pydal/adapters/__init__.py", line 56, in wrap

    return f(*args, **kwargs)

  File "/Users/me/PycharmProjects/myapp/venv/lib/python3.6/site-packages/pydal/adapters/base.py", line 865, in rollback

    return self.connection.rollback()

psycopg2.InterfaceError: connection already closed

[Bart]

I have found a workaround for this issue.

The root cause is probably something in the pyDal connector or psycopg2 or threading. I leave the finding of that to the experts.

The workaround is to add code to gluon/scheduler.py to test the connection before the rest of the Scheduler.send_heartbeat() function is executed.

If the connection-test fails, a new one is set up:

Insert this code at the top of the Scheduler.send_heartbeat() function

if self.db_thread:
    # BKR 20180612 check if connection still works, if not -> throw away self.db_thread and force reconnect
    try:
        x = self.db_thread(self.db_thread.scheduler_worker.worker_name == self.worker_name).select()
    except self.db_thread._adapter.connection.OperationalError:
        self.db_thread = None

With this addition, the scheduler works okay.

Op woensdag 6 juni 2018 21:26:40 UTC+2 schreef Bart:

[Massimo Di Pierro]

Good catch. Will implement this.

[Antonio Salazar]

I separately had the same problem recently and created a github issue. If this got implemented please close that issue.