Table that crashes (becomes corrupt) quite often

[torb]

Using:

MySQL 5.0.67-community-log

PHP 5.2.6

 

My problem is that one of my tables (called the_table here) crashes quite often. About twice a month it becomes corrupt, sometimes four-five times in two days. In other words, not a nice situation. This started this summer, and I've been running the site with using the exact same files/queries since November last year, so I'm thinking it might be a server issue (maybe the server was updated and I didn't notice as I am remotely hosted). Upon fixing, it usually works. Two times, I've had to remove the table and create it again. It's the only table I have trouble with.

 

I doubt it's the queries themselves that are the problem here, since they worked so nicely before. I consider myself pretty good with queries, but I'm not so well taught when it comes to table structures and optimization.

 

There are three queries that run on every page of my site, for handling who is on line:

 

Delete from db those who haven't been on line in a while: (asterisk for IP.):

DELETE FROM the_table WHERE ((ip = '**.***.***.**' && username = 'xguest123') OR (username = 'USER') OR (time < '1226938770') )

 

Insert current status of the user:

INSERT INTO the_table (username, ip, time, location, invisible) VALUES ('USER', '**.***.***.**', LPAD('1226939370', '15', '0'), 'URL', '0')

 

Make sure the user only occurs once in the table:

SELECT count(username) FROM the_table WHERE ( username = 'USER' )

 

Additionally, a typical view of the threads will also cause the following query:

SELECT a.aid, a.filename, a.filetype, a.filesize, a.downloads, p.*, m.*,w.time FROM posts p LEFT JOIN members m ON m.username=p.author LEFT JOIN attachments a ON a.pid=p.pid LEFT JOIN the_table w ON p.author=w.username WHERE p.fid='FORUM_ID' AND p.tid='THREAD_ID' ORDER BY p.pid ASC LIMIT 0, 40

 

Here's the table structure:

CREATE TABLE IF NOT EXISTS `the_table` (
  `username` varchar(32) NOT NULL default '',
  `ip` varchar(15) NOT NULL default '',
  `time` int(10) NOT NULL default '0',
  `location` varchar(150) NOT NULL default '',
  `invisible` set('1','0') default '0',
  `robotname` varchar(50) NOT NULL default '',
  KEY `username` (`username`(),
  KEY `ip` (`ip`),
  KEY `time` (`time`),
  KEY `invisible` (`invisible`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

 

I heard that the priority-setting of a specific table can be altered, and that changing this might make the table more stable. However, I have no idea how changing priority works! If anybody knows if this might be helpful, please let me know - and if you know of a link or two I'm ever grateful

 

Thanks,

-torb

[fenway]

Well, ensuring the user only occurs once can be accomplished with a UNIQUE index... but nothing like that would cause table corruption.

[torb]

Yeah. What I've suspected as most likely is that the table is corrupted while the mysqld process is killed in the middle of a write. This based on what little I could find on http://dev.mysql.com/doc/refman/5.0/en/corrupted-myisam-tables.html

 

The table is small, 100 rows at most.

All the other tables I use are MyIsam too.

 

I can see from my webhosting stats that I've had _some_ slow queries running in the last two years. Not many, though. About 30, I should think. Would a slow query be able to kill a write to the db?

 

 

[xtopolis]

If you know a specific time when the table has become corrupt, you could possibly check the mysql command logs to see what was run closest to the corruption time?

[torb]

Hmmm... Any idea how to find the command logs? The closest thing I find is Runtime Information in phpMyAdmin, by that doesn't really show any specific queries...

[fenway]

The mysql "error logs" are unlikely to show anything to that effect, but it's worth checking anyway.

[torb]

I've got to admit this is the first time I've been waiting and _hoping_ for the table to crash, so I can check error logs...