MySQL check for crashed table?

MySQLMySQLi Database

If a table is crashed that means your ENGINE is NULL or empty. The syntax is as follows to check for crashed table.

SHOW TABLE STATUS FROM yourDatabaseName;

Let us implement the above syntax to check for crashed table Here, our database name is ‘test3’ with some tables

mysql> show table status from test3;

The following is the output

+------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| bestdateformatdemo           | InnoDB | 10      | Dynamic    | 4    | 4096           | 16384       | 0              | 0 | 0 | 5 | 2019-02-22 22:49:43 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| countdemo                    | InnoDB | 10      | Dynamic    | 4    | 4096           | 16384       | 0              | 0 | 0 | 4 | 2019-02-26 15:21:12 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| deletedemo                   | InnoDB | 10      | Dynamic    | 6    | 2730           | 16384       | 0              | 0 | 0 | NULL | 2019-02-13 19:20:04 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| differentdatetime            | InnoDB | 10      | Dynamic    | 4    | 4096           | 16384       | 0              | 0 | 0 | 5 | 2019-02-14 10:26:28 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| expandedoutputdemo           | InnoDB | 10      | Dynamic    | 4    | 4096           | 16384       | 0              | 0 | 0 | 5 | 2019-02-22 22:43:59 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| fieldlessthan5chars          | InnoDB | 10      | Dynamic    | 8    | 2048           | 16384       | 0              | 0 | 0 | 8 | 2019-02-13 19:03:05 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| lastrecordbeforelastone      | InnoDB | 10      | Dynamic    | 8    | 2048           | 16384       | 0              | 0 | 0 | 10 | 2019-02-14 17:31:59 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| mostrecentdatedemo           | InnoDB | 10      | Dynamic    | 6    | 2730           | 16384       | 0              | 0 | 0 | 6 | 2019-02-14 17:15:53 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| nullcasedemo                 | InnoDB | 10      | Dynamic    | 6    | 2730           | 16384       | 0              | 0 | 0 | NULL | 2019-02-14 17:55:39 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| order                        | InnoDB | 10      | Dynamic    | 8    | 2048           | 16384       | 0              | 0 | 0 | NULL | 2019-02-14 14:23:38 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| orderbydatethentimedemo      | InnoDB | 10      | Dynamic    | 4    | 4096           | 16384       | 0              | 0 | 0 | 5 | 2019-02-14 18:05:36 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| posts                        | InnoDB | 10      | Dynamic    | 0    | 0              | 16384       | 0              | 32768 | 0 | NULL | 2019-02-13 18:19:06 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| productdemo                  | InnoDB | 10      | Dynamic    | 4    | 4096           | 16384       | 0              | 0 | 0 | NULL | 2019-02-13 20:06:36 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| radiansdemo                  | InnoDB | 10      | Dynamic    | 2    | 8192           | 16384       | 0              | 0 | 0 | 3 | 2019-02-14 14:33:24 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| selecttextafterlastslashdemo | InnoDB | 10      | Dynamic    | 4    | 4096           | 16384       | 0              | 0 | 0 | 5 | 2019-02-22 23:07:32 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| siglequotesdemo              | InnoDB | 10      | Dynamic    | 0    | 0              | 16384       | 0              | 0 | 0 | NULL | 2019-02-14 10:41:23 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| storeddemo                   | InnoDB | 10      | Dynamic    | 6    | 2730           | 16384       | 0              | 0 | 0 | 6 | 2019-02-26 15:53:57 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| studentinformation           | InnoDB | 10      | Dynamic    | 0    | 0              | 16384       | 0              | 0 | 0 | NULL | 2019-02-14 17:06:07 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| updatestringdemo             | InnoDB | 10      | Dynamic    | 2    | 8192           | 16384       | 0 | 0 | 0 | 3 | 2019-02-13 18:25:59 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
+------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
19 rows in set (0.71 sec)
raja
Updated on 30-Jul-2019 22:30:25

Advertisements