Guess which box I am in? These are the dangers of developing your own CMS for a blog site. Keeping versions straight between your dev environment, your production environment, and your documentation is almost a full time job in and of itself. MySQL has things called storage engines
which are defined on a per table basis. I don't know much beyond MyISAM and InnoDB being the two largest, most used types. Oracle is switching the default to InnoDB (and has for 5.5.x) and, recently, has taken development of this storage engine very seriously. As such, InnoDB has received the bulk of the updates for 5.6.x, which is currently in release candidate two phase (the official release due out sometime early 2013). Sadly, my webhost is still on 5.1.x which means the above problem is very real, and very annoying.
Right now I am working on the post submission section of my CMS, which is how I get the words in my head to the pages here. Often there can be errors, or just unforeseen issues when talking with a database, so having some sort of easy way to undo a set of changes is ideal. This is what transaction support and rollback are for. The PHP centric MySQLi will happily talk about how easy this
is, but conveniently forgets to mention that this works for InnoDB tables only. In practice, the only way to know that the rollback didn't actually work is to inspect the tables, as the PHP is all smiles either way. So what actually happens with a post submission for my site?
Everything is going well until we get to those four purple rectangles. These are actual operations that interact with the database. If there is a problem, these are the operations that need to be undone (as per the bottom left box). InnoDB makes this easy, just start a transaction (or turn off auto commit), and continue until you find a problem, or are happy with the results. From here you can commit the changes, or finish the transaction. MyISAM on the other hand, is not so easy. Lacking any kind of transactional support, you are forced to keep detailed logs of every change so that you are able to step backwards manually should an error be encountered. This is messy, and just plain uncalled for. So I am stuck with three options:
- Do nothing and wait for MySQL 5.6 (which comes with native InnoDB FULLTEXT support), but this could take years to get installed to my web server. In the meantime I will have to rely on my keystrokes always following the intended path through the program logic (removing the need for a rollback)... Ha! fat chance
- Remove FULLTEXT searching from my site and go back to the old way, or look for good alternatives
- Stick with MyISAM and create a transaction ghost table to keep records of my changes so I can step back manually should the need arise... shudder
Looks like I am probably going to go with option number two...