Not getting anything done

As so often seems to happen, events conspired against me to stop me getting any work done today. I checked one of my websites, and it wasn't working. So then I had to try and figure out why, and fix it. It turned out that although the php process was still running, it wasn't responding, so I had to restart PHP and then my websites came back to life.

There was also a config problem with one of the sites where I had managed to upload a copy of the config for that site that I use for local testing, instead of the live site config.

Then I had to try and find out why PHP had stopped responding. Unfortunately the PHP logs and Nginx logs weren't any help there. I found a thread on the Nginx forums where someone else seemed to be having the same problem: php-fpm randomly becomes unresponsive, segfaults and they suggest there that the problem may be due to MySQL.

As far as I'm aware this is the first time I have had php become unresponsive in about 6 months. And last time I tried installing the latest version of MySQL, it was a real pain. I think I would probably need to rebuild PHP as well to get it to use the latest MySQL driver. So although I will get round to upgrading them some day, what I really want is a method to email me when nginx starts generating 502s because php is unresponsive.

First I looked whether Nginx could send an email when it generated a 502. Although Nginx can act as a mailserver, I couldn't see any directives to do with making it send an email though.

I then spent quite a while trying to see if I could write a shell script that connects to the php socket, sends it some data, and then checks if there is a response. This could then be run on a cron job and send an email if there was no response. Unfortunately I couldn't get this to work, all the examples I found seemed to be about tcp sockets rather than unix sockets. Even if I could have made it connect to the socket, I'm not sure my idea would have worked anyway.

Next I looked at using a shell script on a cronjob to parse the last 10 lines of one of the website error log files. The script could then look for unresponsive upstream errors and send an email if there was one. If there were multiple errors, the script could restart php. Googling, it looked like awk and/or sed or maybe grep could be used to do this. I didn't try to write this script though, but sent my hosting provider a ticket to see if they had any better suggestions.

And they did, they suggested using a website monitoring service. So I signed up with mon.itor.us, who are free and looked quite good.

After getting that sorted, I did actually get some work done. I wanted to write a blog post for my photo website with some recent photos. But currently the website only lists photos in order of rating, not by date. So I thought I would write an RSS feed for it. I spent quite a while researching, and it seems that RSS doesn't support a paginated feed, so it only has the last X items. But there is another feed system called ATOM that seems virtually the same as RSS except it does support pagination.

While working on my feed, I found that I needed to format the timestamp stored in the database for use as the last updated date in the feed. The date format required by ATOM includes a timezone, so I wondered what timezone MySQL was using.

So I did some research on that, and how to set MySQL to use UTC. MySQL Server Time Zone Support gives a number of different ways of setting the timezone. It also states that you should use the mysql_tzinfo_to_sql program to fill MySQL's timezone tables. However, I was a bit confused at this as many other websites include info about how to set the timezone in MySQL, but don't mention mysql_tzinfo_to_sql or filling the timezone tables at all.

So I am not sure if all the other websites are giving info that won't work because the timezone tables won't be filled, or the MySQL manual is saying you need to fill the timezone tables when you don't actually need to?