I'm the Administrator of a Moodle site which had its Users table corrupted and it became unusable.
Luckily, a simple REPAIR TABLE mdl_user
made it work back again. The thing is that I don't know why it actually crashed and made it unusable, and I want to make sure that I'm better prepared next time.
I'm not a thoroughly experienced DBA — I'm just a developer who does a lot of stuff, so please bear with me.
I could just restore a backup, but I guess there are ways to prevent crashes.
Those tables are utf8_general_ci and using MyISAM.
Why does a MySQL table crash? What can I do to prevent that from happening?
Best Answer
It is rather easy for a MyISAM table to crash.
In the header of every MyISAM table is a counter that tracks how many open file handles there are against the table.
If you start up mysql and the number in the header does not match the number of actual file handles against, mysqld treates the table as crashed.
If a simple
REPAIR TABLE mdl_user
makes it work again every time without loss of data, this may indicate that you have a very highly trafficked site that writes tomdl_user
.If dozens of tables require this
REPAIR TABLE
, I would convert all the tables to InnoDB. However, if themdl_user
table is the only table with this problem, there is something you can do (for this example, let say the database ismoodle
);If you want all tables left as MyISAM
STEP 01 : Create a Repair Table Script
STEP 02 : Declare the Repair Script as the startup file
Add this to /etc/my.cnf
STEP 03 : Restart mysql
Every restart of mysql will trigger the Repair Table Script
If you want all tables to become InnoDB
Run this code to make a bulk conversion script of MyISAM tables to InnoDB, and view it
Once you are satisfied with the conversion script's contents, then run it
UPDATE 2012-03-15 14:00 EDT
@Kevin, While using MyISAM, what if running out of Disk Space is your isuue?
Here is something to consider: According to MySQL 5.0 Certification Study Guide,
bulletpoint #11 says the following on Pages 408,409 Section 29.2:
When you run out of disk space, do not just shutdown or kill mysql. The count of the open file handles in any currently used MyISAM will not have been cleared. Thus, the MyISAM table is marked crashed. If you can free up disk space in the data volume with mysqld still running, mysqld will soldier on once disk space is made available.