MySQL : How to check the integrity of the tables

The CHECK TABLE command verifies that a table is not damaged, but how to test all the tables in the database?

The PHP script that we provide can do so, it works directly on Wordpress, phpBB and FluxBB and can be modified for other CMS.

It loads the configuration file of the CMS holding the variables to access the database, but different CMS use different variable names and so these names must be assigned to the default names of the script, for CMS other than Wordpress etc. ...

How the script works

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

$hndtable = mysqli_query($base, "SHOW TABLES")

For each table it applies the CHECK TABLE command: 

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

It retrieves the result data and displays them: 

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

The complete algorithm: 

$flag = true;
while(list($tname) = mysqli_fetch_row($hndtable)) 
{
    echo "Checking $tname : ";
    $status = mysqli_query($base, "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 supplemented by the access at the base, which is available in the source to download ...

Exception

Some table such as online on fluxBB throw the following note:

The storage engine for the table doesn't support check

Because it is a MEMORY table or another reason. The table can not be checked but is not damaged.

Download the script

To use it, upload it to the root of the CMS, for example /forum/ if it is a forum, and enter the file name in the browser's URL bar, example: 

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

Then delete the script on the site.