Mysql connector python 3

[Andreas Kuhne]

Hi all,

We went live with a new website, completely rewritten in python 3 and with django 1.6 (upgrade to 1.7 is in progress) on November the 18th. Of course there were some minor issues, but most of them have been resolved and so far everything is working really nice.

HOWEVER... We are having major issues with the mysql-connector-python pip package. We are running the latest stable version (2.0.2), but it seems as if the connector looses it's connection every now and then. This is really irritating as it can happen 10-15 times / day. This means that a lot of our customers are probably having problems. The exception we are getting is:

mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query

We are running on AWS with a RDS instance for the mysql server. This worked perfectly in our previous configuration with python 2.7 and the mysqldb connector. 

Is mysql-connector-python not ready for a production system? Is there anyone else who has had the same problems? 

Is there any other python 3 compatible connector that we can use instead? We are also using south, so it has to be compatible with south as well.

Regards,

Andréas

[François Schiettecatte]

Andreas

Are your sql queries taking a very long time or is this for quick queries. Places to start would be to check that CONN_MAX_AGE in settings.py is less than wait_timeout in my.cnf. You could also check slow query log in MySQL.

I ran into this issue but with a different setup, Django 1.6, Python 2.7 and MySQL-python 1.2.3, but this was specifically when I upgraded from Django 1.5 to 1.6 and the introduction of CONN_MAX_AGE.

François

> --
> 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...@googlegroups.com.
> To post to this group, send email to django...@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/CALXYUbmZYaEhoyt5OCWd_F47a0LbkuQRJ3pjJL3vz98Y6hwZNA%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.

[Andreas Kuhne]

François,

Thanks for your answer. I will check the settings. The problem we have had is that it seems to be on ANY query, both those that take a long time and those that take a short time. But I'll check. 

Being an RDS instance it's not easy for me to get the my.cnf file, but I should be able to see the settings somehow.

Thanks again,

Andréas

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9358A368-FA33-462F-AE58-DB3F86D1A7AC%40gmail.com.

[François Schiettecatte]

Andréas

Django 1.6 introduced persistent connections:

https://docs.djangoproject.com/en/dev/ref/databases/#persistent-database-connections

The problem is that the mysql is dropping the connection while it is still in the pool on the django side (I have run into this with Java connection pools too, its a common gotcha).

You might also want to check this:

https://code.djangoproject.com/ticket/21597

I just found when I was looking at a hack I had to put in because a very long running script was always triggering this issue.

François

> To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CALXYUbmQGv-gDSZiRO6ycuCDCdv6XrJAuuN7GSi3W%3D2NZtcZNA%40mail.gmail.com.

[Michael Pöhn]

On 2014-12-04 20:03, Andreas Kuhne wrote:
> Being an RDS instance it's not easy for me to get the my.cnf file, but
> I should be able to see the settings somehow.

You may use the MySQL Statement: show variables; for listing most
configuration properties, wait_timeout included.

br.
Michael

[Andreas Kuhne]

François,

I hear what you are saying, checked the doc's and as far as I can see I don't have any MAX_CONN_AGE defined (i.e. it should be 0, which means no connection pooling). But what I see here is that there could be an issue even though it should be 0. I'll have to give it a try.

Thanks again,

Andréas

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/C91C47E9-34CB-4B2A-8480-55C335DD8492%40gmail.com.

[Andreas Kuhne]

I now checked the RDS settings and set a CONN_MAX_AGE on the database connection. Previously the connections were being terminated after use, so it probably took a while to get information from the database (so CONN_MAX_AGE was 0). I could see this in the number of connections that are on the server now, which have increased greatly. I will try the configuration this way and see if any change happens.

I am not completely convinced that this is the issue though....

Thanks for all the information so far!

Regards,

Andréas

--
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...@googlegroups.com.
To post to this group, send email to django...@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/5480B425.30209%40fsfe.org.

[Collin Anderson]

Hi Andréas,

If it helps, mysqlclient (python3-compatible fork of mysqldb) is Django's recommended connector/driver.

https://pypi.python.org/pypi/mysqlclient

https://docs.djangoproject.com/en/dev/ref/databases/#mysql-db-api-drivers

Collin

[Andreas Kuhne]

Hi Collin,

Thanks! 

I tried the CONN_MAX_AGE setting and it didn't work. We still get problems, so I'll have to try changing the connector. The only thing left I think.

Regards,

Andréas

--

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...@googlegroups.com.
To post to this group, send email to django...@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/8283294e-2d8d-487c-9bb1-f96c06ef46a5%40googlegroups.com.

[Erik Cederstrand]

> Den 05/12/2014 kl. 10.08 skrev Andreas Kuhne <andrea...@suitopia.com>:
>
> I tried the CONN_MAX_AGE setting and it didn't work. We still get problems, so I'll have to try changing the connector. The only thing left I think.

Do you have access to the MySQL server logs? That might give you an idea of why connections are failing.

Erik

[François Schiettecatte]

Andreas

I have had to use this workaround for scripts that run a long time between database accesses:

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()'


Call checkConnection() before you do a database access, you may need to play around with where you put this. The code I have is crawler which uses the django models to add data to a database, so a lot of time elapses between the start of the script and when the data is actually inserted into the database, so plenty of time for connections to time out.

I spent a while tracking this down a while back but was not able to fully sort out the issue, I came away with the impression that there was a bug somewhere in the connection pool code. If you like I have a little spare time today and can dig into this again.

François

> To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CALXYUbmPrpEwHbrNn29HQ_%2BtDGYgeCdeV9PqyikJLq9C-V2zdQ%40mail.gmail.com.

[Andreas Kuhne]

François,

Thanks for the offer, however I will be trying to change to mysqlclient instead of the mysql-connector-python connector. I can only do this by releasing to our production servers, which will not be until Tuesday. So unfortunately I will have to wait until then.

Regards,

Andréas

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/D2E85288-5BD9-4C78-92BC-474EEF82E6C9%40gmail.com.

[François Schiettecatte]

Hi

Couldn't resist and did a quick test. The original tests I did were with MySQLdb, and I just reran the tests with MySQL Connector/Python and mysqlclient (assuming that django prioritizes mysqlclient over MySQLdb if both are installed).

If I make a request, then sleep for more than the mysql timeout, and then make another request I get an error regardless of what CONN_MAX_AGE is set to, even if it is commented out of the DATABASES dictionary.

I need to ping() the connection not to get an error. This suggests that this is django related and not in the driver.

That being said I am very curious how your test turns out.

François

> To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CALXYUbkMmrGhTYqdrzUTTozR7XxJWtf%2BVa4ErFWetPj4CxH3SA%40mail.gmail.com.

[Andreas Kuhne]

François,

Merci!

That is really good info! I will have to check that as well.. Problem is it's hard to count all of the places where I would need to add a ping, but I guess that'll have to be my step 2 if just changing the connector doesn't work.

Regards,

Andréas

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/15818388-6D37-4760-A216-622EB0A68495%40gmail.com.