0

I am facing the myisam tables get Corrupted frequently but I don't know exact root-cause for this problem. However I will do Repair/MyIsamchk --auto-repair whenever the table gets corrupted. Please see the below actions,

mysql> check table tableName;
+--------------------+-------+----------+----------------------------------------------------------+
| Table              | Op    | Msg_type | Msg_text                                                 |
+--------------------+-------+----------+----------------------------------------------------------+
| database.tableName | check | warning  | 2 clients are using or haven't closed the table properly |
| database.tableName | check | warning  | Size of datafile is: 13540475       Should be: 13540450  |
| database.tableName | check | error    | Found 541619 keys of 541618                              |
| database.tableName | check | error    | Corrupt                                                  |
+--------------------+-------+----------+----------------------------------------------------------+
4 rows in set (3.83 sec)

mysql> repair table tableName;
+--------------------+--------+----------+----------------------------------------------+
| Table              | Op     | Msg_type | Msg_text                                     |
+--------------------+--------+----------+----------------------------------------------+
| database.tableName | repair | warning  | Number of rows changed from 541618 to 541619 |
| database.tableName | repair | status   | OK                                           |
+--------------------+--------+----------+----------------------------------------------+
2 rows in set (15.28 sec)

Server configurations are,

  • CentOS - 64 Bit (Linux - ext3)
  • RAM - 2GB
  • Hard disk - 79GB (Please see the disk free space details below)
Shell# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       79G   57G   18G  76% /
tmpfs          1000M     0 1000M   0% /dev/shm

There will be free space is available on the Hard disk but why the table gets corrupted? and also kindly provide the solution to prevent this problem?

Thanks in advance.

0

MyISAM indexes tend to get corrupted when the server crashes (eg, power failure) or is otherwise rudely terminated (kill -9). You did the 'right' thing by running CHECK and REPAIR.

The datafile size is a bit worrisome; that is worse than the usual (just index issue). The crash may have happened between "writing" and "flushing", since MyISAM is not careful about syncing.

Changing to InnoDB is the 'right' thing to do. For each of your tables, do

ALTER TABLE table_name ENGINE=InnoDB;

Caution: The disk footprint for your tables will double or triple; check that you have enough disk space.

InnoDB automatically recovers after power failure and virtually any other crash.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.