MySQL Forums
Forum List  »  MyISAM

Re: MyISAM Rollback Equivalent
Posted by: Ingo Strüwing
Date: March 17, 2006 10:40AM

MyISAM cannot rollback anything.

What people do after an error, depends on the error.

If the machine or the operating system crashes, one needs to restore a backup (and redo the binlog if one needs a current version).

If only the MySQL server (mysqld) crashes, a REPAIR TABLE is sufficient in most cases. The data are immediately written to the data file, so REPAIR can construct correct indexes from the data file. This approach fails for mass operations however. They can be half done and MyISAM can by no way decide, which part of the operation was done and which not. And the user cannot either.

If it is an error outside of MySQL and the application wants to roll back, it has to remember what it did so far and needs to undo its operations itself.

If you need to be safe, you need to implement kind of an undo log yourself. Everytime you update a row, you must first read the old value and keep it for later undo. Same with deletes. For inserts, you keep the new value, so that you can delete that row later. If you use AUTO_INCREMENT, you need to select that value after the insert and update your "log" entry.

This is all very uncomfortable, yes unacceptable, I know. If I were you, I would try very hard to make it somehow possible to get at a server, which has InnoDB or BDB.

Upcoming MySQL versions will probably have more transactional storage engines. Initially a rollback feature was planned for MySQL 5.2, but this has been cancelled for now.

Ingo Strüwing, Senior Software Developer - Storage Engines
MySQL AB, www.mysql.com

Options: ReplyQuote


Subject
Views
Written By
Posted
9601
March 16, 2006 10:24AM
Re: MyISAM Rollback Equivalent
4957
March 17, 2006 10:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.