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.
Connection is through mysql+pymysql url
Database is local mysql 5.7 server on a ubuntu 18.04 machine Site is hosted through gunicorn
Site is using blueprints.
Flask==1.0.2
Flask-SQLAlchemy==2.4.0
Flask-Login==0.4.1
site was is built from: https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-i-hello-world
In config.py SQLALCHEMY_POOL_RECYCLE=300
from mysql db: wait_timeout = 28800
From /etc/mysql/mysql.conf.d/mysqld.cnf max_allowed_packet = 100M
From other threads on here, sqlalchemy is performing the session closure, so I do not have anything in a after_request function.
I have gone to the third page of google with the following searches:
- flask-sqlalchemy broken pipe
- mysql sqlalchemy broken pipe
flask-login mysql broken pipe
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
Simplified site:
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')
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.
I have yet to go through each of the module versions to confirm why this behaviour now occurs.
EDIT
Full stack trace (removed email address and hash where 'ABC' and 'XYZ' are)
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: [{}]]
