MySQL Check and Repair Statement in PHP

Introduction

In this article I describe how to check and repair a table when the server or operating system shuts down unexpectedly. The table in the database might become corrupted or the user of the database might not be able to access the table data. For those situations MySQL provides many of tools for determining which table needs repairs. In addition MySQL provides built-in tools to repair MyISAM tables.

Built-in tools

When it comes to repairing and checking tables, MySQL offers two options:

The MySQL distribution ships with a tool called "myisamchk," designed specifically to check and repair MyISAM tables (the default table type used by MySQL). This tool can scan your databases, identify problems, optimize tables for faster lookup, and optionally repair corrupted tables. The myisamchk tool is invoked from the command line.
MySQL also allows you check and repair tables using SQL commands. The CHECK TABLE, REPAIR TABLE, and OPTIMIZE TABLE commands can be used on a running MySQL server and need to be entered through a MySQL client.

So in this article, I am simply work with CHECK TABLE and REPAIR TABLE statements.

The check table statement is used to check tables, if the check table statement finds no problems with the tables then it will mark the table ok in the Msg_text column. The check table statement also reports if the table is "already update to date", it is not required to be checked.

Syntax

CHECK TABLE table_list option_list

Options for the CHECK TABLE statements:

Option	Description
Extended	It provides the full scan of each row with ensuring that the table is 100% consistent but it requires a substantial amount of time.
Medium	It provides an average scan of each row; this option is the default for MyISAM tables.
Quick	It does a quick scan of the rows.
Fast	It only checks the tables that have not been closed properly.
Changed	It only checks the tables that have been changed since the last check option.
For Upgrade	It checks whether the tables are compatible with the current version of MySQL.

The statements that check a single table

The following is an image of a query that checks a single table.

musql-check-table-statement for-single-table.gif

The statements that check a single table

The following is an image of a query that shows how to check multiple tables.

mysql-check-table-statement for-multiple-table.gif

Example of MySQL check table statement in PHP

In this example the check table statement checks a single table or view using the default option, however if you want to check multiple tables or views, you can separate the name of the tables with commas such as given in the above example.

<?php
$con=mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
print "<h2>MySQL Check Table statement in PHP!</h2>";
$qry=mysql_query("check table employee");
echo "<table border='1'>
<tr>
<th>TableName</th>
<th>Op</th>
<th>MsgType</th>
<th>MsgText</th>
</tr>";
while($row = mysql_fetch_array($qry))
{
echo "<tr>";
echo "<td>" . $row['Table'] . "</td>";
echo "<td>" . $row['Op'] . "</td>";
echo "<td>" . $row['Msg_type'] . "</td>";
echo "<td>" . $row['Msg_text'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>

Output

REPAIR TABLE statement

This statement works much like the CHECK TABLE statement, however it has fewer options, so it is easier to use, in addition the check table statement does not work for InnoDB tables and when you use the REPAIR TABLE statement, the repair operation can sometimes cause the table to lose data.

Syntax

REPAIR TABLE table_list option_list

Options for REPAIR TABLE statements:

Option	Description
QUICK	It performs standard repair that fixes most common problems.
EXTENDED	It performs more extended repair.

Summary

The CHECK TABLE statement works for InnoDB and MyISAM tables and it is also work with MySQL 5.0 and later and the CHECK and REPAIR statement works when the server is running.