The MySQL ARCHIVE storage engine

Ever wondered how you can treat MySQL as an append-only storage? Enter the ARCHIVE storage engine.

I am pretty sure there will be other, more effective and efficient solutions in the market, but if you want to go the simple way I guess this is a pretty solid solution.

Append-only storages make it impossible to update or delete data that has been written to them (think of logs or a ledger), so that, once an entry is in the DB, you can (kind of) confidently access the DB knowing that clients won’t be able to screw the data much. I believe this is a pretty good use-case when you want to enforce some business logic principles at the storage level.

Of course, it seems like there are some quirks with this engine:

One of the current restrictions of Archive tables is that they do not support any indexes, thus necessitating a table scan for any SELECT tasks.
[…] MySQL is examining index support for Archive tables in upcoming releases.
The engine is not ACID compliant.

It is, though, an interesting option for some scenarios: when implementing a ledger for Namshi we bumped into this requirement (have an append-only table) but then decided to work it out at the application level rather than at the storage level, simply because we didn’t find many battle-tested solutions (or a lot of feedback) for RDS, plus the points highlighted by the article on wikipedia felt a bit scary1 at that time.

Notes
  1. And, by the way, there isn’t much on StackOverflow as well (http://stackoverflow.com/questions/612428/pros-and-cons-of-the-mysql-archive-storage-engine and http://stackoverflow.com/questions/3546567/for-a-stats-systems-whats-better-in-mysql-innodb-archive-or-myisam)

Hi there! I recently wrote an ebook on web application security, currently sold on leanpub, the Amazon Kindle store and gumroad.

It contains 160+ pages of content dedicated to securing web applications and improving your security awareness when building web apps, with chapters ranging from explaining how to secure HTTP cookies with the right flags to understanding why it is important to consider joining a bug bounty program.

Feel free to skim through some of the free chapters published on this blog and, if the content seems interesting enough to you, grab a copy on leanpub, the Amazon Kindle store, gumroad or simply checkout right down below!

Buy the Web Application Security ebook for $9.99

In the mood for some more reading?

...or check the archives.