Now this blog runs on sql:. I use MySQL as my backend. Before that I tested it locally with SQLite (so easy to install!) and MySQL.
I learned today that MySQL, which is provided by my host (DreamHost), uses MyISAM for all tables. MyISAM doesn’t support transactions. AT home I use InnoDB, which supports transactions. However the hope is not lost — DreamHost mulls over InnoDB support. But as far as I know, they don’t have an implementation schedule yet.
It looks like the problem is solved. Django creates one (1) connection to database and reuses it to handle requests. Multithreaded FastCGI creates threads to handle requests. These threads make requests to MySQL, which gets confused and loses its cool^H^Honnection. This process is random and compounded with multiple web requests at the same time. For example, if Google Web Accelerator decided to prefetch some pages, your web site would be hosed.
I complained about instability of this site in my previous post. Now, after intensive googling, it looks like it may be related to multiple threads or forked processes using the same connection to MySQL. Apparently it can confuse MySQL. Children should create their own connections after spawning.
If Django does reuse connections, it may be possible to fix it. Otherwise, CGI is the way to go. The latter solution would be slow.
I am totally at loss. I am getting weird errors like this:
OperationalError: (2013, 'Lost connection to MySQL server during query') They are pretty much random but fairly frequent. Apparently it may be caused by different MySQL timeouts, which I cannot control. It seems that in order to combat those you have to call connection.ping() periodically to reconnect. I added them to Django’s MySQL handler and … now I am getting these errors during execution of connection.