mysql table marked as crashed

Question

I recently created a ajax based instant messaging application and after running for a while I got an error [table] is marked as crashed and should be repaired. How could this have happened and how do I go about preventing it happening again?

Answer 1

This most of times happen on a crash of MySQL Server while it was writing on the table. You should try to run CHECK TABLE <databasename>.<tablename>, which will check the content of the table and ultimately let you know whether it's actually broken or not... Then you may need to run REPAIR TABLE <databasename>.<tablename>. You may also want to read a bit what's on that page.

If the table uses the MyISAM engine, you may as well use the myisamchk tool from the MySQL distribution (in which case I reckon you should refer back to that page).

Answer 2

This is not an answer but an example I have come up with that might help others:

function mysql_check_and_repair($array){
    if( is_array( $array ) ) {
        foreach( $array as $table_name ) {
            $q = mysql_query( "CHECK TABLE `$table_name` QUICK" ) or die( mysql_error() );
            $a = mysql_fetch_array( $q );
            if( $a['Msg_text'] != 'Table is already up to date' && $a['Msg_text'] != 'OK' ) {
                log_error( $a['Msg_text'] );
                mysql_query( "REPAIR TABLE `$table_name`" ) or die( mysql_query() );    
            }
        }
    }else{
        return false;
    }
}

Has anyone any indication on how ofter they think I should perform this ( currently doing before any updates are being made to the database but each request is 2 seconds )