I ran into a corrupt table tonight and it drove me into a mild panic (last backup was a week ago). I think it was caused by several writes and updates happening at the same time. Can this, in itself, corrupt a table. Can locking a table before hand help head off table corruption? Is there a standard procedure or routine for locking, updating, unlocking? I certainly don't want to go through this again. Any insights, or shared experiences, will be greatly appreciated. For me, myisamchk -r mytable saved the day.

I seriously doubt that locking your tables is necessary ...

MySQL is very good at keeping track of open cursors and not allowing two people to update at 'exactly' the same time.

Sound like a 'table-fart' to me ...

What kind of appliance does it support ?

>> Sound like a 'table-fart' to me ...

Wouldn't surprise me, but it's the first time in almost a year. I think I'll have to work up a back up system that's better than just table dumps.

>> What kind of appliance does it support ?

You mean, like, what am I doing with it? Just a file sharing thing where the database stores various info on each file. Not large at all, just a few thousand rows, but certainly large enough I don't look forward to recreating it.

Thanks.

Is it storing the actual files as BLOB's ?? or just info ie. cache location,size etc like /export/users/share/file.ext

If you are storing the files in the DB and people are allowed to modify these and save them back, you may want to lock out others while they are modifying ... otherwise a persons changes could be overwritten by another at the same time ...

Still, this would not cause the 'crash' you described.

What version of MySQL is it ?

Can we see the DB structure ?

It's just storing the info. Some of the files are quite large, so why complicate matters. Here's the basic structure.CREATE TABLE files (
ID smallint(5) unsigned NOT NULL auto_increment,
status tinyint(1) unsigned NOT NULL default '1',
dir enum('T1996','T1997','T1998', 'etcetera') default NULL,
name varchar(64) default NULL,
title varchar(64) NOT NULL default 'none',
maker varchar(64) NOT NULL default 'unknown',
date date NOT NULL default '0000-00-00',
type enum('this','that','the other') default NULL,
size smallint(4) unsigned default NULL,
DL smallint(5) unsigned default NULL,
description varchar(255) default NULL,
brief tinyint(1) unsigned default NULL,
IP varchar(16) default NULL,
PRIMARY KEY (ID)
) TYPE=MyISAM ;Version: mysql Ver 11.16 Distrib 3.23.49, for redhat-linux-gnu (i386)

Right now I have a directory full of unidentified files so me and a few others are sorting them out. I made a script that will either delete the duds or move them to a more appropriate place or add them to the database. It was during an add that it messed up. It worked fine previously, so it could just be coincidence it defected at that moment.

Okay, there's something else going on here. I've lost the ability to upload a file for some reason. This no longer works. Gives a zero byte file if I try to upload.

removed normal upload routine

I have a common-lib file that I moved a sub-routine (http://www.webxpertz.net/forums/showthread.php3?s=&threadid=21002) into but that seems to be working properly. I can try and put it all back the way I had it before but that wouldn't explain why things have suddenly stopped working.

Okay, one more update. It seems my /var/ partition is full. I deleted some log files and first impressions show everything appears fine again.

Hello !!

/var full ??

/var/mysql no longer able to function ... thats where your tables are !

I'll bet you've found your problem ....

check out http://www.bb4.com and load big brother on your box to avoid simple issues like this in the future ... its free, works like a charm, is as configurable as you need it and did I mention free ?? Great mailing list, too .. good community feel ... like here ...