A couple of different problems can occur if your app isn't managing its database connections carefully:
Dealing with OperationalError 1226, User has exceeded the max_user_connections resource¶
Are you having trouble with problems like this: OperationalError: (1226, "User '<username>' has exceeded the 'max_user_connections' resource (current value: X")?
If you're seeing this problem consistently, it means that you have several, simulateneous processes that are all holding on to their database connections and are stuck, refusing to release them. This shouldn't happen in the normal, day-to-day operation of a web application, so it means something is wrong: some part of your code isn't cleaning up its database connections properly.
Make sure you're using a well known ORM (like Django's, or SQLAlchemy), and make sure its options for connection pooling and clean-up are well set.
If you're managing database connections yourself manually, make sure that you
close connections tidily after each use, even if there's an error -- for
example, consider using
try/finally, and put a
connection.close() into the
Dealing with OperationalError 2006, 'MySQL server has gone away'¶
Are you having trouble with problems like this: OperationalError: (2006, 'MySQL server has gone away')?
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.
1. configure your ORM¶
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,
pool_recycle to 280. More info here.
2. handle errors manually¶
If you're not using an ORM, you need to handle errors manually. MySQLdb specifically does not manage the connections and will error if you try to reuse a stale connection. One that has closed or expired. You need to explicitly check for this error case and handle it.
Even if you are using an ORM, under certain circumstances, it won't automatically recycle connections for you (this might happen if you have some long-running task that opens a connection at the beginning for example).
Handling errors manually for MysqlDB:¶
Below is some example code from a stackoverflow answer
import MySQLdb class DB: conn = None def connect(self): self.conn = MySQLdb.connect() def query(self, sql): try: cursor = self.conn.cursor() cursor.execute(sql) except (AttributeError, MySQLdb.OperationalError): self.connect() cursor = self.conn.cursor() cursor.execute(sql) return cursor db = DB() sql = "SELECT * FROM foo" cur = db.query(sql) # wait a long time for the Mysql connection to timeout cur = db.query(sql) # still works
Anticipating errors in Django¶
You're unlikely to see this problem in your web app itself, because django's views manage connections for each request, and requests will time out at the web server end before the database timeout kicks in.
You might see this error in manage.py shell, or in management commands
though. If you have some code that waits a long time in between database queries,
connection.close() will anticipate any problems with the
from django.db import connection make_a_database_query() call_function_that_takes_a_long_time() # if this last takes longer than 5 mins # then the next db function might error so # call connection.close to get a new connection: connection.close() make_another_query()
Dealing with OperationalError 2013, 'Lost connection to MySQL server during query'¶
The MySQL docs suggest this error will usually be to do with a network connection glitch: dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html
However, it may also happen because your query is trying to return too much data
at once (millions of rows?), and it is hitting the 30-second
If you think that might be the case, try limiting the max number of results, or
breaking up your query into several smaller ones.
(this error can be related to the "mysql server has gone away" problem above, so it's worth reading up on that as well).