Unhandled 500 error citing transaction rollback issue.

[jathanism]

Trying to hit /api/sites/1 and this happened:

2015-04-07 20:00:42,272 ERROR   Uncaught exception GET /api/sites (10.16.16.85)
HTTPServerRequest(protocol='http', host='foobar:5150', method='GET', uri='/api/sites', version='HTTP/1.1', remote_ip='10.16.16.85', headers={'Host': 'foobar:5150', 'Accept-Encoding': 'gzip, deflate, compress', 'Authorization': 'AuthToken jathan@localhost:CENSORED', 'Content-Type': 'application/json', 'Accept': 'application/json', 'User-Agent': 'python-requests/1.2.3 CPython/2.7.7 Linux/3.2.0-67-generic'})
Traceback (most recent call last):
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/tornado/web.py", line 1309, in _execute
    result = self.prepare()
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/nsot/handlers/util.py", line 192, in prepare
    BaseHandler.prepare(self)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/nsot/handlers/util.py", line 70, in prepare
    if not self.current_user:
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/tornado/web.py", line 1022, in current_user
    self._current_user = self.get_current_user()
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/nsot/handlers/util.py", line 156, in get_current_user
    user = models.User.verify_auth_token(email, auth_token)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/nsot/models.py", line 306, in verify_auth_token
    user = User.query().filter_by(email=email).scalar()
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2426, in scalar
    ret = self.one()
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2395, in one
    ret = list(self)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2438, in __iter__
    return self._execute_and_instances(context)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2453, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 893, in _execute_context
    None, None)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 887, in _execute_context
    conn = self._revalidate_connection()
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 242, in _revalidate_connection
    "Can't reconnect until invalid "
StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) u'SELECT users.id AS users_id, users.email AS users_email, users.secret_key AS users_secret_key \nFROM users \nWHERE users.email = %s' [immutabledict({})]
2015-04-07 20:00:42,274 ERROR   500 GET /api/sites (10.16.16.85) 3.77ms

[jathanism]

It looks like the root cause was an uncaught OperationalError:

2015-04-07 19:59:32,551 ERROR   Uncaught exception GET /api/sites/1 (10.16.16.85)
Traceback (most recent call last):
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/tornado/web.py", line 1309, in _ex
ecute
    result = self.prepare()
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/nsot/handlers/util.py", line 192,
in prepare
    BaseHandler.prepare(self)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/nsot/handlers/util.py", line 70, i
n prepare
    if not self.current_user:
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/tornado/web.py", line 1022, in cur
rent_user
    self._current_user = self.get_current_user()
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/nsot/handlers/util.py", line 156,
in get_current_user
    user = models.User.verify_auth_token(email, auth_token)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/nsot/models.py", line 306, in veri
fy_auth_token
    user = User.query().filter_by(email=email).scalar()
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 242
6, in scalar
    ret = self.one()
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 239
5, in one
    ret = list(self)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 243
8, in __iter__
    return self._execute_and_instances(context)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 245
3, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 7
29, in execute
    return meth(self, multiparams, params)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line
322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 8
26, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 9
58, in _execute_context
    context)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1
159, in _handle_dbapi_exception
    exc_info
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 1
99, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 9
51, in _execute_context
    context)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/sqlalchemy/engine/default.py", lin
e 436, in do_execute
    cursor.execute(statement, parameters)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in
execute
    self.errorhandler(self, exc, value)
  File "/srv/nsot/envs/nsot/lib/python2.7/site-packages/MySQLdb/connections.py", line 36,
in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (OperationalError) (2006, 'MySQL server has gone away') 'SELECT users.id
 AS users_id, users.email AS users_email, users.secret_key AS users_secret_key \nFROM user
s \nWHERE users.email = %s' ('jathan@locallhost',)

[jathanism]

It looks like on a long-running server instance where nobody has queried the API for a while, the DB session goes idle and loses the connection to the MySQL server. This is a flaw in the scoped_session implementation and needs to be reconsidered.