Reparar tablas de MySQL


MySQL fix

Si por alguna razón se llegan a encontrar con un mensaje como el siguiente al consultar una tabla:

mysql> select * from data_values_queued;
ERROR 1194 (HY000): Table 'data_values_queued' is marked as crashed and should be repaired

La forma de ver y reparar el problema es la siguiente.

CHECK TABLE

Primero se procede a verificar la tabla, para determinar el problema:

mysql> check table data_values_queued;
+----------------------------+-------+----------+----------------------------------------------------------+
| Table                      | Op    | Msg_type | Msg_text                                                 |
+----------------------------+-------+----------+----------------------------------------------------------+
| datbas1.data_values_queued | check | warning  | 4 clients are using or haven't closed the table properly |
| datbas1.data_values_queued | check | error    | Size of datafile is: 4200         Should be: 4220        |
| datbas1.data_values_queued | check | error    | Corrupt                                                  |
+----------------------------+-------+----------+----------------------------------------------------------+
3 rows in set (0.05 sec)

Aquí vemos que la tabla no fue cerrada adecuadamente, por ejemplo producto de un apagón. En mi caso descarto el primer mensaje porque el motor que uso es InnoDB, el cual permite concurrencia en las tablas.

REPAIR TABLE

Ahora para reparar la tabla se puede usar la siguiente sentencia:

mysql> repair table data_values_queued ;
+----------------------------+--------+----------+----------------------------------------+
| Table                      | Op     | Msg_type | Msg_text                               |
+----------------------------+--------+----------+----------------------------------------+
| datbas1.data_values_queued | repair | warning  | Number of rows changed from 144 to 143 |
| datbas1.data_values_queued | repair | status   | OK                                     |
+----------------------------+--------+----------+----------------------------------------+
2 rows in set (0.00 sec)

 

Ya con esto habremos reparado la tabla. Veamos si es así:

mysql> select * from data_values_queued;
+---------+--------+----------+---------------------+
| id      | iodbId | value    | timestamp           |
+---------+--------+----------+---------------------+
| 1476194 |    170 | 297      | 2012-01-11 08:40:45 |
| 1476193 |    170 | 296      | 2012-01-11 08:40:17 |
| 1476176 |     71 | 11692    | 2012-01-11 05:06:46 |
| 1476177 |     72 | 12061    | 2012-01-11 05:06:46 |
| 1476178 |     73 | 11820    | 2012-01-11 05:06:46 |
| 1476179 |    107 | 11703    | 2012-01-11 05:06:46 |
...
| 1476258 |    170 | 282      | 2012-01-11 11:07:43 |
+---------+--------+----------+---------------------+
143 rows in set (0.00 sec)

Referencias


,

  1. #1 by Luis Gallardo on 30/08/2013 - 10:01

    @Ben you are welcome my friend. Best regards!

  2. #2 by Ben Dahara on 30/08/2013 - 2:30

    i have that problem
    error 1194
    thanks luis & adam for the solution..

  3. #3 by Luis Gallardo on 19/12/2012 - 9:44

    @adam thanks for the tip. Regards!

  4. #4 by adam on 18/12/2012 - 11:41

    This error message [{ERROR 1194 (HY000) at line 1: Table ” is marked as crashed and should be repaired] indicates the MySQL database tables get corrupted. In case the ‘REPAIR TABLE’ command get failed to repair MySQL table then you should try MySQL Database Recovery Software to repair corrupt MySQL database & files. Stellar Phoenix MySQL Recovery software is very helpful in solving such errors easily.

Los Comentarios están cerrados