MySQL Forums
Forum List  »  General

Consistently corrupted table.
Posted by: Jorge Torralba
Date: September 10, 2010 03:05PM

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,
KEY `usersonline_idx1` (`time`)

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?


Options: ReplyQuote

Written By
Consistently corrupted table.
September 10, 2010 03:05PM
September 12, 2010 12:03PM
September 13, 2010 06:44PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.