Transition Technology: Ticket #101: 27th June 2010 Site Downtime

attachment set

chris — Mon, 28 Jun 2010 08:04:23 GMT

attachment set to transitiontowns.gaiahost.coop-mysql_bytes-day.png

MySQL throughput

hours, totalhours changed

chris — Mon, 28 Jun 2010 13:22:10 GMT

hours changed from 0.0 to 0.5
totalhours changed from 0.0 to 0.5

There are lots of errors in the logs during the downtime, the first error was triggered by the cron run at Sunday, 27 June 2010 - 12:00am:

Table &#039;variable&#039; is marked as crashed and should be repaired query: UPDATE variable SET value = &#039;d:0.06666666666666666574148081281236954964697360992431640625;&#039; WHERE name = &#039;node_cron_comments_scale&#039; in /web/transitionnetwork.org/www/includes/bootstrap.inc on line 523.

https://www.transitionnetwork.org/admin/reports/event/174362

After 4 log entries like the above the errors changed to:

	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#039;) ORDER BY fit DESC LIMIT 0, 1&#039; at line 1 query: SELECT * FROM menu_router WHERE path IN () ORDER BY fit DESC LIMIT 0, 1 in /web/transitionnetwork.org/www/includes/menu.inc on line 315.

https://www.transitionnetwork.org/admin/reports/event/174367

And the above error then appears to have been repeated for every request to the site.

jim — Mon, 28 Jun 2010 15:19:15 GMT

Interesting.... The variables table holds common variables that are used site-wide by Drupal - it shouldn't be super-heavily updated but some variables might get to be quite big strings of JSON.

All cache* tables are wipe-able, and indeed should be wiped after any major DB operation as a matter of course.

The log file errors are consistent with what you say Chris, and what I just said: variables table is requested in its entirety for each request, and often broken data will get into a cache table which just needs wiping or waiting for its next clearance.

Drupal (or indeed any application) using a DB table should not be able to corrupt it in normal use, unless that database has problems, bugs or issues with the underlying hardware...

My questions are:

Is MySQL patched and up to date?
Has this server's physical memory been tested recently? How about the disks?
What do the MySQL and SysLog? logs show? Could /tmp or /var or /etc (or similar) have run out of disk space momentarily?

john — Tue, 29 Jun 2010 09:52:55 GMT

This is a weird one. The previous cron ran without any problems and I re-created the crashed variable table using Chris's dump to see if anything looked amiss (I couldn't see anything). The problem occurred early on in the 12am cron run, the first 4 errors are from subsequent modules that are running their cron processes and realizing that the variable table is corrupt. As it's not happened before or since and I don't think there is anything special running on the cron at that time, we may not see it happening again. Agree though, would check that there is nothing wrong with the space allocation and possibly hardware (unlikely I know) and that there wan't a brief power outage at that time.

chris — Tue, 29 Jun 2010 12:44:38 GMT

My questions are:

Is MySQL patched and up to date?

The server is running:

Server version: 5.1.39-log FreeBSD port: mysql-server-5.1.39

5.1.48 is available: http://www.freebsd.org/cgi/cvsweb.cgi/ports/databases/mysql51-server/

Has this server's physical memory been tested recently? How about the disks?

I don't know.

What do the MySQL and SysLog?? logs show? Could /tmp or /var or /etc (or similar) have run out of disk space momentarily?

No shortage of disk space:

Filesystem            Size    Used   Avail Capacity  Mounted on
/dev/mirror/gm0s1a    129G     19G    100G    16%    /

I'll check the logs next...

chris — Tue, 29 Jun 2010 13:53:41 GMT

MySQL is currently only set to log slow queries (in /var/db/mysql ), perhaps we should change this, and there is nothing in /var/log/messages

Should we consider if some automatic cleaning up of cache tables and checking of the variable table is needed, or should we just wait to see if this happens again and fix it when it does?

Perhaps we should wait to see if the problem happens again after the site has been migrated to the new hardware.

jim — Wed, 30 Jun 2010 13:17:07 GMT

As long as errors are logged, that's fine - no point logging everything.

And I don't think the cache tables should be auto-cleared, there's no point unless a DB operation is done directly on the database, not through Drupal Drupal clears these tables when it needs to, and developers do whenever they've done something that needs it too. For normal sites running normally, clearing the caches just slows things down.

I definitely agree that, apart from MySQL error logging, waiting for new hardware is the best bet.

status changed; resolution set

jim — Thu, 15 Jul 2010 14:42:10 GMT

status changed from new to closed
resolution set to fixed

Random, new server approaches, re-open if it happens again...