Hi
I have a little experience with this, and I have posted about this here before.
The "MySQL Connection not available" message will occur when MySQL is dropping
the connection before Django is done with it, ie Django is trying to send
something down a dead connection.
I would look at CONN_MAX_AGE in DATABASES (in settings.py) and wait_timeout in
my.cnf, the first should be lower than the second.
For example I have these settings for a number of projects:
'CONN_MAX_AGE': 3500, # 3500 seconds because
wait_timeout = 3600 in my.cnf
And this for another:
'CONN_MAX_AGE': 50, # 50 seconds because
wait_timeout = 60 in my.cnf
Not setting CONN_MAX_AGE (or setting it to 0) in 1.6 caused the "MySQL
Connection not available" issue, I suspect there is a bug in how Django handles
its pooling but I have not checked. Seeing Sleeping connections on MySQL is
suggestive of that. A Sleeping connection just means it is idle and waiting for
stuff.
I have a number of long running scripts, where more than an hour (3600 seconds)
can elapse between database accesses, and I find I have to ping the connections
to see if they are still alive before I do any database accesses, so I call the
following method to ping the connections, and close off dead connections:
from django.db import connection
def checkConnection():
# Check the connection, close if needed
try:
connection.connection.ping()
# print 'INFO: connection.connection.ping()'
except:
connection.close()
# print 'INFO: connection.close()'
Hope this helps.
https://docs.djangoproject.com/en/1.6/ref/databases/
https://docs.djangoproject.com/en/1.6/ref/settings/#std:setting-CONN_MAX_AGE
Cheers
François
On Jul 4, 2014, at 10:46 AM, cercatrova2 <cercatro...@gmail.com> wrote:
> On 04/07/14 16:11, Zemian Deng wrote:
>> @cercatrova2,
>>
>> Yes, my original problem also was (and still is) with "MySQL Connection not
>> available" after the 8 hours timeout inactivity on webfaction hosting. I
>> have to restart it whenever this happens and then problem will go away. My
>> current workaround is to schedule a dummy crontab to hit my site every hour
>> to keep connection timeout refresh. Not ideal, but better than nothing so
>> far. Thus I am looking for some help here.
>>
>> I really like MySQL, and I have tested my app with it already. It would be a
>> bummer to switch because of this just now. I have tried direct usage of
>> mysql.connection with just open and close a connection, and I do see the
>> process go away properly. This plus the number of process list is increasing
>> when I try simple "mysite" app lead me to think it might be django related.
>> I am not sure. Further testing would be need to verify which end is causing
>> the problem.
>>
>> But good to hear PostgreSQL doesn't have the same issue though.
>>
>>
>>
>> On Fri, Jul 4, 2014 at 1:06 AM, cercatrova2 <cercatro...@gmail.com> wrote:
>> On 04/07/14 05:04, Zemian Deng wrote:
>>> Hi,
>>>
>>> In my django settings.py I have set CONN_MAX_AGE=0 to use with MySQL DB,
>>> which I understood as closing conn after each request. However when I start
>>> a simple "mysite" tutorial with "python manage.py runserver", I see
>>> immediately 3 connections in mysql that will not close but in Sleep mode.
>>> Did I miss something here? How do we ensure these connections will get
>>> closed while the app is running?
>>>
>>> mysql> show full processlist;
>>>
>>> +-----+------+-----------------+------+---------+------+-------+-----------------------+
>>>
>>> | Id | User | Host | db | Command | Time | State | Info
>>> |
>>>
>>> +-----+------+-----------------+------+---------+------+-------+-----------------------+
>>>
>>> | 316 | root | localhost | NULL | Query | 0 | init | show full
>>> processlist |
>>>
>>> | 317 | root | localhost:61695 | test | Sleep | 3 | | NULL
>>> |
>>>
>>> | 318 | root | localhost:61696 | test | Sleep | 3 | | NULL
>>> |
>>>
>>> | 319 | root | localhost:61697 | test | Sleep | 3 | | NULL
>>> |
>>>
>>> +-----+------+-----------------+------+---------+------+-------+-----------------------+
>>>
>>> 4 rows in set (0.00 sec)
>>>
>>>
>>> Thanks
>>>
>>> Zemian
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "Django users" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to django-users+unsubscr...@googlegroups.com.
>>> To post to this group, send email to django-users@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/django-users.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/django-users/33fcf156-42bd-4c07-819f-952a6b214b76%40googlegroups.com.
>>> For more options, visit https://groups.google.com/d/optout.
>>
>> Zemian - I've run into this also (I think), but the problem showed up in a
>> different way. First, after the mysql connection timeout expired, django
>> would throw a "MySQL Connection not available" exception when trying to
>> access the server. Second, it would throw the same exception when trying to
>> access the server after the server had been restarted. This was all with
>> CONN_MAX_AGE=0 which as you say should make django open and close a
>> connection on each request.
>>
>> I didn't have time to research things further, but it seems to me that the
>> issue is with mysql.connector.django i.e. mysql connector/python - it is
>> somehow keeping its connection open client-side and when that connection
>> goes away server-side then it gives up. I tried to bring the problem to the
>> attention of the developer on his blog but he didn't respond. I guess a bug
>> report should be posted instead.
>>
>> Anyway, these problems, coupled with some unsolvable timezone issues when
>> trying to use mezzanine django cms with mysql connector/python, forced me to
>> switch to postgresql, which is working very well so far.
>>
>> Good luck.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to django-users+unsubscr...@googlegroups.com.
>> To post to this group, send email to django-users@googlegroups.com.
>> Visit this group at http://groups.google.com/group/django-users.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-users/53B6363A.8090601%40gmail.com.
>>
>> For more options, visit https://groups.google.com/d/optout.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to django-users+unsubscr...@googlegroups.com.
>> To post to this group, send email to django-users@googlegroups.com.
>> Visit this group at http://groups.google.com/group/django-users.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-users/CAK3t1y24v3x7Op1Gw1QgemyRsW4jCsJA6RvvQzpVJT0hE%3Dy6wQ%40mail.gmail.com.
>> For more options, visit https://groups.google.com/d/optout.
>
> Well, I assume you're using python3 like I am. That's the only reason I tried
> mysql connector/python. If you're using python2 you could use the mysqldb
> adapter built into django which is very solid but isn't python3-compatible
> yet. But in any case all of the python database adapters when used with
> django require a django backend module. For most of the adapters the backend
> seems to be provided by the django project but with mysql connector/python
> it's provided by Oracle. You're right that In normal python usage mysql
> connector/python doesn't have the problem we're discussing (I also verified
> that), so the problem seems to be in just this Oracle-provided backend module
> and in that sense it is django-specific but not the django project's fault. I
> see in the django 1.7 doc that mysql connector/python is mentioned, so
> perhaps the django project will take a closer look at the backend. And
> perhaps mysqldb will be released for python3 soon.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users+unsubscr...@googlegroups.com.
> To post to this group, send email to django-users@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/53B6BE58.7050606%40gmail.com.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/3EA4D489-9AF3-45FF-AE11-62768CCC6298%40gmail.com.
For more options, visit https://groups.google.com/d/optout.
