Dealing with DB disconnections

[rcoup]

We use this backed onto a Multi-AZ PostgreSQL on RDS. When the DB fails over during updates/etc, errbot dies.

(psycopg2.OperationalError) SSL connection has been closed unexpectedly
[SQL: 'SELECT core.key AS core_key, core.value AS core_value \nFROM core \nWHERE core.key = %(key_1)s'] [parameters: {'key_1': 'bl_plugins'}] (Background on this error at: http://sqlalche.me/e/e3q8)

Which is ok, there's one error when the PG instance is switched. But it never reconnects, and every command produces output like:

(sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: 'SELECT x.key AS x_key, x.value AS x_value \nFROM x \nWHERE x.key = %(key_1)s'] [parameters: [{}]]

As far as I can tell, every method in SQLStorage that access the DB should be wrapped in a session transaction (not just the methods that write to the DB), as discussed in the SQLAlchemy docs. No idea why @session_scope isn't part of SQLAlchemy, but implementing that would work I think?

Might also be worth adding pool_size=1, pool_recycle=300 or something to the default engine too as a bit of added resiliency.

[oz-linden]

Has anyone looked into either fixing this or some workaround that prevents it?
It's hitting my bot pretty hard.

[oz-linden]

I've got an experimental patch for this at
https://github.com/lindenlab/err-storage-sql/tree/oz_6_mysql_disconn

I've incorporated it into my bot; it runs in AWS and was hitting this about once a week, so it'll be a couple of weeks before I can be sure that the fix is good, but of course comments in the mean time are most welcome.