43
As per the MySQL documentation, your error message is raised when the client can’t send a question to the server, most likely because the server itself has closed the connection. In the most common case the server will close an idle connection after a (default) of 8 hours. This is configurable on the server side.
The MySQL documentation gives a number of other possible causes which might be worth looking into to see if they fit your situation.
An alternative to calling connect()
in every function (which might end up needlessly creating new connections) would be to investigate using the ping()
method on the connection object; this tests the connection with the option of attempting an automatic reconnect. I struggled to find some decent documentation for the ping()
method online, but the answer to this question might help.
Note, automatically reconnecting can be dangerous when handling transactions as it appears the reconnect causes an implicit rollback (and appears to be the main reason why autoreconnect is not a feature of the MySQLdb implementation).
56
Sometimes if you see “OperationalError: (2006, ‘MySQL server has gone away’)”, it is because you are issuing a query that is too large. This can happen, for instance, if you’re storing your sessions in MySQL, and you’re trying to put something really big in the session. To fix the problem, you need to increase the value of the max_allowed_packet setting in MySQL.
The default value is 1048576.
So see the current value for the default, run the following SQL:
select @@max_allowed_packet;
To temporarily set a new value, run the following SQL:
set global max_allowed_packet=10485760;
To fix the problem more permanently, create a /etc/my.cnf file with at least the following:
[mysqld]
max_allowed_packet = 16M
After editing /etc/my.cnf, you’ll need to restart MySQL or restart your machine if you don’t know how.
- [Django]-Django: what is the difference (rel & field)
- [Django]-Django Admin linking to related objects
- [Django]-How to do a HTTP DELETE request with Requests library
2
This might be due to DB connections getting copied in your child threads from the main thread. I faced the same error when using python’s multiprocessing library to spawn different processes. The connection objects are copied between processes during forking and it leads to MySQL OperationalErrors when making DB calls in the child thread.
Here’s a good reference to solve this: Django multiprocessing and database connections
- [Django]-Object does not support item assignment error
- [Django]-Django model "doesn't declare an explicit app_label"
- [Django]-Testing email sending in Django
2
For me this was happening in debug mode.
So I tried Persistent connections in debug mode, checkout the link: Django – Documentation – Databases – Persistent connections.
In settings:
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'dbname',
'USER': 'root',
'PASSWORD': 'root',
'HOST': 'localhost',
'PORT': '3306',
'CONN_MAX_AGE': None
},
- [Django]-Invalid http_host header
- [Django]-Users in initial data fixture
- [Django]-Running Django with Gunicorn – Best Practice
1
Check if you are allowed to create mysql connection object in one thread and then use it in another.
If it’s forbidden, use threading.Local for per-thread connections:
class Db(threading.local):
""" thread-local db object """
con = None
def __init__(self, ...options...):
super(Db, self).__init__()
self.con = MySQLdb.connect(...options...)
db1 = Db(...)
def test():
"""safe to run from any thread"""
cursor = db.con.cursor()
cursor.execute(...)
- [Django]-Accessing function as attribute in a Python class
- [Django]-Track the number of "page views" or "hits" of an object?
- [Django]-How to make an Inner Join in django?
1
I’ve been struggling with this issue too. I don’t like the idea of increasing timeout on mysqlserver. Autoreconnect with CONNECTION_MAX_AGE
doesn’t work either as it was mentioned. Unfortunately I ended up with wrapping every method that queries the database like this
def do_db( callback, *arg, **args):
try:
return callback(*arg, **args)
except (OperationalError, InterfaceError) as e: # Connection has gone away, fiter it with message or error code if you could catch another errors
connection.close()
return callback(*arg, **args)
do_db(User.objects.get, id=123) # instead of User.objects.get(id=123)
As you can see I rather prefer catching the exception than pinging the database every time before querying it. Because catching an exception is a rare case. I would expect django to reconnect automatically but they seemed to refused that issue.
- [Django]-How to copy modules from one virtualenv to another
- [Django]-How can I call a custom Django manage.py command directly from a test driver?
- [Django]-Django: How can I identify the calling view from a template?
1
The most common issue regarding such warning, is the fact that your application has reached the wait_timeout
value of MySQL.
I had the same problem with a Flask app.
Here’s how I solved:
$ grep timeout /etc/mysql/mysql.conf.d/mysqld.cnf
# https://support.rackspace.com/how-to/how-to-change-the-mysql-timeout-on-a-server/
# wait = timeout for application session (tdm)
# inteactive = timeout for keyboard session (terminal)
# 7 days = 604800s / 4 hours = 14400s
wait_timeout = 604800
interactive_timeout = 14400
Observation: if you search for the variables via MySQL batch mode, the values will appear as it is. But If you perform SHOW VARIABLES LIKE 'wait%';
or SHOW VARIABLES LIKE 'interactive%';
, the value configured for interactive_timeout
, will appear to both variables, and I don’t know why, but the fact is, that the values configured for each variable at ‘/etc/mysql/mysql.conf.d/mysqld.cnf’, will be respected by MySQL.
- [Django]-In the Django admin interface, is there a way to duplicate an item?
- [Django]-Django – Clean permission table
- [Django]-Having Django serve downloadable files
1
This error is mysterious because MySQL doesn’t report why it disconnects, it just goes away.
It seems there are many causes of this kind of disconnection. One I just found is, if the query string too large, the server will disconnect. This probably relates to the max_allowed_packets
setting.
- [Django]-Django won't refresh staticfiles
- [Django]-Aggregate (and other annotated) fields in Django Rest Framework serializers
- [Django]-Can I use Socket.IO with Django?
1
This error may occur when you try to use the connection after a time-consuming operation that doesn’t go to the database. Since the connection is not used for some time, MySQL timeout is hit and the connection is silently dropped.
You can try calling close_old_connections()
after the time-consuming non-DB operation so that a new connection is opened if the connection is unusable. Beware, do not use close_old_connections()
if you have a transaction.
- [Django]-Set all pages to require login, globally?
- [Django]-How to render menu with one active item with DRY?
- [Django]-Django REST Framework how to specify error code when raising validation error in serializer
0
How old is this code? Django has had databases defined in settings since at least .96. Only other thing I can think of is multi-db support, which changed things a bit, but even that was 1.1 or 1.2.
Even if you need a special DB for certain views, I think you’d probably be better off defining it in settings.
- [Django]-Can I call a view from within another view?
- [Django]-How do Django models work?
- [Django]-Dynamically add field to a form
0
SQLAlchemy now has a great write-up on how you can use pinging to be pessimistic about your connection’s freshness:
http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
From there,
from sqlalchemy import exc
from sqlalchemy import event
from sqlalchemy.pool import Pool
@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except:
# optional - dispose the whole pool
# instead of invalidating one at a time
# connection_proxy._pool.dispose()
# raise DisconnectionError - pool will try
# connecting again up to three times before raising.
raise exc.DisconnectionError()
cursor.close()
And a test to make sure the above works:
from sqlalchemy import create_engine
e = create_engine("mysql://scott:tiger@localhost/test", echo_pool=True)
c1 = e.connect()
c2 = e.connect()
c3 = e.connect()
c1.close()
c2.close()
c3.close()
# pool size is now three.
print "Restart the server"
raw_input()
for i in xrange(10):
c = e.connect()
print c.execute("select 1").fetchall()
c.close()
- [Django]-Python MySQLDB: Get the result of fetchall in a list
- [Django]-Django Forms: if not valid, show form with error message
- [Django]-How do I package a python application to make it pip-installable?
0
I had this problem and did not have the option to change my configuration. I finally figured out that the problem was occurring 49500 records in to my 50000-record loop, because that was the about the time I was trying again (after having tried a long time ago) to hit my second database.
So I changed my code so that every few thousand records, I touched the second database again (with a count() of a very small table), and that fixed it. No doubt “ping” or some other means of touching the database would work, as well.
- [Django]-Django : Testing if the page has redirected to the desired url
- [Django]-Django multiple template inheritance – is this the right style?
- [Django]-Serving Media files during deployment in django 1.8
0
Firstly, You should make sure the MySQL session & global enviroments wait_timeout
and interactive_timeout
values. And secondly Your client should try to reconnect to the server below those enviroments values.
- [Django]-Django import error – no module named django.conf.urls.defaults
- [Django]-Using {% url ??? %} in django templates
- [Django]-Converting Django QuerySet to pandas DataFrame