I hope it's not tacky to reply to my own post. :)
This is a script I run daily on our database... Seems to work quite well.
// CHECK FOR CORRUPTED TABLES
// get names of all tables
$query = "SHOW TABLES";
$result = mysql_query($query) or trigger_error("\nMySQL said:
".mysql_error(), E_USER_ERROR);
$tableNames = array();
while($row = mysql_fetch_array($result,MYSQL_NUM)) {
$tableNames[] = $row[0];
}
// check all tables
$query = "CHECK TABLE ".implode(',',$tableNames);
$result = mysql_query($query) or trigger_error("\nMySQL said:
".mysql_error(), E_USER_ERROR);
$messageArray = array();
// if any tables are not 'OK', add to an array.
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
if($row['Msg_text'] != 'OK') {
$messageArray[] = $row;
}
}
if(count($messageArray) > 0) {
$message = "MySQL Status Information :
Table\t\tOp\t\tMsg_type\t\tMsg_text
";
foreach($messageArray as $value) {
$message .= implode("\t",$value)."\n";
}
mail('techsupport@resultsnow.com',"MySQL Table
Maintainence",$message);
}
Now that I look at it, I see this script isn't locking tables before
running the 'check tables' statement either, so maybe I was off-base in
my last post. Sorry, just trying to remember this stuff from a MySQL
training session I went to about a year ago. I run into corrupted
tables so rarely I'm foggy on the details. Still, hope this helps.
regards,
alex
Alex Dean wrote:
> There might not be anything wrong at all. I believe you may often get
> this error if clients are currently using the tables. That's the way
> it was with the older 'myisamchk' program (for MySQL 3.23.xx), because
> it worked directly on the data files rather than going through the
> database server. 'mysqlcheck' (for MySQL 4.0.x+), I think, does go
> through the server, but you still might get this error if you're not
> locking tables before doing the check.
>
> From the mysql client, you can try locking all the tables you want to
> check, querying 'CHECK TABLE tablename', then release your locks.
>
> Anyway... 'REPAIR TABLE tablename' is the way to go if you have this
> problem, but I bet it's probably nothing...
>
> Here's some table maintainence/recover info for 'myisamchk'.
> http://dev.mysql.com/doc/mysql/en/Table_maintenance.html I believe
> there are links there to other recovery topics related to mysql.
>
> regards,
> alex
>
> Brock wrote:
>
>> Look at the mysql docs for the "REPAIR TABLE tablenmae" query. There are
>> some other options you can give it to have it rebuild indexes and such.
>>
>> Sorry, link not handy.
>>
>> --Brock
>>
>> On 2004.10.21.08.59, Miles Beck wrote:
>> | | | WARNING: mysqlcheck has found corrupt tables
>> | | The table is listed and then this is displayed.
>> | | "warning: 13 clients are using or haven't closed the table properly"
>> | | This occurs for 19 tables in a database I have running.
>> | | How do tables get corrupted and what should I do to fix this?
>> | | Thanks
>> | | -------------------------------------------------
>> | FastQ Communications | Providing Innovative Internet Solutions
>> Since 1993
>> | | ---------------------------------------------------
>> | PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
>> | To subscribe, unsubscribe, or to change you mail settings:
>> | http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>> ---------------------------------------------------
>> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
>> To subscribe, unsubscribe, or to change you mail settings:
>> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>>
>>
>>
>
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change you mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>
(text/plain)