14.5.1. Repairing and Checking CSV Tables

The CSV storage engines supports the CHECK and REPAIR statements to verify and if possible repair a damaged CSV table.

When running the CHECK statement, the CSV file will be checked for validity by looking for the correct field separators, escaped fields (matching or missing quotation marks), the correct number of fields compared to the table definition and the existence of a corresponding CSV metafile. The first invalid row discovered will report an error. Checking a valid table produces output like that shown below:

mysql> check table csvtest;+--------------+-------+----------+----------+| Table        | Op    | Msg_type | Msg_text |+--------------+-------+----------+----------+| test.csvtest | check | status   | OK       |+--------------+-------+----------+----------+1 row in set (0.00 sec)

A check on a corrupted table returns a fault:

mysql> check table csvtest;+--------------+-------+----------+----------+| Table        | Op    | Msg_type | Msg_text |+--------------+-------+----------+----------+| test.csvtest | check | error    | Corrupt  |+--------------+-------+----------+----------+1 row in set (0.01 sec)

If the check fails, the table is marked as crashed (corrupt). Once a table has been marked as corrupt, it is automatically repaired when you next run CHECK or execute a SELECT statement. The corresponding corrupt status and new status will be displayed when running CHECK:

mysql> check table csvtest;+--------------+-------+----------+----------------------------+| Table        | Op    | Msg_type | Msg_text                   |+--------------+-------+----------+----------------------------+| test.csvtest | check | warning  | Table is marked as crashed || test.csvtest | check | status   | OK                         |+--------------+-------+----------+----------------------------+2 rows in set (0.08 sec)

To repair a table you can use REPAIR, this copies as many valid rows from the existing CSV data as possible, and then replaces the existing CSV file with the recovered rows. Any rows beyond the corrupted data are lost.

mysql> repair table csvtest;+--------------+--------+----------+----------+| Table        | Op     | Msg_type | Msg_text |+--------------+--------+----------+----------+| test.csvtest | repair | status   | OK       |+--------------+--------+----------+----------+1 row in set (0.02 sec)