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 - morningYou feel like the brave Steeve McQueen:
Evening - day 0At 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:
- MySQL Server has gone away
- database connection failure
- TimeoutError: QueuePool limit of size ... overflow ... reached, connection timed out, timeout ...
- pyramid process hangs
1 - adjust your my.ini options like that:
2 - be sure your production.ini file looks like the following one (with Python 2):[mysqld] max_allowed_packet = 64MB # adjust this parameter according to your situation wait_timeout = 28800 interactive_timeout = 2880
sqlalchemy.url = mysql+mysqldb://USER:PASSWORD@127.0.0.1:3306/YOURDB?charset=utf8&use_unicode=03 - you can schedule a restart of your application once a day.
# For Mysql "MySQL Connection Timeout and SQLAlchemy Connection Pool Recycling" issues see:
sqlalchemy.pool_recycle = 3600
4 - [OPTIONAL, not only Windows related] adjust your SqlAlchemy configuration parameters according to how many threads your server runs. For example (production.ini):
'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
No more exceptions or odd behaviours!