Pyramid, MySQL and Windows: the good, the ugly and the bad

Pyramid, MySQL and Windows: the good (Pyramid), the ugly and the bad. This title does not fit perfectly the main characters of this blog post because some of theme are both ugly and bad, but it doesn't matter.

Just in case you are going to set up a Pyramid project based on MySQL and Windows (sometimes you have to)... there are a couple of things useful to know. But let's start with a chronologically order.

Day 0 - morning

You feel like the brave Steeve McQueen:

Evening - day 0

At the end of the day you'll feel also like Steeve McQueen, but a little more proved:

What happened?

Random problems with normal transactions considered too large, thread disconnected, error log entries like:

• InterfaceError
• OperationalError
• MySQL Server has gone away
• database connection failure
• TimeoutError: QueuePool limit of size ... overflow ... reached, connection timed out, timeout ...
• pyramid process hangs
• etc

The solution

1 - adjust your my.ini options like that:

[mysqld]
max_allowed_packet = 64MB    # adjust this parameter according to your situation
wait_timeout = 28800
interactive_timeout = 2880 

2 - be sure your production.ini file looks like the following one (with Python 2):

sqlalchemy.url = mysql+mysqldb://USER:PASSWORD@127.0.0.1:3306/YOURDB?charset=utf8&use_unicode=0
# For Mysql "MySQL Connection Timeout and SQLAlchemy Connection Pool Recycling" issues see:
# http://docs.sqlalchemy.org/en/latest/core/pooling.html#setting-pool-recycle
# http://douglatornell.ca/blog/2012/01/08/staying-alive/
sqlalchemy.pool_recycle = 3600

3 - you can schedule a restart of your application once a day.

4 - [OPTIONAL, not only Windows related] adjust your SqlAlchemy configuration parameters according to how many threads your server runs. For example (production.ini):

sqlalchemy.pool_size = 20
sqlalchemy.max_overflow = 10

5 - if you are using CherryPy as a Windows service, be sure your  'engine.autoreload.on' option is set to False.


6 - [UPDATE20150401] check your sql-mode if you want to prevent not handled ProxyError exceptions due to input longer than the max size provided on the field model. See https://github.com/Kotti/Kotti/issues/404

 Results

No more exceptions or odd behaviours!

Links

• http://docs.sqlalchemy.org/en/rel_0_9/dialects/mysql.html#unicode-encoding-decoding
• http://docs.sqlalchemy.org/en/rel_0_9/dialects/mysql.html#module-sqlalchemy.dialects.mysql.mysqldb
• http://docs.sqlalchemy.org/en/latest/core/pooling.html#setting-pool-recycle
• http://douglatornell.ca/blog/2012/01/08/staying-alive/