Does MyISAM run a risk of actually losing data?

Question

I have a series ofMyISAM database tables and was reading a few points about how it's not as good as InnoDB with respect to integrity. Specifically though, the post went on and seemed to suggest there is a possibility you could lose data. I can't find the link of course (typical luck eh?) but I do remember the takeaway suggesting the above.

Is it possible that you could lose actual rows during inserts? My assumption is this is not true, but would be interested in what the community has to say.

Thanks!

Answer 1

MyISAM has zero support for transactions and because of that, it cannot ensure transactional integrity. It has no journal it can use to recover from a crash. It is, in short, brittle and prone to spectacular failure, especially if your system is abruptly terminated in the middle of a big write.

There is a very high possibility you could lose data during a write. InnoDB is different in that the write itself is not actually committed until it is complete, there's a mechanism for preserving database integrity during a modification so that if there's a failure there it can "roll back" to the previous state. MyISAM has no such mechanism and is at the mercy of the filesystem to save it from total corruption.

I'd strongly recommend not using MyISAM for anything critical, or anything at all if you can avoid it. It was useful in the 1990s when memory was at a premium and CPU power was scarce, a typical high-end MySQL server might have had 512MB of memory, two 200MHz processors, and a pair of 9GB disks in RAID1 configuration. Every little bit of performance counted. InnoDB has gotten a lot faster, and the performance penalty for journaling has shrunk dramatically, especially on SSD.

You wouldn't use a filesystem without a journal, so you really shouldn't use a database without one either.

Answer 2

When MySQL crashes in the middle of a query, many naughty things can happen.

• A write that is in progress may or may not finish; you can't know.
• In particular, if it is an update that is modifying multiple rows, perhaps some of the rows are changed, but others are not.
• Even worse is if the update is SET x = x + 1. If you replay it, the values get bumped again.
• Indexes become "corrupt". (This can almost always be repaired, but it is a nuisance.)
• If replication is involved, that adds another avenue for writes to get lost.
• Transactional operations need special care with LOCK TABLE, else debiting one account, then crediting another, can lose (or fabricate) money.

If you are storing only transient data (news stories, blogs, logs, game play, etc), then maybe none of this matters. If you are storing money, it matters big time.

MyISAM is solid enough to do the right thing under normal circumstances. That is, it won't "lose rows" during an insert, unless something else happens at the same time -- crash, disk error, etc.

Both MyISAM and InnoDB can "lose rows" if you fail to check for errors. Data truncation, divide by zero, deadlock, lost connection, etc, can potentially lead, directly or indirectly, to something lost.

Going even farther, when someone says they are protecting their data by having Master and Slave, but putting both of them in the same room, I ask about floods, earthquakes, tornados, etc.

Nothing is 100% safe, but InnoDB, properly used, is a lot safer than MyISAM against data loss and corruption.