MySQL: How to Check the Integrity of Tables

CHECK TABLE verifies that the table is intact, but how do I check all the tables in the database?

The PHP script we provided allows you to do this, it works directly on Wordpress, phpBB and flowBB and can be changed for other CMS.

It loads a CMS configuration file to obtain database access variables, but different CMSs use different variable names, and so these names must be assigned to the script's default names, for non-Wordpress CMSs, etc.

How the script works

The script retrieves the list of tables using the SQL SHOW TABLES command:

$hndtable = mysql_query("SHOW TABLES")

Each table uses the CHECK TABLE command:

$status = mysqli_query($link, "CHECK TABLE `$tname` EXTENDED")

It extracts the results data and displays them:

while($messages = mysqli_fetch_assoc($status))
{
   ...
   echo $messages['status'];
   ...
}

Full algorithm:

 $flag = true;
 while(list($tname) = mysql_fetch_row($hndtable)) 
 {
    echo "Checking $tname : ";
    $status = mysqli_query($link, "CHECK TABLE `$tname` EXTENDED");
    if (!$status || mysqli_num_rows($status) <= 0) 
    {
      echo "Can not get status for table $tname.<br>";
      $flag = false;
      continue;
    }
    while($messages = mysqli_fetch_assoc($status)) 
    {
      $r = $messages['Msg_text'];
      echo $messages['status']." ".$messages['Msg_type']." $r<br>";
      if($r != "OK") $flag = false;
    }
}

Code that is supplemented by commands to access the database, which can be found in the source for download...

Exception

Some tables, such as online in flowBB, give the following note:

The storage engine for the table doesn't support check

Because it is a table of type MEMORY or another reason. This is not a fact of deterioration of the table .

Download Script

To use it, load it into the CMS root, for example, in/forum/, if it is a forum, and enter the file name in the browser URL, for example: 

http://www.example.com/forum/check.php

Then delete the script on the site .