# Why do MySQL tables crash? How do I prevent it?

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?

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 to mdl_user.

If dozens of tables require this REPAIR TABLE, I would convert all the tables to InnoDB. However, if the mdl_user table is the only table with this problem, there is something you can do (for this example, let say the database is moodle);

# If you want all tables left as MyISAM

## STEP 01 : Create a Repair Table Script

echo "REPAIR TABLE moodle.mdl_user;" > /var/lib/mysql/MoodleStartUp.sql


## STEP 02 : Declare the Repair Script as the startup file

[mysqld]
init-file=/var/lib/mysql/MoodleStartUp.sql


## 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

MYSQL_USER=root
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql ${MYSQL_CONN} -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql less /root/ConvertMyISAMToInnoDB.sql  Once you are satisfied with the conversion script's contents, then run it mysql${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql


# 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:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

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.

• Excellent response. Didn't know the bit of the file handles, and you've already solved how can I prevent (or automatically repair) in case something goes wrong. Any suggestion for safety if the site in fact does a lot of writes to mdl_user (or any other table)? – AeroCross Mar 15 '12 at 16:38
• You should do three things: 1) increase RAM, 2) increase max_connections, 3) switch to InnoDB. – RolandoMySQLDBA Mar 15 '12 at 18:02
• Noob here. Where do I "run" this code? From a file uploaded to the server or from some command line? – UncaughtTypeError Jan 9 '18 at 8:06

I also administer a moodle site on mysql, and the most common cause of table corruption for us is running out of disk space.

• @RolandoMySQLDBA - yeah, disk space is certainly the issue (related to some other processes, and management philosophy in general). Unfortunately, we don't see the problem resolve itself when space is cleared, or it doesn't resolve itself in a timely enough fasion. My response was just to point out that with our moodle/mysql instance, disk space issues can cause table crashes, regardless of what mysql/myisam is supposed to do. – Kevin Mar 15 '12 at 19:49

I found one good line to convert all tables to InnoDB:

mysql -u root -p dbName -e "show table status where Engine='MyISAM';" |
awk 'NR>1 {print "ALTER TABLE "\$1" ENGINE = InnoDB;"}'  |
mysql -u root -p dbName

• The code may be ok for that but it doesn't answer the first part question (Why does a MySQL table crash?). If you mean that it answers the second part (How to prevent it?), add some more explanation, please. – ypercubeᵀᴹ Feb 14 '18 at 10:14
• Also add more details. Will running this convert all tables in a database? Or the whole server? What will happen if a table fails to convert and we get an error? It is possible we end up with some tables converted and some left in MyiSAM? In general, what other things should a DBA consider before running this? – ypercubeᵀᴹ Feb 14 '18 at 10:17