What would cause data loss during a REPAIR TABLE in MySQL using MyISAM?

Question

The MySQL documentation for REPAIR TABLE states that

It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors.

I'd like to know if there are any other causes of data loss besides file system errors. Has anyone seen this happen in the wild? How likely is it that the repair will lose data if there are no file system errors?

My specific situation is as follows. I have Sun T5120 server running Solaris 10 (SPARC) and am using MySQL 5.1.30. I have a table which uses the MyISAM engine which occasionally becomes corrupted. Some of the times that the table has been corrupted have been due to unexpected power outages on our development system which didn't have an UPS. I'm not sure that all corruption was due to power outages, so there may be some additional reasons that this is happening. Such as the causes listed here.

I'd like to setup an automatic repair solution in case these suspected "additional reasons" happen in the production system, or the production UPS fails. I could set a cron job to run mysqlcheck --auto-recover as is suggested in this answer, or I could modify my process that is inserting into that table to instantly perform the REPAIR TABLE EXTENDED command when it detects corruption. However, both of those approaches use REPAIR TABLE and are, thus, susceptible to data loss.

I could backup the table before attempting the repair, as the documentation suggests, but the table is rather large and I'm not sure I will have space available for the backup. I've done some searching, but haven't found any explanation of why REPAIR TABLE would cause data loss besides what is mentioned in the documentation. So is it likely that the repair will lose data when you have a sound file system, or is the documentation just being cautious?

Answer 1

It is not likely that a REPAIR TABLE will corrupt data, but they can of course not guarantee it. It might be just my bad luck, but I have the experience that MySQL is handling nearly full file system exceptionally poorly. Being short on disk space would be one of the situations I would expect REPAIR TABLE to do strange things.

Answer 2

One of the listed possible causes in the MySQL manual is a bug in the software. You should read the release notes / change history from your version forwards to see if any bugs in the repair table code have been fixed, and also read the release notes for new versions as they come out.

Out of interest, how does your process that inserts data detect corruption ?

An additional measure you can take to protect against data loss is to take backups and enable the replication log. In the event of failure, you can restore from the backup and then use the replication logs to bring your database back to the state it was in when it crashed.

Ultimately, if this is a production system, you really need to sort out a good power supply, and also have a second machine acting as a replica.

Answer 3

I've done a reasonably extensive search of the MySQL fixed bugs and known issues lists from version 5.1 and up and a little bit from previous versions. I've found a few reasons why a table repair may lose data. None of them seem to apply to my particular situation, so I probably don't need to worry too much. Hopefully this will be useful information for someone else.

---

Bug #338

REPAIR table USE_FRM results in data loss if killed

Affects Version: All

This was never fixed because it isn't really a bug. It is an inherent (and completely understandable) system limitation.

---

Bug #10437

After large delete repair table causes data loss!!!

Affects Version: 4.1.11

This is a record of repair truncating a table to zero entries after there have been a lot of deletes in the table. The last activity on this bug was in May 2005, and the problem was not reproducible in 4.1.14, so this may have been solved already.

---

Bug #29980

5.1.20 ate my table

Affects Version: 5.1.20

Seems to only happen in MySQL 5.1.20 and prior versions, and only when you specify the USE_FRM option. Also, the cause of the corruption in that case was an upgrade of MySQL to version 5.1.20, so I don't think this is likely to happen for me.

---

Bug #41385

Crash when attempting to repair a #mysql50# upgraded table with triggers

Affects Version: 5.1.30

This can happen when the name of the table contains "extended characters" and the name of the database was prefixed with #mysql50#, such as happens during an upgrade from MySQL 5.0 to 5.1. Fix was pushed to the 5.1.31 release, so this shouldn't be an issue for me.

---

Post to the MySQL forums that I didn't find in the bug tracker.

Repair on merge table causes data loss

Affects Version: 5.0.37

Apparently, a merge table with multiple sub-tables can lead to data loss if you select from the merge table whilst a repair is in progress on a sub-table.

---

Related Tidbits:

The various discussions and how-tos that I read through all seem to indicate that hardware issues are the main concern for table corruption. Also, as Martin mentioned in a comment on his answer, you can get corruption due to large amounts of concurrent connections.

Another thing to watch out for is free space. I saw some comment that you need enough free space for the size of the table that you are repairing in order for the repair to work. I had assumed it would perform the repair in place, and maybe current versions of MySQL do this, but I may not be able to avoid needing the extra space for backups.