Forums

I am so so so so so so so so fed up with this error: "MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')"

I know I'm not the first one to bring this up, but... I've read every other topic that deals with this error, tried EVERYTHING for 2 weeks now, and I can't even get a simple database query to work... I've been working with pythonanywhere for years now. I even got a paid account (not this one), but I think my only option is to go to Heroku :-(

Here is my problem :

I have initialized a database. Everything works fine... I can create tables, insert data, read data, etc. But... whenever the (Flask) website is not used, after 5 minutes, some time-out trigger gets fired (one that I can't change, I tried, I don't have the rights for that) and this is what I'm getting for bloody 2 weeks now:

enter image description here

Then the error log says :

2020-10-14 09:07:37,515: Exception on /admin [GET] Traceback (most recent call last): File "/usr/lib/python3.8/site-packages/flask/app.py", line 2446, in wsgi_app response = self.full_dispatch_request() File "/usr/lib/python3.8/site-packages/flask/app.py", line 1951, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/lib/python3.8/site-packages/flask/app.py", line 1820, in handle_user_exception reraise(exc_type, exc_value, tb) File "/usr/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise raise value File "/usr/lib/python3.8/site-packages/flask/app.py", line 1949, in full_dispatch_request rv = self.dispatch_request() File "/usr/lib/python3.8/site-packages/flask/app.py", line 1935, in dispatch_request return self.view_functionsrule.endpoint File "/home/Exodus2200/mysite/flask_app.py", line 143, in admin users = read_user_data(cur) File "/home/Exodus2200/mysite/database.py", line 76, in read_user_data cur.execute('''SELECT * FROM Users''') File "/usr/lib/python3.8/site-packages/MySQLdb/cursors.py", line 209, in execute res = self._query(query) File "/usr/lib/python3.8/site-packages/MySQLdb/cursors.py", line 315, in _query db.query(q) File "/usr/lib/python3.8/site-packages/MySQLdb/connections.py", line 226, in query _mysql.connection.query(self, query) MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

I've tried EVERYTHING to make this error go away:

  • Closing all connections, then reestablish a new connection before accessing the DB
  • Closing the cursor, then reestablish a new connection before accessing the DB
  • etc. etc.

It's the most frustrating situation I've ever encountered since I started working with Pythonanywhere 5 years ago.

You need to make sure that you close connections when you're done with them (not just the cursor) Also, you should be closing connections after you're done with them, not before you want to use them again. You also need to make sure that you close the connections, even if there was an exception using the connection.

The 5 minute time of non-use to failure suggests to me that you are not actually reopening a new connection, but trying to use an old one that perhaps you created at import time.

Well, thank you for your fast response. I must say Pythonanywhere is pretty good with that.

OK, I'll give it another go. The "close before open" action was a desperate last resort to make sure 'I tried everything'. But I will try to implement the suggestions you are giving me. I will keep you posted.

Thanks !

:-(

New error...

MySQLdb._exceptions.OperationalError: (2006, '')

2020-10-14 12:54:20,503: Exception on / [GET] Traceback (most recent call last): File "/usr/lib/python3.8/site-packages/flask/app.py", line 2446, in wsgi_app response = self.full_dispatch_request() File "/usr/lib/python3.8/site-packages/flask/app.py", line 1951, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/lib/python3.8/site-packages/flask/app.py", line 1820, in handle_user_exception reraise(exc_type, exc_value, tb) File "/usr/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise raise value File "/usr/lib/python3.8/site-packages/flask/app.py", line 1949, in full_dispatch_request rv = self.dispatch_request() File "/usr/lib/python3.8/site-packages/flask/app.py", line 1935, in dispatch_request return self.view_functionsrule.endpoint File "/home/Exodus2200/mysite/flask_app.py", line 22, in home users = cur.execute("SELECT * from Users;") File "/usr/lib/python3.8/site-packages/MySQLdb/cursors.py", line 186, in execute while self.nextset(): File "/usr/lib/python3.8/site-packages/MySQLdb/cursors.py", line 139, in nextset nr = db.next_result() MySQLdb._exceptions.OperationalError: (2006, '')

Are you sure nothing changed on your end ? I've created tons of applications in the past on Pythonanywhere, and I NEVER had this problem before...

Our databases have a 300-second (5-minute) timeout on inactive connections. That means, if you open a connection to the database, and then you don't do anything with it for 5 minutes, then the server will disconnect, and the next time you try to execute a query, it will fail with MySQLdb._exceptions.OperationalError: (2006, '').

So how do I solve that ? And why has this never been a problem in the past ?

You must be keeping db connection open between operations.

2 pieces of advice you give me :

  1. You need to make sure that you close connections when you're done with them (not just the cursor)
  2. You must be keeping db connection open between operations

That seems to be contradicting.

Both options lead to an error message when using the webapp 5 minutes after launch.

  1. leads to : MySQLdb._exceptions.OperationalError: (2006, 'MySQL server has gone away')
  2. leads to : MySQLdb._exceptions.OperationalError: (2006, '')

There's no way to win this...

Never mind, I fixed it !

Changing the interface fixed the problem for me.

So codewise, I changed 'import MySQLdb' to 'from Flask import MySQL'

...then had to rewrite the syntax for connecting, setting the cursor etc. a little.

If you're getting a "MySQL server has gone away" when you're closing connections, then you're not re-opening the connection when you're trying to use it again.

Filip's earlier comment about keeping db connections open was not advice, it was a statement that, if you're getting the error, you are probably keeping connections open.

I'm sure you guys are trying to help. Any comment on me changing the interface, and now my problems are over ?

It sounds like you switched from using unmanaged connections (which you have to close when you're not using them) to using a connection manager, which is looking after the connections for you. It's certainly a better way to do things -- managing your connections yourself is fiddly and complicated, and using an automated system to do that work for you is much easier.

Makes sense. Thanks for bearing with me!