Consistently corrupted table.

[Jorge Torralba]

I have created a photo gallery site using mysql on centos. The site runs quite well. However, occasionally, the site freezes while my other sites on the same server keep running. A myisamchk reveals a table as unusable. I have to shut down mysql run a recovery and get going again.

All this table does is keep track of users on line and what they are doing. So basically, every click on the site gets an entry into the table below.

The table is quite small as you can see here:

CREATE TABLE `usersonline` (
`id` int(11) NOT NULL auto_increment,
`ip` varchar(20) NOT NULL default '',
`time` int(11) NOT NULL default '0',
`referer` varchar(128) default NULL,
`userid` int(11) default NULL,
`username` varchar(30) default NULL,
`goinghere` varchar(128) default NULL,
PRIMARY KEY (`id`),
KEY `usersonline_idx1` (`time`)
) ENGINE=MyISAM AUTO_INCREMENT=1154074 DEFAULT CHARSET=latin1;


and here is how the data gets populated and trimmed via php

$onlinetimeout = 180;
$ipaddr = $_SERVER['REMOTE_ADDR'];
$referer = $_SERVER['HTTP_REFERER'];
$goinghere = $_SERVER['REQUEST_URI'];

$query = "INSERT INTO usersonline VALUES( 0, '$ipaddr', unix_timestamp(), '$referer', $userid, '$username', '$goinghere' )";
mysql_query($query, $connectstring);

$deadline = time() - $onlinetimeout;

this part of the code just returns the num rows based on the query below.

$query = "select ip, time, referer, username, userid, goinghere from usersonline where id in ( select max(id) from usersonline group by ip ) AND time > $deadline";

I also have a cron job that delete from this table where the time is < deadline. This cron job runs every 10 minutes to keep the table from growing.

my frustration is that when the site freezes and I run a myisamchk it always tells me that the useronline table is having problem. Any clues what I can do to keep this table sane?

Thanks