title: Flask-sqlalchemy broken pipe error 32 with mysql and flask-login
body: <p>Broken pipe (error 32) from mysql to flask-sqlalchemy on initial page load (login screen using flask-login), second request works. Site will continue to work for hours, then fail again.</p>
  
  <p>Connection is through mysql+pymysql url</p>
  
  <p>Database is local mysql 5.7 server on a ubuntu 18.04 machine
  Site is hosted through gunicorn</p>
  
  <p>Site is using blueprints.</p>
  
  <pre><code>Flask==1.0.2
  Flask-SQLAlchemy==2.4.0
  Flask-Login==0.4.1
  </code></pre>
  
  <p>site was is built from:
  <a href="https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-i-hello-world" rel="nofollow noreferrer">https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-i-hello-world</a></p>
  
  <p>In config.py
      SQLALCHEMY_POOL_RECYCLE=300</p>
  
  <p>from mysql db:
      wait_timeout = 28800</p>
  
  <p>From /etc/mysql/mysql.conf.d/mysqld.cnf
      max_allowed_packet = 100M</p>
  
  <p>From other threads on here, sqlalchemy is performing the session closure, so I do not have anything in a after_request function.</p>
  
  <p>I have gone to the third page of google with the following searches:</p>
  
  <ul>
  <li>flask-sqlalchemy broken pipe </li>
  <li>mysql sqlalchemy broken pipe </li>
  <li><p>flask-login mysql broken pipe</p>
  
  <p>2019-06-15 10:48:43,061 ERROR: Exception on /user/login [POST] [in /srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py:1761]
   11 Traceback (most recent call last):
   12   File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/connections.py", line 713, in _write_bytes
   13     self._sock.sendall(data)
   14 BrokenPipeError: [Errno 32] Broken pipe</p></li>
  </ul>
  
  <p>Simplified site:</p>
  
  <pre><code>from flask import Flask
  from flask_sqlalchemy import SQLAlchemy
  from flask_login import LoginManager
  
  db = SQLAlchemy()
  login = LoginManager()
  login.login_view = 'user.login'
  
  def create_app(config_class=Config):
      app = Flask(__name__)
      app.config.from_object(config_class)
  
      db.init_app(app)
      login.init_app(app)
  
      from app.main import mod_main
      app.register_blueprint(mod_main, url_prefix='/')
  
      from app.user import mod_user
      app.register_blueprint(mod_user, url_prefix='/user')
  </code></pre>
  
  <p>I had this site on AWS running on a 16.04 VM for 2 years, then I recently migrated to a 18.04 server and upgraded all my flask/python modules.</p>
  
  <p>I have yet to go through each of the module versions to confirm why this behaviour now occurs.</p>
  
  <p>EDIT</p>
  
  <p>Full stack trace (removed email address and hash where 'ABC' and 'XYZ' are)</p>
  
  <pre><code>2019-06-15 10:48:43,061 ERROR: Exception on /user/login [POST] [in /srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py:1761]
  Traceback (most recent call last):
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/connections.py", line 713, in _write_bytes
      self._sock.sendall(data)
  BrokenPipeError: [Errno 32] Broken pipe
  
  During handling of the above exception, another exception occurred:
  
  Traceback (most recent call last):
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
      cursor, statement, parameters, context
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
      cursor.execute(statement, parameters)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
      result = self._query(query)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
      conn.query(q)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/connections.py", line 516, in query
      self._execute_command(COMMAND.COM_QUERY, sql)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/connections.py", line 771, in _execute_command
      self._write_bytes(packet)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/connections.py", line 718, in _write_bytes
      "MySQL server has gone away (%r)" % (e,))
  pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")
  
  The above exception was the direct cause of the following exception:
  
  Traceback (most recent call last):
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
      response = self.full_dispatch_request()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
      rv = self.handle_user_exception(e)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
      reraise(exc_type, exc_value, tb)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
      raise value
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
      rv = self.dispatch_request()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
      return self.view_functions[rule.endpoint](**req.view_args)
    File "/srv/troopnet/app/user/routes.py", line 519, in login
      user = User().authenticate(form.email.data, form.password.data).first()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3215, in first
      ret = list(self[0:1])
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3007, in __getitem__
      return list(res)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3317, in __iter__
      return self._execute_and_instances(context)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3342, in _execute_and_instances
      result = conn.execute(querycontext.statement, self._params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
      return meth(self, multiparams, params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
      return connection._execute_clauseelement(self, multiparams, params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
      distilled_params,
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
      e, statement, parameters, cursor, context
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
      util.raise_from_cause(sqlalchemy_exception, exc_info)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
      reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
      raise value.with_traceback(tb)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
      cursor, statement, parameters, context
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
      cursor.execute(statement, parameters)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
      result = self._query(query)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
      conn.query(q)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/connections.py", line 516, in query
      self._execute_command(COMMAND.COM_QUERY, sql)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/connections.py", line 771, in _execute_command
      self._write_bytes(packet)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/pymysql/connections.py", line 718, in _write_bytes
      "MySQL server has gone away (%r)" % (e,))
  sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")
  [SQL: SELECT users.id AS users_id, users.email AS users_email, users.password AS users_password 
  FROM users 
  WHERE users.email = %(email_1)s AND users.password = %(password_1)s 
   LIMIT %(param_1)s]
  [parameters: {'email_1': 'ABC', 'password_1': 'XYZ', 'param_1': 1}]
  (Background on this error at: http://sqlalche.me/e/e3q8)
  2019-06-15 10:48:47,064 ERROR: Exception on /user/login [POST] [in /srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py:1761]
  Traceback (most recent call last):
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1177, in _execute_context
      conn = self._revalidate_connection()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 463, in _revalidate_connection
      "Can't reconnect until invalid "
  sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back
  
  The above exception was the direct cause of the following exception:
  
  Traceback (most recent call last):
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
      response = self.full_dispatch_request()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
      rv = self.handle_user_exception(e)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
      reraise(exc_type, exc_value, tb)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
      raise value
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
      rv = self.dispatch_request()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
      return self.view_functions[rule.endpoint](**req.view_args)
    File "/srv/troopnet/app/user/routes.py", line 519, in login
      user = User().authenticate(form.email.data, form.password.data).first()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3215, in first
      ret = list(self[0:1])
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3007, in __getitem__
      return list(res)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3317, in __iter__
      return self._execute_and_instances(context)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3342, in _execute_and_instances
      result = conn.execute(querycontext.statement, self._params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
      return meth(self, multiparams, params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
      return connection._execute_clauseelement(self, multiparams, params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
      distilled_params,
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
      e, util.text_type(statement), parameters, None, None
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
      util.raise_from_cause(sqlalchemy_exception, exc_info)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
      reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
      raise value.with_traceback(tb)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1177, in _execute_context
      conn = self._revalidate_connection()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 463, in _revalidate_connection
      "Can't reconnect until invalid "
  sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
  [SQL: SELECT users.id AS users_id, users.email AS users_email, users.password AS users_password
  FROM users 
  WHERE users.email = %(email_1)s AND users.password = %(password_1)s 
   LIMIT %(param_1)s]
  [parameters: [{}]]
  2019-06-15 10:48:50,638 ERROR: Exception on /user/login [POST] [in /srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py:1761]
  Traceback (most recent call last):
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1177, in _execute_context
      conn = self._revalidate_connection()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 463, in _revalidate_connection
      "Can't reconnect until invalid "
  sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back
  
  The above exception was the direct cause of the following exception:
  
  Traceback (most recent call last):
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
      response = self.full_dispatch_request()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
      rv = self.handle_user_exception(e)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
      reraise(exc_type, exc_value, tb)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
      raise value
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
      rv = self.dispatch_request()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
      return self.view_functions[rule.endpoint](**req.view_args)
    File "/srv/troopnet/app/user/routes.py", line 519, in login
      user = User().authenticate(form.email.data, form.password.data).first()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3215, in first
      ret = list(self[0:1])
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3007, in __getitem__
      return list(res)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3317, in __iter__
      return self._execute_and_instances(context)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3342, in _execute_and_instances
      result = conn.execute(querycontext.statement, self._params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
      return meth(self, multiparams, params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
      return connection._execute_clauseelement(self, multiparams, params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
      distilled_params,
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
      e, util.text_type(statement), parameters, None, None
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
      util.raise_from_cause(sqlalchemy_exception, exc_info)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
      reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
      raise value.with_traceback(tb)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1177, in _execute_context
      conn = self._revalidate_connection()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 463, in _revalidate_connection
      "Can't reconnect until invalid "
  sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
  [SQL: SELECT users.id AS users_id, users.email AS users_email, users.password AS users_password
  FROM users 
  WHERE users.email = %(email_1)s AND users.password = %(password_1)s 
   LIMIT %(param_1)s]
  [parameters: [{}]]
  2019-06-15 10:48:56,354 ERROR: Exception on /user/login [POST] [in /srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py:1761]
  Traceback (most recent call last):
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1177, in _execute_context
      conn = self._revalidate_connection()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 463, in _revalidate_connection
      "Can't reconnect until invalid "
  sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back
  
  The above exception was the direct cause of the following exception:
  
  Traceback (most recent call last):
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
      response = self.full_dispatch_request()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
      rv = self.handle_user_exception(e)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
      reraise(exc_type, exc_value, tb)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
      raise value
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
      rv = self.dispatch_request()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
      return self.view_functions[rule.endpoint](**req.view_args)
    File "/srv/troopnet/app/user/routes.py", line 519, in login
      user = User().authenticate(form.email.data, form.password.data).first()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3215, in first
      ret = list(self[0:1])
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3007, in __getitem__
      return list(res)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3317, in __iter__
      return self._execute_and_instances(context)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3342, in _execute_and_instances
      result = conn.execute(querycontext.statement, self._params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
      return meth(self, multiparams, params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
      return connection._execute_clauseelement(self, multiparams, params)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
      distilled_params,
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
      e, util.text_type(statement), parameters, None, None
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
      util.raise_from_cause(sqlalchemy_exception, exc_info)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
      reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
      raise value.with_traceback(tb)
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1177, in _execute_context
      conn = self._revalidate_connection()
    File "/srv/troopnet/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 463, in _revalidate_connection
      "Can't reconnect until invalid "
  sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
  [SQL: SELECT users.id AS users_id, users.email AS users_email, users.password AS users_password
  FROM users 
  WHERE users.email = %(email_1)s AND users.password = %(password_1)s 
   LIMIT %(param_1)s]
  [parameters: [{}]]
  </code></pre>