2013, 'Lost connection to MySQL server during query' : Forums : PythonAnywhere

2013, 'Lost connection to MySQL server during query'

Hello all,

I am getting the following error, relatively frequently, even when there is no particular heavy load on the MySQL database (ie: simply loading the home page):

Exception on /index [GET]
Traceback (most recent call last):
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 747, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/eeb2/.local/lib/python3.10/site-packages/flask/app.py", line 2528, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/eeb2/.local/lib/python3.10/site-packages/flask/app.py", line 1825, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/eeb2/.local/lib/python3.10/site-packages/flask/app.py", line 1823, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/eeb2/.local/lib/python3.10/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/home/eeb2/eeb2/app/routes.py", line 26, in index
    guestcount = Guest.query.count()
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 3140, in count
    self._legacy_from_self(col).enable_eagerloads(False).scalar()
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2833, in scalar
    ret = self.one()
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2806, in one
    return self._iter().one()  # type: ignore
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2855, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2229, in execute
    return self._execute_internal(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2124, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 253, in orm_execute_statement
    result = conn.execute(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
    return meth(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 485, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    return self._exec_single_context(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2325, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/home/eeb2/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 747, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query')
[SQL: SELECT count(*) AS count_1
FROM (SELECT guest.id AS guest_id, guest.name AS guest_name, guest.section AS guest_section, guest.email AS guest_email, guest.registered AS guest_registered, guest.rsvp AS guest_rsvp FROM guest) AS anon_1] (Background on this error at: https://sqlalche.me/e/20/e3q8)

I looked at the different topics here and on the broader web and have ameneded a few of the environment variables as follow, but no luck:

SQLALCHEMY_POOL_RECYCLE = 600
SQLALCHEMY_POOL_TIMEOUT = 600

SQLALCHEMY_TRACK_MODIFICATIONS = False

connect_timeout = 600
net_read_timeout = 600
wait_timeout = 28800
interactive_timeout = 28800

Looking at the link provided in the error message, I see this:

OperationalError

Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc.

This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

The OperationalError is the most common (but not the only) error class used by drivers in the context of the database connection being dropped, or not being able to connect to the database. For tips on how to deal with this, see the section Dealing with Disconnects.

Any ideas on how to stop getting these? I am nearly ready to go to production. :)

eeb2 | 7 posts | March 10, 2023, 9:38 a.m. | permalink

How long does the query take normally?

fjl | 4348 posts | PythonAnywhere staff | March 10, 2023, 5:10 p.m. | permalink

I'd say micro-seconds. In this case, it is a very simple query:

guestcount = Guest.query.count()
usercount = Guest.query.filter_by(registered='yes').count()
rsvpcount = User.query.filter_by(rsvp='yes').count()

There are 2 tables (User and Guest). The User table currently has one user and the Guest table has 170 guests. The query itself is super simple.

This happens periodically, on other pages too. It seems to be when the website is not used.

It looks like some sort of timeout for some reason, but I don't know what would be causing it.

eeb2 | 7 posts | March 10, 2023, 5:23 p.m. | permalink

Here is what support gave me as a solution:

A likely cause of that kind of error is that you're re-using a connection that you created earlier, and haven't used for a while. The connection is timing out on the server side at some time prior to your query, and then when your code tries to re-use it, it gets that error. This help page has more details: https://help.pythonanywhere.com/pages/ManagingDatabaseConnections

(Which would make sense)

I am currently trying it and will update this thread accordingly.

This is the part that pertains to the isse, for future readers:

Some Python frameworks have object relationship managers (ORMs) that manage a pool of database connections for you. If you're using an ORM (like Django's or SQLAlcheny) then you need to configure it to automatically expire/recycle connections at 300 seconds.

Django does this by default. SQLAlchemy needs a little extra help, by setting pool_recycle to 280.

eeb2 | 7 posts | March 10, 2023, 6:10 p.m. | permalink

So, it looks like the right way to go about this is through the following:

app.config['SQLALCHEMY_POOL_RECYCLE'] = 280

But this does not seem to resolve the issue. It looks like SQLALCHEMY_POOL_RECYCLE has been replaced by SQLALCHEMY_ENGINE_OPTIONS.

Example:

SQLALCHEMY_ENGINE_OPTIONS = {
    'pool_recycle': 280,
    'pool_pre_ping': True
}

Still experimenting though. I'll be back. :)

eeb2 | 7 posts | March 10, 2023, 8:25 p.m. | permalink

Okaaaay, this seems to have worked ☝️

Thanks Giles for pointing me in the right direction!

eeb2 | 7 posts | March 10, 2023, 8:45 p.m. | permalink

Glad to help! BTW someone else was asking about the two different ways to specify the pool recycle value, and suggested that we update our docs to reflect the second way to do it; what has happened is that Flask-SQLAlchemy (and, I think, other SQLAlchemy-based tools) have changed their syntax for configuring that value from the old way, with SQLALCHEMY_POOL_RECYCLE, to a new way with SQLALCHEMY_ENGINE_OPTIONS and pool_recycle. I know that we need to update our blog post to reflect that, but if you've seen any other pages on our site using the old system, we'd love to know :-)

giles | 11788 posts | PythonAnywhere staff | March 11, 2023, 4:02 p.m. | permalink

Might be worth adding it here too. ;)

eeb2 | 7 posts | March 11, 2023, 5:10 p.m. | permalink

Thanks! Yes, that page definitely needed an update -- I've improved it, including adding on a reference to the new interface.

giles | 11788 posts | PythonAnywhere staff | March 11, 2023, 7:34 p.m. | permalink

A work of art Giles. Next thing to address is your work-life balance :')

But seriously,thanks for all your help!

eeb2 | 7 posts | March 11, 2023, 7:56 p.m. | permalink

Next thing to address is your work-life balance :')

:-D

Well, someone needs to be on call over the weekend, it's just my turn on the rota...

giles | 11788 posts | PythonAnywhere staff | March 12, 2023, 4:24 p.m. | permalink

Good morning everyone, I want to see if someone could help me with the following error that I am getting, I really don't understand what the error could be. Since the page loads without problems, the detail is that once the "form" is completed it gives me the error 500 internal server error, with the following log error.

2023-11-28 14:11:26,972: Exception on /login [POST]
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2077, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1525, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1523, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1509, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/home/HanmaToji/smartlock/./main.py", line 64, in login
    cursor.execute("SELECT*FROM estudiante WHERE ncontrol=%s AND contraseña=%s",(ncontrol, contraseña),)
  File "/home/HanmaToji/.local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
  File "/home/HanmaToji/.local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/home/HanmaToji/.local/lib/python3.10/site-packages/MySQLdb/connections.py", line 255, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (2013, 'Lost connection to MySQL server during query')

HanmaToji | 3 posts | Nov. 28, 2023, 2:21 p.m. | permalink

How do you manage db connections in your web app? It's a good practice to open the connection whenever you need it and than close it.

pafk | 2973 posts | PythonAnywhere staff | Nov. 28, 2023, 4:21 p.m. | permalink

I do, after each database query or edit, I close the connection.

HanmaToji | 3 posts | Nov. 28, 2023, 5:07 p.m. | permalink

I do, after each database query or edit, I close the connection.

HanmaToji | 3 posts | Nov. 28, 2023, 5:07 p.m. | permalink

That query looks like it's missing spaces. There should be spaces around the "*" in the select statement.

glenn | 9498 posts | PythonAnywhere staff | Nov. 28, 2023, 6:11 p.m. | permalink